How to Work with Excel Files Using NetBeans

Implementing Excel File Loading and Displaying

1. In the Inspector window, right-click jMenuItem1 and choose Events > Action > actionPerformed to generate the action handler.
2. In the generated jMenuItem1ActionPerformed method, we implement actual loading and displaying of Excel data with the following code. (The File object is java.io.File.)

        File file = chooseExcelFile();
        // do nothing if open dialog was cancelled
        if (file == null) {
            return;
        }

        // loading long excel tables may be time consuming, so use wait cursor
        setCursor(Cursor.getPredefinedCursor(Cursor.WAIT_CURSOR));

        Sheet sheet = readExcelSheet(file);
        if (sheet != null) {
            displaySheet(sheet, jTable1);
        }

        setCursor(Cursor.getDefaultCursor());

3. The methods of interest are especially readExcelSheet(File) and displaySheet(Sheet, Table). Here is the implementation of readExcelSheet, which gives an example of how to load Excel data from a file and includes simple error handling:

    /** Reads given excel file and returns its first sheet.
     *
     * @param file Input excel spreadsheet file
     * @return First sheet or null if file can't be read or have no sheets
     */
    private Sheet readExcelSheet (File file) {
        // open excel file (workbook) for reading
        Workbook wbk;
        try {
            wbk = Workbook.getWorkbook(file);
        } catch (Exception ex) {
            JOptionPane message = new JOptionPane(
                    "Can't read excel file " + file.getPath(),
                    JOptionPane.ERROR_MESSAGE);
            return null;
        }
        if (wbk.getNumberOfSheets() <= 0) {
            JOptionPane message = new JOptionPane(
                    "Excel file doesn't have any sheets.",
                    JOptionPane.ERROR_MESSAGE);
            return null;
        }
        // return first sheet
        return wbk.getSheet(0);
    }

4. The preferred way to display a loaded sheet is by following the Swing model-view-controller architecture. This means we will implement our own model for the JTable which will enable the display of data objects from JExcelApi in the JTable. Here is the implementation of the displaySheet method and the TableModel:

    /** Takes given sheet data and displays them in very simple way in
     * given table.
     *
     * @param sheet Excel sheet
     * @param table JTable to display sheet data in
     */
    private void displaySheet(Sheet sheet, JTable table) {
        table.setModel(new SheetTableModel(sheet));
    }

    /** Read-only TableModel implementation, which adapts Sheet object from
     * JExcelApi library for use as JTable model.
     */
    private static class SheetTableModel implements TableModel {

        private Sheet sheet;

        public SheetTableModel (Sheet sheet) {
            this.sheet = sheet;
        }

        public int getRowCount() {
            return sheet.getRows();
        }

        public int getColumnCount() {
            return sheet.getColumns();
        }

        /** Copied from javax.swing.table.AbstractTableModel,
         * to name columns using spreadsheet conventions:
         *  A, B, C, . Z, AA, AB, etc.
         */
        public String getColumnName(int column) {
            String result = "";
            for (; column >= 0; column = column / 26 - 1) {
                result = (char)((char)(column%26)+'A') + result;
            }
            return result;
        }

        public Class<?> getColumnClass(int columnIndex) {
            return String.class;
        }

        public boolean isCellEditable(int rowIndex, int columnIndex) {
            // table is read only for demo purposes
            return false;
        }

        public Object getValueAt(int rowIndex, int columnIndex) {
            Cell cell = sheet.getCell(columnIndex, rowIndex);
            return cell.getContents();
        }

        public void setValueAt(Object aValue, int rowIndex, int columnIndex) {
            // no operation, table is read only for demo purposes
        }

        public void addTableModelListener(TableModelListener l) {
            // no operation, table is read only for demo purposes
        }

        public void removeTableModelListener(TableModelListener l) {
            // no operation, table is read only for demo purposes
        }

    }  // end of SheetTableModel

These were the most interesting parts of the code, and our application is now able to load and display Excel files.

Finalizing and Testing ReadExcel Application

To complete our small application, we need to let the user choose the Excel file that is read and add some other final touches, so let's go for it.

1. Add code which uses JFileChooser to let the user specify an input file:

    /** Opens dialog for user to choose an excel file to open and read.
     *
     * @return Excel file or null if user cancelled the dialog
     */
    private File chooseExcelFile () {
        JFileChooser chooser = new JFileChooser();
        chooser.setFileFilter(new XLSFilter());

        int returnVal = chooser.showOpenDialog(this);
        if (returnVal == JFileChooser.APPROVE_OPTION) {
            return chooser.getSelectedFile();
        }
        // cancel was clicked
        return null;
    }

    /** Filter which accepts only xls files */
    private static class XLSFilter extends FileFilter {
        public boolean accept(File f) {
            return f.isDirectory() || f.getName().endsWith(".xls");
        }

        public String getDescription() {
            return "Excel spreadsheet XLS files";
        }
    }

2. Switch back to the Design view of ReadExcelUI.java by clicking the Design button in the toolbar. Add an exit action handler - right-click the jMenuItem2 node in the Inspector window and choose Events > Action > actionPerformed to generate action handler. In generated jMenuItem2ActionPerformed method, implement a simple exit:

        dispose();
        System.exit(0);

3. Import classes used in code we added in previous steps - choose Source > Fix Imports in main menu and and after the dialog is shown, choose to import javax.swing.filechooser.FileFilter and java.io.File. All red lines on the right side of the editor should vanish and file ReadExcelUI.java should be compilable without any errors at this point.
4. Our application should be complete, so click Shift+F6 key shortcut (while ReadExcelUI.java is active) to actually run it.
5. After application frame is shown, go to its menu File > Open. and find example Excel file, which is located in your JExcelApi installation, namely <JEXCEL_HOME>/jxlrwtest.xls and click Open.
6. Excel file is loaded and shown in the table and should look like this: