通过Java生成Excel文件

package demo;

import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.io.File;
import java.io.FileOutputStream;
import java.io.FileWriter;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.Date;

import javax.swing.JButton;
import javax.swing.JFileChooser;
import javax.swing.JFrame;
import javax.swing.JScrollPane;
import javax.swing.JTable;
import javax.swing.table.DefaultTableModel;
import javax.swing.table.TableModel;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;

public class ExcelExporter {
	private static JFileChooser fc;

	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.write("\n");
		SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		Date date = new Date(System.currentTimeMillis());
		out.write("创建时间" + "\t" + format.format(date) + "\t");
		out.close();
		System.out.println("write out to: " + file);
	}

	// 通过POI写，用于较复杂的应用
	public void exportToExcel(JTable table, File file) throws IOException {
		TableModel model = table.getModel();

		HSSFWorkbook wb = new HSSFWorkbook();
		HSSFSheet sheet = wb.createSheet("统计信息");

		// 创建表格头
		HSSFRow row = sheet.createRow((short) 0);
		HSSFCellStyle headStyle = wb.createCellStyle();

		headStyle.setFillForegroundColor(HSSFColor.GREY_50_PERCENT.index); //添加前景色,内容看的清楚
		headStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		headStyle.setIndention((short) 10);

		HSSFFont font = wb.createFont();
		font.setColor(HSSFColor.BLUE.index);
		headStyle.setFont(font);

		for (int i = 0; i < model.getColumnCount(); i++) {
			HSSFCell cell = row.createCell((short) i);
			cell.setCellStyle(headStyle);
			cell.setCellValue(new HSSFRichTextString((model.getColumnName(i))));
		}
		// 插入表格数据
		for (int i = 0; i < model.getRowCount(); i++) {
			HSSFRow dataRow = sheet.createRow((short) i + 1);
			for (int j = 0; j < model.getColumnCount(); j++) {
				dataRow.createCell((short) j).setCellValue(
						new HSSFRichTextString(model.getValueAt(i, j)
								.toString()));
			}
		}

		// 插入创建时间
		HSSFRow insertTime = sheet.createRow((short) model.getRowCount() + 3);
		insertTime.createCell((short) 0).setCellValue(
				new HSSFRichTextString("创建时间"));
		SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		Date date = new Date(System.currentTimeMillis());
		insertTime.createCell((short) 1).setCellValue(
				new HSSFRichTextString(format.format(date)));
		
               // 根据内容长度自动调整excel列宽
		for(int i=0;i<model.getColumnCount();i++){
			sheet.autoSizeColumn((short) i);
		}

		FileOutputStream fileOut = null;
		try {
			fileOut = new FileOutputStream(file.getAbsolutePath());
			wb.write(fileOut);
			fileOut.close();
		} catch (IOException e) {
			file.deleteOnExit();
			wb.write(fileOut);
			fileOut.close();
			e.printStackTrace();
		}
	}

	public static void main(String[] args) {
		String[][] data = { { "Housewares", "$1275.00" },
				{ "Pets", "$125.00" }, { "Electronics", "$2533.00" },
				{ "Mensware", "$497.00" } };
		String[] headers = { "Department", "Daily Revenue" };

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

		JButton export = new JButton("Export");
		fc = new JFileChooser();

		export.addActionListener(new ActionListener() {
			public void actionPerformed(ActionEvent evt) {
				try {
					fc.addChoosableFileFilter(new ExcelFileFilter());
					fc.setAcceptAllFileFilterUsed(false);
					int returnVal = fc.showSaveDialog(frame);
					if (returnVal == JFileChooser.APPROVE_OPTION) {
						File file = fc.getSelectedFile();
						;
						String ext = ExcelFileFilter.getExtension(file);
						ExcelExporter exp = new ExcelExporter();
						if (ext != null) {
							if ((!ext.equals(ExcelFileFilter.xls))
									|| (!ext.equals(ExcelFileFilter.XLS))) {
								int i = file.getName().lastIndexOf(".");
								String fileName = file.getName()
										.substring(0, i);
								File newfile = new File(fc
										.getCurrentDirectory()
										.getAbsolutePath()
										+ "\\"
										+ fileName
										+ ExcelFileFilter.suffix);
								exp.exportToExcel(table, newfile);
								return;
							}
						} else {
							File newfile = new File(fc.getCurrentDirectory()
									.getAbsolutePath()
									+ "\\"
									+ file.getName()
									+ ExcelFileFilter.suffix);
							exp.exportToExcel(table, newfile);
							return;
						}

					} else {
					}

				} catch (IOException ex) {
					System.out.println(ex.getMessage());
					ex.printStackTrace();
				}
			}
		});

		frame.getContentPane().add("Center", scroll);
		frame.getContentPane().add("South", export);
		frame.pack();
		frame.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
		frame.setVisible(true);
	}
}


外加一个Filter，直接保存为Excel格式，不支持office2007格式

/**
 * 
 */
package demo;

import java.io.File;

import javax.swing.filechooser.FileFilter;

/**
 * @author XiaoQuan
 * 
 */
public class ExcelFileFilter extends FileFilter {

	public final static String xls = "xls";
	public final static String XLS = "XLS";
	public final static String suffix = ".xls";

	public boolean accept(File file) {
		if (file.isDirectory()) {
			return true;
		}
		String extension = getExtension(file);
		if (extension != null) {
			if (extension.equals(ExcelFileFilter.xls)
					|| extension.equals(ExcelFileFilter.XLS)) {
				return true;
			} else {
				return false;
			}
		}
		return false;
	}

	public String getDescription() {
		return "保存为EXCEL";
	}

	public static String getExtension(File f) {
		String ext = null;
		String s = f.getName();
		int i = s.lastIndexOf('.');

		if (i > 0 && i < s.length() - 1) {
			ext = s.substring(i + 1).toLowerCase();
		}
		return ext;
	}

}

评论

2 楼 pipihu_yu 2008-08-15  

HSSF is the POI Project's pure Java implementation of the Excel '97(-2007) file format. It does not support the new Excel 2007 .xlsx OOXML file format, which is not OLE2 based.
POI可以到[url] http://www.apache.org/dyn/closer.cgi/jakarta/poi/[/url] 下载。 编译好的jar主要有这样4个：poi包,poi Browser包,poi hdf包,poi hssf例程包。

1 楼 pipihu_yu 2008-08-15  

org.apache.poi.*包找不到哟！请问一下这是什么包呢？