Spreadsheet +

A comprehensive and fluent way to interact with spreadsheets with BoxLang

A powerful BoxLang module for creating, reading, and manipulating Excel spreadsheet files.

This module is only available to +/++ subscribers only but can be installed in conjunction with the bx-plus Module with a limited trial.

---

🎯 Three Ways to Work with Spreadsheets

The BoxLang Spreadsheet Module (bx-spreadsheet) offers three distinct APIs to suit different coding styles and migration scenarios:

API Type	Entry Point	Status	Use Case	Documentation
Fluent API ✨	Spreadsheet()	✅ Recommended	Modern chainable interface for new code	Fluent API Reference
BIF Functions 📚	SpreadsheetNew(), etc.	🔄 CFML Compatibility	Migration from Adobe ColdFusion/Lucee	BIF Reference
Component Tag 🏷️	<bx:spreadsheet>	🔄 CFML Compatibility	Legacy tag-based code migration	Component Reference

💡 For new code, we strongly recommend using the Fluent API which provides better readability, maintainability, and modern method chaining.

---

✨ Features

• ✨ Fluent Method Chaining - Intuitive, readable code with chainable methods
• 📊 Multiple Formats - Support for .xls (binary) and .xlsx (XML) formats
• 🎨 Rich Formatting - Fonts, colors, borders, alignments, and cell styles
• 🔢 Formula Support - Set, evaluate, and recalculate Excel formulas
• 📈 Data Import/Export - Convert to/from JSON, CSV, Query, and Array formats
• 🖼️ Image Embedding - Add images to spreadsheets with positioning control
• 🔐 Password Protection - Secure spreadsheet files with passwords
• 📄 Multi-Sheet Support - Create, manage, copy, and manipulate multiple worksheets
• 🚀 High Performance - Built on Apache POI for reliable, efficient processing
• 🔧 Comprehensive API - 85+ BIF functions and full component support
• 🤖 Automatic Resource Management - No need to manually close workbooks
• ❄️ Freeze Panes - Lock rows/columns for better viewing
• 📐 Auto-sizing - Automatically adjust column widths
• 🔗 Hyperlinks - Add and manage cell hyperlinks
• 💬 Comments - Add cell comments with rich formatting
• 📎 Merge Cells - Combine cells for better layout
• 🚀 Large File Streaming - Memory-efficient processing of large spreadsheets using Consumer callbacks

---

🤖 BoxLang AI Loader Integration

The spreadsheet module now includes a new loader for BoxLang AI document loading workflows:

• New SpreadsheetLoader in src/main/bx/loaders/SpreadsheetLoader.bx for BoxLang AI document loading workflows
• Loads spreadsheet content as AI Document objects
• Supports one document per sheet (default) or one document per row (rowsAsDocuments)
• Supports header-aware row formatting (hasHeaders) and sheet filtering (sheets)
• Inherits the IDocumentLoader contract via BaseDocumentLoader

---

📋 Requirements

• BoxLang Runtime 1.0.0 or higher
• BoxLang+ License - This module requires a BoxLang+ license

---

📦 Installation

Using CommandBox:

box install bx-spreadsheet@ortus

---

🚀 Quick Start

Create Your First Spreadsheet (Fluent API)

// Create a sales report
Spreadsheet( "sales-report.xlsx" )
    .createAndSelectSheet( "Q1 Sales" )
    .setRowData( 1, [ "Product", "Revenue", "Profit" ] )
    .addRow( [ "Widget A", 50000, 20000 ] )
    .addRow( [ "Widget B", 45000, 18000 ] )
    .setCellFormula( "B2-20000", 2, 3 )
    .formatRow( { bold: true, fgcolor: "blue", fontColor: "white" }, 1 )
    .autoSizeColumns()
    .save();

// Read and export data
data = Spreadsheet( "sales-report.xlsx" ).toArray();
json = Spreadsheet( "sales-report.xlsx" ).toJson();

Quick Examples

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

// Chain multiple operations
Spreadsheet()
    .setCellValue( "Name", 1, 1 )
    .setCellValue( "Age", 1, 2 )
    .addRow( [ "John Doe", 30 ] )
    .addRow( [ "Jane Smith", 25 ] )
    .formatRow( { bold: true }, 1 )
    .save( "employees.xlsx" );

// Export to different formats
csvData = Spreadsheet( "report.xlsx" ).toCSV();
queryData = Spreadsheet( "report.xlsx" ).toQuery();
arrayData = Spreadsheet( "report.xlsx" ).toArray();

---

📚 Documentation

Getting Started

Quick StartUser Guide

Guides

FormattingFormulasData ExportAdvanced FeaturesExamples

API Reference

Reference

---

🔍 Quick Reference

✨ Fluent API (Recommended)

The modern, chainable interface for working with spreadsheets:

Spreadsheet( "file.xlsx" )
    .setCellValue( "Hello", 1, 1 )
    .addRow( [ "Data", "Here" ] )
    .save();

Complete Documentation: Fluent API Reference

📚 Built-in Functions (CFML Migration)

Traditional function-based approach for migrating from Adobe ColdFusion or Lucee:

sheet = SpreadsheetNew( "Report" );
SpreadsheetSetCellValue( sheet, "Hello", 1, 1 );
SpreadsheetWrite( sheet, "file.xlsx", true );

Complete Documentation: BIF Reference

🏷️ Component Tag (Legacy Migration)

Tag-based approach for migrating legacy CFML code:

<bx:spreadsheet action="create" name="sheet" />
<bx:spreadsheet action="setCellValue" name="#sheet#" row="1" column="1" value="Hello" />
<bx:spreadsheet action="write" name="#sheet#" filename="file.xlsx" />

Complete Documentation: Component Reference

---

📖 API Documentation

Complete JavaDoc API documentation with detailed method signatures, parameters, and return types:

📚 BoxLang Spreadsheet Module API Docs

---

🎯 Key Concepts

Fluent API Benefits

The Fluent API provides several advantages over traditional approaches:

• Method Chaining - Chain operations for readable, maintainable code
• Automatic Resource Management - No need to manually close workbooks
• Modern Syntax - Clean, intuitive interface
• Better IDE Support - Enhanced autocomplete and type hints

File Formats

• .xlsx (XML Format) - Modern Excel format, default
• .xls (Binary Format) - Legacy Excel format for compatibility

Common Operations

Operation	Fluent API	BIF Equivalent
Create new	Spreadsheet()	SpreadsheetNew()
Load file	Spreadsheet("file.xlsx")	SpreadsheetRead()
Set cell	.setCellValue( "Value", 1, 1 )	SpreadsheetSetCellValue()
Add row	.addRow(["A", "B"])	SpreadsheetAddRow()
Save	.save("file.xlsx")	SpreadsheetWrite()
Export	.toArray()	N/A

---

💡 Migration Guide

From Adobe ColdFusion / Lucee

If you're migrating from ACF or Lucee:

1. Start with BIFs - Use the BIF Reference for drop-in compatibility
2. Gradually adopt Fluent API - Each BIF page shows the Fluent API equivalent
3. Update incrementally - No need to refactor everything at once

Example Migration:

// Old CFML approach
sheet = SpreadsheetNew("Report");
SpreadsheetAddRow(sheet, "Name,Age");
SpreadsheetAddRow(sheet, "John,30");
SpreadsheetWrite(sheet, "output.xlsx", true);

// Modern Fluent API (Recommended)
Spreadsheet("output.xlsx")
    .addRow(["Name", "Age"])
    .addRow(["John", 30])
    .save();

---