poi-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Eric Hsiung <Er...@BodyMedia.Com>
Subject RE: Reading XLS via ODBC - SQLException
Date Wed, 05 Feb 2003 13:07:05 GMT
My ExcelReader test app does not use POI so that sounds 
reasonable. So JDBC is throwing the exception to relay 
an error encountered by ODBC. So the question is why 
ODBC doesn't like the POI-generated XLS file?


Eric

> -----Original Message-----
> From: Andrew C. Oliver [mailto:acoliver@apache.org]
> Sent: Wednesday, February 05, 2003 8:01 AM
> To: POI Users List
> Subject: Re: Reading XLS via ODBC - SQLException
> 
> 
> 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: poi-user-unsubscribe@jakarta.apache.org
> > For additional commands, e-mail: poi-user-help@jakarta.apache.org
> > 
> > 
> 
> 
> 
> 
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
> For additional commands, e-mail: poi-user-help@jakarta.apache.org
> 

Mime
View raw message