How to import a MS Excel Worksheet in a datagrid?

The first usage that may come to mind with such an Excel library, is to import the content of a worksheet in LiveCode.

What would be the steps to follow for importing a Worksheet?

1. Open the worbook file with Excel
2. Locate the last filled cell in the worksheet
3. Read the data from cell A1 to the last filled cell of the worksheet
4. Copy the retrieved data in datagrid
5. Close Excel

What are the keywords we need in the MS Library, for accomplishing that?

• XCEL_Workbook_Open: Opens an Excel file from the disk
• XCEL_Sheet_LastFilledCell_Get: Returns the last filled cell in a worksheet. Ideal to know what are the extents of the data to import
• XCEL_Range_Values_Get: Retuns the data from a range in a worksheet. The data returned is in the csv format (rows separated by returns, and columns by tab)
• XCEL_Application_Quit: Quits the MS Excel application
• XCEL_Util_CheckForError: Returns true if the value passed contains a Excel Library error
• XCEL_Range_Ref_Add: Retuns a reference to a range.
• XCEL_Range_Ref_CleanAll: Free up the range references from the memory.

The code:

command ImportWorkbookInDG pTheWorbookPath
   local tWorkbookName, tTheLastCell, tImportRange, tTheData, tTheHeaderLine

   ## Step 1: Opening the wookbook
   XCEL_Workbook_Open pTheWorbookPath
   put the result into tWorkbookName
   if (XCEL_Util_CheckForError(tWorkbookName)) then
      answer "Error during the workbook opening!" & cr & tWorkbookName
   else
      ## Step 2: Locating the last filled cell in the first worksheet
      put XCEL_Sheet_LastFilledCell_Get(tWorkbookName,1) into tTheLastCell
      if (XCEL_Util_CheckForError(tTheLastCell)) then
         answer "Error during the location of the last filled cell" & cr & tTheLastCell
      else
         ## Step 3: Reading data from cell A1 to the last filled cell
         put XCEL_Range_Ref_Add(tWorkbookName,1,"A1:" & tTheLastCell) into tImportRange -- Creation of the range
         put XCEL_Range_Values_Get(tImportRange) into tTheData -- Reads the data from the worksheet range
         if (XCEL_Util_CheckForError(tTheData)) then
            answer "Error during the data importation" & cr & tTheData
         else
            ## Step 4: Copying the imported data in a datagrid
            set the dgProp["Columns"] of grp "myDatagrid" to empty
            put the first line of tTheData into tTheHeaderLine
            replace tab with return in tTheHeaderLine
            set the dgProp["Columns"] of grp "myDatagrid" to tTheHeaderLine -- The datagrid columns are created according to the data content
            set the dgText[true] of grp "myDatagrid" to tTheData -- Populates the datagrid
         end if
      end if
      ## Step 5: Quitting MS Excel and cleaning up the memory
      XCEL_Application_Quit
      XCEL_Range_Ref_CleanAll
   end if
end ImportWorkbookInDG

This is our first article about our Excel Library. For importing an Excel Worksheet in a datagrid, we have combined 7 keywords from the Excel Library. 7 keywords only on the 174 keywords actually available.

More articles to come!