16 Replies Latest reply on Dec 6, 2006 2:12 PM by azhotdish

    Excel Output to Specific Worksheet

      Quick summary - i'm trying to populate a specific data tab of an Excel worksheet to automate the process of creating 100+ Excel files with graphs. The tab (named appopriately, "Data") contains all the raw data for the other tabs which contain multiple graphs. Is there a way to export to a specific tab in Excel? Any assistance is appreciated.
        • 1. Re: Excel Output to Specific Worksheet
          MikerRoo Level 1
          Reference: http://www.adobe.com/cfusion/webforums/forum/messageview.cfm?forumid=1&catid=6&threadid=12 19365 .

          What exact CF version are you running? (CF 6.0, CF 6.1, CF 7.01, etc.)

          Are you using the standard edition or are you deployed on Java or Jrun?
          If so, what exact version?

          • 2. Re: Excel Output to Specific Worksheet
            azhotdish Level 1
            Windows 2000 server. Coldfusion Enterprise MX version 6,1,0,83762
            • 3. Re: Excel Output to Specific Worksheet
              MikerRoo Level 1
              You didn't say whether you were using java or Jrun.

              We also need to know your CF root directory...
              When you run this code:

              <h3>Server base dir</h3>
              #Server.Coldfusion.RootDir#<br />

              What does it say?

              For your config it will be typically be something like:

              Whatever it says, you need to copy the two jar files to the lib directory, RoorDir\..\lib
              EG: C:\JRun4\servers\cfusion\cfusion-ear\cfusion-war\WEB-INF\lib

              After copying the javacfx.jar and the Apache POI jar to the correct directory, be sure the CFX is registered in the Coldfusion administrator.

              Lastly, be sure to restart ALL java, Jrun, Macromedia, and Coldfusion services.

              • 5. Re: Excel Output to Specific Worksheet
                MikerRoo Level 1
                Then your RootDir will be slightly different, everything else is the same as my previous post.
                • 6. Re: Excel Output to Specific Worksheet
                  azhotdish Level 1
                  i don't know why but i keep getting the same error. is there any configuration that this won't work in?
                  • 7. Excel Output to Specific Worksheet
                    azhotdish Level 1
                    i have both the poi-2.5.1-final-20040804.jar and javacfx.jar located in CFusionMX\wwwroot\WEB-INF\lib. i registered the cfx in cfadmin and restarted the services. am i missing something?

                    i also tried rebooting the server with no luck.
                    • 8. Re: Excel Output to Specific Worksheet
                      MikerRoo Level 1
                      When you run this code:
                      <h3>Server base dir</h3>
                      #Server.Coldfusion.RootDir#<br />

                      what are the results?

                      • 9. Re: Excel Output to Specific Worksheet
                        azhotdish Level 1
                        Error resolving parameter SERVER.COLDFUSION.ROOTDIR

                        The object COLDFUSION.ROOTDIR is not present in the scope named SERVER. It is likely that you have misspelled the name of the object you are trying to access.

                        • 10. Re: Excel Output to Specific Worksheet
                          MikerRoo Level 1
                          Something's really wrong or this is not CF 6.1 or later.

                          Anyway, run this code:
                          <cfdump var="#Server#">

                          and attach the results here.

                          Edit: Oops, I meant server.
                          • 11. Re: Excel Output to Specific Worksheet
                            azhotdish Level 1
                            CFID 735
                            CFTOKEN 95579965
                            SESSIONID DASHBOARD_735_95579965
                            URLTOKEN CFID=735&CFTOKEN=95579965

                            doing that with SERVER in as the var attribute blows up. and it's 6.1...i may be new to CFXs written in Java, but not to CF.
                            • 12. Re: Excel Output to Specific Worksheet
                              azhotdish Level 1
                              BTW this works on our production server...I think you're right, something is definitely wrong.
                              • 13. Re: Excel Output to Specific Worksheet
                                MikerRoo Level 1
                                Sorry, I meant <cfdump var="#Server#">.

                                Anyway, <cfdump var="#Server#"> does not normally "blow up" in CF MX. Something's wrong here.

                                Anyway, if you are running Enterprise on top of Java 1.4, then CFusionMX\wwwroot\WEB-INF\lib is not the correct directory for the jar files.

                                Open up the CF Administrator. Find the "System Information" page.

                                Attach the entire contents of that page, especially the classpath, here.

                                This will let us see if the jars have been found and, if not, where to put them.
                                • 14. Re: Excel Output to Specific Worksheet
                                  azhotdish Level 1

                                  System Information
                                  Server Details
                                  Server Product
                                  ColdFusion MX



                                  Serial Number

                                  Operating System
                                  Windows 2000

                                  OS Version

                                  JVM Details
                                  Java Version

                                  Java Vendor
                                  Sun Microsystems Inc.

                                  Java Vendor URL

                                  Java Home

                                  File Separator

                                  Path Separator

                                  Line Separator

                                  User Name

                                  User Home

                                  User Dir

                                  Java VM Specification Version

                                  Java VM Specification Vendor
                                  Sun Microsystems Inc.

                                  Java VM Specification Name
                                  Java Virtual Machine Specification

                                  Java VM Version

                                  Java VM Vendor
                                  Sun Microsystems Inc.

                                  Java VM Name
                                  Java HotSpot(TM) Server VM

                                  Java Specification Version

                                  Java Specification Vendor
                                  Sun Microsystems Inc.

                                  Java Specification Name
                                  Java Platform API Specification

                                  Java Class Version

                                  Java Class Path
                                  D:\CFusionMX\runtime\servers\lib; D:\CFusionMX\runtime\..\lib\cfusion.jar; D:\CFusionMX\runtime\..\lib; D:\CFusionMX\runtime\..\lib\bcel.jar; D:\CFusionMX\runtime\..\lib\cf4was.jar; D:\CFusionMX\runtime\..\lib\cf4was_ae.jar; D:\CFusionMX\runtime\..\lib\cfmx-j2ee.jar; D:\CFusionMX\runtime\..\lib\cfmx-ssl.jar; D:\CFusionMX\runtime\..\lib\cfmx_bootstrap.jar; D:\CFusionMX\runtime\..\lib\cfusion.jar; D:\CFusionMX\runtime\..\lib\cfx.jar; D:\CFusionMX\runtime\..\lib\collections.jar; D:\CFusionMX\runtime\..\lib\crimson.jar; D:\CFusionMX\runtime\..\lib\flashgateway.jar; D:\CFusionMX\runtime\..\lib\httpclient.jar; D:\CFusionMX\runtime\..\lib\izmado.jar; D:\CFusionMX\runtime\..\lib\jakarta-oro-2.0.6.jar; D:\CFusionMX\runtime\..\lib\java2wsdl.jar; D:\CFusionMX\runtime\..\lib\jaxp-api.jar; D:\CFusionMX\runtime\..\lib\jce1_2_1.jar; D:\CFusionMX\runtime\..\lib\jdom.jar; D:\CFusionMX\runtime\..\lib\jintegra.jar; D:\CFusionMX\runtime\..\lib\jxl.jar; D:\CFusionMX\runtime\..\lib\ldap.jar; D:\CFusionMX\runtime\..\lib\ldapbp.jar; D:\CFusionMX\runtime\..\lib\local_policy.jar; D:\CFusionMX\runtime\..\lib\log4j.jar; D:\CFusionMX\runtime\..\lib\macromedia_drivers.jar; D:\CFusionMX\runtime\..\lib\mail.jar; D:\CFusionMX\runtime\..\lib\msapps.jar; D:\CFusionMX\runtime\..\lib\NetComponents.jar; D:\CFusionMX\runtime\..\lib\pbclient42RE.jar; D:\CFusionMX\runtime\..\lib\pbembedded42RE.jar; D:\CFusionMX\runtime\..\lib\pbserver42RE.jar; D:\CFusionMX\runtime\..\lib\pbtools42RE.jar; D:\CFusionMX\runtime\..\lib\sunjce_provider.jar; D:\CFusionMX\runtime\..\lib\tools.jar; D:\CFusionMX\runtime\..\lib\tt-bytecode.jar; D:\CFusionMX\runtime\..\lib\US_export_policy.jar; D:\CFusionMX\runtime\..\lib\viewer.jar; D:\CFusionMX\runtime\..\lib\webcharts.jar; D:\CFusionMX\runtime\..\lib\webchartsJava2D.jar; D:\CFusionMX\runtime\..\lib\webservices.jar; D:\CFusionMX\runtime\..\lib\wsdl2java.jar; D:\CFusionMX\runtime\..\lib\xalan.jar; D:\CFusionMX\runtime\..\runtime\lib\jrun.jar; D:\CFusionMX\runtime\..\runtime\lib; D:\CFusionMX\runtime\..\runtime\lib\java2wsdl.jar; D:\CFusionMX\runtime\..\runtime\lib\jrun-ant-tasks.jar; D:\CFusionMX\runtime\..\runtime\lib\jrun-xdoclet.jar; D:\CFusionMX\runtime\..\runtime\lib\jrun.jar; D:\CFusionMX\runtime\..\runtime\lib\jspc.jar; D:\CFusionMX\runtime\..\runtime\lib\migrate.jar; D:\CFusionMX\runtime\..\runtime\lib\oem-xdoclet.jar; D:\CFusionMX\runtime\..\runtime\lib\sniffer.jar; D:\CFusionMX\runtime\..\runtime\lib\webservices.jar; D:\CFusionMX\runtime\..\runtime\lib\wsconfig.jar; D:\CFusionMX\runtime\..\runtime\lib\wsdl2java.jar; D:\CFusionMX\runtime\..\runtime\lib\xmlscript.jar; D:\CFusionMX\runtime\servers\lib; D:\CFusionMX\runtime\..\..\src; D:\CFusionMX\runtime\..\lib\cfusion.jar; D:\CFusionMX\runtime\lib\jrun.jar

                                  Java Ext Dirs

                                  • 15. Re: Excel Output to Specific Worksheet
                                    MikerRoo Level 1
                                    OK, so you are not really deployed on standalone java. Your using the bundled Jrun.

                                    So, in this case, the jars should be installed at:
                                    and you say they are.

                                    They do not show in the classpath, though, and your classpath seems to be missing a lot of values. (Plus the server does not respond properly)
                                    A clean wipe and reinstall may be in order.

                                    But first, try placing the jars in D:\CFusionMX\lib and restarting.

                                    • 16. Re: Excel Output to Specific Worksheet
                                      azhotdish Level 1
                                      MikerRoo - I appreciate your help. When I rebooted earlier, that was the location of the jars and it did not work. Since the server isn't accessing SERVER scope and we've had several other problems with the SQL installation (they're running together, not my choice), I've recommended an alternative server for the CFMX instance. We'll see what happens.