Read an Excel Worksheet Into An Array
This code allows you to read an excel spreadsheet into an array. Once its in an array, you can search, sort, and manipulate data, and save it back to the worksheet, a different worksheet, to a database, or output via JSP. Please note this sample only shows you how to get the sheet into an array -- I leave the rest for future contributions or to your imagination.
AI
AI Summary: This codebase represents a historical implementation of the logic described in the metadata. Our preservation engine analyzes the structure to provide context for modern developers.
Source Code
package bean_ed;
import java.sql.*;
import java.util.*;
/**
* Title: Excel Reader
* Description: Read an MS Excel Spreadsheet into a two dimensional array
* Copyright: Copyright (c) 2001
* Company:
* @author Bruce Van Horn
* @version 1.0
*/
public class read_excel {
//excel worksheets act like tables, but you have to put
//funky braces and a $ at the end of the name.
public static void main(String [] args) {
String sql="select count(*) from [mismatch$]";
//load excel driver
try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
}
catch (java.lang.ClassNotFoundException ex) {
System.err.print("No Such Database Driver");
System.err.println(ex.getMessage());
};
String myDB="jdbc:odbc:Driver={Microsoft Excel Driver (*.xls)};DBQ=d:/bom_process/text.xls;DriverID=22;READONLY=false}";
int mycount=0;
try {
java.sql.Connection db=java.sql.DriverManager.getConnection(myDB,"","");
java.sql.Statement st=db.createStatement();
ResultSet rs=st.executeQuery(sql);
//count number of records so we can build an array
int recordcount=0;
while (rs.next()) {
recordcount=rs.getInt(1);
}
//run the real query
sql="select * from [mismatch$]";
rs.close();
rs=st.executeQuery(sql);
ResultSetMetaData rsmd = rs.getMetaData();
mycount=rsmd.getColumnCount();
String xlsSheet[][] = new String[recordcount][mycount];
int rowcounter=0;
while (rs.next()) {
//add elements to array
for (int i=1; i<mycount; i++) {
xlsSheet[rowcounter][i]=rs.getString(i);
//System.out.println("col: "+i+" row: "+rowcounter);
}
rowcounter++;
}
System.out.println(recordcount);
System.out.println(xlsSheet [5][0] + " " + xlsSheet [5][1] + " " + xlsSheet [5][2] );
rs.close();
st.close();
db.close();
}
catch (java.sql.SQLException ex) {
System.err.print("SQL Exception");
System.err.println(ex.getMessage());
};
}
}
Upload
Original Comments (3)
Recovered from Wayback Machine