Specifying datatypes in Spreadsheets

We have often encountered bugs around datatype of data being written in spreadsheets. For example string like 8E45000 getting converted into a floating type or a string like 4D or 4F getting converted to numeric etc.

The internal logic used to extract the data type of the data sometimes isnt that accurate and we would end up in writing corrupted data in spreadsheets.

With Splendor now we provide users with the option of specifying the datatype of the data being written in spreadsheets. In this way we can avoid the internal logic of determining the datatype which sometimes used to corrupt the data.

This option will be supported in these 3 mehotds : SpreadSheetAddRow,SpreadSheetAddRows,SpreadSheetSetCellValue

Possible datatype of a cell can be : STRING, NUMERIC or DATE

Here is the syntax :

SpreadSheetAddRow( ExcelInfo excelvar, String value, int row, int column, boolean insert,String datatype )

where ‘datatype’ is an expression which describes the datatype of data.

Here are examples :

STRING:1,3;NUMERIC:2 –> Data in 1st and 3rd column of this row will be of type string, and that of 2nd column of type numeric

NUMERIC:1;STRING –> Data in 1st column of this row will be of type numeric, rest all will be of type string

NUMERIC:2-5;STRING:1,6;DATE –> Data in 1st and 6th column of this row will be of type string and data from 2nd to 5th column will be numeric and rest of type date.

STRING –> All cells in this row will be of type string

string;numeric:3,4 –> This is an invalid expression. Only last in the sequence can skip mentioning column numbers. In that case we will assume rest all columns will be of that datatype. For example this will work :NUMERIC:3,4;STRING

If for any cell datatype is not specified, it will fallback to old way of extracting the datatype.

Similarly we take datatype attribute in other methods also :

SpreadSheetAddRows( ExcelInfo excelvar, Object q, int startrow, int startcolumn, boolean insert,Object datatypes )

Where ‘datatypes’ will be an array of datatype expressions

SpreadSheetSetCellValue( ExcelInfo excelvar, String value, int row, int column, String datatype )

Where ‘datatype’ is datatype of the cell


SpreadSheetAddPagebreaks and SpreadSheetAddAutofilter

We have added 2 new methods in spreadsheet.


Spreadsheets can have pagebreaks which breaks sheets into multiple regions when you want to print it. You can put these line breaks along rows as well as columns.

Here is the syntax :

SpreadSheetAddPagebreaks( ExcelInfo info, String rowbreaks,String colbreaks )

where info is the ExcelInfo object, rowbreaks are the row numbers and colbreaks are the column numbers where you want to put the page breaks.

For example, SpreadSheetAddPagebreaks(info,”,’2′) breaks the sheet into two parts with columns A,B,C in the first and D,E,… in the second. Similarly, SpreadSheetAddPagebreaks(info,’2′,”) breaks the sheet into two parts with first three rows (rownum=1…3) in the first part and rows starting with rownum=4 in the second.

Here is another example :

SpreadSheetAddPagebreaks(  info, ‘1,2’,’3,5′ )

This will put page breaks on rows 1 and 2 and columns 3 and 5. So if this sheet is send for print it will have multiple regions to be printed.



This method helps in enabling filtering for a range of cells. Filtering data is a quick and easy way to find and work with a subset of data in a range of cells or table. For example, you can filter to see only the values that you specify, filter to see the top or bottom values, or filter to quickly see duplicate values.

Here is the syntax :

SpreadSheetAddAutofilter ( ExcelInfo info, String autofilter )

where info is the ExcelInfo object, autofilter is the cell range

Here is example :

SpreadSheetAddAutofilter (  info, ‘A1:C2’)

This will apply autofilter on the sheet for the cell renage A1 to C2

Performance improvements in SpreadSheet

There has been considerable improvements in spreadsheet performance. We have done alot of internal code changes to improve this performance. Apart from internal changes we have also exposed something for users.

SpreadSheetWrite method and write action of cfspreadsheet tag now takes one more parameter ‘autosize’.

By default now all the excel sheets have all their columns expanded and resized once they are written. Column expansion is a very expensive process which if avoided can save some valuable milliseconds.

Here is the syntax :

SpreadSheetWrite( ExcelInfo info, String filename, String password, boolean overWrite,Object autosize )

Autosize can be a boolean or an array

–> If it is boolean, true means expand/resize all the columns and false means do not expand/resize any column.

SpreadSheetWrite( info,filename,password, true,false )

–> If it is an array, it should be array of columnn numbers which you want to expand. 

<cfset arr= [1,2]>

<cfspreadsheet  action=”write” filename = “test1.xlsx” name=”store” autosize = “#arr#” overwrite=true>


By default value will be true for backward compatibility. We recommend users to keep it false

Improvements and Enhancements in SpreadSheet

In the coming release we have made few updates in spreadsheet :

1. Overall performance has improved a lot. Read/write/update/format operations should be faster and consume less memory

2.  SpreadSheetWrite method now takes ’autosize’ parameter. If it is false, columns of output spread sheets wont be resized and users can avoid this expensive operation

3. New methods like SpreadSheetAddPagebreaks(adds page breaks in spreadsheet) and SpreadSheetAddAutofilter(Sets autofilter on the specific sheet) have been added

4. Internally POI has also been upgraded to 3.9 version

5. SpreadSheetAddRow/SpreadSheetAddRows/etc methods also take ’datatype’ attribute now where user can specify the datatype of data being added. In this case CF will not use default way of extracting the datatype of data being added (which sometimes lead to inconsistent behavior).

6. Tons of bugs have been fixed in this area

I will be writing in details about each point in next blog posts