For searching in a datagrid, here is an implementation of the datagrid's FindIndexes command. We are using this command in DGH for creating the content of its property palette from a datagrid dictionary. The properties are displayed depending of the context (datagrid form or datagrid table selected, column or form builder opened).

The command makes the usage of the “value” function, a powerful function capable of evaluating a string for rendering the corresponding value.


command dgh_FindIndexes pWhichDataGrid, pTheKey, pSearchType, pSearchString

   local sDataArray, foundAMatch, theFoundIndex, theIndex, tIndexValue, tNotSearch, tTheResult


   put the dgData of pWhichDataGrid into sDataArray


   if (first word of pSearchType is "not") then

      put "not " into tNotSearch

      delete first word of pSearchType


      put empty into tNotSearch

   end if


   repeat for each key theIndex in sDataArray

      put sDataArray[theIndex][pTheKey] into tIndexValue


      put value(tNotSearch & "(" & quote & tIndexValue & quote && pSearchType && quote & pSearchString & quote & ")") into foundAMatch


      if foundAMatch then

         put theIndex into item (number of items of theFoundIndex + 1) of theFoundIndex

      end if

   end repeat


   if (theFoundIndex is empty) then

      put 0 into tTheResult


      put theFoundIndex into tTheResult

   end if


   return tTheResult

end dgh_FindIndexes




1. pWhichDataGrid is the long id of a datagrid group

2. pTheKey is a column name

3. pSearchType accepts one of the following operators:
- is / is not
- begins with / not begins with
- ends with / not ends with
- contains / not contains
- = / > / < / >= / <= / <>

4. pSearchString is the value to find in the datagrid rows


dgh_FindIndexes the longid of grp "myDatagrid",  "Article",  "is", "box"

set the dgHilitedIndexes of grp "myDatagrid" to the result

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