Custom date formatting irrespective of OS date format

gautamvbaindur · December 5, 2016, 4:34am

<span style=“color: rgb(36, 39, 41); font-family: Arial, “Helvetica Neue”, Helvetica, sans-serif; font-size: 15px; background-color: rgb(255, 255, 255);”>I am currently using aspose.cells for .Net and need to set a custom format a date based on the current language being used in our app. Issue is, we are setting the format via code below, but if the system(OS) date format is different, our custom formatting goes for a toss and the excel displays the date in the system(OS) format. Is there a workaround for this? Below is the code:

<pre style=“margin-top: 0px; margin-bottom: 1em; padding: 5px; border: 0px; font-size: 13px; width: auto; max-height: 600px; overflow: auto; font-family: Consolas, Menlo, Monaco, “Lucida Console”, “Liberation Mono”, “DejaVu Sans Mono”, “Bitstream Vera Sans Mono”, “Courier New”, monospace, sans-serif; background-color: rgb(239, 240, 241); word-wrap: normal; color: rgb(36, 39, 41);”><code style=“margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, “Lucida Console”, “Liberation Mono”, “DejaVu Sans Mono”, “Bitstream Vera Sans Mono”, “Courier New”, monospace, sans-serif; white-space: inherit;”>var dateStyle = dataWorksheet.Cells.GetCellStyle(startRowNumber, column);

        dateStyle.Custom = CultureInfo.CreateSpecificCulture("en-US").DateTimeFormat.ShortDatePattern;
        var dateRange = dataWorksheet.Cells.CreateRange(startRowNumber, column, dataTable.Rows.Count + 1, 1);

        dateRange.SetStyle(dateStyle);</code></pre></div>

amjad.sahi · December 5, 2016, 5:16am

Hi,

Well, this is what MS Excel displays data (numbers, DateTime values, etc.) into MS Excel, it displays the data based on the local/regional settings of the underlying OS. I am afraid, there is no better way to cope with it. Well, I think you may try to specify the formattings using your desired Custom formatting string (via Style.Custom attribute) when applying style to the cell (having DateTime values) accordingly and independently, see the sample code below.

e.g

Sample code:

var workbook = new Workbook();

Worksheet worksheet = workbook.Worksheets[0];

Cells cells = worksheet.Cells;

Aspose.Cells.Cell cell = cells[“A1”];

cell.PutValue(DateTime.Now);

Style style = cell.GetStyle();

style.Custom = “m/d/yyyy”;

cell.SetStyle(style);

workbook.Save(“e:\test2\out1.xlsx”);

Thank you.

gautamvbaindur · December 5, 2016, 5:41am

This is what we are doing and you can see it in the code that I have attached above. But unfortunately, it does not display it in the same format in the excel.

shakeel.faiz · December 5, 2016, 5:46am

Hi,

Thanks for your posting and using Aspose.Cells.

We are afraid, we need your console application and sample excel files as well as screenshots to look into this issue closely and precisely and we will then help you asap.

Howsoever, please see the following sample code, the source excel file used in this code and the output excel file generated by it, its console output and the screenshot explaining the relationship between source and output file for your reference. It might help you in dealing this issue by yourself.

The code basically copies the style from cell A1 to F1 and F4. In case of F1, it copies entire style of A1 and in case of F4, it only copies the custom date format of cell A1.

C#

Workbook wb = new Workbook("sample.xlsx"); Worksheet ws = wb.Worksheets[0];

//Retrieve the custom date style from cell A1 and apply it to F1 and F4
Cell a1 = ws.Cells[“A1”];
Cell f1 = ws.Cells[“F1”];
Cell f4 = ws.Cells[“F4”];

Style a1s = a1.GetStyle();

//Appy a1 style directly to cell F1, it will change everything as per A1
f1.SetStyle(a1s);

//Appy only the custom date format of cell a1 to F4, it will only change date format
Style f4s = f4.GetStyle();

//Please first check, if it is a built-in format or custom, if Style.Number property returns 0, then it is custom format
if(a1s.Number == 0)
{
f4s.Custom = a1s.Custom;
f4.SetStyle(f4s);

//Print the custom format on console
Console.WriteLine("Custom Date Format: " + f4s.Custom);

}

//Save the output
wb.Save(“output.xlsx”);

Console Output:

Custom Date Format: mmm+dd/mm--mmmm+yyyy