FORGEBOX Enterprise 🚀 - Take your ColdFusion (CFML) Development to Modern Times! Learn More...

LuceeSpreadsheet

v2.10.0 Public

Spreadsheet library for Lucee (and Adobe ColdFusion)

Originally adapted from the https://github.com/teamcfadvance/cfspreadsheet-railo extension, this is a standalone library for reading, creating and formatting spreadsheets in Lucee Server which does not require installation as an extension. It can also be used with Adobe ColdFusion.

Rationale

Unlike Adobe ColdFusion, Lucee doesn't support spreadsheet functionality out of the box. An extension exists for Lucee, but I decided to create a standalone library which doesn't depend on customisation of the engine.

Library vs Extension

Benefits

  • No installation required, either at the server or individual web context level.
  • read() method offers all the features of the <cfspreadsheet action="read"> tag in script in addition to the basic options of SpreadsheetRead().
  • Offers a number of additional functions and options (see below)
  • Fixes various outstanding bugs/omissions.
  • No dependency on Lucee within the included jar files.
  • Invoking the library doesn't create a workbook instance (a.k.a. Spreadsheet Object), meaning:
    • a blank workbook isn't created unnecessarily when reading an existing spreadsheet
    • the library can be stored as a singleton in application scope
  • Also works with Adobe ColdFusion (see below)
  • Written entirely in CFML script.

Downsides

  • Existing code needs adapting to invoke the library. Existing CFML spreadsheet functions and the <cfspreadsheet> tag won't work with it.

Adobe ColdFusion

Although primarily intended for Lucee, the library can be run under ColdFusion 2016 or higher. This may be useful where you want to your codebase to be cross-compatible between the two engines.

Minimum Requirements for version 2.x

  • Java 8 or higher
  • Lucee 5.x or higher
  • Adobe ColdFusion 2016 or higher

If you are running Java 6 and 7, Lucee 4.5 or ACF11, please use version 1.x.

Usage

Note that this is not a Lucee extension, so does not need to be installed. To use it, simply copy the files/folders to a location where Spreadsheet.cfc can be called by your application code.

The following example assumes the file containing the script is in the same directory as the folder containing the spreadsheet library files, i.e.:

  • root/
    • spreadsheetLibrary/
      • Spreadsheet.cfc
      • etc.
    • script.cfm
<cfscript>
spreadsheet = New spreadsheetLibrary.Spreadsheet();
data = QueryNew( "First,Last", "VarChar, VarChar", [ [ "Susi", "Sorglos" ], [ "Frumpo", "McNugget" ] ] );
workbook = spreadsheet.new();
spreadsheet.addRows( workbook, data );
</cfscript>

You will probably want to place the spreadsheet library files in a central location with an application mapping, and instantiate the component using its dot path (e.g. New myLibrary.spreadsheet.Spreadsheet();).

How to create mappings (StackOverflow).

Full function reference

Supported ColdFusion functions

Extra functions not available in ColdFusion

Additional Convenience methods

Enhanced Read() method

In Adobe ColdFusion, the SpreadsheetRead() script function is limited to just returning a spreadsheet object, whereas the <cfspreadsheet action="read"> tag has a range of options for reading and returning data from a spreadsheet file.

The read() method in this library allows you to read a spreadsheet file into a query and return that instead of a spreadsheet object. It includes all of the options available in <cfspreadsheet action="read">.

<cfscript>
myQuery = spreadsheet.read( src=mypath, format="query" );
</cfscript>

The read() method also features the following additional options not available in ColdFusion or the Spreadsheet Extension:

  • fillMergedCellsWithVisibleValue
  • includeHiddenColumns
  • includeRichTextFormatting
  • password to open encrypted spreadsheets

Full documentation of read()

Date formats

The following international date masks are used by default to read and write cell values formatted as dates:

  • DATE = yyyy-mm-dd
  • TIME = hh:mm: ss
  • TIMESTAMP = yyyy-mm-dd hh:mm: ss

