14 Replies Latest reply on Jul 30, 2015 7:46 AM by WolfShade

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

    jbdobs

      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:

       

      Excel Error.png

       

      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  
        • 1. Re: Is there a limit to the size of a formatted xlsx spreadsheet created by ColdFusion?
          jbdobs Level 1

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

           

          Thanks for any help...

           

          John

          • 2. Re: Is there a limit to the size of a formatted xlsx spreadsheet created by ColdFusion?
            EddieLotter Level 3

            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?

            • 3. Re: Is there a limit to the size of a formatted xlsx spreadsheet created by ColdFusion?
              jbdobs Level 1

              Eddie, thanks for your reply.  Since they're binary files, opening them in a text editor (e.g., Notepad) doesn't help much... the files are unreadable.  Is there another way to examine the binary contents and view the XML?

               

              As for testing with CF9, please do!  If this is a CF bug, chances are probably better it was also in CF9 vs being a new bug introduced in CF10.  I'd love to know if you're able to reproduce the problem (including whether you have to modify the maxRows value to reproduce the failure).

               

              Thanks again,

               

              John

              • 4. Re: Is there a limit to the size of a formatted xlsx spreadsheet created by ColdFusion?
                EddieLotter Level 3

                Rename bigSheetTest.xlsx to bigSheetTest.xlsx.zip and then extract it.

                 

                I also noticed you're writing to the root of the C: drive. You should only write to a folder that is guaranteed to be accessible to the CF server.

                 

                I will test CF9 when I get a chance and let you know what I find.

                 

                • 5. Re: Is there a limit to the size of a formatted xlsx spreadsheet created by ColdFusion?
                  jbdobs Level 1

                  Eddie, yes, I'm aware of the potential issue writing to the root.  For now, the test code is running on my laptop and this isn't an issue.  The production code will be writing to the tempdir.

                   

                  Just ran some math on the good/bad spreadsheets.  Both sheets have 90 columns.  The good spreadsheet has a total of 727 rows (including the headers) and the bad sheet has 728 rows.  This works out to a total of 65,430 and 65,520 cells, respectively.  Given this number is close to 65,636 (256^2), is it possible I'm running into some sort of Excel limit?

                   

                  According to the Excel Specifications and Limits, the number of "Unique cell formats/cell styles" is limited to 64,000.  However, I'm not sure what constitutes "unique".  I have over 64,000 cells with formatting, but I only have four different format specifications defined.

                  • 6. Re: Is there a limit to the size of a formatted xlsx spreadsheet created by ColdFusion?
                    EddieLotter Level 3

                    I'm afraid I am not an expert in Excel, so I am not familiar with its limitations. What you could do is create a macro in Excelto replicate what you're doing in CF. If the macro fails then the problem is with Excel. If the macro succeeds then the problem is with CF.

                     

                    Did you do the comparison after unpacking the file as I mentioned in my previous reply?

                    • 7. Re: Is there a limit to the size of a formatted xlsx spreadsheet created by ColdFusion?
                      jbdobs Level 1

                      Eddie, thanks for the tip on viewing the Excel XML data. I've been trying to do the comparison but the data is just too large to format in a timely manner, and I need to get it formatted so my compare tool can weed out the differences.

                       

                      However, I was able to determine (simply by viewing the XML files) that CF is creating and applying a separate style for each cell, even though I've only defined four styles.  It does not appear that CF is creating a single instance of a style in the spreadsheet and then applying that style to the cells that use it.  I'm pretty sure this is the source of the problem... I'm probably hitting the limit on the number of styles supported in Excel.

                       

                      I'm going to see if there's a way to reduce the problem (for example, but applying styles to ranges wherever possible instead of to individual cells).

                       

                      Does anyone know if there's a way to define Excel styles in CF and then apply those styles, instead of having CF create new styles every time you apply formatting?

                      • 8. Re: Is there a limit to the size of a formatted xlsx spreadsheet created by ColdFusion?
                        jbdobs Level 1

                        Well, I checked my code and I'm at a loss.  My code uses SpreadSheetFormatCellRange to format the values in columns 1 and 2, 4 and 5, etc. using the "formatNumber" format structure:

                         

                        SpreadSheetFormatCellRange(xls, formatNumber, row, col, row, col + 1);

                         

                        I'd expect (at worst) that a single style would be defined and then applied to columns 1 and 2, or (at best) a single style would be defined for all cells that use the "formatNumber" format.  However, check out the XML for row 4 of my output spreadsheet:

                         

                         

                         

                            <row r="4">

                              <c r="A4" t="n" s="181">

                                <v>0.25</v>

                              </c>

                              <c r="B4" t="n" s="182">

                                <v>0.2</v>

                              </c>

                              <c r="C4" t="n" s="183">

                                <v>0.166666666667</v>

                              </c>

                              <c r="D4" t="n" s="184">

                                <v>1.0</v>

                              </c>

                              <c r="E4" t="n" s="185">

                                <v>0.8</v>

                              </c>

                              <c r="F4" t="n" s="186">

                                <v>0.666666666667</v>

                              </c>

                              <c r="G4" t="n" s="187">

                                <v>1.75</v>

                              </c>

                              <c r="H4" t="n" s="188">

                                <v>1.4</v>

                              </c>

                              <c r="I4" t="n" s="189">

                                <v>1.16666666667</v>

                              </c>

                              <c r="J4" t="n" s="190">

                                <v>2.5</v>

                              </c>

                              <c r="K4" t="n" s="191">

                                <v>2.0</v>

                              </c>

                              <c r="L4" t="n" s="192">

                                <v>1.66666666667</v>

                              </c>

                              <c r="M4" t="n" s="193">

                                <v>3.25</v>

                              </c>

                              <c r="N4" t="n" s="194">

                                <v>2.6</v>

                              </c>

                              <c r="O4" t="n" s="195">

                                <v>2.16666666667</v>

                              </c>

                              <c r="P4" t="n" s="196">

                                <v>4.0</v>

                              </c>

                              <c r="Q4" t="n" s="197">

                                <v>3.2</v>

                              </c>

                              <c r="R4" t="n" s="198">

                                <v>2.66666666667</v>

                              </c>

                              <c r="S4" t="n" s="199">

                                <v>4.75</v>

                              </c>

                              <c r="T4" t="n" s="200">

                                <v>3.8</v>

                              </c>

                              <c r="U4" t="n" s="201">

                                <v>3.16666666667</v>

                              </c>

                              <c r="V4" t="n" s="202">

                                <v>5.5</v>

                              </c>

                              <c r="W4" t="n" s="203">

                                <v>4.4</v>

                              </c>

                              <c r="X4" t="n" s="204">

                                <v>3.66666666667</v>

                              </c>

                              <c r="Y4" t="n" s="205">

                                <v>6.25</v>

                              </c>

                              <c r="Z4" t="n" s="206">

                                <v>5.0</v>

                              </c>

                              <c r="AA4" t="n" s="207">

                                <v>4.16666666667</v>

                              </c>

                              <c r="AB4" t="n" s="208">

                                <v>7.0</v>

                              </c>

                              <c r="AC4" t="n" s="209">

                                <v>5.6</v>

                              </c>

                              <c r="AD4" t="n" s="210">

                                <v>4.66666666667</v>

                              </c>

                              <c r="AE4" t="n" s="211">

                                <v>7.75</v>

                              </c>

                              <c r="AF4" t="n" s="212">

                                <v>6.2</v>

                              </c>

                              <c r="AG4" t="n" s="213">

                                <v>5.16666666667</v>

                              </c>

                              <c r="AH4" t="n" s="214">

                                <v>8.5</v>

                              </c>

                              <c r="AI4" t="n" s="215">

                                <v>6.8</v>

                              </c>

                              <c r="AJ4" t="n" s="216">

                                <v>5.66666666667</v>

                              </c>

                              <c r="AK4" t="n" s="217">

                                <v>9.25</v>

                              </c>

                              <c r="AL4" t="n" s="218">

                                <v>7.4</v>

                              </c>

                              <c r="AM4" t="n" s="219">

                                <v>6.16666666667</v>

                              </c>

                              <c r="AN4" t="n" s="220">

                                <v>10.0</v>

                              </c>

                              <c r="AO4" t="n" s="221">

                                <v>8.0</v>

                              </c>

                              <c r="AP4" t="n" s="222">

                                <v>6.66666666667</v>

                              </c>

                              <c r="AQ4" t="n" s="223">

                                <v>10.75</v>

                              </c>

                              <c r="AR4" t="n" s="224">

                                <v>8.6</v>

                              </c>

                              <c r="AS4" t="n" s="225">

                                <v>7.16666666667</v>

                              </c>

                              <c r="AT4" t="n" s="226">

                                <v>11.5</v>

                              </c>

                              <c r="AU4" t="n" s="227">

                                <v>9.2</v>

                              </c>

                              <c r="AV4" t="n" s="228">

                                <v>7.66666666667</v>

                              </c>

                              <c r="AW4" t="n" s="229">

                                <v>12.25</v>

                              </c>

                              <c r="AX4" t="n" s="230">

                                <v>9.8</v>

                              </c>

                              <c r="AY4" t="n" s="231">

                                <v>8.16666666667</v>

                              </c>

                              <c r="AZ4" t="n" s="232">

                                <v>13.0</v>

                              </c>

                              <c r="BA4" t="n" s="233">

                                <v>10.4</v>

                              </c>

                              <c r="BB4" t="n" s="234">

                                <v>8.66666666667</v>

                              </c>

                              <c r="BC4" t="n" s="235">

                                <v>13.75</v>

                              </c>

                              <c r="BD4" t="n" s="236">

                                <v>11.0</v>

                              </c>

                              <c r="BE4" t="n" s="237">

                                <v>9.16666666667</v>

                              </c>

                              <c r="BF4" t="n" s="238">

                                <v>14.5</v>

                              </c>

                              <c r="BG4" t="n" s="239">

                                <v>11.6</v>

                              </c>

                              <c r="BH4" t="n" s="240">

                                <v>9.66666666667</v>

                              </c>

                              <c r="BI4" t="n" s="241">

                                <v>15.25</v>

                              </c>

                              <c r="BJ4" t="n" s="242">

                                <v>12.2</v>

                              </c>

                              <c r="BK4" t="n" s="243">

                                <v>10.1666666667</v>

                              </c>

                              <c r="BL4" t="n" s="244">

                                <v>16.0</v>

                              </c>

                              <c r="BM4" t="n" s="245">

                                <v>12.8</v>

                              </c>

                              <c r="BN4" t="n" s="246">

                                <v>10.6666666667</v>

                              </c>

                              <c r="BO4" t="n" s="247">

                                <v>16.75</v>

                              </c>

                              <c r="BP4" t="n" s="248">

                                <v>13.4</v>

                              </c>

                              <c r="BQ4" t="n" s="249">

                                <v>11.1666666667</v>

                              </c>

                              <c r="BR4" t="n" s="250">

                                <v>17.5</v>

                              </c>

                              <c r="BS4" t="n" s="251">

                                <v>14.0</v>

                              </c>

                              <c r="BT4" t="n" s="252">

                                <v>11.6666666667</v>

                              </c>

                              <c r="BU4" t="n" s="253">

                                <v>18.25</v>

                              </c>

                              <c r="BV4" t="n" s="254">

                                <v>14.6</v>

                              </c>

                              <c r="BW4" t="n" s="255">

                                <v>12.1666666667</v>

                              </c>

                              <c r="BX4" t="n" s="256">

                                <v>19.0</v>

                              </c>

                              <c r="BY4" t="n" s="257">

                                <v>15.2</v>

                              </c>

                              <c r="BZ4" t="n" s="258">

                                <v>12.6666666667</v>

                              </c>

                              <c r="CA4" t="n" s="259">

                                <v>19.75</v>

                              </c>

                              <c r="CB4" t="n" s="260">

                                <v>15.8</v>

                              </c>

                              <c r="CC4" t="n" s="261">

                                <v>13.1666666667</v>

                              </c>

                              <c r="CD4" t="n" s="262">

                                <v>20.5</v>

                              </c>

                              <c r="CE4" t="n" s="263">

                                <v>16.4</v>

                              </c>

                              <c r="CF4" t="n" s="264">

                                <v>13.6666666667</v>

                              </c>

                              <c r="CG4" t="n" s="265">

                                <v>21.25</v>

                              </c>

                              <c r="CH4" t="n" s="266">

                                <v>17.0</v>

                              </c>

                              <c r="CI4" t="n" s="267">

                                <v>14.1666666667</v>

                              </c>

                              <c r="CJ4" t="n" s="268">

                                <v>22.0</v>

                              </c>

                              <c r="CK4" t="n" s="269">

                                <v>17.6</v>

                              </c>

                              <c r="CL4" t="n" s="270">

                                <v>14.6666666667</v>

                              </c>

                            </row>

                         

                        As you can see, separate styles are being created and assigned to EVERY CELL in my sheet, even those cells formatted using SpreadSheetFormatCellRange.  I'm fairly confident now that I'm hitting the Excel limit for the number of styles.  How can I define a style up front and then apply it?  I really only have four styles, but the code is generating over 64,000!

                        • 9. Re: Is there a limit to the size of a formatted xlsx spreadsheet created by ColdFusion?
                          jbdobs Level 1

                          I've opened up Bug 3640428 for this problem.  Hoping for a fix!!!

                          • 10. Re: Re: Is there a limit to the size of a formatted xlsx spreadsheet created by ColdFusion?
                            WolfShade Level 4

                            For anyone else who gets this same error msg.. I just discovered that having debug output turned on will also trigger that error msg.

                             

                            V/r,

                             

                            ^_^

                            • 11. Re: Is there a limit to the size of a formatted xlsx spreadsheet created by ColdFusion?
                              MarcusSKJ

                              Hello there,

                               

                              alltough this thread is rather old, I want to ask something about it.

                               

                              Currently I have the same probelm, that my Excel sheet gets "corrupted" if a certain number of rows is reached, actually it's about 2900 rows in my case.

                               

                              I used spreadsheetformatColumn to format certain columns to be currency, number and so on.

                              Before I use spreadSheetFormatRow to set the Background Color of the whole row to "dark Grey".

                              I found the bug and changed my code to use SpreadSheetFormatCellRange instead of spreadsheetformatColumn and spreadsheetformatRow, hopeing, my issue would be fixed with this.

                               

                              my code is now:

                               

                               

                              public any function formatColumn (string formatName, string col = "1") {

                               

                              try {

                               

                              spreadsheetFormatCellRange(VARIABLES.myXLSObj, "#structFind(VARIABLES.strctFormat, ARGUMENTS.formatName)#", 1, ARGUMENTS.col, VARIABLES.maxRows, ARGUMENTS.col);

                               

                              return true;

                               

                              catch (any e) {

                               

                              return e.message;

                              }

                              }

                               

                               

                               

                               

                              public any function formatRow (string formatName, string row = "1") {

                               

                              try {

                               

                              var tempRow = ARGUMENTS.row;

                               

                              spreadsheetFormatCellRange(VARIABLES.myXLSObj, "#structFind(VARIABLES.strctFormat, ARGUMENTS.formatName)#", tempRow, 1, tempRow, evaluate("VARIABLES.maxCols_#VARIABLES.sheetCount#"));

                               

                              return true;

                               

                              catch (any e) {

                               

                              return e.message;

                                  }

                              }

                               

                               

                               

                               

                               

                              Then I first Format each (second) row to be dark Grey in Background.

                              At last I Format the columns as follows:

                               

                              <cfset excelCellsUnit="4,6,8,">

                               

                              <cfset excelCellsAmount="3,5,7">

                               

                              <cfset excelCellsPercent="9,10,12,14,16,11,13,15">

                               

                               

                              <cfloop list="#excelCellsUnit#" index="CellUnit" delimiters=",">

                               

                              <cfset excelGenerator.formatColumn("Unit","#CellUnit#")>

                               

                              </cfloop>

                               

                              <cfloop list="#excelCellsAmount#" index="CellAmount" delimiters=",">

                               

                              <cfset excelGenerator.formatColumn("Currency","#CellAmount#")>

                               

                              </cfloop>

                               

                              At approx. 2900 rows there are too many style elements in the XML - so I guess - and Excel wont load any formatting at all.

                              Ist there any fault in my code? If I try it with less rows, it looks as expected. Pherhaps there is another way of doing the stuff I wanna do. ;-)

                               

                               

                              My System is as follows:

                               

                              Server Product ColdFusion

                              Version ColdFusion 10,283922

                              Edition Developer

                              Operating System Windows 8

                              OS Version 6.2

                              Update Level  /C:/ColdFusion10/cfusion/lib/updates/chf10000016.jar  

                               

                              Is the bugfix for the BUG #3640428 already contanied in my Version?

                               

                              If not, how can I get the bugfix?

                               

                              Is there any other way to get out of my Situation?

                               

                              Thanks a lot and Kind regards

                               

                              Marcus

                              • 12. Re: Is there a limit to the size of a formatted xlsx spreadsheet created by ColdFusion?
                                WolfShade Level 4

                                If you are getting the same error message that the OP is/was getting (OP provided image of error message), then open the .xlsx file in NotePad or WordPad or Write.  You should be able to determine, fairly quickly, what is breaking the file.

                                 

                                HTH,

                                 

                                ^_^

                                • 13. Re: Is there a limit to the size of a formatted xlsx spreadsheet created by ColdFusion?
                                  MarcusSKJ Level 1

                                  Hello,

                                   

                                  thanks for your reply. I think, it is really clear, that I have the same problem as jbobs reported here, thats not the question.

                                  If I examine the xlsx after extracting as zip, I find a style.xml with > 3 MB, in my full version more than 9 MB! So it is very clear, that there are to many styles.

                                   

                                  The important question is: Is there a bugfix available for CF 10? The bugfix is state "fixed" but there is not mentioned, on which realease the bugfix is in. So wth I could get that bugfix?! Ugrading to CF 11? Thats not an option currently because my company just upgraded from Cf 9 to Cf 10 recently.

                                   

                                  Thanks a lot for help in advance.

                                   

                                  Marcus

                                  • 14. Re: Is there a limit to the size of a formatted xlsx spreadsheet created by ColdFusion?
                                    WolfShade Level 4

                                    As I understand it, the only limit for any file size is related to the buffer assigned in CFAdmin and the buffer assigned to the web server (apache, IIS, etc.), whichever is smaller.

                                     

                                    I have worked with PDF files that are greater than 120Mb with no issues.  I have worked with Excel files that have well over 1,000,000 records, and formatted all the cells, with no issues.

                                     

                                    The unreadable content error message _could_ be a result of debugging information getting into the file, or something else breaking the formatting; or it could be due to the file being so large that it's truncated, which could break the readability.

                                     

                                    Check the memory buffers of CFAdmin and whichever web server you're using.  If you can bump up the buffers, see if that makes any difference.

                                     

                                    I have never used "maxRows" in my Spreadsheet code.

                                     

                                    V/r,

                                     

                                    ^_^