Exporting JTable data to Excel

Recently I wanted to find a way to export JTable data to Excel. I found an excellent example of how to do this in Swing Hacks by Joshusa Marinacci and Chris Adamson. The authors show an example of saving JTable data to a tab delimited file with a “.xls” extension. The file can then be opened by Excel and converted to a spreadsheet.

I liked their approach, but I also wanted to provide the capability to launch Excel with the file opened in it as I’ve seen in other applications. So, I added some code to create a JPopupMenu component with options for saving the data or opening in Excel.

I also wanted to keep Excel from converting product numbers (specifically ISBN numbers) to numeric data in the spreadsheet. The reason for this is that leading zeros can be part of an ISBN.

My example along with the Marinaccci’s and Admanson’s JTable to Excel code follows. One caveat, I offer no explanation of the authors’ code. After all, it’s in their book. For a complete explanation see Swing Hacks. It is an excellent book full of tricks that every Swing developer should have.

Enjoy.

import java.awt.*;
import java.awt.event.*;

import javax.swing.*;
import java.io.*;
import javax.swing.table.*;


public class ExcelTest {

 /**
  * @param args
  */
 public static void main(String[] args) {
//  An array of book titles and their associated ISBN numbers
  String [][] data = {
    {"Summerall","0785214925"},
    {"The Secret Message of Jesus","084990000X"},
    {"Buck Wild","159555064X"},
    {"25 Ways to Win with People","0785260943"},
    {"Aesop and the CEO ","0785260102"},
    {"ALL Business is Show Business ","0785206086"},
    {"Becoming A Person of Influence","0785271007"},
    {"Checklist for Life for Leaders","0785260013"},
    {"Duct Tape Marketing ","078522100X"},
    {"38 Values to Live By ","0849916631"},
    {"Blue Moon","0785260641"},
    {"Blue Like Jazz ","9780785263708"},
    {"Wild at Heart ","0785262989"},
    {"Wild Men, Wild Alaska ","078521772X "},
    {"The Duct Tape Bible, NCV","0718018249"}
  };
  String [] headers = {"Title","ISBN"};
  final JFrame frame = new JFrame("JTable to Excel Hack");
  DefaultTableModel model = new DefaultTableModel(data,headers);
  final JTable table = new JTable(model);
  JScrollPane scroll = new JScrollPane(table);

//  my JPopupMenu component
  final JPopupMenu popup = new JPopupMenu();

//  the save JMenuItem and its associated ActionListener
  JMenuItem save = new JMenuItem("save to file");
  save.addActionListener(new
    ActionListener() {
   public void actionPerformed(ActionEvent action){
    try {
     ExcelExporter exp = new ExcelExporter();
     exp.exportTable(table, new File("results.xls"));
    }
    catch (IOException ex) {
     System.out.println(ex.getMessage());
     ex.printStackTrace();
    }
   }
  });
  popup.add(save);

//  The open JMenuItem and its associated ActionListener
  JMenuItem open = new JMenuItem("open in Excel");
  open.addActionListener(new
    ActionListener() {
   public void actionPerformed(ActionEvent action){
    try {
//     Note that i'm actually saving the file first
     ExcelExporter exp = new ExcelExporter();
     File file = new File("results1.xls");
     exp.exportTable(table, file);
     ExcelOpener opn = new ExcelOpener();
     opn.openTable(file);
    }
    catch (IOException ex) {
     System.out.println(ex.getMessage());
     ex.printStackTrace();
    }

   }
  });
  popup.add(open);

//  the following method only works in JDK 5.0 or greater
//  table.setComponentPopupMenu(popup);

  JLabel label1 = new JLabel("Right Click to Export Data...", JLabel.CENTER);

//  the following code is needed for JDK 1.4
  table.addMouseListener(new MouseAdapter() {
   public void mousePressed(MouseEvent event){
    if(popup.isPopupTrigger(event)){
     popup.show(event.getComponent(), event.getX(),event.getY());
    }
   }
   public void mouseReleased(MouseEvent event){
    if(popup.isPopupTrigger(event)){
     popup.show(event.getComponent(), event.getX(),event.getY());
    }
   }
  });

  frame.getContentPane().add("Center",scroll);
  frame.getContentPane().add("South",label1);
  frame.pack();
  frame.setVisible(true);
 }
}
class ExcelExporter {
 public ExcelExporter() {}
 public void exportTable(JTable table, File file) throws IOException {
  TableModel model = table.getModel();
  FileWriter out = new FileWriter(file);

  for(int i=0; i < model.getColumnCount();i++) {
   out.write(model.getColumnName(i)+"\t");
 }
 out.write("\n");

 for(int i=0; i < model.getRowCount();i++){
  for(int j=0;j < model.getColumnCount();j++){
//   I added this check for the ISBN conversion
   if(j==0) {
//    the book Title
    out.write(model.getValueAt(i,j).toString() + "\t");
   } else {
/*
the ISBN Number
Note that I added a \" to the front of the string
and a \t followed by a closing \" to let Excel know
that this field is to be converted as text
     */
    out.write("\""+model.getValueAt(i, j).toString()+"\t"+"\"");
   }
  }
  out.write("\n");
 }
 out.close();
 System.out.println("write to " + file);
}
}
class ExcelOpener {
 public ExcelOpener() {}
 public void openTable(File file) throws IOException {
  Runtime run = Runtime.getRuntime();
//  I make the assumption that the client has Excel and
//  the file type .XLS is associated with Excel

//  This is a simple check to find out the operating system
  String lcOSName = System.getProperty("os.name").toLowerCase();
  boolean MAC_OS_X = lcOSName.startsWith("mac os x");
  if(MAC_OS_X){
   run.exec("open "+ file);
  } else {
   run.exec("cmd.exe /c start " + file);
  }
  System.out.println(file + " opened");
 }
}