An additional mask is used to output datetime values from the read() method into HTML or CSV formats:

  • DATETIME = yyyy-mm-dd HH:nn: ss

NB: Do not confuse DATETIME and TIMESTAMP. In general you should override the TIMESTAMP mask.

Each of these can be overridden by passing in a struct including the value(s) to be overridden when instantiating the Spreadsheet component. For example:

<cfscript>
spreadsheet = New spreadsheetLibrary.spreadsheet( dateFormats={ DATE: "mm/dd/yyyy" } );
</cfscript>

While the above will set the library defaults, you can format cells with specific masks using the dataFormat attribute which can be passed to formatCell and the other formatting methods, as part of the format argument:

// display datetime value with millisecond precision
spreadsheet.formatColumn( workbook , { dataformat: "yyyy-mm-dd hh:mm:   ss.000" }, 1 );

JavaLoader

A bundled version of Mark Mandel's JavaLoader will be used by default to load the POI and other required java libraries.

For more details and options see: Loading the POI java libraries

CommandBox Installation

You can also download this library through CommandBox.

box install cfsimplicity/lucee-spreadsheet

It will download the files into a modules directory and can be used just the same as downloading the files manually.

If using ColdBox you can use either of the WireBox bindings like so:

spreadsheet = wirebox.getInstance("[email protected]");
spreadsheet = wirebox.getInstance("LuceeSpreadsheet");

Test Suite

The automated tests require TestBox 2.1 or later. You will need to create an application mapping for /testbox

Credits

The code was originally adapted from the work of TeamCfAdvance. Ben Nadel's POI Utility was also used as a basis for parts of the read functionality.

JavaLoader is by Mark Mandel.

The MIT License (MIT)

Copyright (c) 2015-2020 Julian Halliwell

Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the "Software"), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions:

The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software.

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.

Dependencies (0)


Dev Dependencies (1)


2.10.0 - 14 April 2020

  • Enhancements

    • #190 Add getCellComments() as alias for getCellComment() with no row/column specified
    • #188 Allow addAutoFilter to accept a row number instead of a cell range
    • #187 Allow addAutoFilter to default to the first row
  • Fixes

    • #194 setCellComment() with underline throws exception on ACF
    • #193 Prevent setCellComment() throwing an exception on XLSX when unsupported styles are set
    • #191 setCellComment() throws exception on XLSX
    • #189 Row and column number values missing from getCellComment() structs when all returned from sheet
    • #109 Write encryption doesn't work on ACF

2.9.0 - 3 April 2020

  • Enhancements

    • #186 Add option to formatting methods to preserve existing cell styles
    • #184 Add support for DATETIME and DATETIME2 (MSSQL) database column types
  • Fixes

    • #185 Time only values do not respect custom TIME format specifying fractions of a second

2.8.0 - 23 March 2020

  • Enhancements

    • #181 Add "INT" to query column formats cast as numeric
    • #179 Provide a list of all predefined colours available to formatting methods
  • Fixes

    • #182 addInfo() not working with Streaming XLSX
    • #178 Color index lookup is using a deprecated enum class

2.7.0 - 17 February 2020

  • #175 Upgrade POI to 4.1.2
  • #176 Upgrade Apache Commons CSV to 1.8

2.6.0 - 28 November 2019

  • Enhancements

    • #174 Add getColumnWidth() and getColumnWidthInPixels()
  • Fixes

    • #173 Specifying a custom DATETIME date format mask seems to have no effect
    • #172 In ACF query column case and order is not preserved
    • #171 Using autoSizeColumns with a Streaming XLSX workbook causes an exception

2.5.0 - 21 November 2019

  • #170 Upgrade POI to 4.1.1
  • #169 Improve handling of clearly non-date values which Lucee will parse as dates far in the future

2.4.0 - 11 August 2019

  • #168 Allow the active sheet's "fit to page" print options to be controlled

2.3.0 - 9 August 2019

  • #167 Add support for setting sheet print margins

