DataTime incorrectly formatted when using Cells.ImportDataTable

Aspose.Cells Product Family
1

I'm fetching a query result into aspose cells when the intent to export an excel sheet with the query result.

However when I use the Cells.ImportDataTable all datetime fields are parsed as decimal and do not show up as a date in the resulting excel sheet when exporting.

Isn't this a bug or am I doing somethin wrong?

This is my C# code:

           DataTable dataTable = Utils.Database.FetchDataTable(connectionString, "SELECT * FROM NEW_ORDERS");

       <span style="color: rgb(43, 145, 175);">Workbook</span> workbook = <span style="color: blue;">new</span> <span style="color: rgb(43, 145, 175);">Workbook</span>(<span style="color: rgb(43, 145, 175);">FileFormatType</span>.Xlsx);
       <span style="color: rgb(43, 145, 175);">Worksheet</span> sheet = workbook.Worksheets[0];

       sheet.Cells.ImportDataTable(dataTable, <span style="color: blue;">true</span>, <span style="color: rgb(163, 21, 21);">"A1"</span>);</pre><pre style="background: white; color: black; font-family: Consolas;">           workbook.Save(<span style="color: blue;">this</span>.Response, <span style="color: rgb(163, 21, 21);">"Report.xlsx"</span>, <span style="color: rgb(43, 145, 175);">ContentDisposition</span>.Attachment, <span style="color: blue;">new</span> <span style="color: rgb(43, 145, 175);">OoxmlSaveOptions</span>(<span style="color: rgb(43, 145, 175);">SaveFormat</span>.Xlsx));
2

Hi,

Thanks for asking the question. You should use ImportDataTable overloaded method.

Please see the code below and the output xlsx file generated by it. Please see the screenshot.

C#


string path = @“F:\Shak-Data-RW\Downloads\Employee.xlsx”;


DataTable dataTable = new DataTable(“Employee”);

dataTable.Columns.Add(new DataColumn(“Employee Name”, typeof(string)));

dataTable.Columns.Add(new DataColumn(“Joining Date”, typeof(DateTime)));


string[] employeNames = { “John”, “Jake”, “Jim”, “Jat”, “Jill” };


DataRow dataRow;


DateTime currentDate = DateTime.Now;


for (int i = 0; i < 5; i++)

{

dataRow = dataTable.NewRow();

dataRow[“Employee Name”] = employeNames[i];

dataRow[“Joining Date”] = currentDate;

dataTable.Rows.Add(dataRow);


currentDate = currentDate.AddMonths(1);

currentDate = currentDate.AddYears(1);

currentDate = currentDate.AddDays(1);

}


Workbook workbook = new Workbook();


Worksheet worksheet = workbook.Worksheets[0];


worksheet.Cells.ImportDataTable(dataTable, true, 0, 0, dataTable.Rows.Count,dataTable.Columns.Count, true, “MMM-dd-yyyy”);

worksheet.AutoFitColumns();

workbook.Save(path, SaveFormat.Xlsx);

Screenshot:
3

Hi this fixed my issue at hand however I really need to differentiate the format depeding on the context/datatype of the field i.e. if it is a date field then I don't want the time shown nor formated and if it is a datetime field I want both date and time formated.

With the solution you supplied I format all datetime fields the same, is there a way to supply a format specifically for every field? ... and if not how to I go about creating the excel sheet cell by cell and row by row to get the data output as wanted?

4

Hi,

Please see the code below if it suits your requirements. Here I have first imported the data and then applied a date format to column B. Please see the output workbook.

C#


string path = @“F:\Shak-Data-RW\Downloads\Employee.xlsx”;


DataTable dataTable = new DataTable(“Employee”);

dataTable.Columns.Add(new DataColumn(“Employee Name”, typeof(string)));

dataTable.Columns.Add(new DataColumn(“Joining Date”, typeof(DateTime)));


string[] employeNames = { “John”, “Jake”, “Jim”, “Jat”, “Jill” };


DataRow dataRow;


DateTime currentDate = DateTime.Now;


for (int i = 0; i < 5; i++)

{

dataRow = dataTable.NewRow();

dataRow[“Employee Name”] = employeNames[i];

dataRow[“Joining Date”] = currentDate;

dataTable.Rows.Add(dataRow);


currentDate = currentDate.AddMonths(1);

currentDate = currentDate.AddYears(1);

currentDate = currentDate.AddDays(1);

}


Workbook workbook = new Workbook();


Worksheet worksheet = workbook.Worksheets[0];


worksheet.Cells.ImportDataTable(dataTable, true, “A1”);


//Apply date format to column B

Style style = workbook.CreateStyle();

style.Custom = “MMM-dd-YYYY”;


StyleFlag flag = new StyleFlag();

flag.All = true;


worksheet.Cells.Columns[1].ApplyStyle(style, flag);


worksheet.AutoFitColumns();

workbook.Save(path, SaveFormat.Xlsx);