先用Jsp打开Excel,并将Excel Sheet存为CSV文件格式
然后在数据库执行SQL语句、将数据导入,速度最快。
"INSERT INTO " & hksTblNm & " SELECT * FROM [Text;database=" & losPath & "]." & losNm & ";"
然后在数据库执行SQL语句、将数据导入,速度最快。
"INSERT INTO " & hksTblNm & " SELECT * FROM [Text;database=" & losPath & "]." & losNm & ";"
Reiser Beth 102
Ricci Dylan 111
Gugliuzza Brian 116To access this data, we can use the JDBC-ODBC bridge. Microsoft provides an ODBC driver to Excel worksheet.
Define an ODBC datasource (system DSN) named "employee_xls" that points to that worksheet. example 1
import java.io.*;
import java.net.*;
import java.sql.*;
import java.util.*;public class EmployeeReader{
public static final String DRIVER_NAME =
"sun.jdbc.odbc.JdbcOdbcDriver";
public static final String DATABASE_URL = "jdbc:odbc:employee_xls"; public static void main(String[] args)
throws ClassNotFoundException, SQLException{
Class.forName(DRIVER_NAME);
Connection con = null;
try {
con = DriverManager.getConnection(DATABASE_URL);
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery
("select lastname, firstname, id from [Sheet1$]");
while (rs.next()) {
String lname = rs.getString(1);
String fname = rs.getString(2);
int id = rs.getInt(3);
System.out.println(fname + " " + lname + " id : " + id);
}
rs.close();
stmt.close();
}
finally {
if (con != null)
con.close();
}
}
} example 2
import java.io.*;
import java.sql.*;public class EmployeeReader{
public static void main(String[] args){
Connection connection = null;
try{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection con = DriverManager.getConnection( "jdbc:odbc:employee_xls" );
Statement st = con.createStatement();
ResultSet rs = st.executeQuery( "Select * from [Sheet1$]" ); ResultSetMetaData rsmd = rs.getMetaData();
int numberOfColumns = rsmd.getColumnCount();
System.out.println ( "No of cols "+numberOfColumns ); while (rs.next()) {
for (int i = 1; i <= numberOfColumns; i++) {
if (i > 1) System.out.print(", ");
String columnValue = rs.getString(i);
System.out.print(columnValue);
}
System.out.println("");
}
rs.close();
st.close();
}
catch(Exception ex) {
System.err.print("Exception: ");
System.err.println(ex.getMessage());
}
finally {
con.close();
}
}
}