Importing Excel (.xls) in Java Swing application

How to import Microsoft Excel (XLS) fil开发者_Go百科e into the Java Swing application?

There are a couple of open source libraries which you can use to read an excel file such as Apache POI.

Once you've read your excel file into some data structure, this can be written into a DataModel for a JTable. Alternatively, you could write a custom DataModel which will read dynamically from the excel file, but I'd take the two steps seperately and build up the complexity once you understand the excel file reading library you choose.

Here's a simple tutorial that exports data from JTable into excel file, using Tab-Separated Values(TSV) format: How To Export Records From JTable To MS Excel.

The application provides an Export button, which then triggers a dialog box (JFileChooser) to assist the user in specify the file location/destination.

JTable implementation

Exported excel file (manually adjusted column widths)

Hope this code will help you.

import java.io.File;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
public class ExcelSheetReader{
   private static final String DRIVER_NAME = "sun.jdbc.odbc.JdbcOdbcDriver";
   private static final String DATABASE_URL = "jdbc:odbc:Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=%s;DriverID=22;READONLY=false";

   private static final String FILEPATH = "C:/Documents and Settings/web/Desktop/Employee.xlsx";

   private static Connection con = null;
   private static Statement stmt = null;

   private Connection getConnection(File file) throws ClassNotFoundException, SQLException{
       Class.forName(DRIVER_NAME);
       Connection con = DriverManager.getConnection(String.format(DATABASE_URL, file.getAbsolutePath()));
       return con;
   }

     public List<String> getData(File file, String []columns) throws SQLException {

       List<String> list = null;
          try {
              ResultSet rs = getRecord(file, "select emailid from [Sheet1$]");
              ResultSetMetaData rsmd = rs.getMetaData();
              System.out.println(rsmd.getColumnCount());

             while (rs.next()) {
                 if (list==null)
                        list = new  ArrayList<String>();
                 for (int i=0; i<columns.length; i++) {
                     list.add(rs.getString(columns[i]));
                 }
             }
             rs.close();
           } catch (Exception e) {
            }
          finally {
             closeConnection();
             return list;
          }
   }
   private void closeConnection() throws SQLException {
       if (stmt!=null) {
           stmt.close();
           stmt = null;
       }

       if (con!=null) {
           con.close();
           con = null;
       }

}
ResultSet getRecord(File file, String query) throws ClassNotFoundException, SQLException {
        con = getConnection(file);
       stmt = con.createStatement();
       ResultSet rs = stmt.executeQuery(query);
    return rs;
}
public static void main(String[] args) {
       /*ExcelSheetReader reader = new ExcelSheetReader();
       String[] columns = {"emailid"};
       File file = new File(FILEPATH);
       List<String> list = null;
    try {
        list = reader.getData(file,columns);
    } catch (SQLException e) {
        e.printStackTrace();
    }
       for (Iterator<String> iterator = list.iterator(); iterator.hasNext();) {
        String string = (String) iterator.next();
        System.out.println(string); 
       }
       */
       ExcelSheetReader reader = new ExcelSheetReader();
        File file = new File(FILEPATH);
        if (file.exists()) {
            try {
                ResultSet rs = reader.getRecord(file, "select emailid from [Sheet1$]");
                ResultSetMetaData rsmd = rs.getMetaData();
                int count = rsmd.getColumnCount();
                //System.out.println(count);
                while (rs.next()) {
                    for (int i=1; i<=count; i++) {
                        System.out.println(rsmd.getColumnName(i)+": "+rs.getString(rsmd.getColumnName(i)));
                    }
                }
            } catch (ClassNotFoundException e) {
                e.printStackTrace();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
   }
}