EXAMPLE 56

TAdvStringGrid

example 56 : using the new TAdvGridExcelIO component for reading and writing Excel files directly with TAdvStringGrid

With the new TAdvGridExcelIO component directly reading and writing Excel 97, 2000 or XP files without the need to have Excel installed on the machine is easier than ever.

With these quick steps, you are up and running:

1) drop TAdvStringGrid on a form as well as the component TAdvGridExcelIO

2) Assign the instance of TAdvStringGrid to the AdvStringGrid property of the TAdvGridExcelIO component

3) You can set TAdvGridExcelIO properties to control the Excel file read / write behaviour but in most cases default settings will be ok.

4) To read Excel files, use

advgridexcelio.XLSImport(FileName);

or 

advgridexcleio.XLSImport(FileName,SheetName);

5) To write the contents of TAdvStringGrid to an XLS file use

advgridexcelio.XLSExport(filename);

Formatting Excel cells when exporting from with TAdvGridExcelIO.

By default there is no automatic conversion between the numeric formats in AdvStringGrid and Excel since they use different notations.

Imagine you have the number 1200 in the grid, formatted as "$1,200" . If you set TAdvGridExcelIO.Options.ExportCellFormat to true, the cell will be exported as the string "$1,200" to Excel. It will look fine, but it will not be a "real" number, and can not be used in Excel formulas.

If you set TAdvGridExcelIO.Options.ExportCellFormat to  false, the cell will be exported as the number 1200. It will be a real number, that can be added later in Excel, but it will look like "1200" and not "$1,200"

To get a real number that is also formatted in Excel you need to set ExportCellFormat := false, and use the OnCellFormat event in AdvGridExcelIO, and set the desired format for the cell there.

For example, to have 1200 look like "$1,200" for the numbers in the third column, you could use this event:

procedure TMainForm.AdvGridExcelIO1CellFormat(Sender: TAdvStringGrid;
  const GridCol, GridRow, XlsCol, XlsRow: Integer; const Value: WideString;
  var Format: TFlxFormat);
begin
  if (GridCol = 3) then Format.Format:='$ #,##0';
end;

The string you need to write in "Format.Format" is a standard Excel formatting string. It is important to note that this string must be in ENGLISH format, even if your Windows or Excel is not in English.

This means that you must use "." as decimal separator and "," as thousands separator, even if they are not the ones in your language.

For information on the available Formatting string in Excel you can consult the Excel documentation, but there is normally a simple way to find out:

Let's imagine that we want to find out the string for a number with thousands separator and 2 decimal places. So the steps are: 1) Open an empty Excel file, right click a cell and choose "Format Cells"

Once the window opens, choose the numeric format you want. Here we will choose a numeric format with 2 decimal places and a thousands separator

Once we have the format we want, we choose "Custom" in the left listbox. There is no need to close the dialog.

The string that shows in the "Type:" editbox is the one we need to use, converted to English notation. In this example, since our decimal separator is "," and the thousands "." we need to switch them in the final string.

So, the string showing is "#.##0,00", and we need to switch "," and ".", so the final string is "#,##0.00" and the event  is:

procedure TMainForm.AdvGridExcelIO1CellFormat(Sender: TAdvStringGrid;
  const GridCol, GridRow, XlsCol, XlsRow: Integer; const Value: WideString;
  var Format: TFlxFormat);
begin
  if (GridCol = 3) then Format.Format:='#,##0.00';
end;

Delphi project & source files for downloading included in the main demos distribution for Delphi.

The project and source files have been written with Delphi 6,7. To use these files in other versions of Delphi, ignore any remarks when opening the form files and save the files. After this, compilation can be done. The error messages are due to properties included in the Delphi 6 form file, but not available in lower versions of Delphi.