TTMSFNCDataGridExcelIO.XLSExport Method

Exports the grid to a worksheet of an XLS workbook.

API unit family: TMSFNCDataGridExcelIO Declaring type: TTMSFNCDataGridExcelIO

Syntax

FMX.TMSFNCDataGridExcelIO.hppPascalC++

procedure XLSExport(const FileName: TFileName; const SheetName: UTF16String = ''; const SheetPos: integer = -1; const SelectSheet: integer = 1; const InsertInSheet: TInsertInSheet = InsertInSheet_Clear);

void __fastcall XLSExport(const System::Sysutils::TFileName FileName, const UTF16String SheetName = System::UnicodeString(), const int SheetPos = 0xffffffff, const int SelectSheet = 0x1, const TInsertInSheet InsertInSheet = (TInsertInSheet)(0x0));

Remarks

The handling of an existing file depends on Options.ExportOverwrite: by default (omNever) the workbook is opened and its other sheets are preserved; omAlways replaces the file; omWarn asks the user first. When the file does not exist a new one-sheet workbook is created.

Grid cell (DataGridStartCol, DataGridStartRow) is written to worksheet cell (XlsStartCol, XlsStartRow). For each cell the export raises OnGetCellFormula, OnCellFormat, OnExportColumnFormat and, for typed values, OnDateTimeFormat, followed by OnProgress. The grid's data adapter is notified at the start and end of the export, and the grid is updated inside BeginUpdate/EndUpdate.

When Options.ExportShowInExcel is True the file is opened afterwards.

Parameters

Name	Description
FileName	Destination path. The file is always written in the binary .xls format.
SheetName	Name for the worksheet that receives the grid. In an existing workbook a sheet with this name (case-insensitive) is reused according to InsertInSheet; leave empty to always add a new sheet with the default name.
SheetPos	One-based position at which a new worksheet is inserted into an existing workbook. Values of 0 or less, or beyond the last sheet, append it at the end (default -1).
SelectSheet	One-based index of the worksheet that is active when the file is opened in Excel (default 1).
InsertInSheet	How the grid is placed in an existing sheet whose name matches SheetName. Default InsertInSheet_Clear.

Exceptions

Exception	Description
Exception	Raised when no grid is assigned.

Usage examples

Round-trip a DataGrid to and from Excel

Configure the ExcelIO component to import/export with cell formatting and embedded images, then save to XLS and open the file in the default handler.

Round-trip a DataGrid to and from ExcelPascalC++

procedure TForm1.FormCreate(Sender: TObject);
begin
  // Preserve cell formatting and images both ways
  ExcelIO.Options.ImportCellProperties := True;
  ExcelIO.Options.ExportCellProperties := True;
  ExcelIO.Options.ExportImages         := True;

  // Always overwrite an existing file without prompting
  ExcelIO.Options.ExportOverwrite := omAlways;

  // Bind the IO component to the grid
  ExcelIO.DataGrid := Grid;
end;

procedure TForm1.ImportButtonClick(Sender: TObject);
begin
  ExcelIO.XLSImport('CARS.xls');
end;

procedure TForm1.ExportButtonClick(Sender: TObject);
begin
  ExcelIO.XLSExport('gridexport.xls');
  TTMSFNCUtils.OpenFile('gridexport.xls');   // open in the default handler
end;

using namespace Fmx::Tmsfncdatagridexcelio;
void __fastcall TForm1::FormCreate(TObject *Sender)
{
  // Preserve cell formatting and images both ways
  ExcelIO->Options->ImportCellProperties = true;
  ExcelIO->Options->ExportCellProperties = true;
  ExcelIO->Options->ExportImages         = true;

  // Always overwrite an existing file without prompting
  ExcelIO->Options->ExportOverwrite = omAlways;

  // Bind the IO component to the grid
  ExcelIO->DataGrid = Grid;
}

void __fastcall TForm1::ImportButtonClick(TObject *Sender)
{
  ExcelIO->XLSImport("CARS.xls");
}

void __fastcall TForm1::ExportButtonClick(TObject *Sender)
{
  ExcelIO->XLSExport("gridexport.xls");
  TTMSFNCUtils::OpenFile("gridexport.xls");   // open in the default handler
}

Read the related guide