Advertisements

About Rick
Many years of technology experience working in both the public and private sector. I hold undergraduate degrees in Information Systems and Management as well as a Master in Business Administration. Currently I'm Vice President of Information Technology for Thomas Nelson Publishers, Inc.

17 Responses to Exporting JTable data to Excel

  1. Anonymous says:

    I like the idea, but the implementation doesn’t quite work right.

    Thanks for the help though.

  2. Rick says:

    What problems are you having? I’ve tested this on both the PC and the Mac and had no issues.

    Rick.

  3. Anonymous says:

    where is the excelOpener class?

  4. Rick says:

    Sorry for the confusion, I’ve made some changes to the blog and it looks like part of the java code was deleted. I’ll correct and repost.

    Thanks!

    Rick.

  5. mona says:

    import java.awt.*;
    import java.awt.event.*;

    import javax.swing.*;
    import java.io.*;
    import javax.swing.table.*;

    public class ExcelTest {

    /**
    * @param args
    */
    public static void main(String[] args) {
    // An array of book titles and their associated ISBN numbers
    String [][] data = {
    {“Summerall”,”0785214925″},
    {“The Secret Message of Jesus”,”084990000X”},
    {“Buck Wild”,”159555064X”},
    {“25 Ways to Win with People”,”0785260943″},
    {“Aesop and the CEO “,”0785260102”},
    {“ALL Business is Show Business “,”0785206086”},
    {“Becoming A Person of Influence”,”0785271007″},
    {“Checklist for Life for Leaders”,”0785260013″},
    {“Duct Tape Marketing “,”078522100X”},
    {“38 Values to Live By “,”0849916631”},
    {“Blue Moon”,”0785260641″},
    {“Blue Like Jazz “,”9780785263708”},
    {“Wild at Heart “,”0785262989”},
    {“Wild Men, Wild Alaska “,”078521772X “},
    {“The Duct Tape Bible, NCV”,”0718018249″}
    };
    String [] headers = {“Title”,”ISBN”};
    final JFrame frame = new JFrame(“JTable to Excel Hack”);
    DefaultTableModel model = new DefaultTableModel(data,headers);
    final JTable table = new JTable(model);
    JScrollPane scroll = new JScrollPane(table);

    // my JPopupMenu component
    final JPopupMenu popup = new JPopupMenu();

    // the save JMenuItem and its associated ActionListener
    JMenuItem save = new JMenuItem(“save to file”);
    save.addActionListener(new
    ActionListener() {
    public void actionPerformed(ActionEvent action){
    try {
    ExcelExporter exp = new ExcelExporter();
    exp.exportTable(table, new File(“results.xls”));
    }
    catch (IOException ex) {
    System.out.println(ex.getMessage());
    ex.printStackTrace();
    }
    }
    });
    popup.add(save);

    // The open JMenuItem and its associated ActionListener
    JMenuItem open = new JMenuItem(“open in Excel”);
    open.addActionListener(new
    ActionListener() {
    public void actionPerformed(ActionEvent action){
    try {
    // Note that i’m actually saving the file first
    ExcelExporter exp = new ExcelExporter();
    File file = new File(“results1.xls”);
    exp.exportTable(table, file);
    ExcelOpener opn = new ExcelOpener();
    opn.openTable(file);
    }
    catch (IOException ex) {
    System.out.println(ex.getMessage());
    ex.printStackTrace();
    }

    }
    });
    popup.add(open);

    // the following method only works in JDK 5.0 or greater
    // table.setComponentPopupMenu(popup);

    JLabel label1 = new JLabel(“Right Click to Export Data…”, JLabel.CENTER);

    // the following code is needed for JDK 1.4
    table.addMouseListener(new MouseAdapter() {
    public void mousePressed(MouseEvent event){
    if(popup.isPopupTrigger(event)){
    popup.show(event.getComponent(), event.getX(),event.getY());
    }
    }
    public void mouseReleased(MouseEvent event){
    if(popup.isPopupTrigger(event)){
    popup.show(event.getComponent(), event.getX(),event.getY());
    }
    }
    });

    frame.getContentPane().add(“Center”,scroll);
    frame.getContentPane().add(“South”,label1);
    frame.pack();
    frame.setVisible(true);
    }
    }
    class ExcelExporter {
    public ExcelExporter() {}
    public void exportTable(JTable table, File file) throws IOException {
    TableModel model = table.getModel();
    FileWriter out = new FileWriter(file);

    for(int i=0i < model.getcolumncount();i++)
    out.write(model.getColumnName(i)+”\t”);
    }
    out.write(“\n”);
    for(int i=0; i < model.getRowCount();i++){
    for(int j=0;j < model.getColumnCount();j++){
    // I added this check for the ISBN conversion
    if(j==0) {
    // the book Title
    out.write(model.getValueAt(i,j).toString() + “\t”);
    } else {
    /*
    the ISBN Number
    Note that I added a \” to the front of the string
    and a \t followed by a closing \” to let Excel know
    that this field is to be converted as text
    */
    out.write(“\””+model.getValueAt(i, j).toString()+”\t”+”\””);
    }
    }
    out.write(“\n”);
    }
    out.close();
    System.out.println(“write to ” + file);
    }
    }
    class ExcelOpener {
    public ExcelOpener() {}
    public void openTable(File file) throws IOException {
    Runtime run = Runtime.getRuntime();
    // I make the assumption that the client has Excel and
    // the file type .XLS is associated with Excel

    // This is a simple check to find out the operating system
    String lcOSName = System.getProperty(“os.name”).toLowerCase();
    boolean MAC_OS_X = lcOSName.startsWith(“mac os x”);
    if(MAC_OS_X){
    run.exec(“open “+ file);
    } else {
    run.exec(“cmd.exe /c start ” + file);
    }
    System.out.println(file + ” opened”);
    }
    }

    hi the above code is not working actually i am a fresher now i want to practise how to import and export the data from Java using JFrames

    please someone help me,
    my email id is mona.cegonsoft@rediffmail.com

  6. Rick says:

    Mona,

    copy the code now and try it again. it should work fine.

  7. 繁體中文版 says:

    I try your code rick…and it doesnt work….

  8. Rick says:

    You’ll have to give me more information than that. What specifically is not working?

  9. 繁體中文版 says:

    sorry was a confusion…but I have a few questions..Im developing a program….it consist in create a spreadsheet of excel in java, I’ve been trying different API’S to export data from excel(with macros) to my JTable and it didn’t work ..so I saw your code and it really give me a hand…so after all the explanation.
    Refering to your code…
    1.- how can I change the number of columns..I’ll tried and I just receive a lot or errors at the output.
    2.- How can I add a JMenu Bar (I post the code in the next lines)

    protected JMenuBar createMenuBar() {
    JMenuBar menuBar = new JMenuBar();

    JMenu mFile = new JMenu(“File”);
    mFile.setMnemonic(‘f’);

    JMenuItem mData = new JMenuItem(“Retrieve Data…”);
    mData.setMnemonic(‘r’);
    ActionListener lstData = new ActionListener() {
    public void actionPerformed(ActionEvent e) {
    retrieveData();
    }
    };
    mData.addActionListener(lstData);
    mFile.add(mData);
    mFile.addSeparator();

    JMenuItem mExit = new JMenuItem(“Exit”);
    mExit.setMnemonic(‘x’);
    ActionListener lstExit = new ActionListener() {
    public void actionPerformed(ActionEvent e) {
    System.exit(0);
    }
    };
    mExit.addActionListener(lstExit);
    mFile.add(mExit);
    menuBar.add(mFile);

    JMenu mView = new JMenu(“View”);
    mView.setMnemonic(‘v’);
    TableColumnModel model = m_table.getColumnModel();
    for (int k = 0; k < StockTableData.m_columns.length; k++) {
    JCheckBoxMenuItem item = new JCheckBoxMenuItem(
    StockTableData.m_columns[k].m_title);
    item.setSelected(true);
    TableColumn column = model.getColumn(k);
    item.addActionListener(new ColumnKeeper(column,
    StockTableData.m_columns[k]));
    mView.add(item);
    }
    menuBar.add(mView);

    return menuBar;
    }

    Grettings
    繁體中文版

  10. Casey says:

    Great code…worked like a dream!

  11. Ramdas says:

    Liked the idea… Initially thought “do i have to use Apache POI for writing the data to Excel” 🙂 Thanks

  12. Inayat says:

    Works awesome for me!

    Thank you, Rick for sharing this with us.

  13. sandeep says:

    thanks Rick, ur code solved my requirement…
    Thanks alot

  14. Dave says:

    Hy Rick!

    Thank you for your code!
    But, unfortunatelly is doesn’t works.
    You made a little mistake:
    Your code is:
    out.write(“\””+table.getValueAt(i, j).toString()+”\t”+”\””);

    but this writes something like this:
    “xyz \t”
    and you need this:
    “xyz”\t

    So the correct code is:
    out.write(“\””+table.getValueAt(i, j).toString()+”\””+”\t”);

  15. Pingback: Exporting JTable data to Excel Revised « mindsIview

  16. your fan rick says:

    thank u very much rick, may god bless u 🙂 🙂 🙂

  17. your fan rick says:

    thank u very much rick. god bless u with joy and happiness 🙂 🙂

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

%d bloggers like this: