Hello
I am loading an excel template with cells configured with (hh:mm) format from the template store and displaying it in a flexcel grid.
I have a strange problem in that I am inserting time values from code direct into the flexcelImport cells and then updating the grid display.
The error I get is with zero round numbers, for example if I insert 83 hours 0 minutes like so... 83:00
then the grid display shows 82:00. which is exactly 1 hour less.
This happens when ever I insert a value with :00 minutes it always knocks the hour down by 1.
But if I save my flexcel to xls file and open in excel spreadsheet the correct value is displayed!
Please advise.
Hi,
2 questions:
1) Are you calling FlexCelGrid.LoadSheet after the changes? you should always call LoadSheet after you change FlexCelImport.
2)How are you entering the "83:00" time with code?
I've tried with the following code:
procedure TForm16.FormCreate(Sender: TObject);
var
fmt: TFlxFormat;
begin
FlexCelImport1.NewFile();
FlexCelImport1.GetDefaultFormat(fmt);
fmt.Format := '[hh]:mm';
FlexCelImport1.CellValue[1,1] := 83 /24.0;
FlexCelImport1.CellFormat[1,1] := FlexCelImport1.AddFormat(fmt);
end;
And it seems to be working fine?
by the way, this seems like some summertime hour change, this is why I want to know the code you are using.
Hi Adrian
Thanks, you have supplied the answer!
I was doing this... flexcelImport.SetCellString(1,1, floattostr(83/24));
However changing to this fixed it... FlexCelImport.CellValue[1,1] := 83/24;
I am not applying the fmt to the cell because it messes with my grid colors, however the XLS template is already formatted to [hh]:mm and it appears to be working correctly now.
Thanks again for you fast response!
Thanks for the extra info. Indeed, if you convert to a string and back to a number there is a rounding error that causes this. While you shouldn't convert anyway to strings and back, I'll see to fix the rounding issue. We should have a fixed version soon.