Fluent API

Complete reference for the SpreadsheetFile Fluent API - the recommended approach for working with spreadsheets in BoxLang

The Fluent API is the modern, recommended way to work with spreadsheets in BoxLang. It provides a chainable, intuitive interface centered around the SpreadsheetFile object.

Recommended Approach: The Fluent API is the preferred method for all spreadsheet operations. It's more intuitive, easier to read, and provides better code organization than traditional BIFs or component methods.

---

📋 Quick Navigation

• Creating & Loading

• Saving

• Sheet Management

• Cell Operations

• Row Operations

• Column Operations

• Data Conversion

• Formatting

• Advanced Features

• Large File Streaming

• Information & Accessors

---

Creating & Loading

Spreadsheet()

Creates a new empty spreadsheet in XLSX format with a default sheet named "Sheet1".

sheet = Spreadsheet();

Arguments

None

Example

// Create new spreadsheet and add data
Spreadsheet()
    .setCellValue( 1, 1, "Hello World" )
    .save( "output.xlsx" );

---

Spreadsheet( path )

Creates or loads a spreadsheet from the specified path.

sheet = Spreadsheet( path );

Arguments

Name	Type	Required	Description
path	String	Yes	Path to the spreadsheet file

Example

// Create new file reference
sheet = Spreadsheet( "myfile.xlsx" )
    .setCellValue( 1, 1, "Data" )
    .save();

---

Spreadsheet( path, load )

Loads an existing spreadsheet file or creates a new one.

sheet = Spreadsheet( path, load );

Arguments

Name	Type	Required	Description
path	String	Yes	Path to the spreadsheet file
load	Boolean	Yes	If true, loads existing file; if false, creates new file reference

Example

// Load existing file
sheet = Spreadsheet( "existing.xlsx", true );
data = sheet.toArray();

---

load( path )

Loads a spreadsheet file from the specified path.

sheet.load( path );

Arguments

Name	Type	Required	Description
path	String	Yes	Fully qualified path to spreadsheet file

Example

sheet = Spreadsheet().load( "data.xlsx" );

---

load( path, password )

Loads a password-protected spreadsheet file.

sheet.load( path, password );

Arguments

Name	Type	Required	Description
path	String	Yes	Fully qualified path to spreadsheet file
password	String	Yes	Password to decrypt the file

Example

sheet = Spreadsheet().load( "protected.xlsx", "secret123" );

---

fromJson( json )

Creates a spreadsheet from JSON string data (array of objects).

sheet = SpreadsheetFile.fromJson( json );

Arguments

Name	Type	Required	Description
json	String	Yes	JSON string containing array of objects

Example

json = '[{"name":"John","age":30},{"name":"Jane","age":25}]';
sheet = SpreadsheetFile.fromJson( json ).save( "people.xlsx" );

---

fromArray( data )

Creates a spreadsheet from an array of structs.

sheet = SpreadsheetFile.fromArray( data );

Arguments

Name	Type	Required	Description
data	Array	Yes	Array of structs where each struct is a row

Example

data = [
    { name: "John", age: 30 },
    { name: "Jane", age: 25 }
];
sheet = SpreadsheetFile.fromArray( data ).save( "people.xlsx" );

---

fromQuery( query )

Creates a spreadsheet from a BoxLang query.

sheet = SpreadsheetFile.fromQuery( query );

Arguments

Name	Type	Required	Description
query	Query	Yes	A BoxLang query object

Example

qry = queryExecute( "SELECT name, email FROM users" );
sheet = SpreadsheetFile.fromQuery( qry ).save( "users.xlsx" );

---

Saving

save()

Saves the spreadsheet to the currently loaded path.

sheet.save();

Arguments

None

Example

Spreadsheet( "output.xlsx" )
    .setCellValue( 1, 1, "Data" )
    .save();

---

save( path )

Saves the spreadsheet to the specified path.

sheet.save( path );

Arguments

Name	Type	Required	Description
path	String	Yes	Path where file should be saved

Example

sheet.save( "reports/monthly.xlsx" );

---

save( path, password )

Saves the spreadsheet with password protection.

