MS Excel spreadsheet interface 2

[dominochmiel]

Is anyone able to give some suggestions / tips on interfacing with an MS Excel spreadsheet.

Thanks

 

[lespaul]

What are you trying to do? Here's some sample code in Delphi to run excel:

excelapp:= CreateOleObject('Excel.Application');
  excelapp.Visible := False;
  excelapp.Workbooks.Open('R:\Case Management\JMS\Forms\Vouchers.xls');
  excelsht :=  excelapp.WorkSheets.Item['Data'];
  excelsht.Activate;
  //set excel range to size of ProcessList array and transfer array in full
  excelsht.Range[excelsht.Cells.Item[2, 1], excelsht.Cells.Item[(personcount + 1), 8]].Value := ProcessList;
  excelsht.Cells[1,10] := lastvoucher;
  //run macro to process report
  excelapp.Worksheets.Item['VoucherReport'].PageSetup.LeftFooter := 'Voucher Processing for term beginning: ' + frmMainMenu.ProcessDate;
  excelapp.Run('Generate_VoucherRpt');
  lastvoucher := excelsht.Cells[1, 10].Text;

Leslie
landrews@metrocourt.state.nm.us

There are 10 types of people in the world -
those who understand binary
and
those who don't!

 

[VintageWine]

Leslie's method, OLE, is the Rolls-Royce approach and the right answer for many situations.

However there is another approach which is quicker (less programming) and quicker (at runtime), at the price of flexibly. It is using ODBC. You can treat a spreadsheet just like a database and read to/write from your favorite TDataset. The tabs become tables, the columns fields and the rows records.

The only gremlin I have found is this. I had a spreadsheet with a load of header rows before the data proper. Easy enough to discard the first x records having loading the dataset. However, it determines the datatype from the first non-blank cell in the column (it handles column/field names in the first row well). I had a number in the header rows in a column where the data proper was text. ODBC decided that this was a numeric column and so discarded the text data lower down.

Good luck