2.2.1 - 10 July 2019

  • #164 Upgrade Apache Commons CSV to 1.6
  • #166 Bug fix: autoSizeColumn - key [columnIndex] doesn't exist in argument scope

2.2.0 - 12 April 2019

  • #163 Upgrade POI to 4.1.0
  • #162 Support decryption of encrypted binary (XLS) spreadsheets. Add support for decryption in ACF.
  • DEPRECATED: engineSupportsEncryption environment key. Use engineSupportsWriteEncryption

2.1.1 - 29 March 2019

  • #160 includeQueryColumnNames in addRows() produces invalid xlsx

2.1.0 - 15 January 2019

  • #159 Support array data argument for addRow() and addRows()

2.0.2 - 6 January 2019

  • #157 addRows() doesn't apply column offset to header row when using includeQueryColumnNames

2.0.1 - 22 December 2018

  • #156 Bug in setActiveSheet().

2.0.0 - 22 December 2018

  • Breaking changes

    • #142 Upgrade POI to 4.0.1 which requires Java 8+
    • By default, Lucee 5 now uses JavaLoader instead of CreateObject
    • #148 Remove the engineSupportsDynamicClassLoading variable completely, since it is meaningless
    • Remove Lucee 4.5 and ACF11 support: Lucee 5 and ACF2016 are the minimum supported versions
  • Enhancements

    • #155 Add support for the SXSSF streaming XML format for writing large files
    • #136 Upgrade Apache Commons CSV to version 1.5
    • Improve write() outputstream locking.
    • Add dumpPathToClass() diagnostic tool
    • Separate encryption/decryption components no longer needed with POI 4
  • Fixes

    • Various fixes to support POI 4.x
    • #150 Rewrite xlsx encryption to ensure the encrypted stream is closed
    • Use array append() BIF instead of java add()
    • Fix failing setCellValue() test on ACF2016+
    • #154 Using RGB triplet as a colour format with XLSX not working in ACF

1.7.3 - 7 November 2018

  • Fixes
    • #153 Handling of hidden columns fails in ACF2016
    • #152 Testbox should be specified as a CommandBox installation dev dependency

1.7.2 - 14 May 2018

  • Fixes
    • #139 Cell type auto-detection throws error if boolean value is blank or null

1.7.1 - 13 May 2018

  • Fixes
    • #138 Reading a spreadsheet with column header names containing commas into a query results in too many columns

1.7.0 - 28 September 2017

  • #134 Upgrade POI to 3.17

1.6.1 - 7 September 2017

  • Fixes -#130 JavaLoader should not need loadColdFusionClassPath setting (add commons-codec jar to lib)

1.6.0 - 5 September 2017

  • Enhancements:
    • #129 Add getRowCount()

1.5.1 - 7 August 2017

  • Fixes
    • #128 Adding date query cells with blank values causes error

1.5.0 - 2 June 2017

  • Enhancements
    • #125 Add addAutofilter()
    • #126 Add addPageBreaks()
    • #127 Add addPrintGridlines() and removePrintGuidelines()

1.4.1 - 16 May 2017

  • Fixes
    • #124 Handle "indent" format values greater than 15 in xls
    • #123 Underline value returned by getCellFormat() should be a descriptive string
    • Fix unreturned cellStyle when invalid underline format used.

1.4.0 - 15 May 2017

  • Enhancements
    • #119 Add getCellFormat() and formatting tests
    • #104 Add quoteprefixed to cell formatting options
    • #121 Support "double", "single accounting" and "double accounting" underline formats
    • #122 Add hideRow(), showRow() and isRowHidden()
    • Add isColumnHidden() and tests for hideColumn() and showColumn()
  • Fixes
    • #120 Setting underline format on ACF fails

1.3.0 - 20 April 2017

  • #118 Upgrade POI to 3.16