sheet.save( path, password );

Arguments

Name	Type	Required	Description
path	String	Yes	Path where file should be saved
password	String	Yes	Password to protect the file

Example

sheet.save( "secure.xlsx", "secret123" );

---

saveAndClose()

Saves the spreadsheet and closes the workbook.

sheet.saveAndClose();

Arguments

None

Example

sheet.setCellValue( 1, 1, "Final" ).saveAndClose();

---

Sheet Management

createSheet( sheetName )

Creates a new sheet with the specified name.

sheet.createSheet( sheetName );

Arguments

Name	Type	Required	Description
sheetName	String	Yes	Name of the new sheet

Example

sheet.createSheet( "Q1 Data" );

---

createSheet( sheetName, overwrite )

Creates a new sheet with optional overwrite.

sheet.createSheet( sheetName, overwrite );

Arguments

Name	Type	Required	Description
sheetName	String	Yes	Name of the new sheet
overwrite	Boolean	Yes	Whether to overwrite existing sheet

Example

sheet.createSheet( "Sales", true );

---

selectSheet( sheetName )

Selects (activates) a sheet by name for subsequent operations.

sheet.selectSheet( sheetName );

Arguments

Name	Type	Required	Description
sheetName	String	Yes	Name of sheet to activate

Example

sheet.selectSheet( "Q2 Data" )
    .setCellValue( 1, 1, "Quarter 2" );

---

selectSheet( sheetIndex )

Selects (activates) a sheet by index (1-based).

sheet.selectSheet( sheetIndex );

Arguments

Name	Type	Required	Description
sheetIndex	Integer	Yes	Index of sheet to activate (1-based)

Example

sheet.selectSheet( 2 );  // Select second sheet

---

removeSheet( sheetName )

Removes a sheet by name.

sheet.removeSheet( sheetName );

Arguments

Name	Type	Required	Description
sheetName	String	Yes	Name of sheet to remove

Example

sheet.removeSheet( "Obsolete Data" );

---

renameSheet( oldName, newName )

Renames an existing sheet.

sheet.renameSheet( oldName, newName );

Arguments

Name	Type	Required	Description
oldName	String	Yes	Current name of the sheet
newName	String	Yes	New name for the sheet

Example

sheet.renameSheet( "Sheet1", "Sales Data" );

---

copySheet( fromName, toName )

Copies a sheet with a new name.

sheet.copySheet( fromName, toName );

Arguments

Name	Type	Required	Description
fromName	String	Yes	Name of sheet to copy from
toName	String	Yes	Name of new sheet

Example

sheet.copySheet( "Template", "January" );

---

hideSheet( sheetName )

Hides a sheet from view.

sheet.hideSheet( sheetName );

Arguments

Name	Type	Required	Description
sheetName	String	Yes	Name of sheet to hide

Example

sheet.hideSheet( "Raw Data" );

---

unhideSheet( sheetName )

Unhides a sheet to make it visible.

sheet.unhideSheet( sheetName );

Arguments

Name	Type	Required	Description
sheetName	String	Yes	Name of sheet to unhide

Example

sheet.unhideSheet( "Hidden Calculations" );

---

moveSheet( sheetName, toIndex )

Moves a sheet to a new position.

sheet.moveSheet( sheetName, toIndex );

Arguments

Name	Type	Required	Description
sheetName	String	Yes	Name of sheet to move
toIndex	Integer	Yes	Target position (0-based)

Example

sheet.moveSheet( "Summary", 0 );  // Move to first position

---

Cell Operations

setCellValue( row, col, value )

Sets a cell value at the specified row and column.

sheet.setCellValue( row, col, value );

Arguments

Name	Type	Required	Description
row	Integer	Yes	Row number (1-based)
col	Integer	Yes	Column number (1-based)
value	Any	Yes	Value to set (String, Number, Date, Boolean)

Example

sheet.setCellValue( 1, 1, "Product Name" )
    .setCellValue( 1, 2, "Price" )
    .setCellValue( 2, 1, "Widget" )
    .setCellValue( 2, 2, 29.99 );

---

getCellValue( row, col )

Gets a cell value at the specified row and column.

