24 Replies Latest reply on Feb 28, 2018 11:55 AM by bruceh3555

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

    jbdobs Level 1

      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 4

            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 4

                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 4

                    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,

                                     

                                    ^_^

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

                                      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!

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

                                        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,

                                         

                                        ^ _ ^

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

                                          WolfShade Thanks for your prompt response. I'm running CF2016. Do you have any estimate of how many rows it can create in an excel .xlsx formatted sheet? I'm hoping it can handle 100's of thousdands at least. If you have an idea of a size you recall trying, please let me know approx. record count.

                                           

                                          For formatting cells, my need is in some cases to dynamically (on a per cell basis) format the background color at least which is best handled in the row by row loop that is used to construct the data set. There is some rows/columns of the data set that will also need formatting (bold for some columns, underline for a row, etc.) I assume these formatting during and after the build won't be an issue?

                                           

                                          As for datatype I've found that when specifying datatype with the SpreadSheetAddRows (and using a query) that Numeric type does not work for me. It results in numeric values getting changed to Text in the cell and left justified. Have you seen this happen for you? I'm hoping it doesn't fail for the SpreadSheetAddRow function that builds a row at a time. I'm not quite there yet in my project.

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

                                            Hi, Bruce,

                                             

                                            bruceh3555  wrote

                                             

                                            I'm running CF2016. Do you have any estimate of how many rows it can create in an excel .xlsx formatted sheet?

                                            I have not touched CF2016, and plan to never upgrade to it.  I've read too many forum and blog posts regarding issues with it.  When CF11 reaches EOL, I will most likely switch to Lucee, and I'm hoping Lucee will have built-in Excel/Word/PDF functionality.  I hear there is a plugin for Excel on Lucee, but I don't know how robust it is.  But I'm off course:  unfortunately, no, I cannot estimate how many rows.  If you have small physical RAM, or buffer/heap, it will most likely take longer to do, but (barring some kind of timeout or other issue) you should be able to fill to the Excel capacity.

                                             

                                            EDIT: But I'm getting ahead of myself.  There are other factors that I cannot predict which may limit the capacity to less than Excel capacity.  In those cases, it will take some sleuthing to discover why, and how to remedy it.

                                             

                                            bruceh3555  wrote

                                             

                                            For formatting cells, my need is in some cases to dynamically (on a per cell basis) format the background color at least which is best handled in the row by row loop that is used to construct the data set. There is some rows/columns of the data set that will also need formatting (bold for some columns, underline for a row, etc.) I assume these formatting during and after the build won't be an issue?

                                            As far as background color, font color, text decoration, you should be able to format on the fly once inside the output loop and leave it at that.  But if you are altering the cell format from General to, say, text or numeric, you need to format the row or column before the cfoutput loop and after the loop.  Took me HOURS of Google use to finally find a thread that solved the issue by formatting prior to value and after.

                                             

                                            bruceh3555  wrote

                                             

                                            As for datatype I've found that when specifying datatype with the SpreadSheetAddRows (and using a query) that Numeric type does not work for me. It results in numeric values getting changed to Text in the cell and left justified. Have you seen this happen for you? I'm hoping it doesn't fail for the SpreadSheetAddRow function that builds a row at a time. I'm not quite there yet in my project.

                                            I don't use SpreadsheetAddRows().  I avoid that and manually set the value of every cell using SpreadsheetSetCellValue().  It involves using variables for row number, and some math, and some meticulous attention to detail (it helps if you're OCD), but totally worth it.  Yes, you can wind up with a hundred lines of code instead of twelve or twenty, but that's how I roll.  I'm kind of a control freak with my code.  But as I stated in my last paragraph, changing datatype requires the row or column to be formatted before and after the cfoutput loop, or really anything where a value is being inserted into a cell.

                                             

                                            HTH,

                                             

                                            ^ _ ^

                                             

                                            EDIT: If your generated Excel keeps cutting off at a certain point, and that point is consistent and you can define it in code, consider generating the object, fill to just prior to that break point, save the object to a file, then generate more rows and use functions to append the data to the file, over and over until completion.  Just my two cents.  It's hack-ish, I know, but at least it's a band aid until a resolution can be ascertained.

                                            1 person found this helpful
                                            • 19. Re: Is there a limit to the size of a formatted xlsx spreadsheet created by ColdFusion?
                                              bruceh3555 Level 1

                                              Thanks very much for this response! That will likely save me hours in the future in hopefully avoiding or at least quickly mitigating the same issues you faced.

                                               

                                              I'm surprised about your decision to not go to CF2016. I have not seen those blog posts I guess, but then I wasn't in on the decision to move to it where i work. I will hope for the best now that we are on it.

                                               

                                              It seems redundant to have to format data before AND after the loop. Strange! I could see doing one or the other but not both. I'll have to test and see if that is still a requirement in CF2016.

                                               

                                              So you didn't ever use SreadsheetAddRow and construct row by row in a loop? I plan to use that approach but will switch to the cell by cell approach you use if needed. I'm hoping they have done something to improve these functions since CF10. But that may be wishful thinking.

                                               

                                              Again, a big thank you!

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

                                                You are welcome.  Glad I could be of assistance.  Please let me know how it went, when you're done.

                                                 

                                                V/r,

                                                 

                                                ^ _ ^

                                                • 21. Re: Is there a limit to the size of a formatted xlsx spreadsheet created by ColdFusion?
                                                  charlie@carehart.org Adobe Community Professional & MVP

                                                  Wow, Wolfshade, I'm surprised to hear you say that. I'm not aware of any issues with CF2016 that would lead someone to conclude that they should not move to it. I started to write more in reply here, but then it got longer and I realized I should not derail this thread. So I just created a new one:

                                                   

                                                  Any major issues keep many from moving to CF2016, specifically?

                                                   

                                                  And Wolf, to be clear, I do not name you in that thread, nor do I provide a link back to this one. I'm not trying to make this a personal thing at all, and you may be justified in your opinion. I just wanted to address it more broadly, and share my counter-perspective, as I'm not aware of any sort of unresolved show-stopper issue keep a large percentage of people making the move. Again, see the thread for more, and really it's best to respond there, so as not to side-track this discussion.

                                                   

                                                  And I do appreciate what you have added here, about the spreadsheet concerns. (I've got nothing more to add myself.)

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

                                                    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[x],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[x],variables.x+1,2);
                                                            SpreadsheetSetCellValue(variables.ssObj,qryObj.city[x],variables.x+1,3);
                                                            SpreadsheetSetCellValue(variables.ssObj,qryObj.st[x],variables.x+1,4);
                                                            SpreadsheetSetCellValue(variables.ssObj,qryObj.zip[x],variables.x+1,5);
                                                            SpreadsheetSetCellValue(variables.ssObj,qryObj.country[x],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,

                                                     

                                                    ^ _ ^

                                                    1 person found this helpful
                                                    • 23. Re: Is there a limit to the size of a formatted xlsx spreadsheet created by ColdFusion?
                                                      WolfShade Level 4

                                                      Hi, Charlie,

                                                       

                                                      My opinion on CF2016 is based partly on issues that I've seen in threads in the CF and CF Administration forums here, and in blog posts on other sites.  I don't have a collection of them, or remember the URLs to them, and there are not thousands of them, but enough of them that I am quite leery of CF2016.

                                                       

                                                      I appreciate you creating the new thread.  This one is quite old, but I guess it was the only one that Bruce could find that was related to his situation.

                                                       

                                                      I also appreciate you keeping myself and this thread separate from the new one.  Not that I fear repercussions, or anything (I have been quite vocally critical of Adobe for other things, I won't be offended or intimidated if anyone does decide to volley a shot or three), but it really should be a separate thread.

                                                       

                                                      V/r,

                                                       

                                                      ^ _ ^