Searching in a DataGrid - Our implementation of the FindIndex DataGrid's API command

Details

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

   else

      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

   else

      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


Example:

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

set the dgHilitedIndexes of grp "myDatagrid" to the result

MS Excel Library - Importing a Worksheet in a DataGrid

Details

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!

  • You are here:  
  • Home
  • Code Snippets