value = sheet.getCellValue( row, col );

Arguments

Name	Type	Required	Description
row	Integer	Yes	Row number (1-based)
col	Integer	Yes	Column number (1-based)

Example

productName = sheet.getCellValue( 2, 1 );
price = sheet.getCellValue( 2, 2 );

---

formatCell( row, col, format )

Formats a single cell.

sheet.formatCell( row, col, format );

Arguments

Name	Type	Required	Description
row	Integer	Yes	Row number (1-based)
col	Integer	Yes	Column number (1-based)
format	Struct	Yes	Formatting options (bold, italic, fontsize, fgcolor, etc.)

Example

sheet.formatCell( 1, 1, {
    bold: true,
    fontsize: 14,
    fgcolor: "blue",
    fontColor: "white"
});

---

Row Operations

addRow( values )

Adds a new row at the end of the sheet.

sheet.addRow( values );

Arguments

Name	Type	Required	Description
values	Array	Yes	Array of cell values for the row

Example

sheet.addRow( [ "John", "Doe", 30, "Engineering" ] );

---

setRowData( row, values )

Sets multiple cell values in a row.

sheet.setRowData( row, values );

Arguments

Name	Type	Required	Description
row	Integer	Yes	Row number (1-based)
values	Array	Yes	Array of values starting from column 1

Example

sheet.setRowData( 1, [ "Name", "Age", "Department" ] );

---

getRowData( row )

Gets all cell values from a row.

rowData = sheet.getRowData( row );

Arguments

Name	Type	Required	Description
row	Integer	Yes	Row number (1-based)

Example

headers = sheet.getRowData( 1 );

---

formatRow( row, format )

Formats all cells in a single row.

sheet.formatRow( row, format );

Arguments

Name	Type	Required	Description
row	Integer	Yes	Row number (1-based)
format	Struct	Yes	Formatting options

Example

sheet.formatRow( 1, {
    bold: true,
    fgcolor: "gray",
    alignment: "center"
});

---

formatRows( rows, format )

Formats multiple rows specified by range or list.

sheet.formatRows( rows, format );

Arguments

Name	Type	Required	Description
rows	String	Yes	Row specification: "1,3,5" or "1-5" or combinations
format	Struct	Yes	Formatting options

Example

sheet.formatRows( "1-5,10", { bold: true } );

---

removeRow( rowIndex )

Removes a row from the sheet.

sheet.removeRow( rowIndex );

Arguments

Name	Type	Required	Description
rowIndex	Integer	Yes	Row number to remove (1-based)

Example

sheet.removeRow( 5 );  // Remove row 5

---

addRows( data )

Adds multiple rows from a 2D array.

sheet.addRows( data );

Arguments

Name	Type	Required	Description
data	Array	Yes	Array of arrays representing rows

Example

data = [
    [ "John", 30 ],
    [ "Jane", 25 ],
    [ "Bob", 35 ]
];
sheet.addRows( data );

---

Column Operations

formatColumn( column, format )

Formats all cells in a single column.

sheet.formatColumn( column, format );

Arguments

Name	Type	Required	Description
column	Integer	Yes	Column number (1-based)
format	Struct	Yes	Formatting options

Example

sheet.formatColumn( 2, { dataformat: "$#,##0.00" } );

---

formatColumns( columns, format )

Formats multiple columns specified by range or list.

sheet.formatColumns( columns, format );

Arguments

Name	Type	Required	Description
columns	String	Yes	Column specification: "1,3,5" or "1-5"
format	Struct	Yes	Formatting options

Example

sheet.formatColumns( "1-3", { alignment: "center" } );

---

deleteColumn( column )

Deletes a column by clearing all cells.

sheet.deleteColumn( column );

Arguments

Name	Type	Required	Description
column	Integer	Yes	Column number (1-based)

Example

sheet.deleteColumn( 3 );

---

autoSizeColumn( column )

Auto-sizes a specific column to fit content.

sheet.autoSizeColumn( column );

Arguments

Name	Type	Required	Description
column	Integer	Yes	Column number (1-based)

Example

sheet.autoSizeColumn( 1 );

