Smalldatetime in ImportDataTable


#1

from a sql db I get a simple dataset that include fields of datatype smalldatetime.

Using ImportDataTable formats the smalldatetime (2006-01-29 22:23:00.000) to 38746.93264 in the produced Excel file.

How do I best handle this? (I'd like to display the date)


#2

Please set number format for that column.

This is a reference: http://www.aspose.com/wiki/default.aspx/Aspose.Excel/FormatDate.html .


#3

I was hoping for a more generic method.

The datadable is produced by ad hoc sql putting the smalldatetime colum at various positions.

I couldn't figure out how to set the default number format (49) for the entire sheet or a range of cells?


#4

You don't need to set number format for a whole sheet. You just need to set the style for that column.

For example:

Excel excel = new Excel();
Cells cells = excel.Worksheets[0].Cells;

Style styleObj = excel.Styles[excel.Styles.Add()];
styleObj.Number = 49;
cells.Columns[1].Style = styleObj;

Then you can see Column B is formatted as DateTime.


#5

Thanks. I tried this and it works fine, but doesn't solve my problem.

Some times I'll get a datatable where the last column is of type smalldatetime, other times the smalldatetime field will be positioned as the second last etc.

Therefore I was looking/hoping for a method, that would "detect" that a column/field in a supplied datatable (to ImportDataTable) was of type smalldatetime and ensure the "correct" formatting of the cell/column.

I'm thinking of doing some post/pre formatting work after/before "ImportDataTable" based on the first row of the datatable or of some casting work directly on the sql that produce the datatable.


#6

Please check this ImportDataTable method to auto-format datetime:

public int ImportDataTable(DataTable dataTable, bool isFieldNameShown, int firstRow, byte firstColumn, int rowNumber, int columnNumber, bool insertRows, string numberFormatString)

But this method will degrade the perforamance. So if you find the program is too slow, please use my above sample code to format column based on DataColumn type.


#7

I'm not sure how to use that particular overlaod of ImportDataTable, and the documentation hasn't any samples of usage of the param string numberFormatString.

Did you insert any sample code? (you wrote "please use my above sample code")


#8

The number format string can be any valid string available in MS Excel. In Format Cell window, you can get the format string.

For example,

cells.ImportDataTable(dt, false, 0, 0, dt.Rows.Count, dt.Columns.Count, false, "yyyy-m-d");

However, this method will slow down the program. To make it fast, you can set the number format manually:

cells.ImportDataTable(dt, false, 0, 0, dt.Rows.Count, dt.Columns.Count, false);

Style styleObj = excel.Styles[excel.Styles.Add()];
styleObj.Number = 49;


for(int i = 0; i < dt.Columns.Count; i ++)

{

if(dt.ColumnsIdea [I] is DateTime)

{

cells.ColumnsIdea [I].Style = styleObj;

}

}


#9

I the vb.version below I get "Property 'Style' is 'ReadOnly'."

What am I doing wrong?

Friend Overrides Function Execute() As Excel
Dim dt As DataTable = m_dataTable
Dim ExcelFile As Excel = New Excel
Dim cells As Aspose.Excel.Cells = ExcelFile.Worksheets(0).Cells
If dt.Rows.Count > 0 Then
cells.ImportDataTable(dt, True, 0, 0)
Dim styleobj As Aspose.Excel.Style = ExcelFile.Styles(ExcelFile.Styles.Add())
styleobj.Number = 49
For i As Byte = 0 To dt.Columns.Count
If dt.Columns(i).DataType Is GetType(DateTime) Then
cells.Columns(i).Style = styleobj ' <---- ERROR HERE
End If
Next
End If
Return ExcelFile
End Function


#10

Which version of Aspose.Excel are you using? Please try this attached version.


#11

I was using 3.6.2.0. But 3.6.2.1 didn’t change the problem (see screen shot)


#12

Sorry, my mistake. I will fix this issue within this week.


#13

great... so, I wasn't going cracy ;-)

Are you going to release a hotfix?


#14

Yes. You will get a fix very soon.