Applying a number formatting style to date columns in an XLS or CSV file in .NET

Aspose.Cells Product Family
1

I’m doing some data exporting and I first create a datatable and then go through the columns, determine the datatype and style the date column accordingly.

The reason I’m having to to begin with is because the date is formatted as an integer value when printed on the spreadsheet. So I’m trying a workaround to try to get the date formatted the way I’d prefer to see it. Here’s the code I’m using to perform the action.

Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];

DataTable dataTable = new DataTable();

foreach(DataColumn column in dataTable.Columns)
{
if (column.DataType == typeof(DateTime))
{
worksheet.Cells.Columns[column.Ordinal].Style.Number = 14;
}
}

And I swear it was working orignally, but for some reason, now it’s not. In addition, it’s not throwing an error.

Any ideas?

Thanks,
Scott Slaten

2

Hi Scott Slaten,

The Style of Row/Column is readonly. If you want to change the style of row or column,please use Cells.ApplyRowStyle or Cells.ApplyColumnStyle method.

Please change your codes as the following:

Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];
Style style = workbook.Styles[workbook.Styles.Add()];
style.Number = 14;
StyleFlag styleFlag = new StyleFlag();
styleFlag.NumberFormat = true;
workbook.Worksheets[0].CellsDataTable dataTable = new DataTable();

foreach(DataColumn column in dataTable.Columns)
{
if (column.DataType == typeof(DateTime))
{
worksheet.Cells.ApplyColumnStyle(column.Ordinal,style, styleFlag);

}
}
3

Hi Warren,

That did fix the issue. Thanks a lot for your help.

-Scott Slaten