poi-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Andrew C. Oliver" <acoli...@apache.org>
Subject Re: Reading XLS via ODBC - SQLException
Date Wed, 05 Feb 2003 13:00:43 GMT
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
> 
> 




Mime
View raw message