Time in Excel Formats as String and Not Time

Aspose.Cells Product Family
1

I have been trying to get Excel to recognize the Time from a DateTime object. I have been successful in extracting and placing the time in a cell but Excel is recognizing it as a string and not as a time. If I click on the Text to Column button in Excel then it correctly recognizes the value as a time. I have tried numerous formatting options both in my C# code along with formatting in Excel but so far, nothing has enabled Excel to see the time string as an actual time. Has anyone else come across this before? I am using the time data in a stacked bar chart to show start and duration values of different events throughout each day of the month. Currently my code is:

Workbook workbook = new Workbook(“Path to my template XLSX”);
Worksheet dataSheet = workbook.Worksheets[“Data”];

dataSheet.Cells[0, 0].PutValue(MyDateTimeObject) <-- This will give date and time
// dataSheet.Cells[0, 0].PutValue(MyDateTimeObject.ToLongTimeString()); <- this gives me just the time in the format that I need but Excel still sees it as a string even with formatting.

// Style timeStyle = workbook.CreateStyle();
// timeStyle.Custom = “h:mm:ss AM/PM”; <-- I have also tried setting the style number to 19
// StyleFlag flag = new StyleFlag();
// flag.All = true;

// dataSheet.Cells.Columns[0].ApplyStyle(timeStyle, flag);

dataSheet.AutoFitColumns();
workbook.CalculateFormula();
workbook.Save(“D:\Temp\temp.xlsx”, SaveFormat.Xlsx);

Any help is greatly appreciated.

2

Hi,


Thanks for providing us some details.

Well, the values you are putting to the cell (e.g A1) should be of DateTime type, it should not be of object or string type. I think you may try to convert your underlying values to DateTime bofore or while inserting into the cell so you desired DateTime formatting/ style should be applied, see the updated line of code for your reference:
e.g
Sample code:

…
dataSheet.Cells[0, 0].PutValue(Convert.ToDateTime(MyDateTimeObject.ToLongTimeString()));


Hope, this helps a bit.

Thank you.

3

Amjad,

Thank you for your quick reply,

I guess my terminology is off a bit. MyDateTimeObject is of type DateTime already. I tried your sample code. It returned the time that I needed but it also added today’s date to it. I am looking to get just the time value only. The stacked bar chart recognizes the date part even though time formatting has been applied to the cell and only the time is showing in the cell.

Thank you,

4

Hi,


Could you provide us an Excel file that contains your desired (Time) formatting set and your underlying desired chart should be formatted accordingly as per your requirements, you may create your desired chart with formatted data manually in MS Excel. Also provide your current output file by Aspose.Cells APIs, we will check it soon.

Thank you.
5

I have my template that I made to show what I am trying to do complete but I am trying to get my code to format a couple of other cells so that it will work the way that I want it. So far I have been unsuccessful. My hurdle now is trying to get Excel to recognize a TimeSpan as a time value and not a string. I am simply doing the following in my code:

dataSheet.Cells[“C2”].PutValue(MyTimeSpan);

I have attached a copy of my template so that you can see what I am trying to accomplish.

Thank you,

6

Hi,


Thanks for providing us the template file.

Could you also provide the output Excel file with the chart via Aspose.Cells APIs (we need to check what you are getting for the tick labels for value axis), we will evaluate your issue and help you soon.

Thank you.
7

I have attached the report generated from my code using Aspose.Cells. In this report, I have not set the fill property of the Start Time series to no fill so that you can see that data has been plotted. If it was set to no fill as in my example from before, you would not see any data in the chart.

Thank you,

8

I figured it out! In my code, I used the following to get only the time from my DateTime:

dataSheet.Cells[“B1”].Formula = “=TIMEVALUE(”" + MyDateTime.ToLongTimeString() + “”)";

And for my duration I used the same code as above except I replaced MyDateTime.ToLongTimeString() with MyTimeSpan.Duration. Now Excel is seeing the times as time values and the durations as time values.

If anyone else is looking to get only the time value from a DateTime then this will work. If there is a better way to do this, please let me know but for now, this is perfect for what I am trying to do.

Thank you for your support and time on this!

9

Hi,


Good to know that you have figured your issue out. Well, MS Excel by default stores DateTime values as numeric notations. I think your workaround/solution (using TimeValue MS Excel formula/function, etc.) to cope with your issue is fine and you may use it to accomplish your task.

By the way, you may also try to use the relevant Cells.ImportDataTable() overloaded method to import your data with your desired datatime formatting and conversions etc. from the DataTable into the worksheet cells, see the description of one such overload below. Moreover, please see the document/article on importing data from difference datasources for reference here.

But, again, I think if your current solution works for your requirements, you may stick to it.


Cells.ImportDataTable Method (DataTable, Boolean, Int32, Int32, Int32, Int32, Boolean, String, Boolean)

Imports a DataTable into a worksheet.

public int ImportDataTable(
DataTable dataTable,
bool isFieldNameShown,
int firstRow,
int firstColumn,
int rowNumber,
int columnNumber,
bool insertRows,
string dateFormatString,
bool convertStringToNumber
);

Thank you.

10

When I had initially set this code up, I had this in place. Since I ran into issues I decided to abort the data table idea until I came up with a solution. I figured the simpler I make it, the more likely I am to find the problem/solution. Since I have come up with a solution, I will put this code into a data table format and import it like you described.

Thanks,

11

Hi,

Thanks for your posting and using Aspose.Cells.

Please let us know if you still encountering any issue. At the moment, we have closed this thread now. In case, you need some help regarding this issue, please feel free to ask, we will look into it and help you asap.

12

Try adding \ before the colons
timeStyle.Custom = “h\\:mm\\:ss AM/PM”
or
timeStyle.Custom = @“h\:mm\:ss AM/PM”

Or surround the colons with double quotes:
timeStyle.Custom = “h\":\"mm\":\"ss AM/PM”
or
timeStyle.Custom = @“h"":""mm"":""ss AM/PM”

13

@hromkes
Thanks !
We will enhance our documents.