# Spreadsheet + A powerful BoxLang module for creating, reading, and manipulating Excel spreadsheet files. {% hint style="danger" %} This module is only available to [+/++ subscribers only](https://www.boxlang.io/plans) but can be installed in conjunction with the [`bx-plus` Module](https://boxlang.ortusbooks.com/boxlang-framework/boxlang-plus/modules/bx-plus) with a limited trial. {% endhint %} *** ## 🎯 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](https://boxlang.ortusbooks.com/boxlang-framework/boxlang-plus/modules/bx-spreadsheet/reference/fluent-api) | | **BIF Functions** 📚 | `SpreadsheetNew()`, etc. | 🔄 CFML Compatibility | Migration from Adobe ColdFusion/Lucee | [BIF Reference](https://boxlang.ortusbooks.com/boxlang-framework/boxlang-plus/modules/bx-spreadsheet/reference/built-in-functions) | | **Component Tag** 🏷️ | `` | 🔄 CFML Compatibility | Legacy tag-based code migration | [Component Reference](https://boxlang.ortusbooks.com/boxlang-framework/boxlang-plus/modules/bx-spreadsheet/reference/components) | > 💡 **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 *** ## 📋 Requirements * **BoxLang Runtime** 1.0.0 or higher * **BoxLang+ License** - This module requires a BoxLang+ license *** ## 📦 Installation Using CommandBox: ```bash box install bx-spreadsheet@ortus ``` *** ## 🚀 Quick Start ### Create Your First Spreadsheet (Fluent API) ```js // 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 ```js // 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 {% content-ref url="bx-spreadsheet/quick-start" %} [quick-start](https://boxlang.ortusbooks.com/boxlang-framework/boxlang-plus/modules/bx-spreadsheet/quick-start) {% endcontent-ref %} {% content-ref url="bx-spreadsheet/user-guide" %} [user-guide](https://boxlang.ortusbooks.com/boxlang-framework/boxlang-plus/modules/bx-spreadsheet/user-guide) {% endcontent-ref %} ### Guides {% content-ref url="bx-spreadsheet/formatting" %} [formatting](https://boxlang.ortusbooks.com/boxlang-framework/boxlang-plus/modules/bx-spreadsheet/formatting) {% endcontent-ref %} {% content-ref url="bx-spreadsheet/formulas" %} [formulas](https://boxlang.ortusbooks.com/boxlang-framework/boxlang-plus/modules/bx-spreadsheet/formulas) {% endcontent-ref %} {% content-ref url="bx-spreadsheet/data-export" %} [data-export](https://boxlang.ortusbooks.com/boxlang-framework/boxlang-plus/modules/bx-spreadsheet/data-export) {% endcontent-ref %} {% content-ref url="bx-spreadsheet/advanced-features" %} [advanced-features](https://boxlang.ortusbooks.com/boxlang-framework/boxlang-plus/modules/bx-spreadsheet/advanced-features) {% endcontent-ref %} {% content-ref url="bx-spreadsheet/examples" %} [examples](https://boxlang.ortusbooks.com/boxlang-framework/boxlang-plus/modules/bx-spreadsheet/examples) {% endcontent-ref %} ### API Reference {% content-ref url="bx-spreadsheet/reference" %} [reference](https://boxlang.ortusbooks.com/boxlang-framework/boxlang-plus/modules/bx-spreadsheet/reference) {% endcontent-ref %} *** ## 🔍 Quick Reference ### ✨ Fluent API (Recommended) The modern, chainable interface for working with spreadsheets: ```js Spreadsheet( "file.xlsx" ) .setCellValue( "Hello", 1, 1 ) .addRow( [ "Data", "Here" ] ) .save(); ``` **Complete Documentation:** [Fluent API Reference](https://boxlang.ortusbooks.com/boxlang-framework/boxlang-plus/modules/bx-spreadsheet/reference/fluent-api) ### 📚 Built-in Functions (CFML Migration) Traditional function-based approach for migrating from Adobe ColdFusion or Lucee: ```js sheet = SpreadsheetNew( "Report" ); SpreadsheetSetCellValue( sheet, "Hello", 1, 1 ); SpreadsheetWrite( sheet, "file.xlsx", true ); ``` **Complete Documentation:** [BIF Reference](https://boxlang.ortusbooks.com/boxlang-framework/boxlang-plus/modules/bx-spreadsheet/reference/built-in-functions) ### 🏷️ Component Tag (Legacy Migration) Tag-based approach for migrating legacy CFML code: ```xml ``` **Complete Documentation:** [Component Reference](https://boxlang.ortusbooks.com/boxlang-framework/boxlang-plus/modules/bx-spreadsheet/reference/components) *** ## 📖 API Documentation Complete JavaDoc API documentation with detailed method signatures, parameters, and return types: **📚** [**BoxLang Spreadsheet Module API Docs**](https://apidocs.ortussolutions.com/boxlang-modules/bx-spreadsheet/1.0.0/index.html) *** ## 🎯 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](https://boxlang.ortusbooks.com/boxlang-framework/boxlang-plus/modules/bx-spreadsheet/reference/built-in-functions) 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:** ```js // 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(); ``` ***