Question: Can I read an Excel file with Java? If you can, how to do it?
Answer: Yes, you can read Microsoft Excel files with Java. Microsoft provides an Excel's ODBC driver, so we can use JDBC and Sun's JDBC-ODBC driver to read the Excel file.
If you have an Excel file, named book1.xls (Translator Note: I didn't download it because I didn't download it, so I used my own example), and there is a worksheet in this file (Sheet) named Sheet1 The specific format can be found in the figure below:
Microsoft's ODBC driver puts the first line in the work table as a column name (translator Note: ie field name), the work table name as the database table name.
To access a worksheet through the JDBC, we must also create a new ODBC data source that creates a data source on the Windows 2000 system, as follows:
Go to "Control Panel" -> Manage Tool "-> Data Source (ODBC)", (Translator Note: After opening, select System DSN), click Add, select "Driver do Microsoft Excel" in the pop-up window "Driver Do Microsoft Excel" .xls ", as shown in Figure 1:
Figure 1. Create a new data source
Then enter you a name book1 at the data source name (Translator Note: It is equivalent to the database name), then click "Select Workbook" and then find and select your Excel file, see Figure 2:
Figure 2. Select a workbook
After clicking OK, you will appear in the system data source list, see the figure below:
Figure 3. New data source added completion
The data table is now in the data source list (the translator Note: Click OK to complete the configuration).
(Translator Note: My example) Now if we want to pick all the Test value in the Test1 column, you need to use the following SQL query:
SELECT TEST1 from [Sheet1 $] Where test1 = 'test'
It should be noted that the work name is followed by a "$" symbol, which is indispensable. why? Because of his front and rear brackets, because "$" is the reserved word in the SQL statement. Life Is Never Easy (Translator Note: The author is emotional).
The following procedure is an example: 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 ("Check Match 'Test' Test1 record To: "); 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 of a data table and takes out the qualified record. (Translator Note: In addition, I have a program here, which is to read all records, only for reference):
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) // separates each column 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 ());}}}
reference:
http://www.9cbs.net/develop/read_article.asp?id=16298 http://www.javaworld.com/javaworld/javaqa/2001-06/04-qa-0629-excel.html
