Swing Hacks

Export Table Data to an Excel Spreadsheet #25

Chapter 3, Tables and Trees

|

131

HACK

Dealing with Formatting

Excel uses a complicated database-oriented format for its native .xls files.

This format defines the formulas, colors, charts and every other advanced

feature Excel has supported over the years. Writing to the native .xls format

is complicated but, fortunately, Excel supports other formats. The one I’m

going to target is known as a tab-delimited text file, so called because tabs

separate each field. This format is just plain text, so it will be super easy to

write from Java, and open up in Excel with just a double-click.

Tab-delimited files separate each field with a tab character and each row

with a standard Unix line break,

\n

. Since Swing defines a convenient

getValueAt( ) method in the TableModel interface, it’s very easy to just loop

through the table cells and write it out to a file, as seen in Example 3-14.

This code defines an

ExcelExporter class with a single method exportTable( ),

taking a

JTable and a file. All JTables contain an implementation of the

TableModel interface that holds the actual data. The code first retrieves the

table model and opens a new

FileWriter to the file. I used a FileWriter

instead of a FileOutputStream because Writers automatically handle text

encoding issues. This means you don’t have to worry about the language the

program is running on. Using a

Writer ensures that the code will work with

any encoding from simple ASCII to triple-byte Korean Unicode.

Example 3-14. Exporting tab-delimited data from a TableModel

public 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++) {

out.write(model.getValueAt(i,j).toString( )+"\t");

}

out.write("\n");

}

out.close( );

System.out.println("write out to: " + file);

}

132

|

Chapter 3, Tables and Trees

#25 Export Table Data to an Excel Spreadsheet

HACK

The TableModel also defines the names of the columns, which are typically

printed at the top of each column in the final spreadsheet. The code loops

through the column names and prints them to the writer, following each

column name with a

\t

, which represents the tab character, and finally an

\n

(the Unix newline character, which will work fine on both Mac OS X and

Windows) at the end of the line. After that, it loops through each data row

in turn, again separating fields with tabs and rows with the newline. After

writing the fields, it closes the file and prints a status message. And with

that, the core of the table export is done.

The main method in Example 3-15 creates a

JTable with sample data and a

button to generate an Excel file. First, it creates sample data as string arrays,

and then it builds a new

DefaultTableModel (the standard TableModel imple-

mentation that comes with Swing), nesting it inside of a

JTable and then a

JScrollPane.

Example 3-15. Testing JTable data export

public static void main(String[] args) {

String[][] data = {

{ "Housewares", "$1275.00" },

{ "Pets", "$125.00" },

{ "Electronics", "$2533.00" },

{ "Menswear", "$497.00" }

};

String[] headers = { "Department", "Daily Revenue" };

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

JButton export = new JButton("Export");

export.addActionListener(new ActionListener( ) {

public void actionPerformed(ActionEvent evt) {

try {

ExcelExporter exp = new ExcelExporter( );

exp.exportTable(table, new File("results.xls"));

} catch (IOException ex) {

System.out.println(ex.getMessage( ));

ex.printStackTrace( );

}

}

});

frame.getContentPane( ).add("Center",scroll);

frame.getContentPane( ).add("South",export);

frame.pack( );

frame.setVisible(true);

}