Recent

Author Topic: Export elapsed time from Dataset to Excel [Solved]  (Read 932 times)

Vodnik

  • Full Member
  • ***
  • Posts: 137
Export elapsed time from Dataset to Excel [Solved]
« on: January 25, 2020, 11:53:32 pm »
Hello,
My DB (informix) have some fields containing elapsed time in seconds (DataSet.DataType=ftInteger). Elapsed time can exceed a day.
So I export DataSet to Excel with FPSpreadsheet and want this field be displayed as 'd days h:mm:ss'.
The best result achieved was converting Integer to DateTime
Code: Pascal  [Select]
  1. AValue := IncSecond(0,F.Value);
 
and assigning Custom format template:
Code: Pascal  [Select]
  1. AStyleCell := FCustomTemplateCell;
where template is defined as following:
Code: Pascal  [Select]
  1. worksheet.WriteNumberFormat(FCustomTemplateCell, nfCustom, 'd "days" h:nn:ss'
Then exported field display looks like '13 days 19:10:12' or '0 days 0:02:00'
That's OK.
Now I'm trying to hide days display when there are 0 days.
If I specify Custom cell format in Excel as following:
Code: Pascal  [Select]
  1. [>=1]d \days hh:nn:ss;hh:nn:ss
- it works displaying '13 days 19:10:12' or '0:02:00'
But when I set the same format in Pascal:
Code: Pascal  [Select]
  1. worksheet.WriteNumberFormat(FCustomTemplateCell, nfCustom, '[>=1]d "days" hh:nn:ss;hh:nn:ss');
this doesn't work. In that case Excel displays 13,79875 in General format instead of 13 days 19:10:12 in Custom or 0,0833333333333333 instead of 0:02:00.
What's going wrong?
« Last Edit: January 28, 2020, 08:38:39 pm by Vodnik »

wp

  • Hero Member
  • *****
  • Posts: 6698
Re: Export elapsed time from Dataset to Excel
« Reply #1 on: January 26, 2020, 11:11:18 pm »
I don't know - maybe this is a brand-new feature of Excel, but the version that I have does not support a day-interval format. There is an interval format for hours, minutes or seconds, but not for days, as far as I know. Therefore, there is no such format in fpspreadsheet either, only '[h]', '[n]' or '[s]' for hour, minute or second intervals, i.e. using '[h]:nn:ss' your time difference of 13.79875 would be displayed as '331:10:12'.

The only option that I see is to fake the format by writing the integer and fractional parts separately into adjacent cells and formatting them accordingly:
Code: Pascal  [Select]
  1. uses
  2.   fpsTypes, fpspreadsheet, fpsallformats;
  3.  
  4. procedure TForm1.Button1Click(Sender: TObject);
  5. var
  6.   wb: TsWorkbook;
  7.   sh: TsWorksheet;
  8.   t: Double;
  9. begin
  10.   wb := TsWorkbook.Create;
  11.   try
  12.     sh := wb.AddWorksheet('Test');
  13.  
  14.     t := 13.79875;
  15.     sh.WriteNumber(0, 1, trunc(t), nfCustom, '0 "days";'); // integer part of the time difference formatted as number with appended "days"
  16.     sh.WriteDateTime(0, 2, frac(t), nfShortTime);          // fractional part format as time
  17.     sh.WriteHorAlignment(0, 2, haLeft);                    // left-align the fractional part to move ot closer to the integer part
  18.  
  19.     t := -13.79875;
  20.     sh.WriteNumber(1, 1, trunc(t), nfCustom, '0 "days";');
  21.     sh.WriteDateTime(1, 2, frac(abs(t)), nfShortTime);
  22.     sh.WriteHorAlignment(1, 2, haLeft);
  23.  
  24.     wb.WriteToFile('test.xls', sfExcel8, true);
  25.   finally
  26.     wb.Free;
  27.   end;
  28. end;

The format string '0 "days";' applied to the integer part consists of twosemicolon-separated parts for positive and non-positive values. The first part for positive values adds the string "days" to the integer value. The second part for zero and negative values is empty which leaves the cell empty in this case (Of course, you must make sure that time differences are not negative!)
Lazarus trunk / fpc 3.0.4 / all 32-bit on Win-10

Vodnik

  • Full Member
  • ***
  • Posts: 137
Re: Export elapsed time from Dataset to Excel
« Reply #2 on: January 27, 2020, 08:40:23 am »
This is not a new feature of Excel (I have it working in Office 2007) and not a day interval.
Code: Pascal  [Select]
  1. Custom number formats also up to two conditions, which are written in square brackets like [>100] or [<=100].
  2. When you use conditionals in custom number formats, you override the standard [postive];[negative];[zero];[text] structure.
  3. For example, to display values below 100 in red, you can use:
  4. [Red][<100]0;0
  5. To display values greater than or equal to 100 in blue, you can extend the format like this:
  6. [Red][<100]0;[Blue][>=100]0
  7. You can use conditionals to add an "s" to labels greater than zero with a custom format like this:
  8. [=1]0" day";0" days"

Source: https://exceljet.net/custom-number-formats (look for "Conditionals").

Attached is screenshot from Excel 10 (sorry it is in Russian, but idea is clear) with successful conditional format application.

Unfortunately, suggested solution does not fit customer's request, because he use exported table not just for viewing and printing, but for some post-processing, building reports, etc. Variants with "0 days 2:00:00" and "318:00:00" are acceptable for this reason, I just tried to make a bit more beautiful view.
« Last Edit: January 27, 2020, 08:48:53 am by Vodnik »

wp

  • Hero Member
  • *****
  • Posts: 6698
Re: Export elapsed time from Dataset to Excel
« Reply #3 on: January 27, 2020, 11:58:09 pm »
OK I see. It did not work here in Excel because my version is localized and requires a "t" instead of the "d" symbol.

If you want your data being formatted as "13 days" then you mean implicitely that you consider the numbers as time intervals. But now there is a problem even in the Excel format: The symbol "d" in the date format string is replaced by the day number of the calendar date, e.g. by 27 for today's date (Jan 27). This works fine as long as the date number is within January. But try the number 32.79875 with your format string [>=1]d \days hh:nn:ss;hh:nn:ss, and see what happens -- there's an overflow back to 1.

The correct way would be, in my eyes, to introduce a square bracket syntax as it exists for hour, minute and second time intervals, i.e. provide a [d] symbol (like [h], [n], [s]). But this will not be implemented probably because it is not used neither by Excel for by LibreOffice Calc.

If you are willing to accept this possible overflow issue you can use the format string 'd "days" hh:nn:ss' in fpspreadsheet which extracts the day number and inserts the "days" word.

But now there is another problem: Leap year 1900 problem... For some reasons (http://www.cpearson.com/excel/datetime.htm), this is neglected in Excel, but it is respected correctly byLibreOffice and fpspreadsheet. This means that the day numbers are off by 1 before March 1 1900 between Excel on one side and LibreOffice/fpspreadsheet on the other side. (And as I noticed there is a bug in fpspreadsheet because Feb28,1900 appears a second time on March 1 -- I'll fix this soon).

Of course, when you export files only for Excel, not for Calc, and you do not plan to load them into fpspreadsheet you still can use the 'd "days" hh:nn:ss' format string.
« Last Edit: January 28, 2020, 09:32:29 am by wp »
Lazarus trunk / fpc 3.0.4 / all 32-bit on Win-10

Vodnik

  • Full Member
  • ***
  • Posts: 137
Re: Export elapsed time from Dataset to Excel
« Reply #4 on: January 28, 2020, 09:37:24 am »
My version is localized, too, exported format  'd "days" hh:mm:ss;hh:mm:ss' becomes 'Д "days" чч:мм:сс;чч:мм:сс' and works successfully.

Concerning 32 of January you are right, I didn't thought about this.

Leap year 1900 fortunately will not hit me in this project: DB stores data in real time.

Well, most universal solution seems to be [h]:mm:ss. Not very pleasant for eyes, but true and stable. Displays 787:10:12 for 32.79875.

Thank you, wp!