1.2.0 - 18 April 2017

  • Enhancements:
    • Rewrite cell data type handling
    • #112 Allow data type to be specified when using setCellValue()
    • Add getCellType()
  • Fixes:
    • #115: Don't auto-detect any incoming values as boolean: allow them to default to strings or numbers, unless a source query column type or data type parameter has set them as bit/boolean
    • #116: Prevent certain definitely non-date values being detected as dates
    • #117: Allow csvToQuery() to be called with positional arguments

1.1.0 - 11 April 2017

  • Enhancement: #110 Support populating a spreadsheet from CSV data:
    • Add workbookFromCsv()
    • Add public method csvToQuery() for convenience

1.0.0 - 7 April 2017

  • Enhancement: #80 Provide option to use POI jars in the java class path instead of via JavaLoader
  • Enhancement: #107 Remove the dependency on JavaLoader for Lucee 5 and load POI jars directly
  • Enhancement: #108 Officially support ACF11+
  • Enhancement: Add getEnvironment() method to return current environment details/settings

0.11.1 - 24 March 2017

  • Update bundled JavaLoader to 1.2

0.11.0 - 9 March 2017

  • Fix: #102 Fillpattern formatting not working.
  • Fix: #103 Replace deprecated cellstyle setters.
  • Enhancement: #106 Allow a file path to be passed to the info() method instead of a workbook object

0.10.2 - 16 January 2017

  • Fix: #100 Replace deprecated 'boldweight' methods and constants with getBold()/setBold().

0.10.1 - 16 January 2017

  • Fix: Tweak argument handling of setSheetPrintOrientation() to catch errors.

0.10.0 - 15 January 2017

  • Enhancement: #99 Add setSheetPrintOrientation().

0.9.4 - 21 December 2016

  • Enhancement: #98 Allow cell format color values to be specified in RBG triplets
  • Fix: Refactor buildCellStyle() to improve performance and fix boolean issue with "bold".

0.8.4 - 01 December 2016

  • Enhancement: #97 Add newXlsx() and newXls() as aliases for new( xmlFormat=true/false )
  • Fix: #96 Fix error when adding image to xlsx (xml format) spreadsheet

0.8.3 - 22 November 2016

  • Fix #95 Change to readBinary() in 0.8.2 causes MS Excel to crash

0.8.2 - 17 October 2016

  • Enhancement: Improve performance of readBinary() by using java ByteBuffer.
  • Fix: Update JavaLoader to include post 1.1 release patches to fix #94

0.8.1 - 29 September 2016

  • Enhancement: #92 Catch formula errors when reading

0.8.0 - 26 September 2016

  • Enhancements:
    • #90 Upgrade POI to 3.15
    • #91 Allow an existing JavaLoader installation to be used instead of the bundled one.

0.7.9 - 4 August 2016

  • Fix #88 When reading a file, warn if the Excel format is too old for POI.

0.7.8 - 25 July 2016

  • Fix #87 Invalid color after moving from Lucee 4.5.3 to Lucee 5.0

0.7.7 - 14 July 2016

  • Fix #86 Zeros are being interpreted as boolean false by addRow() and other methods.

0.7.6 - 14 July 2016

  • Fix #85 AddRow() causing "maximum number of cell styles was exceeded" error when inserting large number of rows including dates.

0.7.5 - 13 July 2016

  • Fix #84 formatColumn() fails when workbook contains more than 4000 rows

0.7.4 - 1 July 2016

  • Fix isSpreadsheetFile() not working in ACF for non-spreadsheet files.

0.7.3 - 1 July 2016

  • Enhancements:
    • #83 Add isSpreadsheetFile() and isSpreadsheetObject()

0.7.2 - 11 June 2016

  • More ACF compatibility fixes
    • Drop use of this scope for internal method calls
    • Drop unnecessary ExpandPath() when getting POI jar paths for JavaLoader
    • Another missing colon at EOL

0.7.1 - 18 May 2016

  • Updates to test suite for case-sensitive filesystems

