Swing Application - Imports contents to JTable from opted Excel sheet/tab

This feed shows how to import excel contents to JTable. To start, this application has a provision for users to select excel workbook (JFileChooser). Once selected excel file is imported, all available worksheets are listed to user(JOptionPane). Now based upon the opted excel sheet corresponding contents are loaded in Tabular format (JTable)

To Note,

• Accepts only .xls format
• Only one excel sheet can be imported at a time
• Empty cells are respected.
• Very first row of excel sheet is considered as header for the JTable
• HSSF is used to access excel workbook through Java

excelToJTable.java 

import java.awt.*;
import java.awt.event.*;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.Vector;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.*;
import javax.swing.table.DefaultTableModel;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

public class excelTojTable extends JFrame {

    static JTable table;
    static JScrollPane scroll;
    // header is Vector contains table Column
    static Vector headers = new Vector();
    static Vector data = new Vector();
    // Model is used to construct
    DefaultTableModel model = null;
    // data is Vector contains Data from Excel File 
    static Vector data = new Vector();
    static JButton jbClick;
    static JFileChooser jChooser;
    static int tableWidth = 0;
    static int tableHeight = 0;
 
public excelTojTable()
 {
      super("Import Excel To JTable");
      setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
      JPanel buttonPanel = new JPanel();
      buttonPanel.setBackground(Color.white);
      jChooser = new JFileChooser();
      jbClick = new JButton("Select Excel File");
      buttonPanel.add(jbClick, BorderLayout.CENTER);

        // Show Button Click Event
          jbClick.addActionListener(new ActionListener()
            {
                     @Override public void actionPerformed(ActionEvent arg0)
                              {
                                     jChooser.showOpenDialog(null);
                                     jChooser.setDialogTitle("Select only Excel workbooks");
                                     File file = jChooser.getSelectedFile();
                                    if(file==null)
                                      {
                                          JOptionPane.showMessageDialog(
                                          null, "Please select any Excel file",
                                          "Help",
                                          JOptionPane.INFORMATION_MESSAGE);
                                          return;
                                        }
                                    else if(!file.getName().endsWith("xls"))
                                       {
                                             JOptionPane.showMessageDialog(
                                             null, "Please select only Excel file.",
                                            "Error",JOptionPane.ERROR_MESSAGE);
                                       }
                                    else
                                      {
                                            fillData(file);
                                            model = new DefaultTableModel(data, headers);
                                            tableWidth = model.getColumnCount() * 150;
                                            tableHeight = model.getRowCount() * 25;
                                            table.setPreferredSize(new Dimension( tableWidth, tableHeight));
                                             table.setModel(model);
                                         }
                              }
            });
         table = new JTable();
         table.setAutoCreateRowSorter(true);
         model = new DefaultTableModel(data, headers);
         table.setModel(model);
         table.setBackground(Color.pink);
         table.setAutoResizeMode(JTable.AUTO_RESIZE_OFF);
         table.setEnabled(false);
         table.setRowHeight(25);
         table.setRowMargin(4);
         tableWidth = model.getColumnCount() * 150;
         tableHeight = model.getRowCount() * 25;
         table.setPreferredSize(new Dimension( tableWidth, tableHeight));
         scroll = new JScrollPane(table);
         scroll.setBackground(Color.pink);
         scroll.setPreferredSize(new Dimension(300, 300));
         scroll.setHorizontalScrollBarPolicy( JScrollPane.HORIZONTAL_SCROLLBAR_AS_NEEDED);
         scroll.setVerticalScrollBarPolicy( JScrollPane.VERTICAL_SCROLLBAR_AS_NEEDED);
         getContentPane().add(buttonPanel, BorderLayout.NORTH);
         getContentPane().add(scroll, BorderLayout.CENTER);
         setSize(600, 600);
         setResizable(true);
         setVisible(true);
 }

// Fill JTable with Excel file data. * * @param file * file :contains xls file to display in jTable 

