Monkeybread Software - MBS FileMaker Plugin: XL.Sheet.CellType

Components	All	New	MacOS		Windows		Linux		iOS
Examples	Mac & Win		Server	Client	Guides	Statistic	FMM	Blog	Deprecated	Old

XL.Sheet.CellType

Queries type of cell.

Component	Version	macOS	Windows	Linux	Server	iOS SDK
XL	3.5	✅ Yes	✅ Yes	✅ Yes	✅ Yes	✅ Yes

MBS( "XL.Sheet.CellType"; BookRef; SheetIndex; Row; 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

Row The row for the cell. First row has index 0. $row

Column The column for the cell. First column has index 0. $column

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
Row	The row for the cell. First row has index 0.	$row
Column	The column for the cell. First column has index 0.	$column

Result

Returns type or error message.

Description

Queries type of cell.
Types can be empty, number, text, boolean, blank or error.

Examples

Try the function:

# Initialize the XL functions if not already initialized
If [MBS("XL.IsInitialized") ≠ 1]
Set Variable [$libPath; Value: "/Library/FileMaker Server/libxl.dylib"]
Set Variable [$initResult; Value: MBS("XL.Initialize"; $libPath)]
End If

# Create a new Excel workbook
Set Variable [$book; Value: MBS("XL.NewBook"; 0)]

# Add a sheet named "TestSheet"
Set Variable [$sheet; Value: MBS("XL.Book.AddSheet"; $book; "TestSheet")]

# Write a number to cell A1
Set Variable [$r; Value: MBS("XL.Sheet.CellWriteNumber"; $book; $sheet; 0; 0; 123)]

# Write a text to cell B1
Set Variable [$r; Value: MBS("XL.Sheet.CellWriteText"; $book; $sheet; 0; 1; "Hello World")]

# Write a boolean to cell C1
Set Variable [$r; Value: MBS("XL.Sheet.CellWriteBoolean"; $book; $sheet; 0; 2; True)]

# Write a formula to cell D1
Set Variable [$r; Value: MBS("XL.Sheet.CellWriteFormula"; $book; $sheet; 0; 3; "=SUM(A1)")]

# Query the type of each cell
Set Variable [$typeA1; Value: MBS("XL.Sheet.CellType"; $book; $sheet; 0; 0)]
Set Variable [$typeB1; Value: MBS("XL.Sheet.CellType"; $book; $sheet; 0; 1)]
Set Variable [$typeC1; Value: MBS("XL.Sheet.CellType"; $book; $sheet; 0; 2)]
Set Variable [$typeD1; Value: MBS("XL.Sheet.CellType"; $book; $sheet; 0; 3)]

# Display the results
Show Custom Dialog ["Cell Types"; "A1: " & $typeA1 & "¶B1: " & $typeB1 & "¶C1: " & $typeC1 & "¶D1: " & $typeD1]

# Save the workbook to a container field
Set Field [YourTable::ExcelFile; MBS("XL.Book.Save"; $book; "TestWorkbook.xls")]

# Release the workbook to free memory
Set Variable [$r; Value: MBS("XL.Book.Release"; $book)]

<?xml version="1.0" encoding="UTF-8"?>
<fmxmlsnippet type="FMObjectList">
<Step enable="True" id="89" name="# (comment)"><Text> Initialize the XL functions if not already initialized</Text></Step>
<Step enable="True" id="68" name="If">
    <Restore state="False"/>
    <Calculation>MBS("XL.IsInitialized") ≠ 1</Calculation>
  </Step>
  <Step enable="True" id="141" name="Set Variable">
    <Value>
      <Calculation>"/Library/FileMaker Server/libxl.dylib"</Calculation>
    </Value>
    <Repetition>
      <Calculation>1</Calculation>
    </Repetition>
    <Name>$libPath</Name>
  </Step>
  <Step enable="True" id="141" name="Set Variable">
    <Value>
      <Calculation>MBS("XL.Initialize"; $libPath)</Calculation>
    </Value>
    <Repetition>
      <Calculation>1</Calculation>
    </Repetition>
    <Name>$initResult</Name>
  </Step>
<Step enable="True" id="70" name="End If"/>
<Step enable="True" id="89" name="# (comment)"><Text> Create a new Excel workbook</Text></Step>
<Step enable="True" id="141" name="Set Variable">
    <Value>
      <Calculation>MBS("XL.NewBook"; 0)</Calculation>
    </Value>
    <Repetition>
      <Calculation>1</Calculation>
    </Repetition>
    <Name>$book</Name>
  </Step>
<Step enable="True" id="89" name="# (comment)"><Text> Add a sheet named "TestSheet"</Text></Step>
<Step enable="True" id="141" name="Set Variable">
    <Value>
      <Calculation>MBS("XL.Book.AddSheet"; $book; "TestSheet")</Calculation>
    </Value>
    <Repetition>
      <Calculation>1</Calculation>
    </Repetition>
    <Name>$sheet</Name>
  </Step>
<Step enable="True" id="89" name="# (comment)"><Text> Write a number to cell A1</Text></Step>
<Step enable="True" id="141" name="Set Variable">
    <Value>
      <Calculation>MBS("XL.Sheet.CellWriteNumber"; $book; $sheet; 0; 0; 123)</Calculation>
    </Value>
    <Repetition>
      <Calculation>1</Calculation>
    </Repetition>
    <Name>$r</Name>
  </Step>
<Step enable="True" id="89" name="# (comment)"><Text> Write a text to cell B1</Text></Step>
<Step enable="True" id="141" name="Set Variable">
    <Value>
      <Calculation>MBS("XL.Sheet.CellWriteText"; $book; $sheet; 0; 1; "Hello World")</Calculation>
    </Value>
    <Repetition>
      <Calculation>1</Calculation>
    </Repetition>
    <Name>$r</Name>
  </Step>
<Step enable="True" id="89" name="# (comment)"><Text> Write a boolean to cell C1</Text></Step>
<Step enable="True" id="141" name="Set Variable">
    <Value>
      <Calculation>MBS("XL.Sheet.CellWriteBoolean"; $book; $sheet; 0; 2; True)</Calculation>
    </Value>
    <Repetition>
      <Calculation>1</Calculation>
    </Repetition>
    <Name>$r</Name>
  </Step>
<Step enable="True" id="89" name="# (comment)"><Text> Write a formula to cell D1</Text></Step>
<Step enable="True" id="141" name="Set Variable">
    <Value>
      <Calculation>MBS("XL.Sheet.CellWriteFormula"; $book; $sheet; 0; 3; "=SUM(A1)")</Calculation>
    </Value>
    <Repetition>
      <Calculation>1</Calculation>
    </Repetition>
    <Name>$r</Name>
  </Step>
<Step enable="True" id="89" name="# (comment)"><Text> Query the type of each cell</Text></Step>
<Step enable="True" id="141" name="Set Variable">
    <Value>
      <Calculation>MBS("XL.Sheet.CellType"; $book; $sheet; 0; 0)</Calculation>
    </Value>
    <Repetition>
      <Calculation>1</Calculation>
    </Repetition>
    <Name>$typeA1</Name>
  </Step>
  <Step enable="True" id="141" name="Set Variable">
    <Value>
      <Calculation>MBS("XL.Sheet.CellType"; $book; $sheet; 0; 1)</Calculation>
    </Value>
    <Repetition>
      <Calculation>1</Calculation>
    </Repetition>
    <Name>$typeB1</Name>
  </Step>
  <Step enable="True" id="141" name="Set Variable">
    <Value>
      <Calculation>MBS("XL.Sheet.CellType"; $book; $sheet; 0; 2)</Calculation>
    </Value>
    <Repetition>
      <Calculation>1</Calculation>
    </Repetition>
    <Name>$typeC1</Name>
  </Step>
  <Step enable="True" id="141" name="Set Variable">
    <Value>
      <Calculation>MBS("XL.Sheet.CellType"; $book; $sheet; 0; 3)</Calculation>
    </Value>
    <Repetition>
      <Calculation>1</Calculation>
    </Repetition>
    <Name>$typeD1</Name>
  </Step>
<Step enable="True" id="89" name="# (comment)"><Text> Display the results</Text></Step>
<Step enable="True" id="87" name="Show Custom Dialog">
    <Title>
      <Calculation>"Cell Types"</Calculation>
    </Title>
    <Message>
      <Calculation>"A1: " & $typeA1 & "¶B1: " & $typeB1 & "¶C1: " & $typeC1 & "¶D1: " & $typeD1</Calculation>
    </Message>
    <Buttons>
      <Button CommitState="True">
        <Calculation>"OK"</Calculation>
      </Button>
      <Button CommitState="False">
        <Calculation>"Cancel"</Calculation>
      </Button>
      <Button CommitState="False"/>
    </Buttons>
  </Step>
<Step enable="True" id="89" name="# (comment)"><Text> Save the workbook to a container field</Text></Step>
<Step enable="True" id="76" name="Set Field">
    <Calculation>MBS("XL.Book.Save"; $book; "TestWorkbook.xls")</Calculation>
    <Field table="YourTable" id="99999" name="ExcelFile"/>
  </Step>
<Step enable="True" id="89" name="# (comment)"><Text> Release the workbook to free memory</Text></Step>
<Step enable="True" id="141" name="Set Variable">
    <Value>
      <Calculation>MBS("XL.Book.Release"; $book)</Calculation>
    </Value>
    <Repetition>
      <Calculation>1</Calculation>
    </Repetition>
    <Name>$r</Name>
  </Step>
</fmxmlsnippet>

XL.Sheet.CellType

Parameters

Result

Description

Examples

See also