BoxLang Spreadsheet Module 📊

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

╔═══════════════════════════════════════════════════════╗
║          ⚡ B o x L a n g  S p r e a d s h e e t     ║
║      Dynamic · Powerful · Production-Ready            ║
╚═══════════════════════════════════════════════════════╝

Copyright Since 2023 by Ortus Solutions, Corp
www.boxlang.io | www.ortussolutions.com

---

🎯 Overview

The BoxLang Spreadsheet Module (bx-spreadsheet) is a comprehensive library for Excel file manipulation in BoxLang. Built on Apache POI, it provides three distinct APIs to suit different coding styles:

API Type	Entry Point	Use Case
Fluent API ✨	Spreadsheet()	Modern chainable interface (recommended)
BIF Functions 📚	SpreadsheetNew(), etc.	Traditional function-based approach
Component Tag 🏷️	<bx:spreadsheet>	Declarative CFML-compatible syntax

💡 Recommended: Use the Fluent API (Spreadsheet()) for the most modern, readable, and maintainable code.

✨ Key Features

• ✨ Fluent Method Chaining - Intuitive, readable code
• 📊 Multiple Formats - .xls (binary) and .xlsx (XML) support
• 🎨 Rich Formatting - Fonts, colors, borders, alignments
• 🔢 Formula Support - Set, evaluate, and recalculate formulas
• 📈 Data Import/Export - JSON, CSV, Query, and Array formats
• 🖼️ Image Embedding - Add images with positioning control
• 🔐 Password Protection - Secure spreadsheet files
• 📄 Multi-Sheet Support - Create and manage multiple worksheets
• 🚀 High Performance - Built on Apache POI

📋 Requirements

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

---

📦 Installation

Using CommandBox:

box install bx-spreadsheet

---

🚀 Quick Start

Create Your First Spreadsheet

// Create a sales report with the fluent API
Spreadsheet( "sales-report.xlsx" )
    .createAndSelectSheet( "Sales Report" )
    .setRowData( 1, [ "Product", "Q1", "Q2", "Q3", "Q4", "Total" ] )
    .addRow( [ "Widget A", 1000, 1200, 1100, 1300, "=SUM(B2:E2)" ] )
    .addRow( [ "Widget B", 800, 900, 950, 1050, "=SUM(B3:E3)" ] )
    .formatRow( 1, { bold: true, fgcolor: "blue", fontColor: "white" } )
    .autoSizeColumns()
    .save();

Read an Existing Spreadsheet

// Read and convert to different formats
data = Spreadsheet( "sales-data.xlsx" ).toArray();
csvData = Spreadsheet( "report.xlsx" ).toCSV();
jsonData = Spreadsheet( "report.xlsx" ).toJson();
queryData = Spreadsheet( "report.xlsx" ).toQuery();

Using Traditional BIF Functions

// Create with BIFs
spreadsheet = SpreadsheetNew( "My Report", true );
SpreadsheetSetCellValue( spreadsheet, "Product", 1, 1 );
SpreadsheetAddRow( spreadsheet, "Widget A,29.99" );
SpreadsheetFormatRow( spreadsheet, { bold: true }, 1 );
SpreadsheetWrite( spreadsheet, "products.xlsx", true );

// Read with BIFs
data = SpreadsheetRead( "products.xlsx" );

Using Components

<!-- Create and populate -->
<bx:spreadsheet action="create" name="mySheet" sheetname="Report" />
<bx:spreadsheet action="setCellValue" name="#mySheet#" row="1" column="1" value="Name" />
<bx:spreadsheet action="addRow" name="#mySheet#" data="#['John Doe', 'Engineer']#" />
<bx:spreadsheet action="write" name="#mySheet#" filename="output.xlsx" overwrite="true" />

---

📖 Complete Documentation

For comprehensive documentation including detailed API references, advanced features, and extensive examples, visit the official documentation:

📚 BoxLang Spreadsheet Module Documentation

The official documentation covers:

• Getting Started Guide - Installation and first steps
• Fluent API Reference - Complete method chaining guide
• BIF Functions Reference - All 85+ functions documented
• Component Reference - Declarative tag-based approach
• Formatting Guide - Styling cells, rows, and columns
• Advanced Features - Images, formulas, merging, and more
• Code Examples - Real-world usage patterns

🔗 Additional Resources

• JavaDoc API Documentation - Complete Java API reference
• GitHub Repository - Source code and issue tracking
• BoxLang Documentation - Main BoxLang documentation

---

💡 Common Patterns

AI Document Loading with SpreadsheetLoader

Use SpreadsheetLoader when you want to convert spreadsheet data into AI-ready Document objects (for pipelines, memory ingestion, and retrieval workflows).

ℹ️ This loader extends BaseDocumentLoader from the bx-ai module, so bx-ai must be available in your runtime.

import bxModules.bxSpreadsheet.loaders.SpreadsheetLoader;

// One document per sheet (default)
loader = new SpreadsheetLoader( source: "./data/customers.xlsx" );
docs = loader.load();

// One document per data row, with explicit sheet selection
rowDocs = new SpreadsheetLoader( source: "./data/customers.xlsx" )
    .rowsAsDocuments()
    .sheets( [ "Customers" ] )
    .load();

Creating and Formatting

// Fluent API with chaining
Spreadsheet()
    .createAndSelectSheet( "Report" )
    .setRowData( 1, [ "Name", "Age", "Salary" ] )
    .addRow( [ "John Doe", 30, 50000 ] )
    .formatRow( 1, { bold: true, fgcolor: "blue" } )
    .autoSizeColumns()
    .save( "output.xlsx" );

Working with Existing Files

// Load, modify, and save
Spreadsheet( "existing.xlsx" )
    .selectSheet( "Data" )
    .setCellValue( 2, 1, "Updated Value" )
    .setCellFormula( 2, 5, "SUM(B2:D2)" )
    .recalculateAllFormulas()
    .save();

Data Export

// Export to multiple formats
sheet = Spreadsheet( "data.xlsx" );

arrayData = sheet.toArray();         // Array of structs
queryData = sheet.toQuery();         // Query object
jsonData = sheet.toJson( true );     // Pretty JSON string
csvData = sheet.toCSV();             // CSV string

---

💬 Support & Community

• 📧 Email: [email protected]
• 💬 Slack: Join BoxLang Community
• 🐛 Issues: GitHub Issues
• 📖 Forums: BoxLang Forums

---

Built with ❤️ by Ortus Solutions

Professional Services Available - Need help with BoxLang implementation, training, or consulting? Contact Ortus Solutions