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!!

How to add Help menu to a Swing application

A good tester, don’t suppose customers to ramble for needy  material with application in hand. A simple help file should do the needful. Let’s jump to cram about Help file in Swing. JavaHelp is an Open Source help system. To Download JavaHelp, visit http://javahelp.java.net/. I have used Eclipse IDE for this snippet. Configuring JavaHelp in Eclipse and Creating a HelpSet will be covered in future posts.

Prerequisite

·         JavaHelp should be Downloaded.

·         HelpSet(.hs) should be available.

·         Path of HelpSet(.hs) should be configured in your current class path.

import java.awt.event.KeyEvent;

import javax.help.CSH;

import javax.help.HelpBroker;

import javax.help.HelpSet;

import javax.help.HelpSetException;

import javax.swing.Box;

import javax.swing.JFrame;

import javax.swing.JMenu;

import javax.swing.JMenuBar;

import javax.swing.JMenuItem;

public class SwingAppwithhelp

{

                JFrame appFrame;              

                JMenuBar bar;

                JMenu helpMenu ;

                JMenuItem helpMenuItem;

                HelpSet helpset;

        

               

   public static void main(String args[]){

      new SwingAppwithhelp();

   }

   public SwingAppwithhelp()

   {

                    createGUI();     

   }

                public void createGUI(){

                               

                                appFrame = new JFrame("Swing App with Help");

                                bar=new JMenuBar();        

                               

                                //Arrange components on contentPane and set Action Listeners to each component

                                arrangeComponents();

                               

                                appFrame.setSize(240,300);

                                appFrame.setResizable(false);

                                appFrame.setVisible(true);

                                appFrame.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);

                }

               

                public void arrangeComponents(){

                               

                               

                      // Move help menu to right side           

                    bar.add(Box.createHorizontalGlue());

                    helpMenu = new JMenu("Help");

                    helpMenu.setMnemonic(KeyEvent.VK_H);

                    helpMenuItem = new JMenuItem("Launch Help");

                    helpMenu.add(helpMenuItem);

                    bar.add(helpMenu);

                    appFrame.setJMenuBar(bar); 

                               

                                ClassLoader loader = null;

                    java.net.URL url = HelpSet.findHelpSet(loader, "HelpSet.hs");

                    System.out.println(url.getPath());

                    try {

         helpset = new HelpSet(loader, url);

       } catch (HelpSetException e) {

         System.err.println("Error loading");

         return;

       }

                    

                    HelpBroker helpbroker = helpset.createHelpBroker();                                                          

                               

                                helpMenuItem.addActionListener(new CSH.DisplayHelpFromSource(helpbroker));

                }   

   }

So this how your app should look like, if you followed the above code.Signing off, with this quote "We can't help everyone, but everyone can help someone"



Click Launch help menu item, to invoke Help file