Bug in cfspreadsheet “update”?

Question

To those with experience with , 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:// create empty spreadsheet objectssheetOne = spreadsheetNew("topicList", "true");sheetTwo = spreadsheetNew("topicDetails", "true");// populate spreadsheet objects from queriesspreadsheetAddRows(sheetOne, qrytopicList);spreadsheetAddRows(sheetTwo, qrytopicDetails);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: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 Answer

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

Adam Cameron. · Answer

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

Here's a more portable repro case:

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>

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

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded