Admin: 1 версия

2010-06-01T05:06:18Z

1 версия

в 17:44, 31 мая 2010

2010-05-31T17:44:27Z

Новая страница

== 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>

← Предыдущая	Версия 05:06, 1 июня 2010
(нет различий)

Java Tutorial/Database/Excel - История изменений

Admin: 1 версия

в 17:44, 31 мая 2010