I'm using cfscript to create a (fairly) large xlsx spreadsheet. As I was developing the script and the spreadsheet grew in size, I started getting the following error when attempting to open the spreadsheet:
I narrowed down the problem to my use of the functions used to format cells (specifically, SpreadSheetFormatCell and SpreadSheetFormatCellRange). I found that by commenting out some of those function calls, the error would disappear.
After much trial and error, I've now come to believe that the issue isn't incorrect usage of the formatting functions, but rather some sort of internal limit I'm running into. I threw together a simple test case to show this. The following code (named "bigSheetTest.cfm" on my system) can be used to show the problem (at least on my machine):
<cfscript>
maxrows = 725;
formatBoldCenter = StructNew();
formatBoldCenter.bold = "true";
formatBoldCenter.alignment = "center";
formatBoldUnderlineCenter = StructNew();
formatBoldUnderlineCenter.bold = "true";
formatBoldUnderlineCenter.bottomborder = "medium";
formatBoldUnderlineCenter.alignment = "center";
formatNumber = StructNew();
formatNumber.dataformat = "##,####0.00";
formatMoney = StructNew();
formatMoney.dataformat = "$##,####0.00";
xls = SpreadSheetNew("Big Sheet", true);
row = 1;
bps = 30;
for(bp=1;bp LE bps;bp++) {
col = 3*bp - 2;
SpreadSheetMergeCells(xls, row, row, col, col+2);
SpreadSheetSetCellValue(xls, "Reporting Period #bp#", row, col);
SpreadSheetSetCellValue(xls, "Current RG", row+1, col);
SpreadSheetSetCellValue(xls, "Hours", row+2, col);
SpreadSheetSetCellValue(xls, "Current OT", row+1, col+1);
SpreadSheetSetCellValue(xls, "Hours", row+2, col+1);
SpreadSheetSetCellValue(xls, "Current", row+1, col+2);
SpreadSheetSetCellValue(xls, "Cost", row+2, col+2);
}
lastCol = bps * 3;
row++;
SpreadSheetFormatCellRange(xls, formatBoldCenter, row, 1, row, lastCol);
row++;
SpreadSheetFormatCellRange(xls, formatBoldUnderlineCenter, row, 1, row, lastCol);
row++;
for(i=1;i LE maxrows;i++) { // 724 ok, 725 results in Excel error "Excel found unreadable content in 'bigSheetTest.xlsx'. Do you want to recover the contents of this workbook? If you trust the source of this workbook, click Yes."
for(bp=1;bp LE bps;bp++) {
col = 3*bp - 2;
SpreadSheetSetCellValue(xls, i*col/(i+3), row, col);
SpreadSheetSetCellValue(xls, i*col/(i+4), row, col + 1);
SpreadSheetSetCellValue(xls, i*col/(i+5), row, col + 2);
SpreadSheetFormatCellRange(xls, formatNumber, row, col, row, col + 1);
SpreadSheetFormatCell(xls, formatMoney, row, col + 2);
}
row++;
}
SpreadSheetWrite(xls, "C:\bigSheetTest.xlsx", true);
</cfscript>
Notice the "maxrows" variable at the top of the code. When set to 724 (on my machine) the resulting spreadsheet may be opened without error. However, if I change that variable to 725, Excel complains with the error "Excel found unreadable content in 'bigSheetTest.xlsx'. Do you want to recover the contents of this workbook?" when I try top open the spreadsheet. Any ideas on what causes this issue and how to avoid it?
If you are not able to recreate this problem with maxrows set to 725, try increasing the value until you get the problem.
Here are the specifics on my environment:
Windows 7 Enterprise (64 bit) with 8 Gig of RAM, Intel Core i7-3520M CPU @ 2.90GHz
Microsoft Office Professional Plus 2010
Microsoft Excel Version 14.0.7106.5001 (32-bit)
| System Information | ||||||||||||||||
| ||||||||||||||||
| JVM Details | ||||||||||||||||||||||||||||||||||||||||||||||||||
| ||||||||||||||||||||||||||||||||||||||||||||||||||
24
Replies
It occurred to me that you may not be that familiar with SpreadsheetSetCellValue or how to go about doing what I suggested. On the chance that you change your mind and go with how I do it, I am providing an example of how I do it for your review. Say you have a query of names/addresses:
<cfscript>
/* CREATE EXCEL OBJECT AND FORMAT SHEET */
ssObj = SpreadsheetNew("Contact Information","true"); // true = .xlsx, false = .xls (old)
formatCols = StructNew();
formatCols.font = "Arial";
formatCols.textwrap = "true";
formatCols.alignment = "left";
formatCols.verticalalignment = "vertical_top";
SpreadsheetSetColumnWidth(variables.ssObj,1,20);
SpreadsheetSetColumnWidth(variables.ssObj,2,40);
SpreadsheetSetColumnWidth(variables.ssObj,3,30);
SpreadsheetSetColumnWidth(variables.ssObj,4,10);
SpreadsheetSetColumnWidth(variables.ssObj,5,20);
SpreadsheetSetColumnWidth(variables.ssObj,6,30);
SpreadsheetFormatColumns(variables.ssObj,variables.formatCols,"1-6"); // Apply formatting to first six columns (A-F)
SpreadsheetFormatRow(variables.ssObj,{bold="true",alignment="center"},1); // Change formatting for top row (header)/* CREATE HEADER ROW VALUES AND INSERT INTO EXCEL OBJECT */
rowHeader = "NAME,ADDRESS,CITY,ST,ZIP,COUNTRY";
rhLen = ListLen(rowHeader);
for(x=1; variables.x lte variables.rhLen; x++){ // Standard for loop to insert each list item in a separate cell
SpreadsheetSetCellValue(variables.ssObj,ListGetAt(variables.rowHeader,variables.x),1,variables.x); // SpreadsheetSetCellValue(excelObj,value,row,column) is the format
}/* GET NUMBER OF RECORDS IN QUERY FOR THE NEXT LOOP TO POPULATE WITH CONTACT INFORMATION */
qryLen = qryObj.recordCount;
for(x=1; variables.x lte variables.qryLen; x++){
SpreadsheetSetCellValue(variables.ssObj,qryObj.name,variables.x+1,1); // Since we put headers in first row, need to up by one for the following rows
SpreadsheetSetCellValue(variables.ssObj,qryObj.address,variables.x+1,2);
SpreadsheetSetCellValue(variables.ssObj,qryObj.city,variables.x+1,3);
SpreadsheetSetCellValue(variables.ssObj,qryObj.st,variables.x+1,4);
SpreadsheetSetCellValue(variables.ssObj,qryObj.zip,variables.x+1,5);
SpreadsheetSetCellValue(variables.ssObj,qryObj.country,variables.x+1,6);
}
SpreadsheetSetColumnWidth(variables.ssObj,1,20);
SpreadsheetSetColumnWidth(variables.ssObj,2,40);
SpreadsheetSetColumnWidth(variables.ssObj,3,30);
SpreadsheetSetColumnWidth(variables.ssObj,4,10);
SpreadsheetSetColumnWidth(variables.ssObj,5,20);
SpreadsheetSetColumnWidth(variables.ssObj,6,30);
SpreadsheetFormatColumns(variables.ssObj,variables.formatCols,"1-6");
SpreadsheetFormatRow(variables.ssObj,{bold="true",alignment="center"},1);
</cfscript>
Now you have a single-sheet Excel object that you can either save to disk, or attach to an email and send out.
If you have any questions, ask. 
V/r,
^ _ ^
AdChoices