---

autoSizeColumns()

Auto-sizes all columns in the active sheet.

sheet.autoSizeColumns();

Arguments

None

Example

sheet.addRows( data )
    .autoSizeColumns()
    .save();

---

Data Conversion

toArray()

Converts sheet data to array of structs (first row as headers).

data = sheet.toArray();

Arguments

None

Example

employees = Spreadsheet( "employees.xlsx", true ).toArray();
// [ { Name: "John", Age: 30 }, { Name: "Jane", Age: 25 } ]

---

toQuery()

Converts sheet data to query format (array of structs).

data = sheet.toQuery();

Arguments

None

Example

customers = sheet.toQuery();

---

toJson()

Converts sheet data to JSON string.

json = sheet.toJson();

Arguments

None

Example

json = sheet.toJson();
writeFile( "data.json", json );

---

toJson( pretty )

Converts sheet data to JSON string with optional formatting.

json = sheet.toJson( pretty );

Arguments

Name	Type	Required	Description
pretty	Boolean	Yes	Whether to pretty-print the JSON

Example

json = sheet.toJson( true );  // Pretty-printed JSON

---

toMatrix()

Converts sheet to 2D array matrix (including headers).

matrix = sheet.toMatrix();

Arguments

None

Example

matrix = sheet.toMatrix();
// [ ["Name","Age"], ["John",30], ["Jane",25] ]

---

toCSV()

Converts sheet data to CSV format.

csv = sheet.toCSV();

Arguments

None

Example

csv = sheet.toCSV();
writeFile( "export.csv", csv );

---

toCSV( options )

Converts sheet data to CSV with custom options.

csv = sheet.toCSV( options );

Arguments

Name	Type	Required	Description
options	Struct	Yes	CSV options: delimiter, includeHeaders, lineSeparator

Example

csv = sheet.toCSV({
    delimiter: ";",
    includeHeaders: false,
    lineSeparator: "\n"
});

---

Formatting

formatCell( row, col, format )

Formats a cell with multiple style options.

sheet.formatCell( row, col, format );

Arguments

Name	Type	Required	Description
row	Integer	Yes	Row number (1-based)
col	Integer	Yes	Column number (1-based)
format	Struct	Yes	Style options

Format Options:

• bold (Boolean) - Bold text

• italic (Boolean) - Italic text

• underline (Boolean) - Underline text

• fontsize (Integer) - Font size in points

• font (String) - Font name

• fontColor (String) - Font color

• fgcolor (String) - Background color

• alignment (String) - Horizontal alignment

• verticalalignment (String) - Vertical alignment

• dataformat (String) - Number/date format

Example

sheet.formatCell( 1, 1, {
    bold: true,
    fontsize: 14,
    fgcolor: "blue",
    fontColor: "white",
    alignment: "center",
    dataformat: "$#,##0.00"
});

---

Advanced Features

addFreezePane( column, row )

Freezes columns and rows for scrolling.

sheet.addFreezePane( column, row );

Arguments

Name	Type	Required	Description
column	Integer	Yes	Columns to left are frozen
row	Integer	Yes	Rows above are frozen

Example

sheet.addFreezePane( 1, 1 );  // Freeze first row and column

---

addSplitPane( xSplitPos, ySplitPos, leftmostColumn, topRow )

Adds a split pane to the sheet.

sheet.addSplitPane( xSplitPos, ySplitPos, leftmostColumn, topRow );

Arguments

Name	Type	Required	Description
xSplitPos	Integer	Yes	Horizontal split position (1/20th pixel)
ySplitPos	Integer	Yes	Vertical split position (1/20th pixel)
leftmostColumn	Integer	Yes	Leftmost visible column in right pane
topRow	Integer	Yes	Top visible row in bottom pane

Example

sheet.addSplitPane( 2000, 2000, 2, 2 );

---

setRowBreak( row )

Sets a page break at the specified row.

sheet.setRowBreak( row );

Arguments

Name	Type	Required	Description
row	Integer	Yes	Row number (1-based)

Example

sheet.setRowBreak( 25 );  // Page break after row 25

---

