delphi - How do I set Excel column types and formatting?

Question

I'm exporting data from a CxDBGrid to an Excel file. I'm able to create the file and copy data in it, but I'm having real trouble with the column formatting. Since I'm pulling the data from a DB I'd like the spreadsheet to reflect the type: NUMBER, VARCHAR2,DATE and so on. I visually created a macro, went to look for the VBA code, and replicated it in the Delphi project:

sheet.Columns[K+2].NumberFormat := '0,000'; //Number
sheet.Columns[K+2].NumberFormat := '@'; //Text
sheet.Columns[K+2].NumberFormat := 'm/d/yyyy'; //Date

Number formatting works ok most of the times, but the other two don't. When I open the generated file, the text columns show up as type "Custom" and every cell displays "-64". If I go to edit a cell, the correct value is actually there. Date is another issue: the DB's format is dd/mm/yyyy and if I feed it to Excel as-is, it gets all messed up. I tried setting the correct format, but then Excel doesn't recognize it.

Any clues?

I'm also setting column width. That works flawlessly.

Answer 1

The problem is that the assigned values are Unicode strings. Try this:

sheet.Columns[K+2].NumberFormat := AnsiChar('@');
sheet.Columns[K+2].NumberFormat := AnsiString('m/d/yyyy');