Java Tutorial/Database/Excel
Содержание
A JDBC Program to Access/Read Microsoft Excel
<source lang="java">
import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.Statement; public class Main {
public static Connection getConnection() throws Exception { String driver = "sun.jdbc.odbc.JdbcOdbcDriver"; String url = "jdbc:odbc:excelDB"; String username = "yourName"; String password = "yourPass"; Class.forName(driver); return DriverManager.getConnection(url, username, password); } public static void main(String args[]) throws Exception { Connection conn = null; Statement stmt = null; ResultSet rs = null; conn = getConnection(); stmt = conn.createStatement(); String excelQuery = "select * from [Sheet1$]"; rs = stmt.executeQuery(excelQuery); while (rs.next()) { System.out.println(rs.getString("FirstName") + " " + rs.getString("LastName")); } rs.close(); stmt.close(); conn.close(); }
}</source>
Read data from Excel
<source lang="java">
import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.ResultSetMetaData; public class Main {
public static void main(String[] argv) throws Exception { Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); ResultSet rs = DriverManager.getConnection("jdbc:odbc:employee_xls") .createStatement().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++) { String columnValue = rs.getString(i); System.out.println(columnValue); } } rs.close(); }
}</source>
Read data from Excel worksheet
<source lang="java">
import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.Statement; public class Main {
public static void main(String[] args) throws Exception { Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); Statement stmt = DriverManager.getConnection("jdbc:odbc:employee").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(); }
}</source>
Use JDBC ODBC bridge to read from Excel
<source lang="java">
import java.sql.Connection; import java.sql.DriverManager; public class Main {
public static void main(String[] argv) throws Exception { Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); String myDB = "jdbc:odbc:Driver={Microsoft Excel Driver (*.xls)};DBQ=c:/data.xls;" + "DriverID=22;READONLY=false"; Connection con = DriverManager.getConnection(myDB, "", ""); }
}</source>
Write Data to a Microsoft Excel Spreadsheet File
<source lang="java">
import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.Statement; public class Main {
public static Connection getConnection() throws Exception { String driver = "sun.jdbc.odbc.JdbcOdbcDriver"; String url = "jdbc:odbc:excelDB"; String username = ""; String password = ""; Class.forName(driver); // load JDBC-ODBC driver return DriverManager.getConnection(url, username, password); } public static void main(String args[]) throws Exception { Connection conn = null; Statement stmt = null; ResultSet rs = null; conn = getConnection(); stmt = conn.createStatement(); String excelQuery = "insert into [Sheet1$](FirstName, LastName) values("A", "K")"; stmt.executeUpdate(excelQuery); rs.close(); stmt.close(); conn.close(); }
}</source>