Member Avatar for agrarian

I am creating a Delphi program that needs to extract data from our database and create an Excel report. I would prefer to use the Delphi components TExcelApplication, TExcelWorkbook and TExcelWorksheet, but I haven't been able to get them to work. Most of the examples I have found are fairly old and I don't know if that's the problem or not.

Can anyone point me to a tutorial or example that uses these components in Delphi to create an Excel spreadsheet, populate that spreadsheet (I will be using data from the database), and then save that spreadsheet as a given filename?

I have been able to get fairly far using CreateOleObject('Excel.Application') to return me a variant, but then I'm running fairly blind not having any documentation and using just trial and error to make any progress.

Using the components, I have only gotten this far:

oXL.Connect;
  ExcelApplication1.Visible[0] := True;
  ExcelApplication1.Caption := 'Terry''s Special Excel Application';

  ExcelWorkBook1.ConnectTo(ExcelApplication1.Workbooks[0]);  <= This dies: Invalid Index
  ExcelWorksheet1.ConnectTo(ExcelWorkBook1.ActiveSheet as _Worksheet);
  ExcelApplication1.UserControl := True;
  • 2 Contributors
  • 5 Replies
  • 3K Views
  • 1 Week Discussion Span
  • Latest Post Latest Post by Wolfgan

Dani AI

Generated

the crash comes from two things: Excel collections are 1-based and you are indexing the Workbooks collection before any workbook exists. Create or open a workbook first, then attach the component wrappers to it; the wrapper method/property names map 1:1 to the Excel VBA object model, which is the best reference to discover what is available. If you have not already, import the Microsoft Excel 14.0 Object Library (Component > Import Component) so you get the early-bound wrappers and IntelliSense-like help. See Importing Type Library Information and the Excel VBA object model reference.

Minimal example using the components (note the LCID and 1-based indices):

var
  LCID: Integer;
  Data: OleVariant;
begin
  LCID := GetUserDefaultLCID;
  ExcelApplication1.Connect;
  ExcelApplication1.Visible[LCID] := True;

  ExcelApplication1.Workbooks.Add(EmptyParam, LCID);  // create first
  ExcelWorkbook1.ConnectTo(ExcelApplication1.ActiveWorkbook);
  ExcelWorksheet1.ConnectTo(ExcelWorkbook1.Worksheets.Item[1] as _Worksheet);

  // fast bulk write
  Data := VarArrayOf([VarArrayOf(['Region','Sales']), VarArrayOf(['West',1234])]);
  ExcelWorksheet1.Range['A1','B2'].Value2 := Data;
end;

Turn a block into a formatted table and save as .xlsx:

var
  Tbl: ListObject;
begin
  Tbl := ExcelWorksheet1.ListObjects.Add(
           xlSrcRange, ExcelWorksheet1.Range['A1','B2'], EmptyParam, xlYes, EmptyParam);
  Tbl.TableStyle := 'TableStyleMedium9';

  ExcelWorkbook1.SaveAs('C:\Reports\MyReport.xlsx', xlOpenXMLWorkbook, EmptyParam,
    EmptyParam, False, False, xlNoChange, EmptyParam, False, EmptyParam, EmptyParam, True, LCID);
end;

References: creating a workbook with Workbooks.Add (Microsoft), writing arrays with Range.Value2 (Microsoft), converting a range to a table via ListObjects.Add and styling (Microsoft), and Workbook.SaveAs with file formats like xlOpenXMLWorkbook (Microsoft). Tip: wrap automation in try/finally, call ExcelApplication1.Quit, and Disconnect to avoid orphaned Excel.exe processes. ’s 1-based note is spot on; use Workbooks.Item(1) or ActiveWorkbook after you add/open a file.

Recommended Answers

All 5 Replies

Member Avatar for Wolfgan

Try to start counting from 1. I have not worked with the components, always use another method. I know that almost all the indices in the COM Excel ticking is from 1.

Member Avatar for agrarian

Try to start counting from 1. I have not worked with the components, always use another method. I know that almost all the indices in the COM Excel ticking is from 1.

Thanks for your reply. I first tried "0" but got the same index error.

Member Avatar for Wolfgan

I think the traditional approach is simpler.

var
  fObject: Variant;
begin
 fObject := CreateOleObject('Excel.Application');
  if not VarIsEmpty(fObject) then begin
    fObject.Visible := true;
    fObject.IgnoreRemoteRequests := True;
    fObject.WorkBooks.Open(FileName, ReadOnly:=false);
    fObject.ActiveSheet.Cells[1, 1].Text := 'Hello world';
end;
Member Avatar for agrarian

I think the traditional approach is simpler.

I would agree except then one has no idea what calls are available. How do I find out how to turn a block of cells into a formatted table in Excel 2010?

Member Avatar for Wolfgan

This code works the same in office XP,2003,2007. There does not specify the version of office. Checked it works. If Microsoft what it has not changed that will work in 2010.To clarify the version you want to use something like "Excel.Application.12"

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.