0.7.0 - 17 May 2016

  • More ACF compatibility fixes
    • Move all private methods from includes to within the body of Spreadsheet.cfc
    • Another missing colon at EOL
    • Use compatible script syntax for downloads
    • Variable name being used twice for different purposes

0.6.1 - 28 April 2016

  • Fixes (preventing use with ACF):
  • Missing colons at EOL
  • Throw attribute typo

0.6.0 - 9 March 2016

  • Enhancements:
    • #76 Upgrade POI to 3.14
    • #77 Add getColumnCount()

0.5.11 - 7 January 2016

  • Better exception message when adding too many rows to a binary spreadsheet.

0.5.10 - 30 December 2015

  • Better exception message when read() src file is not a spreadsheet.
  • Make final closing of java streams dependent on existence of stream variable to prevent embedded exceptions.

0.5.9 - 28 December 2015

  • Enhancements:
    • #70 Support reading encrypted XML files (only) using supplied password
    • Better file handling when encrypting files

0.5.8 - 23 December 2015

  • Enhancements:
    • #73 Allow encryption algorithm to be specified when writing password protected workbooks

0.5.7 - 23 December 2015

  • Fixes:
    • #71 and #72 Adjust tests to support TestBox 2.2

0.5.6 - 15 December 2015

  • Enhancements:
    • #69 Add explicit setReadOnly() method for binary workbooks (only)
  • Fixes:
    • #68 Supplying a password to write() provides whole file encryption for XML spreadsheet files (only)

0.5.5 - 3 December 2015

  • Enhancements:
    • #66 Add setRepeatingColumns() and setRepeatingRows()

0.5.4 - 12 November 2015

  • Enhancements:
    • #65 Upgrade POI to 3.13.
    • #64 Add download() function for an existing workbook object.
    • #63 Add includeQueryColumnNames option to AddRows().

0.5.3 - 5 September 2015

  • Improve performance of read() by using native Java concatenation instead of arrays which are slow in Lucee.

0.5.2 - 21 August 2015

  • REMOVED:
  • #61 Support for font family and size with read() and includeRichTextFormatting when different from the cell's base font. Better to be consistent and not support these attributes anywhere so the expectation is clear.

0.5.1 - 21 August 2015

  • Enhancements:
  • #61 Support font family and size with read() and includeRichTextFormatting when different from the cell's base font
  • Bug fixes:
  • #60 includeRichTextFormatting option in read() results in empty span style if format not supported

0.5.0 - 20 August 2015

  • Enhancements:
  • #57 Add includeRichTextFormatting option to read()

0.4.9 - 29 July 2015

  • Enhancements:
  • #56 Add extra argument to read() to allow excluding hidden columns
  • #58 Add hideColumn() and showColumn()

0.4.8 - 8 June 2015

  • Enhancements:
  • #52 Add csv format support to read()
  • #55 Allow csv file to be downloaded from a spreadsheet file

0.4.6 - 6 June 2015

  • Enhancements:
  • #43 Add html format support to read().
  • #54 Allow default date formats to be overridden
  • Bug fixes:
  • #53 Fix incorrect formatter reference when evaluating formula cells.

0.4.5 - 3 June 2015

  • Enhancements:
  • #44 Support reading specified row or column ranges
  • #45 Support being able to specify the column names when reading a spreadsheet from file

0.4.4 - 31 May 2015

  • Bug fix:
    • #51 Empty cells are skipped when reading a spreadsheet into a query.

0.4.3 - 29 May 2015

  • Upgrade POI to 3.12

0.4.2 - 29 May 2015

  • Enhancements:
    • #47 Add fillMergedCellsWithVisibleValue option to read()
    • #48 Add setCellRangeValue()
    • #49 Add emptyInvisibleCells option to mergeCells()
  • Bug fixes:
    • Fix read() includeBlankRows=false option only suppressing null rows and not empty ones
    • Missing var declarations

0.4.1 - 10 March 2015

  • Bug fix:
    • POI Loader server variable name should be unique to the current library path

