Hi,
I'm generating an Excel file using values from a database. For this I use an existing Excel template (format .xls, Office 2003). One of columns I have set as format hh:mm. After putting in values and saving the file, this column does have the right values. However, the column cannot calculate the sum of these rows. This only works after selecting each row manually and hitting enter in the editing field (alignment changes from left to right). See below the code I use, do you know what should be changed in order to make the sum function possible right away?
Dim DuurItem As TimeSpan = MyValue
Worksheet.Cells("I" & Num1).PutValue([String].Format("{0}:{1}", DuurItem.TotalHours.ToString("00"), DuurItem.Minutes.ToString("00")), False)
See attachment for the example file, column A shows how it is generated initially, column B shows the values after I edited each manually.
Kind regards,
Martin de Ruiter
Hi,
Well, in MS Excel the DateTime values are stored in numeric values, so you need to convert the date & time values to numbers before the format should be implemented. You may apply your desired DateTime formatting using Cell.GetStyle() and Cell.SetStyle() methods. Once the date & time values are in proper formatting, your formulas would be calculated fine. See the following sample code, it works fine:
int Num1 = 1;
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];
DateTime value1 = DateTime.Now;
DateTime value2 = DateTime.Now;
TimeSpan DuurItem = value2 - value1;
worksheet.Cells[“I” + Num1].PutValue(String.Format("{0}:{1}", DuurItem.TotalHours.ToString(“02”), DuurItem.Minutes.ToString(“30”)), true);
Style style = worksheet.Cells[“I” + Num1].GetStyle();
style.Custom = “h:mm”;
worksheet.Cells[“I” + Num1].SetStyle(style);
workbook.Save(“e:\test\outputValues.xls”);
Thank you.
Hi,
I tried your sample code, however the same problem remains, the formulas still don't calculate before editing manually, so I guess it's still not outputting numeric values. I used your exact sample code, but it doesn't solve it. Do you know what could be wrong?
Dim Num1 As Integer = 1
Dim workbook As New Workbook()
Dim worksheet As Worksheet = workbook.Worksheets(0)
Dim value1 As DateTime = DateTime.Now
Dim value2 As DateTime = DateTime.Now
Dim DuurItem As TimeSpan = value2 - value1
worksheet.Cells("I" & Num1).PutValue([String].Format("{0}:{1}", DuurItem.TotalHours.ToString("02"), DuurItem.Minutes.ToString("30")), True)
Dim style As Style = worksheet.Cells("I" & Num1).GetStyle()
style.[Custom] = "h:mm"
worksheet.Cells("I" & Num1).SetStyle(style)
workbook.Save("test.xls", FileFormatType.Excel2007Xlsx, SaveType.OpenInExcel, Response)
This generates the excel file as attached, in which you can see the value isn't usable for formulas before manually changing it.
Kind regards,
Martin de Ruiter
Hi,
I think you might be using some older version of the product. I have tested your case with the latest version (attached) and it works fine. Kindly try it and let us know if you still find the issue.
Thank you.