Code to read an Excel file into a temp-table

Is this easier to create using .NET controls provided by MicroSoft or Infragistics?

Hi Peter, if you are going to use the .NET ComInterop classes from Microsoft you'll need to write your own C# wrapper class as Excel uses a two-dimensional collection and the ABL does only support one-dimensional collections.

Here's some sample code that can get you going.  It's not entirely generic (it loads data in a fixed temp-table), but it wouldn't be a big modification.  If you do make it generic or faster, please post it back.

It uses Infragistics.

[View:~/cfs-file.ashx/__key/communityserver-discussions-components-files/19/sample-code-to-read-data-from-Excel.txt.zip:550:0]

This certainly looks promising and I would be quite happy to make this generic and post it back.

At the moment, however, this code is a bit too limited to get me started. Some questions I have: How do I open the spreadsheet? What is the definition of ttItemEditableFields?

It would be great if you can post a little more code to get me started!

Yes, I forgot the code to load the worksheet...

objWorkbook = Infragistics.Excel.Workbook:Load(cExcelTempFileFullPath).
THIS-OBJECT:objWorksheet = objWorkbook:Worksheets:Item["MassChange":U].

ttItemEditableFields is the temp-table that contains the fields to load from the Excel file.  If a column header has the name of a field, the values of this column will be put in that field.

  DEFINE TEMP-TABLE ttItemEditableFields NO-UNDO
   FIELD RowInWorksheet AS INTEGER
   FIELD ItemNo AS CHARACTER
   FIELD Description2 AS CHARACTER INITIAL ?
   FIELD Mli AS CHARACTER INITIAL ?
   FIELD Type AS CHARACTER INITIAL ?
   FIELD PrGroup AS CHARACTER INITIAL ?
   FIELD BuyerCode AS CHARACTER INITIAL ?
   FIELD ComCode AS CHARACTER INITIAL ?
   FIELD VendorCode AS CHARACTER INITIAL ?
   FIELD DutyCode AS CHARACTER INITIAL ?
   FIELD CurrencyCode AS CHARACTER INITIAL ?
   FIELD TransportCode AS CHARACTER INITIAL ?
...
   INDEX ByRowInWorksheet IS PRIMARY UNIQUE RowInWorksheet

OK David thanks now I get the picture.

I guess the next challenge is to find out the dimensions of each worksheet. Any suggestions?

-peter

OK please find attached a generic class that reads any Excel file into a dynamic dataset using Infragistics.

[View:~/cfs-file.ashx/__key/communityserver-discussions-components-files/19/ExcelHelper.zip:550:0]

The syntax is as following:

myDataset = tools.ExcelHelper:readExcelFile(myFile).

Thanks David for getting me started!

-peter

Based on our experience, I wouldn't suggest using the IG Excel export in production if you are going to allow importing Excel generated outside your application. There are quite a few Exceptions (errors) that you may encounter depending on the file you are importing. Such Exceptions can be trigger by the following; special characters, cell containing formula. Other Exceptions that we have encountered are IndexOutofRangeException and IsolatedStorageException when calling the Workbook:Load method.

I would suggest using the Microsoft Office automation.