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?

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
      ## 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
         ## 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
            ## 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
   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!