Bug in cfspreadsheet “update”?

[Guest]

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?

[-__cfSearching__- · Accepted solution]

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);
...

[Guest]

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.

[Adam Cameron.]

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

--

Adam

[Guest]

Adam,

Just did: http://cfbugs.adobe.com/cfbugreport/flexbugui/cfbugtracker/main.html#bugId=87135

Thanks.

[Adam Cameron.]

Cool / cheers / voted.

--

Adam

[Adam Cameron.]

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__-]

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)

[Adam Cameron.]

-==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__-]

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...

It is not as arbitrary as it seems. The POI api mentions the width of certain methods includes 10 pixels of padding per the ooxml spec.

But I suspect my earlier theory was way off. (Well not totally off as the length of the values do come into play, but ..) Looks like cloneSheet() uses getColumnWidth() to assign the column widths. So it could be as simple as the returned value being off  (which it seems to be) or not validating it.  Not that theorizing is going to fix anything

Message was edited by: -==cfSearching==-

[-__cfSearching__-]

cloneSheet() uses getColumnWidth() to assign the column widths. So it could be as simple as the returned value being off  (which it seems to be) or not validating it.

Yep, seems like that is the cause. If you use the underlying POI object to reset the column width to a valid unit value (1/256th of a character width) first, then updating works just fine. Though I do not see the need to "update" in this case. Doing a single write, like I showed above, would be more efficient.

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

>>        It is not as arbitrary as it seems. The POI api mentions the width of certain methods includes 10 pixels of padding per the ooxml spec. 

Ooops. My tired brain read 257, not 267. The comment about the addition of pixels stands.

Message was edited by: -==cfSearching==-