Topics   All   MacOS (Only)   Windows (Only)   Linux (Only, Not)   iOS (Only, Not)  
Components   Crossplatform Mac & Win   Server   Client   Old   Deprecated   Guides   Examples   Videos
New in version: 11.4   11.5   12.0   12.1   12.2   12.3   12.4   12.5   13.0   13.1    Statistic    FMM    Blog  

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
MBS( "XL.Sheet.ReadCellsAsMatrix"; bookRef; sheetIndex; First row; Last row; First column; Last column )   More

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) ]

See also

Release notes

Example Databases

Blog Entries

This function checks for a paid license.

Created 24th November 2022, last changed 8th December 2022


XL.Sheet.PictureCount - XL.Sheet.RemoveCol

💬 Ask a question or report a problem


Start Chat