0.4.0 - 25 February 2015

  • Breaking changes
    • Use "freeze" instead of "split" for argument names of addFreezePane

0.3.0 - 24 February 2015

  • Breaking changes
    • #27 Drop deleteSheet[Number]() in favour of removeSheet[Number]()
  • Bug fixes:
    • #25 Font values not being applied
    • #40 Ensure non-string data types (numeric, date, boolean) are respected when processing cells
  • Enhancements:
    • #17 Add setActiveSheetNumber()
    • #18 Add formatRows()
    • #19 Support reading sheets by name
    • #20 Add deleteRows()
    • #21 Add deleteColumn() and deleteColumns()
    • #22 Add shiftColumns()
    • #23 Add getCellValue() and setCellValue()
    • #24 Add formatColumn(), formatColumns() and formatCellRange()
    • #23 Add isBinaryFormat() and isXmlFormat()
    • #28 Add mergeCells()
    • #29 Add addFreezePane() and addSplitPane()
    • #30 Add addInfo() and info()
    • #31 Add setCellFormula() and getCellFormula()
    • #34 Add setColumnWidth()
    • #35 Add setRowHeight()
    • #36 Add setHeader() and setFooter()
    • #33 Add setCellComment() and getCellComment()
    • #32 Add addImage()
    • #37 Add autoSizeColumn()
    • #41 Add option to auto size columns when using addColumn, addRow and addRow
    • #39 Add renameSheet()
    • #38 Add clearCell() and clearCellRange()

0.2.0 - 18 February 2015

  • Breaking changes
  • read() method sheet argument should now be sheetNumber (for consistency)
  • When specifying 1-based sheet numbers as arguments, always use sheetNumber (not sheet or sheetIndex).
  • When specifying sheet names as arguments, use sheetName, not sheet.
  • Enhancements
  • #13 Add support for createSheet()
  • #14 Add support for removeSheet()
  • #15 Add deleteSheet() which can delete a sheet by name or number

0.1.0 - 17 February 2015

  • Bug fixes:
  • Treat null rows/cells as blank not null
  • #5 new() method ignores xmlFormat argument
  • #6 ShiftRows offset argument misspel
  • #7 ShiftRows calls require workbook as argumen
  • #8 AddRow insert argument not working
  • #10 Cannot read XLSX files
  • #11 Read method errors if no format specified. Should return workbook object
  • Enhancements:
  • #2 Testbox BDD style test suite
  • #3 Upgrade POI to 3.11
  • #3 Option to include blank rows when reading into a query
  • #4 Simplify dependencies by including tools and formatting as mixins
  • #9 writeFileFromQuery(): detect if xml from file extension
  • #12 Change ACF excludeHeaderRow default=false to includeHeaderRow, default=false

0.0.5 - 13 February 2015

  • read method
    • changed radically to work under Lucee. Some attributes/functionality disabled for now, but can return a query or workbook object.
    • changed excludeHeaderRow default from false to true
  • Added flushPoiLoader utility method

0.0.4 - 12 February 2015

  • Added
  • write method matching SpreadSheetWrite()
  • writeFileFromQuery custom method

0.0.3 - 25 January 2015

  • Workbook creation separated from instantiation. Create a workbook using new() and then pass it to other functions. Same as ACF functions.
  • Use JavaLoader to load newer POI jars to allow support for read()
  • Added methods
  • new
  • read (matches cfspreadsheet action="read")
  • setActiveSheet

0.0.2 - 19 January 2015

  • Added custom method: downloadFileFromQuery

0.0.1 - 18 January 2015

  • Initial release with support for the following standard CFML functions only:
    • addColumn
    • addRow
    • addRows
    • deleteRow
    • formatCell
    • formatRow
    • shiftRows
    • readBinary
  • Custom method: binaryFromQuery

 

$ box install lucee-spreadsheet

No collaborators yet.
     
  • Feb 24 2020 11:49 AM
  • Apr 14 2020 10:38 AM
  • 376
  • 185
  • 89