Set DateTime regional numbers' format in Excel worksheet using C#.NET

Aspose.Cells Product Family
1

Hello,
I’m trying to set time format that depend on date time format on machine, opens generated file.
(see attached printscreen of wanted format from excel: ‘TimeFormat.jpg’)
Here is a code I’m running to test time formats. Each line is of the different format listed in built-in number formats:

Workbook wb = new Workbook();

Worksheet worksheet = wb.Worksheets[0];

DateTime date = new DateTime(2017,1,5);

TimeSpan time = new TimeSpan(17,45,33);

Style st = worksheet.Cells[“B5”].GetStyle();

worksheet.Cells[“B5”].Value = date;

st.Number = 14;

worksheet.Cells[“B5”].SetStyle(st,new StyleFlag() { NumberFormat = true });

st = worksheet.Cells[“D5”].GetStyle();

worksheet.Cells[“D5”].Value = time;

st.Number = 19;

worksheet.Cells[“D5”].SetStyle(st, new StyleFlag() { NumberFormat = true });

st = worksheet.Cells[“D6”].GetStyle();

worksheet.Cells[“D6”].Value = time;

st.Number = 20;

worksheet.Cells[“D6”].SetStyle(st, new StyleFlag() { NumberFormat = true });

st = worksheet.Cells[“D7”].GetStyle();

worksheet.Cells[“D7”].Value = time;

st.Number = 21;

worksheet.Cells[“D7”].SetStyle(st, new StyleFlag() { NumberFormat = true });

st = worksheet.Cells[“D8”].GetStyle();

worksheet.Cells[“D8”].Value = time;

st.Number = 22;

worksheet.Cells[“D8”].SetStyle(st, new StyleFlag() { NumberFormat = true });

st = worksheet.Cells[“D9”].GetStyle();

worksheet.Cells[“D9”].Value = time;

st.Number = 45;

worksheet.Cells[“D9”].SetStyle(st, new StyleFlag() { NumberFormat = true });

st = worksheet.Cells[“D10”].GetStyle();

worksheet.Cells[“D10”].Value = time;

st.Number = 46;

worksheet.Cells[“D10”].SetStyle(st, new StyleFlag() { NumberFormat = true });

st = worksheet.Cells[“D11”].GetStyle();

worksheet.Cells[“D11”].Value = time;

st.Number = 47;

worksheet.Cells[“D11”].SetStyle(st, new StyleFlag() { NumberFormat = true });

wb.Save(“out.xlsx”);

The result file is attached as well.
As you see:
1) time fields were set with Custom format at place of Time format.
2) all the fields look the same.

The Date field works perfectly with number = 14.


2

Hi,


Thank you for contacting support. Please convert timespan value to datetime as below:

[.NET, C#]
<span style=“font-family: “Courier New”, Consolas, Courier, monospace; font-size: small; white-space: pre; background-color: rgb(255, 255, 0);”>worksheet.Cells[“D5”].Value = Convert.ToDateTime(time.ToString());

It’ll display fields in the appropriate format. However, we managed to replicate the problem of Custom format of cells. We have logged an improvement under ticket ID CELLSNET-45102. We’ll notify you once it is fixed. We’re sorry for the inconvenience caused.

3

Thank you for the reply.
The idea is to display time in local format, on each machine, opens the xlsx doc.
For that, time format should be set as TIME, and not as Custom Format (see printscreen attachment in original message).
It could be a very nice option, that works with date, btw.

Thanks again.
4
Hi,

Thanks for using Aspose.Cells.

For your issue logged as CELLSNET-45102, please note, some of the formats in the number format category are custom. Please format the cells in MS Excel, save the file, unzip the file and you will find the custom numFmt records in style.xml file.

You will have to set the same custom formats to make your formats in the number format category.