We're sorry Aspose doesn't work properply without JavaScript enabled.

Free Support Forum - aspose.com

ImportDataTable and datetime

Hi!

I am doing the following (cant send the source code for legal reasons):

I create an ADO.NET DataTable, where all columns are of type string.
I use ImportDataTable (with DateFormatString specified) into an Excel sheet.
Some of the cells have been formatted as Date in the range where the data is loaded.

Now, it looks correct, i.e. the cells that are formatted as dates contain date-values.

However, if I link to one of the date cells, I see something strange:
When the data has first been loaded, the linked cell contains the exact same value. However, if I enter another date in the field, the linked field presents the serial eqvuivalent value of the date.
It seems as if Excel doesn't really "understand" that it is a date unless the end-user enters the date.

So, to summarize:
Field A1 is formatted as DateTime.
Field A2 contains the formula ="A1".

Using ImportDataTable, I insert data into A1. However, the type of the column which is loaded is string.

Now, A1 shows a date (e.g. 2011-01-01). A2 shows the same value (2011-01-01)
If I change the value in A1 to 2011-01-02, A2 shows 40545 (this serial number)

Is this by design or a bug?

/Fredrik

Hi Fredrik,


The reason why you are getting numeric notations in the A2 cell is MS Excel saves dates in numeric notations. I think the best way is your data source should have date type values on the back end rather than string fields having date time values. For your issue, I think you should set Date formatting for your formulated Cell (i.e. A2), see the example code below for your reference:

Sample code:

DataTable dataTable = new DataTable(“Employee”);
dataTable.Columns.Add(new DataColumn(“Joining Date”, typeof(string)));
DateTime currentDate = DateTime.Now;
DataRow dataRow = null;
dataRow = dataTable.NewRow();
dataRow[“Joining Date”] = currentDate.ToString();
dataTable.Rows.Add(dataRow);

Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];
worksheet.Cells.ImportDataTable(dataTable, false, “A1”);

worksheet.Cells[“A2”].Formula = “=A1”;
//Also format the A2 cell to your specified datetime format as a workaround.
Style style = worksheet.Cells[“A2”].GetStyle();
style.Custom = “yyyy-mm-dd”;
worksheet.Cells[“A2”].SetStyle(style);

worksheet.AutoFitColumn(0);

workbook.Save(“e:\test2\outputFile.xlsx”, SaveFormat.Xlsx);



Hi, Amjad!I know how formatting, links etc works.The thing is that, due to the way we dynamically generate our Excel-documents, we can't have code like "SetStyle("yyyy-mm-dd") and the like.To me, the behaviour seems a bit buggy.To see what I mean, have a look at the attached xlsm-file and apply the code below.

Note there are two sheets in the document, Aspose and Macro. The Aspose-sheet will be filled in with data by the code below. The Macro-sheet will be filled in with data by a macro in the document.

You should see a different behaviour in the two sheets - in the Aspose-sheet, the date value doesn't seem to become a "real" date value until the user has edited the cell.

Again, note that we can't format the cell that contains the link to the date cell. This is for reasons out of scope in this message.

class Date
{
public static void DoIt()
{
WorkbookDesigner designer = new WorkbookDesigner();
Workbook workbook = new Workbook(Constants.sourcePath + "Date.xlsm");
designer.Workbook = workbook;

Range range = workbook.Worksheets.GetRangeByName("DATA");
Worksheet worksheet = range.Worksheet;
DataTable dt = CreateValidTable1();
range.Worksheet.Cells.ImportDataTable(dt, true, range.FirstRow, range.FirstColumn, dt.Rows.Count, dt.Columns.Count, false);

string output = Constants.destPath + "Date_result.xlsm";
workbook.Save(output);
Process.Start(output);
}

private static DataTable CreateValidTable1()
{
DataTable dt = new DataTable("MyDataSource");
dt.Columns.Add("Id", typeof(int));
dt.Columns.Add("Value", typeof(string));
dt.Columns.Add("DataType", typeof(string));
dt.Rows.Add(1001, "2011-01-01", "Date");
dt.Rows.Add(1002, "123", "Int");
dt.Rows.Add(1003, "123.456", "Decimal");
// dt.Rows.Add("val2", "val2", 4);
//dt.Rows.Add("val3", "val3", 4);
return dt;
}
}
class Date
{
public static void DoIt()
{
WorkbookDesigner designer = new WorkbookDesigner();
Workbook workbook = new Workbook(Constants.sourcePath + "Date.xlsm");
designer.Workbook = workbook;

Range range = workbook.Worksheets.GetRangeByName("DATA");
Worksheet worksheet = range.Worksheet;
DataTable dt = CreateValidTable1();
range.Worksheet.Cells.ImportDataTable(dt, true, range.FirstRow, range.FirstColumn, dt.Rows.Count, dt.Columns.Count, false);

string output = Constants.destPath + "Date_result.xlsm";
workbook.Save(output);
Process.Start(output);
}

private static DataTable CreateValidTable1()
{
DataTable dt = new DataTable("MyDataSource");
dt.Columns.Add("Id", typeof(int));
dt.Columns.Add("Value", typeof(string));
dt.Columns.Add("DataType", typeof(string));
dt.Rows.Add(1001, "2011-01-01", "Date");
dt.Rows.Add(1002, "123", "Int");
dt.Rows.Add(1003, "123.456", "Decimal");
// dt.Rows.Add("val2", "val2", 4);
//dt.Rows.Add("val3", "val3", 4);
return dt;
}
}

Hi,


Well, I think you may try to use the overloaded version of the method if it works for your need, i.e…,
public int ImportDataTable(DataTable dataTable, bool isFieldNameShown, int firstRow,
int firstColumn, int rowNumber, int columnNumber,
bool insertRows, string dateFormatString,
bool convertStringToNumber)
Put “true” for convertStringToNumber parameter.


Also, It is not a bug, but, I am afraid, we do not support to calculate styles/formatting for formulas (even by explicitly using Workbook.CalculateFormula()), so, we do not apply number formatting for E2 cell by default. I have logged a feature enhancments with an id: CELLSNET-30023. Once we support it, we will let you know.

Thank you.