Results 1 to 2 of 2

Thread: Working with excel files in Delphi

  1. #1

    Working with excel files in Delphi

    Hi guys!
    While this is not game related I'll still ask here first rather than over Stack Owerflow becouse I'm afraid that I'll simply get "this thread is duplicate" kind of an anser or "this thread is out of topic" answer there.

    What I'm looking for is a god working example of working with excel files by using Delphi, preferably one that doesen't use ADO database approach (my expirience of working with databases is pretty low and my knowlege of SQL is terrible ).

    Features that I require are:
    - openin existing excel files. If only .xls format is supported that is fine with me since I can alwys save .xlsx to .xls format with Excel.
    - automatically finding the sheet names
    - accesing specific cells preferably in form of row number column letter (A14, B56, etc)
    - saving of modified excel files
    Features that would be handy but are not necessary:
    - creating of new files
    - creating of new sheets
    - working with built in tables
    - and probably some other that I don't remember now

    Why I need this?
    I have several excel documents containing some data which I need to combine and then make some statistical analisies. The problem is that the data isn't equally formated in these documents and some of them actually for relationship connections (similar as relational tables).
    So what I need now is to format the data in these files in a proper way so I can combine it.
    I already found which columns could be used as relational conections but I need to format the text in them a bit. They contain a case number which is genarally constructed like this:

    aaaa-nnnn/yyyy/eeeeeee

    a = case type number
    n = serial number
    y = year in which case was opened
    e = employer number to whom the case was assigned

    Now the problem is that different documents store this case number in a bit different format. So I have these:
    aaaa-nnnn
    aaaa-nnnn/yyyy
    aaaa - nnnn / yyyy / eeeeeee
    I don't belive Excel itself has the ability to automatically format hese for me or does it. So I thought to myself that maybe I would go and bit a small aplication that would do this. And maybe someday in the future I go and upgrade it with even more functionality.

    Thanks for your help in advance!

  2. #2
    PGD Community Manager AthenaOfDelphi's Avatar
    Join Date
    Dec 2004
    Location
    South Wales, UK
    Posts
    1,245
    Blog Entries
    2
    It just so happens that I can assist

    One of the applications I created at work can import data directly from Excel spreadsheets. It requires Excel to be installed on the machine, but uses late binding to get around potential version differences.

    Code:
    fExcelApp:=CreateOleObject('Excel.Application');
    
    if (not varIsEmpty(fExcelApp)) then
    begin
      if (fConfiguration.workbookPassword<>'') then
      begin
        password:=fConfiguration.workbookPassword;
      end
      else
      begin
        password:='ThisIsAnEmptyPassword'; // emptyParam;
      end;
    
      try
        fExcelApp.Workbooks.Open(sourceFilename,ReadOnly:=true,UpdateLinks:=false,Password:=password);
      except
        on e:exception do
        begin
          fExcelApp.Quit
          exit;
        end;
      end;
    
      try
        fExcelWorkbook:=fExcelApp.ActiveWorkbook;
      except
        on e:Exception do
        begin
          fExcelWorkbook:=null;
          fExcelApp.Quit;
          exit;
        end;
      end;
    
      if (fExcelWorkbook.Worksheets.Count>0) then
      begin
        sheetName:=fConfiguration.worksheetName;
    
        try
          fExcelWorksheet:=fExcelWorkbook.Worksheets.Item[sheetName];
        except
          on e:exception do
          begin
            fExcelWorksheet:=null;
            fExcelWorkbook:=null;
            fExcelApp.quit;
            exit;
          end;
        end;
      end;
      
      // Access the cells in the open worksheet like this:- fExcelWorksheet.cells.item[1,column]
      
      fExcelWorksheet:=null;
      fExcelWorkbook:=null;
      fExcelApp.quit;
    end;
    fExcelApp, fExcelWorkbook and fExcelWorksheet are all OleVariants. I can't post anymore than this snippet (which is lacking ALOT of error checking) as it's code from the product itself. For more information about the various methods etc. available to you, your best bet is the Microsoft Interop References (http://msdn.microsoft.com/en-us/libr...ice.15%29.aspx - For Excel). It can be a real pain in the proverbial backside but once you get used to using them and working in this way you can do an awful lot. The same app that imports from Excel also opens InfoPath forms, renders them to a PDF document and copies all the attachments out to self contained files for later processing... all done in a similar convoluted fashion.

    If you have further questions, feel free to ask
    :: AthenaOfDelphi :: My Blog :: My Software ::

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •