Aspose.Cells TIME format in ImportDataTable

I am iporting some details from datatable


[date] column,[from time] column, [to time] column and [total time] column

Code snippet :

------------------------------------------------------------------------------------------------------------

try
{
DataTable dt1 = new DataTable();
if (rbListUser.SelectedIndex == 0)
{
dt1 = BALEntity.SelectTimesheetExport(“EXPORT”, Convert.ToDateTime(txtUserFrom.Text).ToString(“yyyy/MM/dd”), Convert.ToDateTime(txtUserTo.Text).ToString(“yyyy/MM/dd”), Convert.ToInt32(ViewState[“project_Id”].ToString()), drpUser.SelectedValue);
}
else if (rbListUser.SelectedIndex == 1)
{
dt1 = BALEntity.SelectTimesheetExport(“EXPORT”, Convert.ToDateTime(txtUserDate.Text).ToString(“yyyy/MM/dd”), string.Empty, Convert.ToInt32(ViewState[“project_Id”].ToString()), drpUser.SelectedValue);
}
else if (rbListUser.SelectedIndex == 2)
{
dt1 = BALEntity.SelectTimesheetExport(“EXPORT”, string.Empty, string.Empty, Convert.ToInt32(ViewState[“project_Id”].ToString()), drpUser.SelectedValue);
}
Aspose.Cells.License license = new Aspose.Cells.License();

license.SetLicense(Server.MapPath("~/Aspose.Total.lic"));
Workbook workBook = new Workbook();
Worksheet workSheet = workBook.Worksheets[0];
workSheet.Cells[“A1”].PutValue(“Export Project Details”);
workSheet.Cells[“A3”].PutValue("Project no: “+txtProjectNo.Text);
workSheet.Cells[“A4”].PutValue(“Omschrijving: " + ViewState[“project_Description”].ToString());


workSheet.Cells.ImportDataTable(dt1, true, 6,0,false, false);

int count = dt1.Rows.Count;
for (int i = 1; i < count + 10; i++)
{
Aspose.Cells.Style styleDate = workSheet.Cells[“B” + i].GetStyle();
styleDate.Custom = “dd-mm-yyyy”;

Aspose.Cells.Style styleTotaal = workSheet.Cells[“E” + i].GetStyle();
// styleTotaal.Custom = “h:mm:ss”;
styleTotaal.Number = 20;

Aspose.Cells.Style styleStart = workSheet.Cells[“C” + i].GetStyle();
styleStart.Custom = “h:mm:ss”;

Aspose.Cells.Style styleStop = workSheet.Cells[“D” + i].GetStyle();
styleStop.Custom = “h:mm:ss”;

workSheet.Cells[“B” + i].SetStyle(styleDate);
workSheet.Cells[“E” + i].SetStyle(styleTotaal);
workSheet.Cells[“C” + i].SetStyle(styleStart);
workSheet.Cells[“D” + i].SetStyle(styleStop);

}
workSheet.AutoFitColumns();
workSheet.AutoFitRows();
ViewState[“Filename”] = Server.MapPath(”~/Export/ImportProject” + DateTime.Now.ToString(“ddMMyyyyhhmmss”) + ViewState[“project_Id”].ToString() + “.xlsx”);
workBook.Save(ViewState[“Filename”].ToString(), SaveFormat.Xlsx);
hyperDownload.NavigateUrl = “DownloadFile.ashx?FileName=” + ViewState[“Filename”].ToString();
hyperDownload.Text = “De export is gegenereerd, klik hier om te downloaden”;
string strFilename = “EmpDetails.xls”;
UploadDataTableToExcel(dt1, strFilename);
}
catch (Exception ex)
{
lblExceptionExport.Text = “ExportProjects:” + ex.Message;
}

-------------------------------------------------------------------------------------------------------------


The problem is :

The importing is fine. But I want to sum the [total time] column using SUM(row 1,row10)…thats is not possible…its showing 00:00 as sum.


Any help will be thankful

Hi,


Thanks for providing us some details and sample code.

I have tested your scenario/ case a bit. Well, your issue is due to the fact that your underlying DateTime values are stored as “Text” into the cells when you import data from the data source/ data table, so you cannot apply formatting or formula to those cells. To apply formula to make it work correctly, you first need to convert the DateTime values as DateTime type. I think you may try to change the line of code i.e.,
workSheet.Cells.ImportDataTable(dt1, true, 6, 0, false, false);

to:
workSheet.Cells.ImportDataTable(dt1, true, 6, 0, false, true);//you should put “true” for the last boolean parameter convertStringToNumber so your DateTime values should be converted automatically to make your formulas work fine.

Since your code segment is not complete and I am not sure about some of your objects/ variables used in your code, so I have used may own code to test the scenario. Please refer to it and write/ update your code accordingly for your needs. I have also attached the output Excel file for your reference.
e.g
Sample code:

DataSet ds = new DataSet();
System.Data.DataTable dt = new DataTable(“MyTable”);
dt.Columns.Add(“Total1”, typeof(string));

for (int i = 1; i <= 10; i++)
{
System.Data.DataRow dr = dt.NewRow();
dr[“Total1”] = “00:20:10”;
dt.Rows.Add(dr);
}

ds.Tables.Add(dt);

Workbook workbook = new Workbook();

Worksheet sheet = workbook.Worksheets[0];

sheet.Cells.ImportDataTable(ds.Tables[0], true, 0, 0, false, true);

Aspose.Cells.Style styleTotaal = workbook.CreateStyle();
styleTotaal.Number = 20;
StyleFlag flag1 = new StyleFlag();
flag1.NumberFormat = true;
sheet.Cells.ApplyColumnStyle(0, styleTotaal, flag1);


sheet.Cells[“A11”].Formula = “=SUM(A2:A10)”;

workbook.Save(“e:\test2\outdatatimecells1.xlsx”);


Let us know if you still have any issue.

Thank you.