I can say with absolute certainty the SQLException is not coming from POI.
Eric Hsiung wrote:
Been using a POI a few months now and recently had a
customer report a problem accessing POI-generated XLS
files. Specifically, he gets a "SQLException: Column not found" when trying to access data in the last column. Has anyone else seen this?
I was able to re-create the problem using simple test
programs:
public class ExcelWriter {
public static void main( String [] args )
{
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet("Data");
int nColumns = 10;
HSSFRow row = sheet.createRow((short)0);
for (short i = 0; i < nColumns; i++) {
HSSFCell cell = row.createCell(i);
cell.setCellValue("Column" + i);
}
// Workaround is to add an extra blank column
//HSSFCell extraCell = row.createCell((short)nColumns);
//extraCell.setCellValue("");
int nDataRows = 6;
for (short i = 1; i <= nDataRows; i++) {
row = sheet.createRow(i);
for (short j = 0; j < nColumns; j++) {
HSSFCell cell = row.createCell(j);
cell.setCellValue(j);
}
}
// Write the output to a file
try {
FileOutputStream fileOut = new FileOutputStream("test.xls");
workbook.write(fileOut);
fileOut.close();
} catch (IOException e) {
System.out.println("error: " + e);
}
System.out.println("ExcelWriter created test.xls successfully.");
}
}
public class ExcelReader {
public static void main( String [] args )
{
System.out.println("ExcelReader: accessing test.xls via ODBC.");
System.out.println("Remember to define an ODBC User DSN called
'test' that points to test.xls.");
Connection c = null;
Statement stmnt = null;
try
{
Class.forName( "sun.jdbc.odbc.JdbcOdbcDriver" );
c = DriverManager.getConnection( "jdbc:odbc:test", "", "" );
stmnt = c.createStatement();
String query = "select * from [Data$]";
ResultSet rs = stmnt.executeQuery( query );
System.out.println( "Found the following records:" );
while( rs.next() )
{
System.out.println("Row " + rs.getRow());
System.out.println(" Column0 = " +
rs.getString("Column0"));
System.out.println(" Column1 = " +
rs.getString("Column1"));
System.out.println(" Column2 = " +
rs.getString("Column2"));
System.out.println(" Column3 = " +
rs.getString("Column3"));
System.out.println(" Column4 = " +
rs.getString("Column4"));
System.out.println(" Column5 = " +
rs.getString("Column5"));
System.out.println(" Column6 = " +
rs.getString("Column6"));
System.out.println(" Column7 = " +
rs.getString("Column7"));
System.out.println(" Column8 = " +
rs.getString("Column8"));
// Without the workaround, the following line will fail.
System.out.println(" Column9 = " +
rs.getString("Column9"));
}
}
catch( Exception e )
{
System.err.println( e );
}
finally
{
try
{
stmnt.close();
c.close();
}
catch( Exception e )
{
System.err.println( e );
}
}
}
}
So far I've come up with two workarounds.
1. Open the XLS in Excel and save it again.
2. When generating the file, create an extra blank last column.
This happens in both POI 1.5.1 and the nightly build from Feb 4.
Is this a bug or am I doing something wrong? Thanks for any info.
Eric
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
--------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