setFitToPage( fitToPage, pagesWide, pagesHigh )

Sets fit-to-page print options.

sheet.setFitToPage( fitToPage, pagesWide, pagesHigh );

Arguments

Name	Type	Required	Description
fitToPage	Boolean	Yes	Enable fit-to-page
pagesWide	Integer	Yes	Pages wide (0 = no constraint)
pagesHigh	Integer	Yes	Pages high (0 = no constraint)

Example

sheet.setFitToPage( true, 1, 0 );  // Fit to 1 page wide

---

recalculateAllFormulas()

Recalculates all formulas in the workbook.

sheet.recalculateAllFormulas();

Arguments

None

Example

sheet.setCellValue( 1, 3, "=A1+B1" )
    .recalculateAllFormulas();

---

Large File Streaming

process( path, consumer )

Streams rows from a file using memory-efficient processing.

sheet.process( path, consumer );

Arguments

Name	Type	Required	Description
path	String	Yes	Path to spreadsheet file
consumer	Consumer	Yes	Callback receiving each row as Array

Example

Spreadsheet().process( "large-file.xlsx", ( row ) => {
    println( "Name: #row[1]#, Value: #row[2]#" );
    saveToDatabase( row );
});

---

process( path, sheetName, consumer )

Streams rows from a specific sheet.

sheet.process( path, sheetName, consumer );

Arguments

Name	Type	Required	Description
path	String	Yes	Path to spreadsheet file
sheetName	String	Yes	Name of sheet to process
consumer	Consumer	Yes	Callback receiving each row as Array

Example

Spreadsheet().process( "report.xlsx", "Sales", ( row ) => {
    if ( row[3] > 10000 ) {
        processHighValueOrder( row );
    }
});

---

process( consumer )

Streams rows from currently loaded workbook.

sheet.process( consumer );

Arguments

Name	Type	Required	Description
consumer	Consumer	Yes	Callback receiving each row as Array

Example

sheet = Spreadsheet( "data.xlsx", true );
sheet.process( ( row ) => {
    println( row );
});

Memory Efficient: Streaming keeps only ~100 rows in memory at a time. See the Large File Streaming Guide for complete details.

---

Information & Accessors

getSheetNames()

Gets all sheet names in the workbook.

names = sheet.getSheetNames();

Arguments

None

Example

sheets = sheet.getSheetNames();
// [ "Sheet1", "Data", "Summary" ]

---

getRowCount()

Gets the number of rows in the active sheet.

count = sheet.getRowCount();

Arguments

None

Example

totalRows = sheet.getRowCount();

---

getColumnCount()

Gets the number of columns in the active sheet.

count = sheet.getColumnCount();

Arguments

None

Example

totalColumns = sheet.getColumnCount();

---

getColumnNames()

Gets column names from the first row.

names = sheet.getColumnNames();

Arguments

None

Example

headers = sheet.getColumnNames();
// [ "Name", "Age", "Department" ]

---

info()

Returns properties of the spreadsheet as a struct.

properties = sheet.info();

Arguments

None

Example

info = sheet.info();
println( "Sheet count: #info.sheetCount#" );

---

hasSheet( sheetName )

Checks if a sheet exists.

exists = sheet.hasSheet( sheetName );

Arguments

Name	Type	Required	Description
sheetName	String	Yes	Name of sheet to check

Example

if ( sheet.hasSheet( "Summary" ) ) {
    sheet.selectSheet( "Summary" );
}

---

🔗 Method Chaining

Most Fluent API methods return the SpreadsheetFile object, enabling method chaining:

Spreadsheet( "chained.xlsx" )
    .setRowData( 1, [ "Header" ] )
    .formatRow( 1, { bold: true } )
    .addRow( [ "Data" ] )
    .autoSizeColumns()
    .save();

---

📚 See Also

• Large File Streaming Guide - Memory-efficient processing

• Quick Start Guide - Get started in 5 minutes

• User Guide - Comprehensive usage guide

• Examples - Real-world code samples

• Built-In Functions - Traditional BIF reference

---

🔗 External Resources

• Full API Documentation

• Apache POI Documentation - Underlying library