2 January 2012

Import Excel Data into JTable

Import Excel Data into JTable

Hello Friends,

Nice to see you again. This post is the requirement which is asked from my fellow friends amongst you..
In Previous Sessions  we have seen the examples related to JTable with excel and jTable with MySQL database.

Here We will reverse the previous JTable example of export JTable data into Excel sheet.. 
We will create one Window which  will ask from user for import excel file.

After click on Select Excel File button, fileChooser will be opened and User 
has to select only Excel file and the excel sheet data will be displayed in JTable.

As there is no such direct method which provide you this facility at once.
So we have to write some sort of code to enable of filling JTable with Data from Excel file.

It is very easy to understand the code for this. When you run the code following will be output:



Here in above window, user will see nothing but one button for asking Excel file. Just click on that button and you will see fpllowing:



Here is the jFilechooser, from this you have to choose excel file having extension ".xls".  Otherwise following Error message will be displayed:

Note: If you want to go with MS Office 2007 Excel, you have to customise this code little bit.
Now  after select excel file the data in that Excel file will be shown in JTable as below:

OK For this you have to create one class called  excelTojTable.java 
and paste the below code in it.

And run it....Simple right..?

Note: In this code All the Dynamic Part logic is done in 
              fillData()  method.  So focus on that method...

Note:    To run this example successfully you have to download and add "jxl.jar" file.


import java.awt.*;
import java.awt.event.*;
import java.io.File;
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 jxl.Cell;

import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;

public class excelTojTable extends JFrame {

 static JTable table;
 static JScrollPane scroll;
 // header is Vector contains table Column
 static Vector headers = new Vector();
 // Model is used to construct JTable
 static 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; // set the tableWidth
 static int tableHeight = 0; // set the tableHeight

 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);
    
    File file = jChooser.getSelectedFile();
    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) {

  Workbook workbook = null;
  try {
   try {
    workbook = Workbook.getWorkbook(file);
   } catch (IOException ex) {
    Logger.getLogger(
      excelTojTable.class.
      getName()).log(Level.SEVERE, 
        null, ex);
   }
   Sheet sheet = workbook.getSheet(0);
   
   headers.clear();
   for (int i = 0; i < sheet.getColumns(); i++) {
    Cell cell1 = sheet.getCell(i, 0);
    headers.add(cell1.getContents());
   }

   data.clear();
   for (int j = 1; j < sheet.getRows(); j++) {
    Vector d = new Vector();
    for (int i = 0; i < sheet.getColumns(); i++) {

     Cell cell = sheet.getCell(i, j);
     
     d.add(cell.getContents());

    }
    d.add("\n");
    data.add(d);
   }
  } catch (BiffException e) {
   e.printStackTrace();
  }
 }

 public static void main(String[] args) {

  new excelTojTable();
 }
}

--------------------------------------------------------------------

So I hope this code is helpful in understanding Of Import Excel Data in JTable.

Feel free to ask me if you have any dought regarding this.
You can also give me suggessions to improve this tutorial.

Leave comments if you like this post...!

Thank you.
Nirav Raval

----------------------------------------------------------------------------------
Follow Other Java Swing Tricks....

14 comments:

  1. thanks yaar awesome......:-)

    ReplyDelete
    Replies
    1. hey bro ... what changes we need to do for .xlsx format...
      thanks in advance

      Delete
  2. thanks Nirav.... Also i want to import from excel 2007 also...tell the Modification...Thanks in advance...

    ReplyDelete
  3. hey dude
    nice tutorial
    u r the best
    btw .. i need to discuses things with u regarding this
    can you email me plz: cheat.2.win.m@hotmail.com

    ReplyDelete
  4. Really very useful.

    thanks for such wonderful code.

    ReplyDelete
  5. Very Nice and easy to understand.....Thanks a lot ....

    ReplyDelete
  6. i need to read excel 2007 and 2010. Can you please help..

    ReplyDelete
  7. very good...thanks

    ReplyDelete
  8. Nice Tutorial i googled a lot but found it best.
    This program import only the zero indexed excel sheet if my excel file have multiple sheets how could i import 1 of my choice.
    I use a JComboBox where i get all sheet names as item but when i add listener and call filldata(). it gives me error please help me

    ReplyDelete
  9. Thank you very much....!! good work dude..

    ReplyDelete
  10. How to change the code for Excel2012.Please help me

    ReplyDelete
  11. Need Help regarding updating the JTable as soon as i make changes in Excel

    ReplyDelete

Leave Comment If You Like This...Thank You..!