Java Tutorial/Database/Excel

Материал из Java эксперт
Перейти к: навигация, поиск

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>