Hi,
We are currently using Aspose.Cells and we have an issue when exporting data.
I have attached a small c# project that reproduce the issue.
This is the scenario:
- Create a simple object called Item, with 2 properties: Date (datetime nullable) and Score (double nullable)
- Get a collection of 3 items (previously populated)
- Write the collection into the worksheet (2 columns (Date, Score) and 3 rows)
- Clicking the Get Excel button, each cell will have a value. Instead clicking the Get Excel (with null) button, the first row will contain NULL value for each column.
- Read related data table (I have used ExportDataTable() method, because columns are strongly typed) and display data types for Date and Score columns
As you can see, in the first case Date column is Datetime and Score column is double. In the second case Date column keeps Datetime type, instead Score column began String…
As we need to export data from the data table to an Access file programmatically, we need to be sure about the column types.
Aspose.Cells seems to have a different behaviour if the column is Datetime or Numeric. Is this behaviour correct? Is there a way to get the correct type from a Numeric column that contains some NULL values?
Thanks.
Best regards,
Massimo Gaspari
Hi,
Well, Generally a column’s data type is determined by the very first value in the source list/cells range, so you are getting String data type for second column in the DataTable. I think it is better that you should create your own DataTable having columns with your desired data types and then export to fill that data table (by using its other overloads). Please see the updated sample code segment (from your source project) that will work for your needs.
Sample code:
// get 3 items
Items items = new Items();
items.Get(withNull);
// write items into workbook
Workbook wb = new Workbook();
Worksheet sheet = wb.Worksheets[0];
sheet.Cells[0, 0].PutValue(“Date”);
sheet.Cells[0, 1].PutValue(“Score”);
int row = 1;
int col = -1;
foreach(Item myItem in items)
{
col = -1;
sheet.Cells[row, ++col].PutValue(myItem.Date);
sheet.Cells[row, ++col].PutValue(myItem.Score);
row++;
}
// apply date style
Style style = new Style();
style.Custom = “yyyy-MM-dd hh:mm:ss”;
StyleFlag flag = new StyleFlag();
flag.All = true;
sheet.Cells.Columns[0].ApplyStyle(style, flag);
// apply score style
style = new Style();
style.Number = 2;
flag = new StyleFlag();
flag.All = true;
sheet.Cells.Columns[1].ApplyStyle(style, flag);
DataTable dt = new DataTable();
dt.Columns.Add(“Date”, typeof(DateTime));
dt.Columns.Add(“Score”, typeof(double));
// get data table
sheet.Cells.ExportDataTable(dt,0, 0, sheet.Cells.MaxRow + 1,true, false);
// write columns type
lblDateType.Text = dt.Columns[0].DataType.ToString(); // date
lblScoreType.Text = dt.Columns[1].DataType.ToString(); // score