Searching in a DataGrid - Our implementation of the FindIndex DataGrid's API command
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
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
if (theFoundIndex is empty) then
put 0 into tTheResult
put theFoundIndex into tTheResult
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
MS Excel Library - Importing a Worksheet in a DataGrid
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?
- Open the worbook file with Excel
- Locate the last filled cell in the worksheet
- Read the data from cell A1 to the last filled cell of the worksheet
- Copy the retrieved data in datagrid
- 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.
command ImportWorkbookInDG pTheWorbookPath
local tWorkbookName, tTheLastCell, tImportRange, tTheData, tTheHeaderLine
## Step 1: Opening the wookbook
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
## Step 5: Quitting MS Excel and cleaning up the memory
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!