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');

Answer 2

You haven't said "how you are doing things manually" which means people have to completely guess what you're doing. So here's my wild guess:

1. If I assume you're using the Express Spreadsheet component from Developer Express I have estensive experience with this component. It does not support arbitrary numeric formats. It supports a "money" format (0.00) with exactly two decimal places. It does not support three or one, or any other number of decimal places. If so, this is a known by-design issue in the Express spreadsheet.

2. If by manually you mean that "sheet" as you show above is an OLE object and you're communicating via OLE Automation to Excel itself, then you should be formatting CELLS either individually or as a range, and not column objects. I am not sure how column object formats would ever override the cell values, if they do at all. The formatting of cells is generally a cell by cell matter, and must be dealt with as such.

3. If you really want this to work properly you won't use Excel via OLE automation, you'll get a proper Excel XLS format capable writing library. I was quite sure that you could get proper results directly from the CX (DevEx) db grid, but I would ask on their forums, not here. With a regular DB Grid, I'd just use TJvDBGridExcelExport which comes in the Jedi JVCL, and which works with the regular VCL DB Grid.