  void fillData(File file)
      {
         int index=-1;
         HSSFWorkbook workbook = null;
        try {
               try {
                       FileInputStream inputStream = new FileInputStream (file);
                        workbook = new HSSFWorkbook(inputStream);
                    }
               catch (IOException ex)
                    {
                         Logger.getLogger(excelTojTable.class. getName()).log(Level.SEVERE, null, ex);
                     }

                       String[] strs=new String[workbook.getNumberOfSheets()];
                      //get all sheet names from selected workbook
                        for (int i = 0; i < strs.length; i++) {
                             strs[i]= workbook.getSheetName(i); }
                        JFrame frame = new JFrame("Input Dialog");
                     
                        String selectedsheet = (String) JOptionPane.showInputDialog(
                           frame, "Which worksheet you want to import ?", "Select Worksheet",
                          JOptionPane.QUESTION_MESSAGE, null, strs, strs[0]);
               
                       if (selectedsheet!=null) {
                            for (int i = 0; i < strs.length; i++)
                              {
                                 if (workbook.getSheetName(i).equalsIgnoreCase(selectedsheet))
                                 index=i; }
                            HSSFSheet sheet = workbook.getSheetAt(index);
                            HSSFRow row=sheet.getRow(0);
                       
                           headers.clear();
                           for (int i = 0; i < row.getLastCellNum(); i++)
                          {
                             HSSFCell cell1 = row.getCell(i);
                             headers.add(cell1.toString());
                          }
                       
                          data.clear();
                          for (int j = 1; j < sheet.getLastRowNum() + 1; j++)
                          {
                             Vector d = new Vector();
                             row=sheet.getRow(j);
                             int noofrows=row.getLastCellNum();
                             for (int i = 0; i < noofrows; i++)
                             {    //To handle empty excel cells 
                                   HSSFCell cell=row.getCell(i,
                                   org.apache.poi.ss.usermodel.Row.CREATE_NULL_AS_BLANK );
                                   d.add(cell.toString());
                             }
                            d.add("\n");
                            data.add(d);
                          }
                     }
                    else { return; }
        }
      catch (Exception e) { e.printStackTrace(); } }
     public static void main(String[] args)
       { new excelTojTable(); }
   }

Here is the flow

Want to download above code ?? click this link excelToJTable. Below cited, is used/sample excel data.

Preview of the excel sheet used as input

Let me know if you encounter any issues with above code. As usual, share your comments any time!!

Swing Application - Searches DB & display results through JTable in JTabbedPane

This feed narrates how to search for a record in a JTabbedPane. The search results are displayed in tabular form using JTable. To download these java files, please click on corresponding filename. Lets drill down, to the code.

• OrderView - Main class, which calls out layout design. (i.e UI)
• ItemTabelModel - An abstract table model, to display search results in tabular form
• OrderDAO - Takes care of DB interaction, I have used MySQL
• OrderInfo - A class to demonstrate order object

OrderView.java

