CODEWITHSUNDEEP
exceldelphiformattingdelphi-2009
TomR
TomR

Reputation: 3056

Delphi: how to write time-string into Excel cell but preserve General NumberFormat

I have Excel from the client (which generated it from the print-to-Excel function), strange thing is that Excel contains time string '06:15:00' into the cell with the General format. I should create identitcal Excel from the Delphi.

May code is: 

 FWorksheet.Cells.Item[4, 5].NumberFormat:='';
 FWorksheet.Cells.Item[4, 5].NumberFormat:='06:15:00';
 FWorksheet.Cells.Item[4, 5].NumberFormat:='';

But it results into 0.260416666666667 in the General format. I can observe 2 strange scenarious:

  1. If I format cell as General, then write 6:15:00, in that case the format is automatically converted to Custom and converting the format to General results in 0.26041666...
  2. If I click with mouse into the cell (General: 6:15:00) of the client Excel, then Excel converts the cell format into Custom and subsequent converting into the General of Text also results in 0.26...

So, I should find the way how to write into the General cell the string '06:15:00' and at the same time preserve the General format. It is impossible to generate Delphi code from the recorded VBA macro code, because I can not find the way to compose visual Excel commands so to arrive at this state.

I am using OLE Excel Excel_TLB.

I have found the command sequence in Excel that gives the desired result, this sequence can be recorded as Macro:

 Range("O4").Select
 Selection.NumberFormat = "@"
 Range("O4").Select
 ActiveCell.FormulaR1C1 = "06:15:00"
 Range("O4").Select
 Selection.NumberFormat = "General"
 Range("O6").Select

But my translation of this Macro in the Delphi code:

 FWorksheet.Cells.Item[4, 5].NumberFormat:='@';
 FWorksheet.Cells.Item[4, 5].FormulaR1C1:='06:15:00';
 FWorksheet.Cells.Item[4, 5].NumberFormat:='';

still produces 0.260...

Upvotes: 3

Views: 1625

Answers (1)

TomR
TomR

Reputation: 3056

The use of AnsiChar is the solution:

 FWorksheet.Cells.Item[4, 5].NumberFormat:=AnsiChar('@');
 FWorksheet.Cells.Item[4, 5].FormulaR1C1:='06:15:00';
 FWorksheet.Cells.Item[4, 5].NumberFormat:='';

Answer https://stackoverflow.com/a/24034170/1375882 pointed out that it is really important to use AnsiChar @ here, not UnicodeChar which is provided by Delphi2009 by default.

Upvotes: 2

Related Questions