Components | All | New | MacOS | Windows | Linux | iOS | ||||
Examples | Mac & Win | Server | Client | Guides | Statistic | FMM | Blog | Deprecated | Old |
XL.Sheet.ReadCellsAsMatrix
Reads all the values from cells in an area into a new matrix.
Component | Version | macOS | Windows | Linux | Server | iOS SDK |
XL | 13.0 | ✅ Yes | ✅ Yes | ✅ Yes | ✅ Yes | ✅ Yes |
Parameters
Parameter | Description | Example |
---|---|---|
BookRef | The reference to the workbook. Please use XL.LoadBook to load a file. | $ref |
SheetIndex | The index of the sheet. This number ranges from 0 to XL.Book.SheetCount-1. | 0 |
First row | The first row to process. First row has index 0. | 3 |
Last row | The last row to process. First row has index 0. | 6 |
First column | The first column to process. First column has index 0. | 2 |
Last column | The last column to process. First column has index 0. | 4 |
Result
Returns matrix reference or error.
Description
Reads all the values from cells in an area into a new matrix.Depending on content we return number, boolean, text or timestamp.
Empty, blank or error cells will return empty value.
Numbers and dates are formatted by FileMaker, so they should be in localized format.
Can be combined later with Matrix.CSV to get CSV text or Matrix.InsertRecords to import records into your FileMaker database.
Examples
Read cells:
Set Variable [ $sheet ; Value: 0 ]
# find the range we like to get
Set Variable [ $firstRow ; Value: MBS( "XL.Sheet.GetFirstRow"; $book; $sheet ) ]
Set Variable [ $lastRow ; Value: MBS( "XL.Sheet.GetLastRow"; $book; $sheet ) ]
Set Variable [ $firstCol ; Value: MBS( "XL.Sheet.GetFirstCol"; $book; $sheet ) ]
Set Variable [ $lastCol ; Value: MBS( "XL.Sheet.GetLastCol"; $book; $sheet ) ]
# read cells to a new matrix
Set Variable [ $matrix ; Value: MBS( "XL.Sheet.ReadCellsAsMatrix"; $bookRef; $sheet; $firstRow; $lastRow; $firstCol; $lastCol) ]
# and get CSV from those cells
Set Variable [ $text ; Value: MBS( "Matrix.CSV"; $matrix; 0; MBS( "Matrix.Height"; $matrix )-1; 0; MBS( "Matrix.Width"; $matrix )-1; ¶; ";" ) ]
Set Field [ XL::Text ; $text ]
# cleanup
Set Variable [ $r ; Value: MBS( "Matrix.Release"; $matrix) ]
Convert Excel to CSV:
Set Variable [ $matrix ; Value: MBS( "XL.Sheet.ReadCellsAsMatrix";
$bookRef;
0; // sheet index
0; // first row
9; // last row
0; // first column
5) // last column ]
Set Variable [ $r ; Value: MBS( "Matrix.CSV"; $matrix) ]
See also
- Matrix.CSV
- Matrix.Height
- Matrix.InsertRecords
- Matrix.Width
- XL.Book.SheetCount
- XL.LoadBook
- XL.Sheet.CellWriteMatrix
- XL.Sheet.GetFirstCol
- XL.Sheet.GetFirstRow
- XL.Sheet.GetLastRow
Release notes
- Version 13.0
- Added XL.Sheet.ReadCellsAsMatrix function to copy cell values into a new matrix.
Example Databases
Blog Entries
This function checks for a license.
Created 24th November 2022, last changed 12nd May 2023
