READING EXCEL FILES IN JAVA APPLICATIONS

Have you wondered about how to read excel files(.xls,xlsx), in Java Applications without using any API, then here is your stop. With this you can read excel 2003 and also 2007.We will be going through simple steps in reading the content of excel files and populating it into a Jtable. We will be working in the Microsoft Windows environment.

First, you have to create a datasource pointing to where your excel file is located. To do this, Go to Control panel ——— >   Administrative Tools ———- > Data Sources (ODBC). Below is an interface you are likely to see.

datasource face

Select the “User DSN” tab and click on the add button and select the driver “Microsoft Excel Driver (.xls,xlsx)” . You will be presented with the interface below, enter the name of the data source (Note, this is the name you will reference from you Java Application) and click on the “Select Workbook” button to select the excel file you want to read. You are now done with setting up the datasource.

choose driverselect workbook

Let’s now go to our Jave IDE (Netbeans) and do some small coding.  We will be using the JDBC-ODBC driver for reading our data. You don’t need to add the driver to your application as Windows OS comes with the Driver already installed.

We are creating a simple application where the user enters the datasource name and the sheet name, then on click of a button the data if populated into a JTable

 

We do this by creating a method

private void GetData(String excelDSN, String sheetname) {

        Connection connection = null;

        Statement st;

        ResultSet rsltSet;

        ResultSetMetaData rsltSetMetaData;

        String excelURL = "jdbc:odbc:" + excelDSN;

        String query = "Select * from [" + sheetname + "$]";

 

        try {

            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

            connection = DriverManager.getConnection(excelURL);

            //System.out.println("ReadExcel connection = " + connection);

            st = connection.createStatement();

            //System.out.println("ReadExcel connection.createStatement() = " + st);

            rsltSet = st.executeQuery(query);

            //System.out.println("ReadExcel rsltSet = " + rsltSet);

            rsltSetMetaData = rsltSet.getMetaData();

           

          //creating a datamodel for our JTable

            DefaultTableModel aModel = new DefaultTableModel() {

                @Override

                //setting the JTable uneditalble

                public boolean isCellEditable(int row, int column) {

                    return false;

                }

            };

            //adding the excel column names to the JTable

            Object[] tableColumnNames = new Object[rsltSetMetaData.getColumnCount()];

            for (int i = 1; i <= rsltSetMetaData.getColumnCount(); i++) {

                tableColumnNames[i-1] = rsltSetMetaData.getColumnName(i);

            }

            aModel.setColumnIdentifiers(tableColumnNames);

            //adding the rows to the JTable

            Object[] objects = new Object[rsltSetMetaData.getColumnCount()];

            while (rsltSet.next()) {

                for (int i = 1; i <= rsltSetMetaData.getColumnCount(); i++) {

                    objects[i-1] = rsltSet.getObject(i);

                }

                aModel.addRow(objects);

            }

            // setting the table model of the jtable

            this.jTable1.setModel(aModel);

        } catch (Exception ex) {

            JOptionPane.showMessageDialog(null, ex.toString() );

        }

    }

Note , In reading records from excel, the index starts from one not zero(0). Once you starting getting this exception “[Microsoft][ODBC Driver Manager] Invalid descriptor index.” Get the index , its 1 not zero

Sample interface of the Application

the app

Advertisements

One thought on “READING EXCEL FILES IN JAVA APPLICATIONS

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s