Exporting JTable data to Excel Revised

Someone alerted me to a problem with my JTable exporting example. It seems with all the changes I've been making to my blog, I lost some of the code at the end of the example. It's been restored and can be found here: Tech Thoughts: Exporting JTable data to Excel. Rick.

Making a JTable Non Editable

There are times when you want to display a JTable in your Swing app, but do not want to the user to be able to edit the cells. Here's a simple way to accomplish this task via the DefaultTableModel:

  DefaultTableModel model = new DefaultTableModel(6,7){
   
 public boolean isCellEditable(int row, int column)
 {
     return false;
 }
  };

In the example above, I'm creating a DefaultTableModel model that has 6 rows and 7 columns. I'm overriding the isCellEditable() method, the method used by other methods and classes to know whether or not a JTable cell can be edited, and returning a boolean value of false in all cases. I could have also returned false for only selected rows and columns by adding conditional logic .

Using the Preferences API

Like many Java developers, I tend to find something that works for me and use it for years without researching better approaches. For example, I've used the Properties class for years to load and save configuration information in my Java programs. The Properties class works fine, but does have some drawbacks.

Last week I stumbled across another API, the Preferences class, that's been available since 1.4. It can be used the same way as the Properties class, to load and save configuration stuff. The big difference between it and Properties is that the Preference class allows you to store data in a consistent format, it allows you to take advantage of centralized repositories such as the Windows registry, and provides more flexibility with regards to storing the preferences in user folders.

Without going into a lot of detail, I've created a simple swing application that demonstrates the basics of the Preferences class. All the program TestPrefs does is save an loads the most recent window size and location.

Looking at the code below, I've highlighted the Preferences api calls for you. As you can see, the first thing to do is to decide where you want the preferences file to be located. In this case, I've chosen user root, which means that I've decided to store the preferences file under the user tree rather than the system tree. Next, I've told the program to store the file in the root folder of TestPrefs class. You can also specify a folder with something like node = root.node("/com/prefs/...").

Now that the class knows where to locate the preferences file, how do we write information to it? Looking at the windowClosing() method, you'll see node.putInt(..). The putInt() method has two parameters: name and value. For example, if you wanted to save a frame's width, you might create a name value/pair like this: node.putInt("width", jFrame.getWidth()).

Next, we want to read information from the preferences file and use it to position our window on the next execution of the program. looking at the getJFrame() method, you can see the jFrame.setSize() method. This time I use node.getInt(). Like putInt(), getInt() has a name/value pair. This time it works a little differently. In this case, the value parameter acts as a default value.The name parameter tries to find the associated value within the preferences file. If it can't, then the value parameter is used.

There are other methods associated with the Preferences api, and I encourage you to explore these. Hopefully, I've whetted your appetite enough to get started.

___________________

package test;

import java.awt.BorderLayout;
import javax.swing.SwingUtilities;
import java.util.prefs.Preferences;

import javax.swing.JPanel;
import javax.swing.JFrame;

public class TestPrefs {

private JFrame jFrame = null;

private JPanel jContentPane = null;

public static final int DEFAULT_WIDTH = 300;
public static final int DEFAULT_HEIGHT = 200;
public static final int DEFAULT_LEFT = 0;
public static final int DEFAULT_TOP = 0;

Preferences root = Preferences.userRoot();
 final Preferences node = Preferences.userNodeForPackage(this.getClass());

/**
* This method initializes jFrame
*
* @return javax.swing.JFrame
*/
private JFrame getJFrame() {
if (jFrame == null) {
jFrame = new JFrame();
jFrame.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
jFrame.setSize(node.getInt("width", DEFAULT_WIDTH),node.getInt("height", DEFAULT_HEIGHT));
jFrame.setLocation(node.getInt("left",DEFAULT_LEFT), node.getInt("top",DEFAULT_TOP));
jFrame.setContentPane(getJContentPane());
jFrame.setTitle("Test Preferences API");
jFrame.addWindowListener(new java.awt.event.WindowAdapter() {
public void windowClosing(java.awt.event.WindowEvent e) {
node.putInt("left", jFrame.getX());
     node.putInt("top", jFrame.getY());
     node.putInt("width",jFrame.getWidth());
     node.putInt("height", jFrame.getHeight());
}
});
}
return jFrame;
}

/**
* This method initializes jContentPane
*
* @return javax.swing.JPanel
*/
private JPanel getJContentPane() {
if (jContentPane == null) {
jContentPane = new JPanel();
jContentPane.setLayout(new BorderLayout());
}
return jContentPane;
}



/**
* Launches this application
*/
public static void main(String[] args) {
SwingUtilities.invokeLater(new Runnable() {
public void run() {
TestPrefs application = new TestPrefs();
application.getJFrame().setVisible(true);
}
});
}

}

Java Windowing Toolkit Poll

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");
 }
}

Java Confession

I have a confession to make about Java. As much as I like the language for its portability, features, etc., I hate Java Swing. I understand why Swing is the way it is. But, I don't have to like it. And I don't.

The problem is in order to maintain the "write once run anywhere" philosophy, Swing adds a layer of complexity not present in platform specific GUI development languages. I'm not saying I can't develop in Swing...I can and have on multiple platforms. I'm just saying I will do anything to avoid using it. Often I look for solutions that can be JSP/servlet based instead. While I think that Java is a cool feature rich language, I must confess that, at least to me, Swing is one of its least desirable features.