HSSF操作Excel

• 介绍

在实际开发的项目中，经常会遇到需要操作Excel的需求。本文介绍的是使用HSSF来操作Excel的例子。

• Helloworld

下面的代码是导出对象列表到Excel中的例子： [codesyntax lang="java"]

/**
 * @author surenpi.com
 * @date 2015年5月25日
 */

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.List;
import java.util.TreeMap;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

/**
 * 将对象列表导出到文件中的工具类
 * 
 * @author surenpi.com
 * 
 * @2015年5月25日
 */
public class ExcelExport {
	private static final String HEAD_USER = "用户";
	private static final String HEAD_PROJECT = "项目";
	private static final String HEAD_ACCMOUNT = "金额";
	private static final String HEAD_COMMENT = "备注";
	private static final String HEAD_POST_LOCATION = "邮寄地址";
	private static final String HEAD_POST_CODE = "邮编";
	private static final String HEAD_CONTACTS = "联系人";
	private static final String HEAD_CONTACT_PHONE = "联系人电话";
	private static final String HEAD_STATUS = "开票状态";
	
	private static final TreeMap<String, String> headerMap = new TreeMap<String, String>();
	
	private void init() {
		headerMap.put(HEAD_USER, "userName");
		headerMap.put(HEAD_PROJECT, "project");
		headerMap.put(HEAD_ACCMOUNT, "amount");
		headerMap.put(HEAD_COMMENT, "comment");
		headerMap.put(HEAD_POST_LOCATION, "postLocation");
		headerMap.put(HEAD_POST_CODE, "postCode");
		headerMap.put(HEAD_CONTACTS, "contacts");
		headerMap.put(HEAD_CONTACT_PHONE, "contactPhone");
		headerMap.put(HEAD_STATUS, "status");
	}
	
	/**
	 * 将objList中的对象列表到导出到文件中
	 * 
	 * @param objList 对象列表
	 * @param file 输出文件
	 * @return 导出成功返回true
	 */
	public boolean export(List<Object> objList, File file) {
		FileOutputStream fos = null;
		try {
			fos = new FileOutputStream(file);
			
			return exportStream(objList, fos);
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} finally {
			if(fos != null)
			{
				try {
					fos.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
		}
		
		return false;
	}
	 
	/**
	 * 将objList中的对象列表到导出到输出流中
	 * 
	 * @param objList 对象列表
	 * @param outStream 输出流
	 * @return 导出成功返回true
	 */
	public boolean exportStream(List<Object> objList, OutputStream outStream) {
		HSSFWorkbook workbook = new HSSFWorkbook();
		HSSFSheet sheet = workbook.createSheet("发票列表");
		
		init();
		
		createTitle(sheet);
		
		if(objList != null)
		{
			int size = objList.size();
			for(int i = 1; i < size + 1; i++) {
				fillContent(sheet, i, objList.get(i - 1));
			}
		}
		
		try {
			workbook.write(outStream);
			
			return true;
		} catch (IOException e) {
			e.printStackTrace();
		}
		
		return false;
	}

	/**
	 * 测试函数
	 * 
	 * @param args
	 * @throws Exception
	 */
	public static void main(String[] args) throws Exception {
		ExcelExport excelExport = new ExcelExport();
		
		List<Object> invoiceList = new ArrayList<Object>();
		
		Invoice invoice = new Invoice();
		invoice.setUserName("username");
		invoiceList.add(invoice);
		
		invoice = new Invoice();
		invoice.setUserName("名称");
		invoice.setProject("project");
		invoiceList.add(invoice);
		
		excelExport.export(invoiceList, new File("d:/test.xls"));
	}

	/**
	 * 设置表头
	 * 
	 * @param sheet
	 */
	private void createTitle(HSSFSheet sheet) {
		HSSFRow row = sheet.createRow(0);
		int column = 0;
		
		for(String key : headerMap.keySet()) {
			HSSFCell cell = row.createCell(column++);
			cell.setCellValue(key);
		}
	}
	
	/**
	 * 根据一个对象填充一行数据
	 * 
	 * @param sheet
	 * @param rowNum 行号
	 * @param data 数据对象
	 */
	private void fillContent(HSSFSheet sheet, int rowNum, Object data) {
		if(sheet == null || data == null) {
			return;
		}
		
		HSSFRow row = sheet.createRow(rowNum);
		int column = 0;

		Class<? extends Object> dataCls = data.getClass();
		for(String key : headerMap.keySet()) {
			String name = headerMap.get(key);
			
			try {
				Method method = dataCls.getMethod(String.format("get%s%s",
						name.substring(0, 1).toUpperCase(), name.substring(1)));
				Object value = method.invoke(data);
				if(value != null) {
					HSSFCell cell = row.createCell(column);
					cell.setCellValue(value.toString());
				}
			} catch (SecurityException e) {
				e.printStackTrace();
			} catch (IllegalArgumentException e) {
				e.printStackTrace();
			} catch (IllegalAccessException e) {
				e.printStackTrace();
			} catch (NoSuchMethodException e) {
				e.printStackTrace();
			} catch (InvocationTargetException e) {
				e.printStackTrace();
			} finally {
				column++;
			}
		}
	}
}

[/codesyntax]

• 参考

http://www.360doc.com/content/12/0517/14/987036_211663524.shtml

转载于:https://my.oschina.net/surenpi/blog/481904