Have you ever needed to save the contents of a JTable? One convenient way to save this data is to make it available as a Microsoft Excel spreadsheet. At first, this may not seem like an easy task, but with Apache’s POI API, we can do it without too much frustration.
Recently for a project, I had to save my JTable data to an Excel spreadsheet. Doing a quick search, I came across Apache’s POI API and wrote the following class to save my table data. Note that upon calling the constructor of this class, we automatically call the save() method which saves the data to a specified file (also given from the constructor). The data is read from the JTable, and column header names are taken from the JTable, as well. In this example, I’ve set formatting for strings and integers — you can add more to this to suit your needs. After saving the data to the file, the user will be asked if s/he would like to open the file (I’m pretty sure this would only work in Windows, though).
Usage
I used a custom designed NonEditableTableModel class (which is not shown here) extending the DefaultTableModel to update my JTable data (I did not want the client modifying the table data). You must modify this parameter if you wish to use your own table model, or even if you want to use the default table model. Otherwise, usage is quite simple: Pass in your table model and a string representing the file name / location, and the data will be saved to the specified file.
new ExcelSaver( myNonEditableTableModel, filePathAndName ); // save() is automatically called after the constructor
Excel Saver
Note: You will need Apache’s POI class files in order to use this class. Please download them, and add them to your project as a library.
import java.awt.Toolkit;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import javax.swing.JOptionPane;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
public class ExcelSaver {
private String filename = null; // File name
private FileOutputStream out = null; // Output Stream
private NonEditableTableModel tableModel = null; // Table Model
/**
* ExcelSaver Constructor
*
* @param tableModel
* @param filename
*/
public ExcelSaver( NonEditableTableModel tableModel, String filename ) {
this.tableModel = tableModel; // Table Model
this.filename = filename; // File name
save(); // Call save() method
} // end constructor
/**
* This method returns the file name.
*
* @return String filename
*/
public String getFilename() {
return filename;
} // end method getFilename()
/**
* This method returns the table
* model.
*
* @return NonEditableTableModel tableModel
*/
public NonEditableTableModel getTableModel() {
return tableModel;
} // end method getTableModel()
/**
* This method saves the given file
* to an excel spreadsheet.
*/
@SuppressWarnings("deprecation")
public void save() {
/**
* Create a new file
*/
try {
out = new FileOutputStream( filename );
} catch (FileNotFoundException e) {
JOptionPane.showMessageDialog( null, "Could not save file. Please try again.", "Error Saving File",
JOptionPane.ERROR_MESSAGE);
}
/**
* Set up workbook
*/
HSSFWorkbook workbook = new HSSFWorkbook(); // New workbook
HSSFSheet worksheet = workbook.createSheet(); // New worksheet
HSSFRow row = null; // Row
HSSFCell column = null; // Column
/**
* Header Cell Style
*/
HSSFCellStyle headerCellStyle = workbook.createCellStyle(); // Header Cell Style
HSSFFont headerFont = workbook.createFont(); // Header Font
headerFont.setFontHeightInPoints( (short) 10 ); // Font Size
headerFont.setBoldweight( HSSFFont.BOLDWEIGHT_BOLD ); // Bold Font
headerCellStyle.setFont( headerFont ); // Set style
headerCellStyle.setAlignment( HSSFCellStyle.ALIGN_CENTER ); // Center align
/**
* String Cell Style
*/
HSSFCellStyle stringCellStyle = workbook.createCellStyle(); // Data Cell Style
HSSFFont stringFont = workbook.createFont(); // Data Font
stringFont.setFontHeightInPoints( (short) 10 ); // Font Size
stringCellStyle.setFont(stringFont); // Set style
/**
* Integer Cell Style
*/
HSSFCellStyle integerCellStyle = workbook.createCellStyle(); // Data Cell Style
HSSFDataFormat format = workbook.createDataFormat(); // Data Format
HSSFFont integerFont = workbook.createFont(); // Data Font
integerFont.setFontHeightInPoints( (short) 10 ); // Font Size
integerCellStyle.setFont(integerFont); // Set style
integerCellStyle.setAlignment( HSSFCellStyle.ALIGN_RIGHT ); // Right align
integerCellStyle.setDataFormat( format.getFormat("0") ); // Data Format
/**
* Column Widths
*/
worksheet.setColumnWidth( (short) 0, (short) 10000 );
worksheet.setColumnWidth( (short) 1, (short) 10000 );
worksheet.setColumnWidth( (short) 2, (short) 3500 );
/**
* Add headers to worksheet
*/
for ( short colNumber = 0; colNumber < getTableModel().getColumnCount(); colNumber++ ) {
row = worksheet.createRow( 0 );
column = row.createCell( colNumber );
column.setCellStyle( headerCellStyle );
column.setCellValue( getTableModel().getColumnName( colNumber ) );
}
/**
* Add data to worksheet
*/
int temp = 0; // Row value after the header row
// Rows
for ( int rowNumber = 0; rowNumber < getTableModel().getRowCount(); rowNumber++ ) {
temp = rowNumber + 1; // Add one row to account for header
row = worksheet.createRow( temp );
// Columns
for ( short colNumber = 0; colNumber < getTableModel().getColumnCount(); colNumber++ ) {
column = row.createCell( colNumber );
Class<?> dataClass = getTableModel().getValueAt( rowNumber, colNumber ).getClass();
if ( dataClass.equals( String.class ) ) {
column.setCellStyle( stringCellStyle );
column.setCellValue( getTableModel().getValueAt( rowNumber, colNumber ).toString() );
}
else if ( dataClass.equals( Integer.class ) ) {
column.setCellStyle( integerCellStyle );
column.setCellValue( Double.parseDouble( getTableModel().getValueAt( rowNumber, colNumber ).toString() ) );
}
else {
column.setCellValue( getTableModel().getValueAt( rowNumber, colNumber ).toString() );
}
} // end for loop (cols)
} // end for loop (rows)
/**
* Write workbook to output stream
*/
try {
workbook.write(out);
out.close();
} catch (IOException e) {
JOptionPane.showMessageDialog( null, "Could not save file. Please try again.", "Error Saving File",
JOptionPane.ERROR_MESSAGE);
} // end try
/**
* Ask the user if he wants to open
* the spreadsheet.
*/
Toolkit.getDefaultToolkit().beep();
int n = JOptionPane.showConfirmDialog( null, "<html><body>Successfully saved results to:<br><strong>" + getFilename() +
"</strong><br>Would you like to open this spreadsheet now?</body></html>", "Successfully Saved Results",
JOptionPane.YES_NO_OPTION);
if ( n == 0 ) {
try {
Runtime.getRuntime().exec("rundll32 SHELL32.DLL,ShellExec_RunDLL " + getFilename() );
} catch (IOException e) {
e.printStackTrace();
} // end try
} else {
return;
} // end if
} // end method save()
} // end class ExcelSaver
Note: I realize you can also save your data as a CSV file which may be an even better idea, but that’s not what this article is focused on (since these were not the requirements of my project). Enjoy! 
October 30th, 2009 at 4:48 pm
THANKS for this post!!!!!!
This HOW_TO was very useful for me.
Just there is a little bug in the code on line 141.
The cellrow is being created for every column, the result is that at the end just the last column name is displayed.
The fix for me, was just a condition.
if ( colNumber == 0 ){
row = worksheet.createRow( 0 );
}
Thank You!!!!
November 6th, 2009 at 6:54 pm
Thanks for your comment! For some reason I didn’t experience this bug, but thanks for posting a fix