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

Free Support Forum - aspose.com

DataTime incorrectly formatted when using Cells.ImportDataTable

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));

 

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:

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?

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);