My blog has moved! Redirecting...

You should be automatically redirected. If not, visit http://mindsiview.wordpress.com/ and update your bookmarks.

Monday, August 28, 2006

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

Saturday, August 26, 2006

A Mac in a Windows World

Remember that Mac commercial, no not that one, the other one. I'm talking about the one where the PC and Mac are holding hands to show how compatible they are. Never mind the disturbing image of the young woman pulling a picture from her backside. Nope, I'm talking about the compatibility thing. In general the Macs are compatible, but there are some challenges to running a Mac in a Windows world.

Issue 1: WINS (Windows Internet Naming Service). Like DNS, WINS provides a way to name network devices such as servers and printers instead of referring to them by an IP address. Why does this matter? It makes life easier for us IT guys...and ultimately easier for you.

Using WINS not only makes it easier to find and categorize network devices (much like typing a URL in your browser as opposed to typing the IP address), it also makes it easier to manage the network. You see, IT guys are constantly tweaking the network to do things like add and remove servers, update segments, and move equipment from one location to another.

What does this have to do with the Mac? Well, Macs don't always recognize WINS names, sometimes you have to use the IP address or create a host file that contains both the WINS name and associated IP address. What's the big deal? The big deal is that every time the a network device you're connecting to changes IP addresses in the network, WINS is updated and Windows PCs don't know the difference. You, on the other hand, have to make the corresponding network changes on the Mac.

Issue 2: Windows Network Password Expiration. If your company is like ours it has a password expiration policy. This policy requires passwords to periodically expire, forcing you to create a new one. Now I know that you believe that the only reason your company has this policy is because your IT department is evil. While I can't speak about the intent of your IT department, the reason this policy exists is that it helps reduce incidents of hacking. It's also required by your auditors.

Now, the Mac is blissfully unaware of the Windows network password expiration policy. When your Windows network password expires, the first time you find out about it is when you try to connect to a network device. And, you don't get the opportunity to change your password. Instead, you have to find a Windows PC and us it to change your password.

One solution to this problem is to set to set the Mac users' passwords to never expire. As an evil IT guy (insert maniacal laughter here), I personally think this is a poor practice.

Issue 3: Fantasy Football Live Draft Applications. This is undoubtedly the most egregious compatibility issue. Tonight while trying to access my fantasy football league's draft I found out that the live draft app didn't work correctly. Fortunately I was able to use my wife's Windows XP laptop and all was well.

----------
Lest you think I'm dissing the Macs, I'm not really. My primary business computer is a MacBook Pro. I'm just pointing out some of the challenges IT departments face as Macs become more popular in the corporate world. If you've resolved some of these issues I'd love to hear from you. Also, as I find solutions I'll pass them along.

In the meantime, I wonder if there's a way I can steal Shaun Alexander for my fantasy football team...

Tuesday, August 22, 2006

Installing Eventum on Mac OS X

Last weekend while attempting to install Eventum, a MySQL/PhP based issues tracking system, on my Mac, I ran into a common problem. I say common because of the number of questions I found on the web relating to it. In order to successfully install the software, you have to modify your php.ini file and set allow_call_time_pass_reference = On...which turns out is not an easy task.

First, it's not easy to find the php.ini file. The Finder app will not find it. The best way to locate the file is to create a one line php file on your webserver that contains a php.phpinfo() ? statement. Chances are you did this as part of your php installation anyway, particularly if you used Entropy PhP for the Mac. Running this php app will reveal the location of your php.ini file. On my system, php.ini is located at /usr/local/php5/lib/php.ini (the location on your system may vary).

The next issue becomes, how to get to /usr/local/php5/lib. The easiest way to do this (and maybe the only way) is by pulling up the Terminal application found in your Utilities folder. This app takes you into the Unix shell. Note: Before you do this, make sure that a) you are signed in as the Administrator, b) you are comfortable working in Unix. To get to the directory in Unix, you type cd /usr/local/php5/lib. Once you're there you'll quickly realize that you the php.ini file is read only. In fact, the entire directory is read only. To modify the file, you'll need to back up one level to the /usr/local/php5/ directory and do a chmod 755 lib to allow the files to be modified. Next, go back to the lib directory and vi the php.ini file. Now you're ready to successfully install Eventum.

Good luck, and let me know if you have a better solution.

Technorati search