Friday, 1 October 2010

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); 
 

33 comments:

  1. Hi.

    Where is declared the TExcelWrapper class?

    Sorry, if my english is bad.

    Thanks.

    Eduardo Laguna

    ReplyDelete
    Replies
    1. Hi Eduardo,

      TExcelWrapper is just a form. The example just needs to be changed by TForm1.... As it is the event for the dataset.

      Delete
  2. when i executed this procedure TForm1.LoadCombo();
    i get an EAssertionFailed with message 'Default Interface is Null' Component is not Connected to Server. you must call connect or connectTo. Please Help...
    Thanks

    ReplyDelete
    Replies
    1. Hi Andreas,

      Do you have installed Microsoft Excel? Or do you have C drive? Could it be that you are referencing something wrong in here?

      Jordi

      Delete
  3. Thanks for the Information, but what is TAEClientDataSet ???

    ReplyDelete
    Replies
    1. Hi Eiad,

      it's just a custom TClientDataSet. All changes reflected in the code.

      Jordi

      Delete
  4. Hi Jordi.
    When implemmented the code before mencioned, did works very well, but not now and i dont know why. Raise an exception "Ole error 800a03ec" please help me!!

    ReplyDelete
    Replies
    1. Hi,

      It seems a common excel crash. Try to restart everything and see if it makes any change. There are few entries on the web that could help you with this issue:
      Ole Error
      But I would suggest to see if excel is working fine.

      Jordi

      Delete
  5. Hi,

    How can we calculate the result of a function in the excel. When we want to render a function in Excel, as shett*.xml files need function as well as result of that function to write it in the Excel sheet. For an example to render SUM(1,2,3) function we need to write like bellow

    SUM(1,2,3)
    6

    How can we calculate the value of value attribute here. or
    is there any other way of rendering function????.
    Thank you

    ReplyDelete
    Replies
    1. Hi Manikanta,

      I think you will have to play with it. Excel will handle any operation by itself. So I imagine that after leaving the field, the operation will be automatically calculated. After that you should go one step back and the value of the function should be there.

      To render the function try writing =SUM(1,2,3) and check later the result on the field.

      Let me know your findings.

      Jordi

      Delete
    2. Hi Jordi,

      Thank you for replying me.

      If I want to manually create one xslx sheet using Delphi, I need to create some xml files and I need to zip them. For an example rename your .xlsx file to .zip and extract it, you will find a set of xml files. Like that I want to encode one function in the xlsx sheet manually.Please create one Excel sheet with any function then unzip it and check in the xl\worksheets\sheet1.xml, You can find the format how the Micro soft created environment to write a function. If you see that then you can come to know my problem.

      Thank you.

      Delete
    3. Hi Manikanta,

      Here you'll have to play with the XML files. I'm aware of the format as I've been playing a lot with it and seeing how they store data and documents. You could create the xml file on your own but why don't you try third party components for example? I've used FlexCel from TMS and it was really powerful to play with Excel.

      Jordi

      Delete
  6. Hi Jordi,

    what is this objManager, how can I declare it as my D2010 does not know what is it.
    Thank you,

    Laszlo

    ReplyDelete
    Replies
    1. Hi Lazlo,

      Thanks for spotting it. It was a type. It should've said objWrapper instead. It's already fixed on the post.

      Thanks again,
      Jordi

      Delete
    2. Hi Jordi,

      thank you for your fix.
      Unfortunately I have fatal problem with the 1st code's first line :)
      newWorkbook := XLSXReader.Workbooks.Add('C:\book1.xlsx', GetUserDefaultLCID);
      It causes access violation. I have 14.0 excel and office version. What should I change? (I have C: drive and excel2010 installed :))

      Best regards,

      Laszlo

      Delete
    3. Hi Laszlo,

      I've never tried this with the latest version of Excel. What you will have to do is to regenerate the Excel_TBL file for that particular version and try again. This will only work with Office 2007.

      Regards,
      Jordi

      Delete
  7. Hi Jordi,
    have the opportunity to see the very form and download the source code of the program?, since the implementation of the program, I have a lot of problems and errors. I would be very grateful for your help.
    Anton (Russia)

    ReplyDelete
    Replies
    1. Hi Anton,

      What kind of problems are you experiencing? Post them in the comments and I'll review them.

      Thanks,
      Jordi

      Delete
  8. Hi Jordi,
    have the opportunity to see the very form and download the source code of the program?, since the implementation of the program, I have a lot of problems and errors. I would be very grateful for your help.
    Anton (Russia)

    ReplyDelete
  9. Hi Jordi,

    problem occurs as follows:
    cdsExcel
    objWrapper: TExcelWrapper;
    procedure TExcelWrapper.FillDataSet(var cdsExcel: TClientDataSet);
    how they should be written and what ingredients to use the program to start working?

    Thanks,
    Anton

    ReplyDelete
  10. Hi Anton,

    cdsExcel is a client data set. TExcelWrapper is just a bespoke class to wrap all the items from the Excel object. So now when you open your book using the ExcelWrapper class you need to fill in all your details into the dataset so you can display the values in a grid. That's the procedure and the whole idea. First of all try reading the excel spreadsheet and displaying the values on the screen. Then try populating a data set as I'm doing.

    Regards,
    Jordi

    ReplyDelete
  11. Hi Jordi,
    what did you use for the net output of books Excel?
    Regards,
    Anton.

    ReplyDelete
    Replies
    1. Hi Anton,

      I was using a DBGrid. You can attach the dataset to it and display the values. That was the whole purpose of the exercise.

      Regards,
      Jordi

      Delete
  12. Hi Jordi,

    All I understood, thank you for your time.

    Regards,
    Anton.

    ReplyDelete
  13. hi Jordi,

    What Is Excel_TLB ?
    [DCC Error] ImportExcelForm.pas(30): E2003 Undeclared identifier: 'Excel_TLB'

    This Error Give me.

    ReplyDelete
    Replies
    1. Hi Milan,

      You need to follow the steps in the post and import "Microsoft Excel 12.0 Object Library", then the library and the files will get created.

      I did this test long ago, so I don't know if with the new versions of Excel this has changed.

      Jordi

      Delete
    2. Hi Milan,

      You need to follow the steps from the post and import "Microsoft Excel 12.0 Object Library" into the project. Once it has been imported, the files will get automatically created.

      Jordi

      Delete
  14. Hi,
    I did everything step by step and I had a problem while trying to compile my project with this message:
    [DCC Fatal Error] Excel_TLB.pas(378): F1026 File not found: 'VBIDE_TLB.dcu'

    Thanks

    ReplyDelete
    Replies
    1. Hi Richard,

      Which version of Excel are you using?

      Jordi

      Delete
  15. Hi Jordi
    I am using a very similar code the that one of yours, and i am trying to fill out data from a dataset into a work sheet, then printing the sheet without saving it ( it works only as a form).
    everything works fine except when the data is written in a language other than English, everything prints like this (ãÍãÏ ÛÕä), even if i save the sheet to a file it is the same, i am not sure what the problem is, cause excel works fine with the intended language when written directly.
    any ideas???

    Eiad

    ReplyDelete
    Replies
    1. Hi Eiad,

      Could be a Unicode problem. Maybe you need to try with a different version.

      Jordi

      Delete
  16. Hi.. I have this problems...

    [DCC Error] Unit1.pas(99): E2003 Undeclared identifier: 'FSheet'

    [DCC Error] Unit1.pas(105): E2003 Undeclared identifier: 'LastModule'

    can you help me please?

    ReplyDelete
    Replies
    1. Hi Pablo,

      FSheet is a stringGrid that I have with some values and LastModule can be discarded.

      Regards,
      Jordi

      Delete