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!