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

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

Release notes

Example Databases

Blog Entries

This function checks for a license.

Created 24th November 2022, last changed 12nd May 2023


XL.Sheet.PictureCount - XL.Sheet.RemoveCol