ImportDataTable and DateTime Formatting

Aspose.Cells Product Family
1

Hi


I’m currently testing out Aspose.Words and Cells and find it to be great products.

But i’m having trouble with getting Date columns correctly formatted.

I’m using the Cells.ImportDataTable method. This imports a couple of DateTime columns, but these are shown as : decimals since the column in Excel is not formatted as datetime.

Is there an easy way to get arround this.

I’m trying to set the column format using a Style object, but really can’t get it working…

It seems like using ApplyColumnStyle only uses the settin gs from the FLag property. The Style is ignorred…

Thanks in advance

Allan Bredahl
2

Hi Allan,


Thank you for your interest in Aspose Products.

Attached is my output file generated through below source code. In my output, column[0] is formatted in date format.

I guess you have already gone through the technical articles on Formatting of Rows and Columns, if not then please visit below link
http://www.aspose.com/documentation/.net-components/aspose.cells-for-.net/formatting-rows-columns.html

Also check the documentation for Number and Date Formats,
http://www.aspose.com/documentation/.net-components/aspose.cells-for-.net/setting-display-formats-of-numbers-dates.html

C# Code:

//Create a Datatable and insert some date type data

System.Data.DataTable dt = new System.Data.DataTable();

dt.Columns.Add(“Date”,typeof(DateTime));

System.Data.DataRow dr = dt.NewRow();

dr[0] = System.DateTime.Now;

dt.Rows.Add(dr);

dr = dt.NewRow();

dr[0] = System.DateTime.Now.ToShortDateString();

dt.Rows.Add(dr);


//initialize workbook

Workbook book = new Workbook();

//Import data from Datatable

book.Worksheets[0].Cells.ImportDataTable(dt, true, “A1”);


//Add a style

int i = book.Styles.Add();

Aspose.Cells.Style style = book.Styles[i];

style.Number = 14; // predefined number for Date type formatting


//Creating StyleFlag

StyleFlag styleFlag = new StyleFlag();

//setting style Flag to apply number type formatting

styleFlag.NumberFormat = true;


//Accessing a column from the Columns collection

Aspose.Cells.Column col = book.Worksheets[0].Cells.Columns[0];


//Assigning the Style object to the Style property of the Column

col.ApplyStyle(style, styleFlag);


//Saving the workbook

book.Save(“C:\temp\out.xls”,SaveFormat.Excel97To2003);



3

Thanks a lot for the reply.


I was on the right path. Just missed the part with setting Flag.NumberFormat, which was not totally logical :slight_smile: