19 Replies Latest reply on Dec 9, 2009 5:37 PM by -==cfSearching==-

    CF9 broke poi.jar reference.... please help

    LoanXEngine

      We use a poi.jar with our application to read and write excel spreadsheets (very specific stuff)

       

      I have this server pointing to it as a Java mapping and it's a java CFX app.

       

      CF7 and 8, no problem.  CF9 it's broken.

       

      Here is the error below....   Anyone have any ideas??   I had to roll back to CF8 since CF9 broke it.

       

      org.apache.poi.hssf.usermodel.HSSFDateUtil.isCellDateFormatted(Lorg/apache/poi/hssf/usermo del/HSSFCell;)Z The specific sequence of files included or processed is: D:\LOANXENGINE\frm_workbook.cfm, line: 77

       

       

      "Error","jrpp-4","12/02/09","14:44:18","LoanXenginecom","org.apache.poi.hssf.usermodel.HSS FDateUtil.isCellDateFormatted(Lorg/apache/poi/hssf/usermodel/HSSFCell;)Z The specific sequence of files included or processed is: D:\LOANXENGINE\frm_workbook.cfm, line: 77 "

      java.lang.NoSuchMethodError: org.apache.poi.hssf.usermodel.HSSFDateUtil.isCellDateFormatted(Lorg/apache/poi/hssf/userm odel/HSSFCell;)Z

                      at com.lxe.importer.ExcelImporter.getRangeValues(ExcelImporter.java:109)

                      at com.lxe.importer.ExcelImporter.importDataDescriptor(ExcelImporter.java:66)

                      at com.lxe.importer.ExcelImporter.getImport(ExcelImporter.java:49)

                      at com.lxe.importer.DefaultImporter.getImport(DefaultImporter.java:45)

                      at com.lxe.bean.SpreadsheetImporter.getResult(SpreadsheetImporter.java:75)

                      at com.lxe.cfx.SpreadsheetImporter.processRequest(SpreadsheetImporter.java:37)

                      at coldfusion.tagext.CfxTag.doStartTag(CfxTag.java:102)

                      at coldfusion.runtime.CfJspPage._emptyTcfTag(CfJspPage.java:2722)

                      at cfqry_msexcel_java2ecfm1069017253.runPage(D:\LOANXENGINE\qry_msexcel_java.cfm:77)

                      at coldfusion.runtime.CfJspPage.invoke(CfJspPage.java:231)

                      at coldfusion.tagext.lang.IncludeTag.doStartTag(IncludeTag.java:416)

                      at coldfusion.runtime.CfJspPage._emptyTcfTag(CfJspPage.java:2722)

                      at cflist_workbook_data2ecfm1224912403.runPage(D:\LOANXENGINE\list_workbook_data.cfm:13)

                      at coldfusion.runtime.CfJspPage.invoke(CfJspPage.java:231)

                      at coldfusion.tagext.lang.IncludeTag.doStartTag(IncludeTag.java:416)

                      at coldfusion.runtime.CfJspPage._emptyTcfTag(CfJspPage.java:2722)

                      at cffrm_workbook2ecfm991657085._factor2(D:\LOANXENGINE\frm_workbook.cfm:745)

                      at cffrm_workbook2ecfm991657085._factor13(D:\LOANXENGINE\frm_workbook.cfm:728)

                      at cffrm_workbook2ecfm991657085._factor14(D:\LOANXENGINE\frm_workbook.cfm:26)

                      at cffrm_workbook2ecfm991657085.runPage(D:\LOANXENGINE\frm_workbook.cfm:1)

                      at coldfusion.runtime.CfJspPage.invoke(CfJspPage.java:231)

                      at coldfusion.tagext.lang.IncludeTag.doStartTag(IncludeTag.java:416)

                      at coldfusion.filter.CfincludeFilter.invoke(CfincludeFilter.java:65)

                      at coldfusion.filter.ApplicationFilter.invoke(ApplicationFilter.java:363)

                      at coldfusion.filter.MonitoringFilter.invoke(MonitoringFilter.java:40)

                      at coldfusion.filter.PathFilter.invoke(PathFilter.java:87)

                      at coldfusion.filter.ExceptionFilter.invoke(ExceptionFilter.java:70)

                      at coldfusion.filter.BrowserDebugFilter.invoke(BrowserDebugFilter.java:74)

                      at coldfusion.filter.ClientScopePersistenceFilter.invoke(ClientScopePersistenceFilter.java:2 8)

                      at coldfusion.filter.BrowserFilter.invoke(BrowserFilter.java:38)

                      at coldfusion.filter.NoCacheFilter.invoke(NoCacheFilter.java:46)

                      at coldfusion.filter.GlobalsFilter.invoke(GlobalsFilter.java:38)

                      at coldfusion.filter.DatasourceFilter.invoke(DatasourceFilter.java:22)

                      at coldfusion.filter.CachingFilter.invoke(CachingFilter.java:53)

                      at coldfusion.CfmServlet.service(CfmServlet.java:200)

                      at coldfusion.bootstrap.BootstrapServlet.service(BootstrapServlet.java:89)

                      at jrun.servlet.FilterChain.doFilter(FilterChain.java:86)

                      at coldfusion.monitor.event.MonitoringServletFilter.doFilter(MonitoringServletFilter.java:42 )

                      at coldfusion.bootstrap.BootstrapFilter.doFilter(BootstrapFilter.java:46)

                      at jrun.servlet.FilterChain.doFilter(FilterChain.java:94)

                      at jrun.servlet.FilterChain.service(FilterChain.java:101)

                      at jrun.servlet.ServletInvoker.invoke(ServletInvoker.java:106)

                      at jrun.servlet.JRunInvokerChain.invokeNext(JRunInvokerChain.java:42)

                      at jrun.servlet.JRunRequestDispatcher.invoke(JRunRequestDispatcher.java:286)

                      at jrun.servlet.ServletEngineService.dispatch(ServletEngineService.java:543)

                      at jrun.servlet.jrpp.JRunProxyService.invokeRunnable(JRunProxyService.java:203)

                      at jrunx.scheduler.ThreadPool$DownstreamMetrics.invokeRunnable(ThreadPool.java:320)

                      at jrunx.scheduler.ThreadPool$ThreadThrottle.invokeRunnable(ThreadPool.java:428)

                      at jrunx.scheduler.ThreadPool$UpstreamMetrics.invokeRunnable(ThreadPool.java:266)

                      at jrunx.scheduler.WorkerThread.run(WorkerThread.java:66)

       

       

        • 1. Re: CF9 broke poi.jar reference.... please help
          -==cfSearching==- Level 4

          java.lang.NoSuchMethodError:

          org.apache.poi.hssf.usermodel.HSSFDateUtil.isCellDateFormatted(

          Lorg/apache/poi/hssf/usermodel/HSSFCell;)

           

          That method still exists in the ColdFusion 9 poi.jar. Though it is implemented differently due to the added ooxml support. I took a quick and it should be backward compatible.  Can you post the java code corresponding to that error?

           

          -Leigh

          • 2. Re: CF9 broke poi.jar reference.... please help
            LoanXEngine Level 1

            Apologies in advance for being verbose, but here is the whole file here.

             

             

            package com.lxe.importer;

            import java.io.*;
            import java.util.*;
            import java.text.*;
            import org.apache.poi.poifs.filesystem.*;
            import org.apache.poi.hssf.usermodel.*;

            /**
            * Provides a way to retrieve the contents of a grid of Excel spreadsheet
            * cells as delimeted strings.  This class represents one XLS file.

            * @author stan
            * @version 1.00, Jan 27, 2004
            */
            final class ExcelImporter implements Importer {
            private final String xlsFile;
            private DataDescriptor dataDescriptor = null;

            public ExcelImporter(String xlsFile) {
              this.xlsFile = xlsFile;
            }
            public String getFileName() {
              return this.xlsFile;
            }
            // Example: 1234,Fixed_Balloon,A21:E34;G21:K34
            public void setDescriptor(String descriptor) throws Exception {
              String parts[] = descriptor.split("[,]");
              if(parts.length < 2) throw new Exception("Descriptor should be '<sheet>,<ranges>': " + descriptor);
              String sheetName = parts[0].trim();
              String ranges[] = parts[1].trim().split("[;]");
              this.dataDescriptor = new DataDescriptor(sheetName, ranges);
            }

            public String getImport() throws Exception {
                File inFile = new File(this.xlsFile).getAbsoluteFile();
              InputStream in = new BufferedInputStream(new FileInputStream(inFile), 4096);
              POIFSFileSystem fs = new POIFSFileSystem(in);
              HSSFWorkbook wb = new HSSFWorkbook(fs);
              DataDescriptor descr = (DataDescriptor)this.dataDescriptor;
              StringBuffer str = new StringBuffer();
              this.importDataDescriptor(wb, descr);
              // Append the range data, one range per line
              String data[] = descr.getData();
              int len = data.length;
              for(int d = 0; d < len; d++) {
               str.append(data[d]).append('\n');
              }
              in.close();
              return str.toString();
            }

            private DataDescriptor importDataDescriptor(HSSFWorkbook wb, DataDescriptor descr)
                 throws Exception {
              HSSFSheet sheet = this.getWorkbookSheet(wb, descr.sheetName);
              if(sheet == null) throw new SheetNotFoundException(descr.sheetName);
              int len = descr.ranges.length;
              for(int i = 0; i < len; i++) {
               descr.setRangeData(i, this.getRangeValues(wb, sheet, descr.ranges[i]));
              }
              return descr;
            }
             
              private HSSFSheet getWorkbookSheet(HSSFWorkbook wb, String sheetName) {
                HSSFSheet sheet = null;
                int size = wb.getNumberOfSheets();
                for(int i = 0; i < size; i++) {
                  String name = wb.getSheetName(i).trim();
                  if(name.equals(sheetName)) {
                    sheet = wb.getSheetAt(i);
                    break;
                  }
                }
                return sheet;
              }

            // Return a delimeted list of values for the given range
            // Example range: "B22:B24" could return "1.0,4.3,5.1"
            private String getRangeValues(HSSFWorkbook wb, HSSFSheet sheet, String range) throws Exception {
              if(!range.matches("[A-Za-z][A-Za-z]?[0-9]+[:][A-Za-z][A-Za-z]?[0-9]+")) {
               throw new RangeFormatException("Improper Range Format: " + range);
              }
              StringBuffer str = new StringBuffer();
              int colon = range.indexOf(':');
              int rangeStart[];
              int rangeEnd[];
              rangeStart = this.getCell(range.substring(0, colon));
              rangeEnd = this.getCell(range.substring(colon + 1));
              for(int r = rangeStart[1]; r <= rangeEnd[1]; r++) {
               HSSFRow row = sheet.getRow(r);
               boolean addedFirstCell = false;
               for(int c = rangeStart[0]; c <= rangeEnd[0]; c++) {
                HSSFCell cell = (row==null)?null:row.getCell((short)c); 
                if(addedFirstCell) str.append(',');
                else addedFirstCell = true;
                if(cell == null) {
                 str.append("");
                }
                else {
                 int type = cell.getCellType();
                 switch(type) {
                   case HSSFCell.CELL_TYPE_BLANK:
                   case HSSFCell.CELL_TYPE_ERROR:
                     str.append("");
                     break;
                   case HSSFCell.CELL_TYPE_FORMULA:
                     //String formula = cell.getCellFormula();
                     //System.err.println("ExcelImporter Formula: " + formula);
                     //Object value = this.evaluateFormula(sheet, formula);
                     //if(!(value instanceof String)) str.append(value.toString());
                     //else str.append('\"').append((String)value).append('\"');
                     str.append("");
                     break;
                   case HSSFCell.CELL_TYPE_NUMERIC:
                     if(HSSFDateUtil.isCellDateFormatted(cell)) {
                     Date date = HSSFDateUtil.getJavaDate(cell.getNumericCellValue());
                     SimpleDateFormat format = new SimpleDateFormat("MM/dd/yyyy");
                     str.append('\"').append(format.format(date)).append('\"');
                     }
                     else {
                      if(this.getDataFormat(wb, cell).indexOf("%") > -1) {  // Handle percentages
                     str.append(cell.getNumericCellValue() * 100.0);          
                      }
                      else {
                     str.append(cell.getNumericCellValue());
                      }   
                     }
                    break;
                   case HSSFCell.CELL_TYPE_STRING:  
                     str.append('\"').append(cell.getStringCellValue()).append('\"'); 
                     break;
                  default:
                    throw new Exception("Unsupported cell type: " + type);
                 }
                }
               }
               str.append('\n');
              }
              return str.toString();
            }

            // Example cell: B22
            private int[] getCell(String cell) {
              String column = cell.replaceFirst("[0-9]+", "");
              String row = cell.replaceFirst("[A-Za-z]+", "");
              return new int[]{ this.getColumnNumber(column), Integer.parseInt(row) - 1 };
            }
              // Return the integer value of the given column designation
              // Note: This method only handles columns of 1 or 2 chars ("A" or "AA", not "AAA")
            private int getColumnNumber(String columnLetters) {
              int column;
              if(columnLetters.length() == 1) {
               column = Character.toUpperCase(columnLetters.charAt(0)) - 'A';
              }
              else {
               column = Character.toUpperCase(columnLetters.charAt(1)) - 'A';
               column += (Character.toUpperCase(columnLetters.charAt(0)) - 'A' + 1) * 26;
              }
              return column;
            }
            private Object evaluateFormula(HSSFSheet sheet, String formula) {
              return formula;
            }
            private String getDataFormat(HSSFWorkbook wb, HSSFCell cell) {
              String format = "";
              try {
               HSSFDataFormat dformat = wb.createDataFormat();
                format = dformat.getFormat(cell.getCellStyle().getDataFormat());
              }
              catch(Exception ex) {}
              return format;
            }


            // Contains information for importing one set of ranges and their data for a sheet
            private static class DataDescriptor {
              final String sheetName;
              final String ranges[];
              private String data[];
              DataDescriptor(String sheetName, String ranges[]) {
               this.sheetName = sheetName;
               this.ranges = ranges;
               this.data = new String[ranges.length];
              }
              public void setRangeData(int index, String data) {
               this.data[index] = data;
              }
              public String[] getData() {
               return this.data;
              }
            }
            }

            • 3. Re: CF9 broke poi.jar reference.... please help
              -==cfSearching==- Level 4

              Can you give me an example of a valid value for setDescriptor()?

              • 4. Re: CF9 broke poi.jar reference.... please help
                -==cfSearching==- Level 4

                We use a poi.jar

                I have this server pointing to it as a Java mapping

                and it's a java CFX app.

                 

                When you say jar, you mean just the jar containing your CFX class(es) right? Not that you added another poi jar the CF9 classpath.

                 

                -Leigh

                • 5. Re: CF9 broke poi.jar reference.... please help
                  -==cfSearching==- Level 4

                  java.lang.NoSuchMethodError:

                   

                  BTW: I think the error message is misleading. In a simple test, it seems to work just fine in CF9.

                   

                   

                      wb = createObject("java", "org.apache.poi.hssf.usermodel.HSSFWorkbook").init();

                      sheet = wb.createSheet("new sheet");

                      row = sheet.createRow(0);

                      cell = row.createCell(0);

                      util = createObject("java", "org.apache.poi.hssf.usermodel.HSSFDateUtil");

                      result = util.isCellDateFormatted(cell);

                      WriteOutput("result=#result#");

                  </cfscript

                  • 6. Re: CF9 broke poi.jar reference.... please help
                    LoanXEngine Level 1

                    We add a poi.jar to our class paths and have been doing so since version 6, I believe.  Here's a typical example.

                     

                    D:\LoanXEngine\JARs,D:\LoanXEngine\JARs\lxe.jar,D:\LoanXEngine\JARs\poi.jar

                    • 7. Re: CF9 broke poi.jar reference.... please help
                      LoanXEngine Level 1

                      What version of poi is Adobe loading in CF9?   I looked and previous versions sucha as CF8  have things like poi-2.5 and such...

                       

                      Could it be that the file now in the lib folder named poi.jar is getting in the way of the class path loaded poi.jar one we've been using??

                       

                      thanks....

                      • 8. Re: CF9 broke poi.jar reference.... please help
                        -==cfSearching==- Level 4

                        We add a poi.jar to our class paths and have been doing so

                        since version 6, I believe.  Here's a typical example.

                         

                        No, you do not want to do that with later versions of CF.  The reason being MX7+ already ships with a version of POI.  Having multiple versions in the classpath can sometimes cause conflicts. That might be your problem here.  IIRC, it was necessary with MX 6 because it did not ship with POI. 

                         

                        -Leigh

                         

                        • 9. Re: CF9 broke poi.jar reference.... please help
                          -==cfSearching==- Level 4

                          What version of poi is Adobe loading in CF9?  

                           

                          Version 3.5-beta7-20090726

                           

                          Could it be that the file now in the lib folder named

                          poi.jar is getting in the way of the class path loaded

                          poi.jar one we've been using??

                           

                          Yep. That is a distinct possibility. Try removing the jar, restarting and see if it resolves the error.

                           

                          -Leigh

                          • 10. Re: CF9 broke poi.jar reference.... please help
                            LoanXEngine Level 1

                            I assume you mean removing the CF9 version of the jar and keeping our own, correct?   Or vice versa...?

                            • 11. Re: CF9 broke poi.jar reference.... please help
                              -==cfSearching==- Level 4

                              I assume you mean removing the CF9 version of the jar and

                              keeping our own, correct?   Or vice versa...?

                               

                              Vice versa.  Never mess with the built-in ColdFusion jars unless you know what you are doing. It can break things ... badly

                               

                              -Leigh

                              • 12. Re: CF9 broke poi.jar reference.... please help
                                LoanXEngine Level 1

                                I'll set up a test server (I had to roll back to CF8 where I had rolled it forward) and give it a try...  Will let you know the results.

                                 

                                thanks ---

                                • 13. Re: CF9 broke poi.jar reference.... please help
                                  LoanXEngine Level 1

                                  I removed our poi.jar file from the class path...  Not getting an error, but

                                  also not getting any results now.

                                  • 14. Re: CF9 broke poi.jar reference.... please help
                                    -==cfSearching==- Level 4

                                    I removed our poi.jar file from the class path...  Not

                                    getting an error, but

                                    also not getting any results now.

                                     

                                    Have you actually tested it in a java IDE with the new CF9 jar? Maybe something has else has changed.  Also, check the log files. Just to make sure there are no errors listed.

                                    • 15. Re: CF9 broke poi.jar reference.... please help
                                      LoanXEngine Level 1

                                      Yes, that's the next step.   We are going to run/test it

                                      against the release poi 3.5 version since this is the current release and is only 2 months later than the poi.jar Adobe is using.

                                       

                                      Will let you know how it goes.  Thanks again for your help.

                                      • 16. Re: CF9 broke poi.jar reference.... please help
                                        -==cfSearching==- Level 4

                                        Yes, I am curious about the issue. So keep me posted. 

                                         

                                        -Leigh

                                        • 17. Re: CF9 broke poi.jar reference.... please help
                                          LoanXEngine Level 1

                                          the thought was that another one of the cold fusion jars had  the method in it, because it clearly exists in the poi.jar.

                                           

                                          The original jar had been compiled with an older JVM, so we recomiled it (same code), to the current JVM and put it in place an it worked.

                                          • 18. Re: CF9 broke poi.jar reference.... please help
                                            LoanXEngine Level 1

                                            Another CF jar file likely had that method in it that superceded the one we were trying to use, and thus the method not found message, despite it clearly being in the poi.jar.   We took the same original code that was compile with and older JVM and recompiled it to the current JVM and it began to work.

                                            • 19. Re: CF9 broke poi.jar reference.... please help
                                              -==cfSearching==- Level 4

                                              That is a weird one. I am not very familiar with the effects of compiling against a different version. Since you say it worked after you recompiled, maybe there is some linkage there.  I will have to read up on that.

                                               

                                              BTW: You can also get a NoMethodFound error even when there is only one version of a jar in your class loader. Though I do not think it applies in your case, it can happen if you are using multiple class loaders. Class loaders only recognize objects they loaded. So you could load the exact same class (from the same jar) in second class loader and it would be not recognized by the first. Even though it is exactly the same.

                                               

                                              Anyway, I am glad to here you got it sorted out .. and exactly how you fixed it.

                                               

                                              -Leigh