ExcelTools.java

//$Id: ExcelTools.java,v 1.11 2006/12/19 13:00:31 chaostone Exp $
/*
 *
 * Copyright c 2005-2009.
 * 
 * Licensed under GNU  LESSER General Public License, Version 3.  
 * http://www.gnu.org/licenses
 * 
 */
/********************************************************************************
 * @author ssq
 * 
 * MODIFICATION DESCRIPTION
 * 
 * Name                 Date                Description 
 * ============         ============        ============
 * ssq              2005-11-4         Created
 *  
 ********************************************************************************/

package org.beanfuse.transfer;

import java.text.DecimalFormat;
import java.util.Collection;
import java.util.Iterator;
import java.util.List;
import java.util.StringTokenizer;

import org.apache.poi.hssf.usermodel.HSSFCell;
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.beanfuse.transfer.exporter.DefaultPropertyExtractor;
import org.beanfuse.transfer.exporter.PropertyExtractor;

/**
 * 写到excel中的工具
 * 
 * @author songshuquan,chaostone
 * 
 */
public class ExcelTools {
	DecimalFormat format = new DecimalFormat("#");

	DecimalFormat intoExcelNumberformat = new DecimalFormat("#0.00");

	public ExcelTools() {
	}

	public HSSFWorkbook toExcel(Collection datas, String propertyShowKeys) throws Exception {
		// 建立新HSSFWorkbook对象
		HSSFWorkbook wb = new HSSFWorkbook();
		return toExcel(wb, "export data", datas, propertyShowKeys);
	}

	/**
	 * 将一个对象数组的集合导出成excel
	 * 
	 * @param datas
	 * @param propertyShowKeys
	 * @return
	 * @throws Exception
	 */
	public HSSFWorkbook toExcel(HSSFWorkbook wb, String sheetName, Collection datas,
			String propertyShowKeys) throws Exception {
		HSSFSheet sheet = wb.createSheet(sheetName);
		HSSFRow row = null;
		HSSFCell cell = null;

		/** **************** 取得传入的list列名称和显示名称 ********** */
		String[] pShowKeys = Tokenizer2StringArray(propertyShowKeys, ",");
		// 创建�?行（标题)
		row = sheet.createRow(0); // 建立新行
		// 显示标题列名
		for (int i = 0; i < pShowKeys.length; i++) {
			cell = row.createCell((short) i); // 建立新cell
			// cell.setEncoding(HSSFCell.ENCODING_UTF_16);
			cell.setCellValue(new HSSFRichTextString(pShowKeys[i]));
		}
		// 逐行取数
		int rowId = 1;// 数据行号（从2行开始填充数�?)
		for (Iterator iter = datas.iterator(); iter.hasNext(); rowId++) {
			row = sheet.createRow(rowId); // 建立新行
			Object[] objs = (Object[]) iter.next();
			// 生成每一�?
			for (int j = 0; j < objs.length; j++) {
				cell = row.createCell((short) j); // 建立新cell
				// cell.setEncoding(HSSFCell.ENCODING_UTF_16);
				cell.setCellValue(new HSSFRichTextString((objs[j] == null) ? "" : objs[j]
						.toString()));
			}
		}
		return wb;
	}

	/**
	 * @see List数据集导出生成Excel文件
	 * @param list
	 *            对象数据�?
	 * @param propertyKeys
	 *            对象属�?�字符串，中间以","间隔
	 * @param propertyShowKeys
	 *            显示字段的名字字符串，中间以","间隔
	 * @return 返回�?个HSSFWorkbook（excel）类型数�?.
	 * @throws Exception
	 */
	public HSSFWorkbook object2Excel(Collection list, String propertyKeys, String propertyShowKeys,
			PropertyExtractor exporter) throws Exception {
		HSSFWorkbook wb = new HSSFWorkbook(); // 建立新HSSFWorkbook对象
		object2Excel(wb, "export result", list, propertyKeys, propertyShowKeys, exporter);
		return wb;
	}

	/**
	 * 将数据导出excel指定名称的数据页�?.
	 * 
	 * @param wb
	 * @param sheetName
	 * @param list
	 * @param propertyKeys
	 * @param propertyShowKeys
	 * @param exporter
	 * @return
	 * @throws Exception
	 */
	public HSSFWorkbook object2Excel(HSSFWorkbook wb, String sheetName, Collection list,
			String propertyKeys, String propertyShowKeys, PropertyExtractor exporter)
			throws Exception {
		HSSFSheet sheet = wb.createSheet(sheetName);
		HSSFRow row = null;
		HSSFCell cell = null;
		Object cellVal = null;

		/** **************** 取得传入的list列名称和显示名称 ********** */
		String[] pKeys = Tokenizer2StringArray(propertyKeys, ",");
		String[] pShowKeys = Tokenizer2StringArray(propertyShowKeys, ",");
		/** *************** insert data to excel*************** */
		// 创建（标题)
		row = sheet.createRow(0); // 建立新行
		// 显示标题列名�?
		for (int i = 0; i < pShowKeys.length; i++) {
			cell = row.createCell((short) i); // 建立新cell
			// cell.setEncoding(HSSFCell.ENCODING_UTF_16);
			cell.setCellValue(new HSSFRichTextString(pShowKeys[i]));
		}
		// 逐行取数�?
		int rowId = 1;// 数据行号（从�?2行开始填充数�?)
		for (Iterator iter = list.iterator(); iter.hasNext(); rowId++) {
			row = sheet.createRow(rowId); // 建立新行
			Object obj = iter.next();
			// 生成每一
			for (int i = 0; i < pKeys.length; i++) {
				cell = row.createCell((short) i); // 建立新cell
				cellVal = exporter.getPropertyValue(obj, pKeys[i]);
				// cell.setEncoding(HSSFCell.ENCODING_UTF_16);
				String cellValue = "";
				if (null != cellVal) {
					cellValue = cellVal.toString();
				}
				if (cellVal instanceof Float) {
					cellValue = intoExcelNumberformat.format(cellVal);
				}
				cell.setCellValue(new HSSFRichTextString(cellValue));
			}
		}
		return wb;
	}

	public HSSFWorkbook object2Excel(List list, String propertyKeys, String propertyShowKeys)
			throws Exception {
		return object2Excel(list, propertyKeys, propertyShowKeys, new DefaultPropertyExtractor());

	}

	/**
	 * @see 将StringTokenizer类型数据转化生成字符串数�?
	 * @param sourceStr
	 *            解析","间隔的字符串，变成字符串数组
	 * @param strDot
	 * @return
	 */
	private String[] Tokenizer2StringArray(String sourceStr, String strDot) {
		StringTokenizer st = new StringTokenizer(sourceStr, strDot);
		int size = st.countTokens();
		String[] strArray = new String[size];
		for (int i = 0; i < size; i++) {
			strArray[i] = st.nextToken();
		}

		return strArray;
	}
}