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:
