Skip to main content
A
Anonymous
October 7, 2011
Answered

Bug in cfspreadsheet “update”?

  • October 7, 2011
  • 2 replies
  • 4807 views

To those with experience with <cfspreadsheet>, I’d really appreciate your feedback. I’m trying to use the cfspreadsheet tag to generate a two page spreadsheet based on the output of two database queries. SheetOne uses "qryTopicList" and SheetTwo uses "qryTopicDetails".

Here's my code:

<cfscript>

// create empty spreadsheet objects

sheetOne = spreadsheetNew("topicList", "true");

sheetTwo = spreadsheetNew("topicDetails", "true");

// populate spreadsheet objects from queries

spreadsheetAddRows(sheetOne, qrytopicList);

spreadsheetAddRows(sheetTwo, qrytopicDetails);

</cfscript>

<!--- Write the two spreadsheet objects to a single file. --->

<cfspreadsheet action="write" filename="#theFile#" name="sheetOne" sheetname="Topic List" overwrite="true" />

<cfspreadsheet action="update" filename="#theFile#" name="sheetTwo" sheetname="Topic Details" />

When I run this code, I get an error:

java.lang.IllegalArgumentException: The maximum column width for an individual cell is 255 characters.

I tracked the error down to the last line of my code which adds sheet 2. I found that the query “qrytopicDetails” has columns with more than 255 characters in them. But I know that Excel is not limited to 255 characters per cell, so I thought this might be a java/POI/ColdFusion limitation. But then I reversed the sheet order and it worked:

<cfspreadsheet action="write" filename="#theFile#" name="sheetTwo" sheetname="Topic Details" overwrite="true" />

<cfspreadsheet action="update" filename="#theFile#" name="sheetOne" sheetname="Topic List" />

So, it seems that cfspreadsheet is able to "write" a column with more than 255 characters but cannot "update" if the query contains more than 255 characters. Is this a bug or am I missing something?

This topic has been closed for replies.
Correct answer -__cfSearching__-

My guess .. cfspreadsheet is using the query values to gauge how wide the columns in the new sheet should be ie auto-fit. Probably using the number of characters to determine the width.  Since the maximum width for columns (not the same as content) is 255, that would explain the error.

If the issue only occurs with updates, just append your second sheet instead. Then do a single write at the end.

...
sheet = spreadsheetNew("Topic List", "true");
spreadsheetAddRows(sheet , qrytopicList);
SpreadsheetCreateSheet(sheet , "Topic Details");
SpreadsheetSetActiveSheet(sheet , "Topic Details");
SpreadsheetAddRows(sheet , qrytopicDetails);
// save to disk
SpreadSheetWrite(sheet, outputToFile);
...

2 replies

A
Adam Cameron.
Inspiring
October 7, 2011

Yeah, it's a bug.  Well, indeed, I can see two bugs here.

Here's a more portable repro case:

<cfparam name="URL.length" type="integer">

<cfparam name="URL.xml" type="boolean">

<cfscript>

    qrytopicList = queryNew("col1");

    queryAddRow(qrytopicList);

    querySetCell(qrytopicList, "col1", "xxx");

   

    qrytopicDetails = queryNew("col1");

    queryAddRow(qrytopicDetails);

    querySetCell(qrytopicDetails, "col1", repeatString("x", URL.length));

   

    // create empty spreadsheet objects

    sheetOne = spreadsheetNew("topicList", URL.xml);

    sheetTwo = spreadsheetNew("topicDetails", URL.xml);

   

    // populate spreadsheet objects from queries

    spreadsheetAddRows(sheetOne, qrytopicList);

    spreadsheetAddRows(sheetTwo, qrytopicDetails);

   

    if (URL.xml){

        extn = "xlsx";

    }else{

        extn = "xls";

    }

    theFile = expandPath("./test.#extn#");

</cfscript>

<!--- Write the two spreadsheet objects to a single file. --->

<cfspreadsheet action="write" filename="#theFile#" name="sheetOne" sheetname="Topic List" overwrite="true">

<cfspreadsheet action="update" filename="#theFile#" name="sheetTwo" sheetname="Topic Details">

This code breaks when one specifies "?length=267&xml=true" on the query string.  It's fine, however, with "?length=267&xml=false".

So the first bug is that it breaks at all.  The second is that it's reporting 255 as some sort of significant number whereas that's not the error boundary.  And, yes, before anyone starts, I know the significance of 255, but it's not relevant to the problem here.

As to whether it's CF or POI: dunno.

You should raise a bug though.

http://cfbugs.adobe.com/cfbugreport/flexbugui/cfbugtracker/main.html

--

Adam

-
-__cfSearching__-
Inspiring
October 7, 2011

And, yes, before anyone starts, I know the significance of 255, but it's not relevant to the problem here.

Certainly seems relevant to me.  {Shrug}.  Either way, I agree you should raise a bug.

java.lang.IllegalArgumentException: The maximum column width for an individual cell is 255 characters.

    at org.apache.poi.xssf.usermodel.XSSFSheet.setColumnWidth(XSSFSheet.java:1697)

    at coldfusion.excel.Excel.cloneSheet(Excel.java:4433)

    at coldfusion.excel.Excel.writeExcel(Excel.java:4517)

    at coldfusion.excel.Excel.writeExcel(Excel.java:4257)

A
Adam Cameron.
Inspiring
October 7, 2011

-==cfSearching==- wrote:

And, yes, before anyone starts, I know the significance of 255, but it's not relevant to the problem here.

Certainly seems relevant to me.  {Shrug}.  Either way, I agree you should raise a bug.

java.lang.IllegalArgumentException: The maximum column width for an individual cell is 255 characters.

    at org.apache.poi.xssf.usermodel.XSSFSheet.setColumnWidth(XSSFSheet.java :1697)

    at coldfusion.excel.Excel.cloneSheet(Excel.java:4433)

    at coldfusion.excel.Excel.writeExcel(Excel.java:4517)

    at coldfusion.excel.Excel.writeExcel(Excel.java:4257)

Sorry, just to be clear: "?length=267&xml=true" errors. Amything less than 267 (267, not 256) does not.

267 wasn't a number I pulled out of my... hat.  However it seems "255" is one that Adobe pulled out of theirs... ;-)

--

Adam

-
-__cfSearching__-Correct answer
Inspiring
October 7, 2011

My guess .. cfspreadsheet is using the query values to gauge how wide the columns in the new sheet should be ie auto-fit. Probably using the number of characters to determine the width.  Since the maximum width for columns (not the same as content) is 255, that would explain the error.

If the issue only occurs with updates, just append your second sheet instead. Then do a single write at the end.

...
sheet = spreadsheetNew("Topic List", "true");
spreadsheetAddRows(sheet , qrytopicList);
SpreadsheetCreateSheet(sheet , "Topic Details");
SpreadsheetSetActiveSheet(sheet , "Topic Details");
SpreadsheetAddRows(sheet , qrytopicDetails);
// save to disk
SpreadSheetWrite(sheet, outputToFile);
...
A
Anonymous
October 11, 2011

Thanks cfSearching! Your suggestion, to build the object with two sheets and write it once, solved my problem. Thank you for the great example code.

A
Adam Cameron.
Inspiring
October 11, 2011

Did you raise a bug with Adobe about your initial findings?

--

Adam

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices