cfsimplicity / spreadsheet-cfml Goto Github PK

Needs detailed documentation on all available functions and their arguments.

Implement ACF function as an alias for the more descriptive deleteSheet()

If different versions of the library are instantiated on the same server, the server scoped _poiLoader may be overwritten and use an invalid path for the current JavaLoader.

To match ACF/Railo Extension: add columns and rows arguments to read() to allow reading only parts of a spreadsheet.

Is there a reason for using POI 3.7 over the newer 3.11?

Just curious.

New method to set a range of cells to the specified value.

Should make calling via cfobject possible.

Handy function not available in ACF

To match ACF/Railo. Add columnNames argument to read()

The default includeBlankRows=false attribute for read() should exclude rows whose cells all contain blank/empty values. Currently only suppresses rows which exist but with null cell values.

By default mergeCells() retains the underlying values in the merged cells. A new option emptyInvisibleCells will set value of the invisible cells (all but the top-left-most cell) to an empty string.

By default addColumn, addRow() and addRows() do not auto-size the newly added columns. Add a boolean argument to each which will cause each new column to be autosized to its content. Default should be false.

To match ACF and Railo Extension: allow a spreadsheet to be read and output as HTML table rows.

addRow and addRows both do basic type checking when setting cell values, but addColumn and setCellValue do not, so all values are set as strings. This means formulae may not work.

Reading into a query, header rows should normally be excluded from the data. That should be the default, but provide an option to include it.

If the input Excel file contains merged cells, then some cells in the query will be blank, but should be filled in.
Cells can be unmerged using the underlying POI classes, as long as the query contains blank rows and columns, or the numbering is off.

q = spreadsheet.read( src=filepath,format="query", sheetName=sheetName,includeBlankRows=true);//req blank rows to undo cell merge
o = spreadsheet.read( src=filepath );//a org.apache.poi.hssf.usermodel.HSSFWorkbook
o = o.getSheet(sheetName);
for(r=0;r lt o.getNumMergedRegions();r++){
    reg=o.getMergedRegion(r);//a org.apache.poi.ss.util.CellRangeAddress
    roww=o.getRow( reg.getFirstRow() );
    cell=roww.getCell( reg.getFirstColumn() );
    str=cell.getRichStringCellValue().getString();
    for(row=reg.getFirstRow();row lte reg.getLastRow();row++){
        for(col=reg.getFirstColumn();col lte reg.getLastColumn();col++){
            q['column'&col+1][row+1]=str;
        }
    }
}

It would be nice to have this as an option to read(format="query"), but without having to include blank rows/cols, which probably means moving the blank removal to after the read, hence not attaching a trivial patch here !
I suppose you could throw an error if format="query",includeblanklines!=true,unmerge=true instead ?