Skip to main content
Participating Frequently
September 23, 2013
Question

Is there a limit to the size of a formatted xlsx spreadsheet created by ColdFusion?

  • September 23, 2013
  • 1 reply
  • 7729 views

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
Server Details
Server Product ColdFusion
Version ColdFusion 10,285437
Edition Developer  
Operating System Windows 7  
OS Version 6.1  
Update Level /C:/ColdFusion10/cfusion/lib/updates/chf10000011.jar  
Adobe Driver Version 4.1 (Build 0001)  

JVM Details
Java Version 1.7.0_15  
Java Vendor Oracle Corporation  
Java Vendor URL http://java.oracle.com/  
Java Home C:\ColdFusion10\jre  
Java File Encoding Cp1252  
Java Default Locale en_US  
File Separator \  
Path Separator ;  
Line Separator Chr(13)  
User Name F1610-7X7HKX1-L$  
User Home C:\  
User Dir C:\ColdFusion10\cfusion\bin  
Java VM Specification Version 1.7  
Java VM Specification Vendor Oracle Corporation  
Java VM Specification Name Java Virtual Machine Specification  
Java VM Version 23.7-b01  
Java VM Vendor Oracle Corporation  
Java VM Name Java HotSpot(TM) 64-Bit Server VM  
Java Specification Version 1.7  
Java Specification Vendor Oracle Corporation  
Java Specification Name Java Platform API Specification  
Java Class Version 51.0  
CF Server Java Class Path ;C:/ColdFusion10/cfusion/lib/updates/chf10000011.jar;  C:/ColdFusion10/cfusion/lib/ant-launcher.jar;  C:/ColdFusion10/cfusion/lib/ant.jar;  C:/ColdFusion10/cfusion/lib/antlr-2.7.6.jar;  C:/ColdFusion10/cfusion/lib/apache-solr-core.jar;  C:/ColdFusion10/cfusion/lib/apache-solr-solrj.jar;  C:/ColdFusion10/cfusion/lib/asm-all-3.1.jar;  C:/ColdFusion10/cfusion/lib/asn1.jar;  C:/ColdFusion10/cfusion/lib/axis.jar;  C:/ColdFusion10/cfusion/lib/backport-util-concurrent.jar;  C:/ColdFusion10/cfusion/lib/bcel-5.1-jnbridge.jar;  C:/ColdFusion10/cfusion/lib/bcel.jar;  C:/ColdFusion10/cfusion/lib/bcmail-jdk14-139.jar;  C:/ColdFusion10/cfusion/lib/bcprov-jdk14-139.jar;  C:/ColdFusion10/cfusion/lib/cdo.jar;  C:/ColdFusion10/cfusion/lib/cdohost.jar;  C:/ColdFusion10/cfusion/lib/certj.jar;  C:/ColdFusion10/cfusion/lib/cf-acrobat.jar;  C:/ColdFusion10/cfusion/lib/cf-assembler.jar;  C:/ColdFusion10/cfusion/lib/cf-logging.jar;  C:/ColdFusion10/cfusion/lib/cf4was.jar;  C:/ColdFusion10/cfusion/lib/cf4was_ae.jar;  C:/ColdFusion10/cfusion/lib/cfusion-req.jar;  C:/ColdFusion10/cfusion/lib/cfusion.jar;  C:/ColdFusion10/cfusion/lib/chart.jar;  C:/ColdFusion10/cfusion/lib/clibwrapper_jiio.jar;  C:/ColdFusion10/cfusion/lib/commons-beanutils-1.8.0.jar;  C:/ColdFusion10/cfusion/lib/commons-codec-1.3.jar;  C:/ColdFusion10/cfusion/lib/commons-collections-3.2.1.jar;  C:/ColdFusion10/cfusion/lib/commons-compress-1.0.jar;  C:/ColdFusion10/cfusion/lib/commons-digester-2.0.jar;  C:/ColdFusion10/cfusion/lib/commons-discovery-0.4.jar;  C:/ColdFusion10/cfusion/lib/commons-httpclient-3.1.jar;  C:/ColdFusion10/cfusion/lib/commons-lang-2.4.jar;  C:/ColdFusion10/cfusion/lib/commons-logging-1.1.1.jar;  C:/ColdFusion10/cfusion/lib/commons-logging-api-1.1.1.jar;  C:/ColdFusion10/cfusion/lib/commons-net-3.0.1.jar;  C:/ColdFusion10/cfusion/lib/commons-vfs2-2.0.jar;  C:/ColdFusion10/cfusion/lib/crystal.jar;  C:/ColdFusion10/cfusion/lib/derby.jar;  C:/ColdFusion10/cfusion/lib/derbyclient.jar;  C:/ColdFusion10/cfusion/lib/derbynet.jar;  C:/ColdFusion10/cfusion/lib/derbyrun.jar;  C:/ColdFusion10/cfusion/lib/derbytools.jar;  C:/ColdFusion10/cfusion/lib/dom4j-1.6.1.jar;  C:/ColdFusion10/cfusion/lib/dpHibernate.jar;  C:/ColdFusion10/cfusion/lib/ehcache-core-2.5.1.jar;  C:/ColdFusion10/cfusion/lib/ehcache-web-2.0.4.jar;  C:/ColdFusion10/cfusion/lib/esapi-2.0.1.jar;  C:/ColdFusion10/cfusion/lib/EWSAPI-1.1.5.jar;  C:/ColdFusion10/cfusion/lib/FCSj.jar;  C:/ColdFusion10/cfusion/lib/flashgateway.jar;  C:/ColdFusion10/cfusion/lib/flex-messaging-common.jar;  C:/ColdFusion10/cfusion/lib/flex-messaging-core.jar;  C:/ColdFusion10/cfusion/lib/flex-messaging-opt.jar;  C:/ColdFusion10/cfusion/lib/flex-messaging-proxy.jar;  C:/ColdFusion10/cfusion/lib/flex-messaging-remoting.jar;  C:/ColdFusion10/cfusion/lib/flex-rds-server.jar;  C:/ColdFusion10/cfusion/lib/geronimo-stax-api_1.0_spec-1.0.1.jar;  C:/ColdFusion10/cfusion/lib/hibernate3.jar;  C:/ColdFusion10/cfusion/lib/httpclient-4.1.1.jar;  C:/ColdFusion10/cfusion/lib/httpclient-cache-4.1.1.jar;  C:/ColdFusion10/cfusion/lib/httpclient.jar;  C:/ColdFusion10/cfusion/lib/httpcore_4.1.2.jar;  C:/ColdFusion10/cfusion/lib/httpmime-4.1.1.jar;  C:/ColdFusion10/cfusion/lib/ib6addonpatch.jar;  C:/ColdFusion10/cfusion/lib/ib6core.jar;  C:/ColdFusion10/cfusion/lib/ib6http.jar;  C:/ColdFusion10/cfusion/lib/ib6swing.jar;  C:/ColdFusion10/cfusion/lib/ib6util.jar;  C:/ColdFusion10/cfusion/lib/im.jar;  C:/ColdFusion10/cfusion/lib/iText.jar;  C:/ColdFusion10/cfusion/lib/iTextAsian.jar;  C:/ColdFusion10/cfusion/lib/izmado.jar;  C:/ColdFusion10/cfusion/lib/jai_codec.jar;  C:/ColdFusion10/cfusion/lib/jai_core.jar;  C:/ColdFusion10/cfusion/lib/jai_imageio.jar;  C:/ColdFusion10/cfusion/lib/jakarta-oro-2.0.6.jar;  C:/ColdFusion10/cfusion/lib/jakarta-slide-webdavlib-2.1.jar;  C:/ColdFusion10/cfusion/lib/java-xmlbuilder-0.4.jar;  C:/ColdFusion10/cfusion/lib/javasysmon-0.3.3.jar;  C:/ColdFusion10/cfusion/lib/jax-qname.jar;  C:/ColdFusion10/cfusion/lib/jaxb-api.jar;  C:/ColdFusion10/cfusion/lib/jaxb-impl.jar;  C:/ColdFusion10/cfusion/lib/jaxb-libs.jar;  C:/ColdFusion10/cfusion/lib/jaxb-xjc.jar;  C:/ColdFusion10/cfusion/lib/jaxrpc.jar;  C:/ColdFusion10/cfusion/lib/jcifs-1.3.15.jar;  C:/ColdFusion10/cfusion/lib/jdom.jar;  C:/ColdFusion10/cfusion/lib/jeb.jar;  C:/ColdFusion10/cfusion/lib/jersey-core.jar;  C:/ColdFusion10/cfusion/lib/jersey-server.jar;  C:/ColdFusion10/cfusion/lib/jersey-servlet.jar;  C:/ColdFusion10/cfusion/lib/jets3t-0.8.1.jar;  C:/ColdFusion10/cfusion/lib/jetty-continuation-7.0.0.v20091005.jar;  C:/ColdFusion10/cfusion/lib/jetty-http-7.0.0.v20091005.jar;  C:/ColdFusion10/cfusion/lib/jetty-io-7.0.0.v20091005.jar;  C:/ColdFusion10/cfusion/lib/jetty-security-7.0.0.v20091005.jar;  C:/ColdFusion10/cfusion/lib/jetty-server-7.0.0.v20091005.jar;  C:/ColdFusion10/cfusion/lib/jetty-servlet-7.0.0.v20091005.jar;  C:/ColdFusion10/cfusion/lib/jetty-servlets-7.0.0.v20091005.jar;  C:/ColdFusion10/cfusion/lib/jetty-util-7.0.0.v20091005.jar;  C:/ColdFusion10/cfusion/lib/jetty-xml-7.0.0.v20091005.jar;  C:/ColdFusion10/cfusion/lib/jintegra.jar;  C:/ColdFusion10/cfusion/lib/jnbcore.jar;  C:/ColdFusion10/cfusion/lib/jpedal.jar;  C:/ColdFusion10/cfusion/lib/js.jar;  C:/ColdFusion10/cfusion/lib/jsch-0.1.44m.jar;  C:/ColdFusion10/cfusion/lib/jsr107cache.jar;  C:/ColdFusion10/cfusion/lib/jsr311-api-1.1.1.jar;  C:/ColdFusion10/cfusion/lib/jta.jar;  C:/ColdFusion10/cfusion/lib/jutf7-0.9.0.jar;  C:/ColdFusion10/cfusion/lib/ldap.jar;  C:/ColdFusion10/cfusion/lib/ldapbp.jar;  C:/ColdFusion10/cfusion/lib/log4j-1.2.15.jar;  C:/ColdFusion10/cfusion/lib/lucene-analyzers-3.4.0.jar;  C:/ColdFusion10/cfusion/lib/lucene-core-3.4.0.jar;  C:/ColdFusion10/cfusion/lib/lucene-highlighter-3.4.0.jar;  C:/ColdFusion10/cfusion/lib/lucene-memory-3.4.0.jar;  C:/ColdFusion10/cfusion/lib/lucenedemo.jar;  C:/ColdFusion10/cfusion/lib/macromedia_drivers.jar;  C:/ColdFusion10/cfusion/lib/mail.jar;  C:/ColdFusion10/cfusion/lib/metadata-extractor-2.4.0-beta-1.jar;  C:/ColdFusion10/cfusion/lib/mlibwrapper_jai.jar;  C:/ColdFusion10/cfusion/lib/msapps.jar;  C:/ColdFusion10/cfusion/lib/mysql-connector-java-commercial-5.1.17-bin.jar;  C:/ColdFusion10/cfusion/lib/namespace.jar;  C:/ColdFusion10/cfusion/lib/nekohtml.jar;  C:/ColdFusion10/cfusion/lib/netty-3.2.5.Final.jar;  C:/ColdFusion10/cfusion/lib/ooxml-schemas.jar;  C:/ColdFusion10/cfusion/lib/pdfencryption.jar;  C:/ColdFusion10/cfusion/lib/poi-contrib.jar;  C:/ColdFusion10/cfusion/lib/poi-ooxml-schemas.jar;  C:/ColdFusion10/cfusion/lib/poi-ooxml.jar;  C:/ColdFusion10/cfusion/lib/poi-scratchpad.jar;  C:/ColdFusion10/cfusion/lib/poi.jar;  C:/ColdFusion10/cfusion/lib/portlet_20.jar;  C:/ColdFusion10/cfusion/lib/postgresql-8.3-604.jdbc3.jar;  C:/ColdFusion10/cfusion/lib/quartz.jar;  C:/ColdFusion10/cfusion/lib/relaxngDatatype.jar;  C:/ColdFusion10/cfusion/lib/ri_generic.jar;  C:/ColdFusion10/cfusion/lib/rome-cf.jar;  C:/ColdFusion10/cfusion/lib/saaj.jar;  C:/ColdFusion10/cfusion/lib/saxon9he.jar;  C:/ColdFusion10/cfusion/lib/serializer.jar;  C:/ColdFusion10/cfusion/lib/slf4j-api-1.5.6.jar;  C:/ColdFusion10/cfusion/lib/slf4j-log4j12-1.5.6.jar;  C:/ColdFusion10/cfusion/lib/smack.jar;  C:/ColdFusion10/cfusion/lib/smpp.jar;  C:/ColdFusion10/cfusion/lib/STComm.jar;  C:/ColdFusion10/cfusion/lib/tagsoup-1.2.jar;  C:/ColdFusion10/cfusion/lib/tika-core-0.6.jar;  C:/ColdFusion10/cfusion/lib/tika-parsers-0.6.jar;  C:/ColdFusion10/cfusion/lib/tools.jar;  C:/ColdFusion10/cfusion/lib/tt-bytecode.jar;  C:/ColdFusion10/cfusion/lib/wc50.jar;  C:/ColdFusion10/cfusion/lib/webchartsJava2D.jar;  C:/ColdFusion10/cfusion/lib/wsdl4j-1.6.2.jar;  C:/ColdFusion10/cfusion/lib/wsrp4j-commons-0.5-SNAPSHOT.jar;  C:/ColdFusion10/cfusion/lib/wsrp4j-producer.jar;  C:/ColdFusion10/cfusion/lib/xalan.jar;  C:/ColdFusion10/cfusion/lib/xercesImpl.jar;  C:/ColdFusion10/cfusion/lib/xml-apis.jar;  C:/ColdFusion10/cfusion/lib/xmlbeans-2.3.0.jar;  C:/ColdFusion10/cfusion/lib/xmpcore.jar;  C:/ColdFusion10/cfusion/lib/xsdlib.jar;  C:/ColdFusion10/cfusion/lib/;  C:/ColdFusion10/cfusion/lib/axis2/axiom-api-1.2.13.jar;  C:/ColdFusion10/cfusion/lib/axis2/axiom-dom-1.2.13.jar;  C:/ColdFusion10/cfusion/lib/axis2/axiom-impl-1.2.13.jar;  C:/ColdFusion10/cfusion/lib/axis2/axis2-adb-1.7.0.jar;  C:/ColdFusion10/cfusion/lib/axis2/axis2-adb-codegen-1.7.0.jar;  C:/ColdFusion10/cfusion/lib/axis2/axis2-codegen-1.7.0.jar;  C:/ColdFusion10/cfusion/lib/axis2/axis2-jaxws-1.7.0.jar;  C:/ColdFusion10/cfusion/lib/axis2/axis2-kernel-1.7.0.jar;  C:/ColdFusion10/cfusion/lib/axis2/axis2-transport-http-1.7.0.jar;  C:/ColdFusion10/cfusion/lib/axis2/axis2-transport-local-1.7.0.jar;  C:/ColdFusion10/cfusion/lib/axis2/commons-fileupload-1.2.jar;  C:/ColdFusion10/cfusion/lib/axis2/commons-io-1.4.jar;  C:/ColdFusion10/cfusion/lib/axis2/geronimo-ws-metadata_2.0_spec-1.1.2.jar;  C:/ColdFusion10/cfusion/lib/axis2/httpcore-4.0.jar;  C:/ColdFusion10/cfusion/lib/axis2/neethi-3.0.2.jar;  C:/ColdFusion10/cfusion/lib/axis2/woden-api-1.0.jar;  C:/ColdFusion10/cfusion/lib/axis2/woden-impl-commons-1.0.jar;  C:/ColdFusion10/cfusion/lib/axis2/woden-impl-dom-1.0.jar;  C:/ColdFusion10/cfusion/lib/axis2/wsdl4j-1.6.2.jar;  C:/ColdFusion10/cfusion/lib/axis2/wstx-asl-3.2.9.jar;  C:/ColdFusion10/cfusion/lib/axis2/XmlSchema-1.4.8.jar;  C:/ColdFusion10/cfusion/lib/axis2/;  C:/ColdFusion10/cfusion/gateway/lib/examples.jar;  C:/ColdFusion10/cfusion/gateway/lib/;  C:/ColdFusion10/cfusion/wwwroot/WEB-INF/flex/jars/cfgatewayadapter.jar;  C:/ColdFusion10/cfusion/wwwroot/WEB-INF/flex/jars/concurrent.jar;  C:/ColdFusion10/cfusion/wwwroot/WEB-INF/flex/jars/;  C:/ColdFusion10/cfusion/wwwroot/WEB-INF/cfform/jars/batik-awt-util.jar;  C:/ColdFusion10/cfusion/wwwroot/WEB-INF/cfform/jars/batik-css.jar;  C:/ColdFusion10/cfusion/wwwroot/WEB-INF/cfform/jars/batik-ext.jar;  C:/ColdFusion10/cfusion/wwwroot/WEB-INF/cfform/jars/batik-transcoder.jar;  C:/ColdFusion10/cfusion/wwwroot/WEB-INF/cfform/jars/batik-util.jar;  C:/ColdFusion10/cfusion/wwwroot/WEB-INF/cfform/jars/commons-discovery.jar;  C:/ColdFusion10/cfusion/wwwroot/WEB-INF/cfform/jars/commons-logging.jar;  C:/ColdFusion10/cfusion/wwwroot/WEB-INF/cfform/jars/concurrent.jar;  C:/ColdFusion10/cfusion/wwwroot/WEB-INF/cfform/jars/flex.jar;  C:/ColdFusion10/cfusion/wwwroot/WEB-INF/cfform/jars/jakarta-oro-2.0.7.jar;  C:/ColdFusion10/cfusion/wwwroot/WEB-INF/cfform/jars/jcert.jar;  C:/ColdFusion10/cfusion/wwwroot/WEB-INF/cfform/jars/jnet.jar;  C:/ColdFusion10/cfusion/wwwroot/WEB-INF/cfform/jars/jsse.jar;  C:/ColdFusion10/cfusion/wwwroot/WEB-INF/cfform/jars/oscache.jar;  C:/ColdFusion10/cfusion/wwwroot/WEB-INF/cfform/jars/;  
Java Class Path C:\\ColdFusion10\\cfusion\lib\oosdk\lib;
C:\\ColdFusion10\\cfusion\lib\oosdk\classes;
C:\ColdFusion10\cfusion\bin\..\runtime\bin\tomcat-juli.jar;
C:\ColdFusion10\cfusion\bin\cf-bootstrap.jar  
Java Ext Dirs C:\ColdFusion10\jre\lib\ext;C:\Windows\Sun\Java\lib\ext  
This topic has been closed for replies.

