How to read Excel file contents using Java

Question: Can I read an Excel file using Java? If so, how?
Answer: Yes, Microsoft Excel files can be read using Java. Microsoft provides an ODBC driver for Excel, so we can use JDBC and Sun's JDBC-ODBC driver to read Excel files.
If you have an Excel file named Book1.xls (Translator's Note: Since I did not download the original example, I used my own example), and there is a worksheet in the file Named Sheet1, Microsoft's ODBC driver uses the first row in the worksheet as the column name (Translator's Note: Field name), and the worksheet name as the database table name. To access the worksheet through JDBC, we must also create a new ODBC data source. The process of creating a data source on a Windows 2000 system is as follows:

Enter "Control Panel" Management Tools"Data Source (ODBC), (Translator's Note: Select the system DSN after opening), click Add, select "Driver do Microsoft Excel(*.xls)

" in the pop-up window and enter your name Book1 in the data source name (Translator Note: Equivalent to the database name), then click "Select workbook, then find and select your Excel file

After clicking OK, the data source name you set will appear in the system data source list, and now the data table It is already in the data source list (Translator's Note: Click OK to complete the configuration).

(Translator's Note: In my example) Now if we want to select all the "test values" in the test1 column, then You need to use the following SQL query:

SELECT test1 FROM [Sheet1$] WHERE test1='Test'

It should be noted that the worksheet name is followed by a "$ symbol. This symbol It is indispensable. Why? Because there are square brackets before and after it, because "$ is a reserved word in SQL statements.

The following is an example program:

##import java.sql.Connection;

import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.DriverManager;
public class ExcelReader {
public static void main(String［］args) {
Connection c = null;
Statement stmnt = null;
try {
Class.forName( "sun.jdbc.odbc.JdbcOdbcDriver" );
c = DriverManager.getConnection( "jdbc:odbc:Book1", "", " " );
stmnt = c.createStatement();
String query = "SELECT test1 FROM [Sheet1$] WHERE test1='test'";
ResultSet rs = stmnt.executeQuery( query );
System.out.println( "The record of test1 matching 'test' is:" );
while( rs.next() ) {
System.out.println( rs.getString( " test1" ) );
}
}
catch( Exception e ) {
System.err.println( e );
}
finally {
try {
stmnt.close();
c.close();
}
catch( Exception e ) {
System.err.println( e );
}
}
}
}

In this program, the main function main() establishes a connection to the data table and retrieves the records that meet the conditions.

(Translator's Note: In addition, I also have a program here, which is a program that reads all records, for reference only):

##

import java.sql.Connection;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.DriverManager; 
public class ExcelReader { 
 public static void main(String［］ args){
  Connection connection = null; 
  try{
   Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
   Connection con = DriverManager.getConnection( "jdbc:odbc:Book1","","" );
   Statement st = con.createStatement();
   ResultSet rs = st.executeQuery( "SELECT * FROM [Sheet1$]" ); 
   ResultSetMetaData rsmd = rs.getMetaData();
   int numberOfColumns = rsmd.getColumnCount(); 
   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("");
   } 
   st.close();
   con.close(); 

  } catch(Exception ex) {
   System.err.print("Exception: ");
   System.err.println(ex.getMessage());
  }
 }
}

The above is the detailed content of How to read Excel file contents using Java. For more information, please follow other related articles on the PHP Chinese website!