Working with Microsoft Excel (.xlsx) files in Delphi 2010

Due to the improvement of the new format of Microsoft office 2007 files (.docx, .xlsx based on xml) the way we use to access this documents has changed. Instead of using OLE we can use the power of Delphi and the type library Borland (TLB Files).

In this article we'll talk about how to create the TLB files and the code we need to access the .Excel (.xlsx file) of the example I've created.

First of all, we need to import the components via -> Component -> Import a Type Library. Once we are there, we need to select the "Microsoft Excel 12.0 Object Library" and go on with the import. Mark the "Generate component wrappers" check-box and finally add the component to the working unit. This will generate the Excel_TLB.pas.

Once we have integrated this file into our project, we need to go on with importing the second component: Component -> Import a Type Library and then install the "Microsoft Office 12.0 Object Library". This will generate the Office_TLB.pas (this unit is necessary because it's used by Excel_TLB.pas).

1 - Knowing the number and name of the sheets of the Workbook.

The following code will open a excel file and will load all the sheets into a combo box. Even though it represents a small portion of code, the library is quite complicated to use.

procedure TForm1.LoadCombo();
var
    XLSXReader: Excel_TLB.TExcelApplication;
    newWorkbook: Excel_TLB._Workbook;
    objXLS: Excel_TLB.TExcelWorkbook;
    objSheet: Excel_TLB.TExcelWorksheet;
    i: Integer;
begin
    newWorkbook := XLSXReader.Workbooks.Add('C:\book1.xlsx', GetUserDefaultLCID);
    objXLS := Excel_TLB.TExcelWorkbook.Create(nil);
    objXLS.ConnectTo(newWorkbook);
    objSheet := Excel_TLB.TExcelWorksheet.Create(nil);
    for i := 1 to objXLS.Worksheets.count do
    begin
        objSheet.ConnectTo(objXLS.Worksheets[i] as _Worksheet);
        ComboBox1.Items.Add(objSheet.Name);
    end;
    newWorkbook.Close(false, EmptyParam, EmptyParam, GetUserDefaultLCID);
    FreeAndNil(objXLS);
    FreeAndNil(objSheet);
end;

2 - Accessing to Excel fields.

The following example will get the objSheet and will get all the data from the sheet and will dump into a dataset to display then the result into a grid.

procedure TForm1.Open(Sender: TObject);
var
    XLSXReader: Excel_TLB.TExcelApplication;
    newWorkbook: Excel_TLB._Workbook;
    objXLS: Excel_TLB.TExcelWorkbook;
    objSheet: Excel_TLB.TExcelWorksheet;
    objWrapper: TExcelWrapper;
begin
    newWorkbook := XLSXReader.Workbooks.Add('C:\book1.xlsx', GetUserDefaultLCID);
    objXLS := Excel_TLB.TExcelWorkbook.Create(nil);
    objXLS.ConnectTo(newWorkbook);
    objSheet := Excel_TLB.TExcelWorksheet.Create(nil);
    objSheet.ConnectTo(objXLS.Worksheets['sheet1'] as _Worksheet);
    objWrapper := TExcelWrapper.Create(objSheet);
    if cdsExcel.Active then
        cdsExcel.Close;
    objWrapper.FillDataSet(cdsExcel);
    cdsExcel.Open;
    cdsExcel.First;
    newWorkbook.Close(false, EmptyParam, EmptyParam, GetUserDefaultLCID);
end;

Then the code for getting the data from the sheet:

procedure TExcelWrapper.FillDataSet(var cdsExcel: TClientDataSet);
var
    count: Integer;
    i: Integer;
    bFinished, correct: boolean;
    Value: String;
begin
    if Assigned(FSheet) then
    begin
        try
            cdsExcel.DisableControls;
            count := 0;
            i := 2;
            LastModule := '';
            bFinished := false;
            while not bFinished do
            begin
                Value := FSheet.Cells.Item[i, 1];
                if Value = '' then
                    count := count + 1
                else
                begin
                    count := 0;
                    cdsExcel.Append;
                    cdsExcel.FieldByName('Column1').AsString := FSheet.Cells.Item[i, 1];
                    cdsExcel.FieldByName('Column2').AsString := FSheet.Cells.Item[i, 2];
                    cdsExcel.FieldByName('Column3').AsString := FSheet.Cells.Item[i, 3];
                end;
                if count > 2 then
                    bFinished := true;
                i := i + 1;
            end;
            cdsExcel.Post;
        finally
            cdsExcel.EnableControls;
        end;
    end;
end;

3 - Things to take into account.

You need to remember that the library creates an instance of Excel application, and you must close all the opened connections using the Close procedure.

newWorkbook.Close(false, EmptyParam, EmptyParam, GetUserDefaultLCID); 

Related links:

• http://powerdelphi.wikispaces.com/Exportar+Query+a+Excel