1 reply

jbdobsAuthor
Participating Frequently
September 26, 2013

Is anyone able to reproduce this problem?  I'm kinda stuck

Thanks for any help...

John

EddieLotter
Inspiring
September 26, 2013

I'm using CF9 so running the test won't help you in your environment.

However, I am interested to know if you opened the good and the bad xlsx files in a plain text editor and compared them? Is CF10 emitting bad XML in the bad file?

WolfShade
Legend
February 27, 2018

WolfShade I realize it's been a few years now but, can you please share any tips on how you achieved working with creating Excel files containing 1M records that were formatted and without issues? Did you find any impact of how you formatted cells (as you construct the rows vs after the sheet was filled with data) and in which order? What heap memory settings and other config/admin settings did you find were necessary?

Any such tips would be much appreciated!


Hi, Bruce,

In my personal development environment, I am running CF 10 on a Windows 2008 R2 server with 16M of RAM.  CFAdmin shows that the min/max buffer is set to 4096/8192.  Apache 2.4 running on an Ubuntu Server VM, I forget what those settings are.

Here are all the limits of a single Excel file, as you can see the max number of rows is just a hair over 1M.  I admit, I was kind of exaggerating the 1M rows, but not about the >100Mb files.

Formatting the cells using the process I used (SpreadsheetNew() and manually inserting values, not just throwing a database query at a cfspreadsheet tag) I discovered that you have to format the cells both before and after the data has populated the cells.  I'm not sure why, but especially if you are using large numbers but need them to display as string in a TEXT cell.

HTH,

^ _ ^