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

Report · Dec 02, 2009

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/usermodel/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.HSSFDateUtil.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/usermodel/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:28)

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)

Report · Dec 02, 2009

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

Report · Dec 02, 2009

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).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));
}
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;
}
}
}

Report · Dec 02, 2009

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

Report · Dec 02, 2009

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

Report · Dec 02, 2009

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

Report · Dec 02, 2009

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

Report · Dec 02, 2009

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

Report · Dec 02, 2009

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....

Report · Dec 02, 2009

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

Report · Dec 02, 2009

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

Report · Dec 02, 2009

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

Report · Dec 02, 2009

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 ---

Report · Dec 02, 2009

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

also not getting any results now.

Report · Dec 02, 2009

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.

Report · Dec 02, 2009

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.

Report · Dec 03, 2009

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

-Leigh

Report · Dec 09, 2009

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.

Report · Dec 09, 2009

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.

Report · Dec 09, 2009

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

Adobe Community

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