• Creates JTabbedPane with four panels - Create, Edit, Delete & Search
• Searches for record in DB using OrderDAO class
• Search is invoked by "Enter" key as well as "Search" button
• Zero & Empty search results are handled
• Search result-set size need not be one.
• Implements listeners for KeyTyped, KeyPressed and KeyReleased events
• Display search results in tabular form using JTable
• Snippet shared here is confined to "Search" tab, other tabs are not designed

  public class OrderView implements ActionListener {

ArrayList orderList;
OrderDAO oDAO; <- For DB interaction
JFrame appFrame;
    JLabel jlbName, jlbItem;
    JTextField jtfName,jtfDate;
    JTabbedPane appPane;
    JButton jbbSave, jbnDelete, jbnClear, jbnUpdate, jbnSearch;
    JTable jtbOrder;
    JScrollPane sp;
    JPanel jPaneCreate,jPaneEdit,jPaneDelete,jPaneSearch;
   
    String name, address, email;
    int recordNumber;
    String nameStr;   
    private ItemTableModel ItemTabModel;
   
    public static void main(String args[]){
        new OrderView(); 
     }
    
    public OrderView()
    {       
      createGUI();    
      orderList = new ArrayList();
      oDAO=new OrderDAO();
    }
    
    public void createGUI(){

    /*Create a frame, get its contentpane and set layout*/
    appFrame = new JFrame("View your order status");
    appPane=new JTabbedPane();
    jPaneCreate=new JPanel(new GridLayout(5,2));
    jPaneEdit=new JPanel(new GridLayout(5,2));
    jPaneDelete=new JPanel(new GridLayout(5,2));
    jPaneSearch=new JPanel(new GridLayout(5,2));   
   
    appPane.setTabPlacement(JTabbedPane.LEFT);   
    appFrame.getContentPane().add(appPane);
    appPane.add("Create New",jPaneCreate);
    appPane.add("Edit/Update",jPaneEdit);
    appPane.add("Delete",jPaneDelete);
    appPane.add("Search",jPaneSearch);
   
    //set shortcuts for each tab
      appPane.setMnemonicAt(0 , KeyEvent.VK_C);
    appPane.setMnemonicAt(1 , KeyEvent.VK_E);
    appPane.setMnemonicAt(2 , KeyEvent.VK_D);
    appPane.setMnemonicAt(3 , KeyEvent.VK_S);   
    //Arrange components on contentPane and set Action Listeners to each JButton
    arrangeComponentsCreate();
     arrangeComponentsEdit();
    arrangeComponentsDelete();
    arrangeComponentsSearch(); 
           
    appFrame.pack();
    appFrame.setVisible(true);
    appFrame.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
    }
   
    private void arrangeComponentsSearch() {
     jlbName = new JLabel("Customer Name");
     jlbItem = new JLabel("Item Ordered");  
     jtfName=new JTextField(20);
     jtfDate=new JTextField(20);
     //listeners ensure typed search strings are converted to uppercase, as & when key typed
     jtfName.addKeyListener(new KeyAdapter(){
     public void keyReleased(KeyEvent e) {
                JTextField textField = (JTextField) e.getSource();
                String text = textField.getText();
                textField.setText(text.toUpperCase());
            }

            public void keyTyped(KeyEvent e) {
                // TODO: Do something for the keyTyped event
             JTextField textField = (JTextField) e.getSource();
                String text = textField.getText();
                textField.setText(text.toUpperCase());
            }
          //Invoke search person on "Enter" button event
            public void keyPressed(KeyEvent e) {
                // TODO: Do something for the keyPressed event
             JTextField textField = (JTextField) e.getSource();
                String text = textField.getText();
                textField.setText(text.toUpperCase());
                if (e.getKeyCode()== KeyEvent.VK_ENTER )
                {
                 searchPerson();
                }
            }
     });
    
       GridBagConstraints c = new GridBagConstraints();
        setMyConstraints(c,0,0,GridBagConstraints.CENTER);
        jPaneSearch.add(getFieldPanel(),c);
        setMyConstraints(c,0,1,GridBagConstraints.CENTER);
        jPaneSearch.add(getButtonPanelSearch(),c);         
        jtbOrder=new JTable();
ItemTabModel=new ItemTableModel();
jtbOrder.setModel(ItemTabModel); 
 sp=new JScrollPane(jtbOrder); 
        jbnSearch.addActionListener(this);
}  
    
    public void actionPerformed (ActionEvent e){  
     if (e.getSource() == jbnSearch)
      searchPerson();//clicking search button should invoke searchPerson() function   
    }

private JPanel getButtonPanelSearch() {
//Search Button
JPanel p = new JPanel(new GridBagLayout());
    GridBagConstraints c = new GridBagConstraints();
    setMyConstraints(c,0,0,GridBagConstraints.CENTER);
     p.add(jbnSearch,c);
return p;
}

     private JPanel getFieldPanel() {
       JPanel p = new JPanel(new GridBagLayout());
       p.setBorder(BorderFactory.createTitledBorder("Details"));
       GridBagConstraints c = new GridBagConstraints();
       setMyConstraints(c,0,0,GridBagConstraints.EAST);
       p.add(jlbName,c);
       setMyConstraints(c,1,0,GridBagConstraints.WEST);
       p.add(jtfName,c);
       setMyConstraints(c,0,1,GridBagConstraints.EAST);
       p.add(jlbItem,c);
       setMyConstraints(c,1,1,GridBagConstraints.WEST);
       p.add(jtfDate,c);
       return p;
     }

    private static void setMyConstraints(GridBagConstraints c, 
           int gridx, int gridy, int anchor) {
           c.gridx = gridx;//manages the layout of controls
           c.gridy = gridy;
           c.anchor = anchor;
        }
    
public void searchPerson() {        
     name = jtfName.getText();    
    /*clear contents of arraylist if there are any from previous search*/    
    orderList.clear();
    recordNumber = 0;

    if(name.equals("")){
    JOptionPane.showMessageDialog(null,"Please enter person name to search.");
                         //when a empty string is searched
    clear();
    }
    else{
    /*get an array list of searched persons using PersonDAO*/
    orderList = oDAO.searchPerson(name);
    if(orderList.size() == 0){
    JOptionPane.showMessageDialog(null, "No records found.");
    //Perform a clear if no records are found.-Refer screenshot
    clear();
    }
    else
    {    
    //Erasing previous history
    recordNumber=orderList.size();
    ItemTabModel.removeall();
    jPaneSearch.remove(jtbOrder);
    jPaneSearch.remove(sp);
    jPaneSearch.repaint();    
    //If there are more search results, display all in tabular form
    while(recordNumber>0)
    {
    /*downcast the object from array list to OrderInfo*/
    OrderInfo person = (OrderInfo) orderList.get(recordNumber-1); 
                  ItemTabModel.addOrderInfo(person);
                   recordNumber--;
    }    
    //Redraws the table
    jPaneSearch.add(sp);
    jPaneSearch.revalidate();   
    }    
    clear();        
    }
}
private void clear() {
//Clears the textbox and sets focus
jtfName.setText("");
jtfName.requestFocusInWindow();
}
 }

Here are the snapshots of OrderView Application

I have not briefed other java files since they are trivial in function. Modify these code to your needs and let me know if you face any errors. To note, ensure you have MySQL connector jar in java build path to access database. Kindly share your comments, if any