You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by Eric Hsiung <Er...@BodyMedia.Com> on 2003/02/05 13:49:51 UTC
Reading XLS via ODBC - SQLException
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
Re: Reading XLS via ODBC - SQLException
Posted by "Andrew C. Oliver" <ac...@apache.org>.
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
>
>
Re: Reading XLS via ODBC - SQLException
Posted by Danny Mui <da...@muibros.com>.
Have you tried using numerical indices instead of column names? Other
than that, never had to do anything this way :)
If you dont mind me asking, what requirement has you doing this? Are
you using the Excel spreadsheet as a pseudo-database so others can
generate reports?
Just curious...i know what happened to the cat...but..
danny
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
>
>
>