站内搜索: 请输入搜索关键词
当前页面: 图书首页 > Swing Hacks

Hack 25. Export Table Data to an Excel Spreadsheet - Swing Hacks

Previous Page
Next Page

Hack 25. Export Table Data to an Excel Spreadsheet

I don't want an entire spreadsheet API, I just want to get a table of values into Excel.

Most corporate intranet applications require interfacing with standard office software, which usually means Microsoft Word and Excel. Interfacing with Microsoft products can be tricky business. Whole suites of products have been created just to address this issue. One of the most commonly requested features is generating a report from the data in a JTable. You could use a library like Poi (http://jakarta.apache.org/poi/) to read and write Excel files natively, but most of the time that's overkillyou probably don't need to support Excel formulas or complicated formatting. All most users really want to do is dump tabular data into a file that will open in Excel with a double-click. And with a little bit of cleverness, you can do just that.

3.6.1. 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.

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

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 FileInputStream 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.

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

The export button has a simple action listener that calls exportTable() on a new ExcelExporter. The trick to getting Excel to open the file with a doubleclick on the desktop is to name the file with an .xls extension. It won't be a real Excel file, but the operating system will think that it is and pass it to Excel anyway. Then Excel will look at the file, realize it's actually a tab-delimited text file, and load it with the right import filter.

Figure 3-10 shows what the program looks like.

Figure 3-10. An Excel-exporting JTable


    Previous Page
    Next Page