随笔：excel导出

2019/4/10–poi导出excel导出

package com.neusoft.issa.util;

import java.io.File;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Set;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFPatriarch;
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.HSSFColorPredefined;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.xmlbeans.impl.piccolo.io.FileFormatException;

import com.neusoft.issa.items.module.appManage.bean.AppCheckInfoBean;
import com.neusoft.issa.items.module.appManage.bean.AppOsReformBean;
import com.neusoft.issa.items.module.appManage.bean.AppReformStatBean;
import com.neusoft.issa.items.module.appManage.bean.AppRiskProBean;
import com.neusoft.issa.items.module.appManage.bean.AppRiskStatBean;
import com.neusoft.issa.items.module.appManage.bean.AppRiskSystemBean;

public class WriteExcel {

	private static final String EXTENSION_XLS = "xls";
	private static final String EXTENSION_XLSX = "xlsx";

	/**
	 * 根据文件类型创建workbook
	 * 
	 * @param type
	 * @return
	 * @throws Exception
	 */

	private Workbook getWorkbook(String type) throws Exception {
		Workbook workbook = null;
		if (type.endsWith(EXTENSION_XLS)) {
			workbook = new HSSFWorkbook();
		} else if (type.endsWith(EXTENSION_XLSX)) {
			workbook = new XSSFWorkbook();
		} else {
			throw new FileFormatException("传入的文件不是excel");
		}
		return workbook;
	}

	/**
	 * 往Excel写入数据并导出
	 * 
	 * @param filePath
	 * @param fileName
	 * @param sheetName
	 * @param tHead
	 * @param data
	 * @return
	 * @throws Exception
	 */
	public HSSFWorkbook writeExcel(String sheetName, String[] tHead, List<Map<String, Object>> data) throws Exception {
		// 判断数据是否为空
		if (data == null) {
			return null;
		}

		if (sheetName == null || "".equals(sheetName)) {
			sheetName = "sheet1";
		}
		HSSFWorkbook workbook = (HSSFWorkbook) getWorkbook("xls");
		HSSFSheet sheet = workbook.createSheet(sheetName);

		MyCellStyle myStyle = new MyCellStyle();
		CellStyle headStyle = myStyle.confCellStyle(workbook, true, true, true, false).getStyle();
		
		//headStyle = myStyle.setBgColor(HSSFColorPredefined.LIGHT_BLUE.getIndex()).getStyle();
		// 表头字段名
		Row head = sheet.createRow(0);

		for (int i = 0; i < tHead.length; i++) {
			setCellValue(setStyle(head.createCell(i), headStyle), tHead[i]);
		}
		myStyle = new MyCellStyle();
		CellStyle dataStyle = myStyle.confCellStyle(workbook, false, true, true, true).getStyle();

		int width = 0;//总列数
		// 循环写入行数据
		for (int i = 0; i < data.size(); i++) {
			Map<String, Object> map = data.get(i);
			Set<Entry<String, Object>> set = map.entrySet();
			width = set.size();
			Row row = sheet.createRow(i + 1);
			int colIndex = 0;
			for (Entry<String, Object> entry : set) {
				setCellValue(setStyle(row.createCell(colIndex++), dataStyle), entry.getValue());
			}
		}
		
		setSizeColumn(sheet, width);

		return workbook;
	}


	/**
	 * 漏洞统计导出，三个sheet页面
	 * 
	 * @param data
	 * @return
	 * @throws Exception
	 */
	@SuppressWarnings("unchecked")
	public HSSFWorkbook writeFlawExcel(List<Map<String, Object>> list) throws Exception {
		// 判断数据是否为空
		if (list == null) {
			return null;
		}

		HSSFWorkbook workbook = (HSSFWorkbook) getWorkbook("xls");
		String sheetName = "sheet1";
		for (Map<String, Object> map : list) {
			if ("0".equals(map.get("flaw_level"))) { // 高危
				sheetName = "高危漏洞";
			} else if ("1".equals(map.get("flaw_level"))) { // 中危
				sheetName = "中危漏洞";
			} else if ("2".equals(map.get("flaw_level"))) { // 低危
				sheetName = "低危漏洞";
			}
			HSSFSheet sheet = workbook.createSheet(sheetName);
			MyCellStyle myStyle = new MyCellStyle();
			CellStyle headStyle = myStyle.confCellStyle(workbook, true, true, true, false).getStyle();
			// 表头字段名
			Row head = sheet.createRow(0);
			String[] tHead = { "序号", "漏洞", "数量" };
			for (int i = 0; i < tHead.length; i++) {
				setCellValue(setStyle(head.createCell(i), headStyle), tHead[i]);
			}

			myStyle = new MyCellStyle();
			CellStyle dataStyle = myStyle.confCellStyle(workbook, false, true, true, true).getStyle();

			// 循环写入行数据
			List<Map<String, Object>> data = (List<Map<String, Object>>) map.get("data");
			if (data == null || data.isEmpty()) {
				continue;
			}
			for (int i = 0; i < data.size(); i++) {
				Map<String, Object> dataMap = data.get(i);
				Set<Entry<String, Object>> set = dataMap.entrySet();
				Row row = sheet.createRow(i + 1);

				setCellValue(setStyle(row.createCell(0), dataStyle), (i + 1));
				int colIndex = 1;
				for (Entry<String, Object> entry : set) {
					setCellValue(setStyle(row.createCell(colIndex++), dataStyle), entry.getValue());
				}
			}
			sheet.setColumnWidth(0, 2500);
			sheet.setColumnWidth(1, 10000);
			sheet.setColumnWidth(2, 2500);
		}
		return workbook;
	}

	public HSSFWorkbook writeReformStat(List<AppReformStatBean> list) throws Exception {
		HSSFWorkbook workbook = (HSSFWorkbook) getWorkbook("xls");
		HSSFSheet sheet = workbook.createSheet("app整改统计");
		
		// 表头
		HSSFRow title = sheet.createRow(0);
		MyCellStyle myStyle = new MyCellStyle();
		CellStyle headStyle = myStyle.confCellStyle(workbook, true, true, true, false).setBgColor(HSSFColorPredefined.LIGHT_CORNFLOWER_BLUE.getIndex()).getStyle();

		setCellValue(setStyle(title.createCell(0), headStyle), "序号");
		setCellValue(setStyle(title.createCell(1), headStyle), "应用名称");
		setCellValue(setStyle(title.createCell(2), headStyle), "系统");
		setCellValue(setStyle(title.createCell(3), headStyle), "提交次数");
		setCellValue(setStyle(title.createCell(4), headStyle), "版本号");
		setCellValue(setStyle(title.createCell(5), headStyle), "提交顺序");
		setCellValue(setStyle(title.createCell(6), headStyle), "提交时间");
		setCellValue(setStyle(title.createCell(7), headStyle), "测试结果");
		setCellValue(setStyle(title.createCell(8), headStyle), "当前状态");
		// 合并单元格
		int span1 = 1;// 记录前下标 为0和1列当前位置
		int span2 = 1;// 记录前下标 为2和3列当前位置
		for (AppReformStatBean b1 : list) {
			List<AppOsReformBean> l2 = b1.getChildren();
			int subSize = 0;
			for (AppOsReformBean b2 : l2) {
				List<AppCheckInfoBean> l3 = b2.getChildren();
				// 下标 为2和3列从span2位置开始合并l3.size()个单元格
				if (l3.size() >= 2) {
					sheet.addMergedRegion(new CellRangeAddress(span2, span2 + l3.size() - 1, 2, 2));
					sheet.addMergedRegion(new CellRangeAddress(span2, span2 + l3.size() - 1, 3, 3));
				}
				span2 += l3.size();
				subSize += l3.size();
			}
			// 下标 为0和1列从span1位置开始合并subSize个单元格
			if (subSize >= 2) {
				sheet.addMergedRegion(new CellRangeAddress(span1, span1 + subSize - 1, 0, 0));
				sheet.addMergedRegion(new CellRangeAddress(span1, span1 + subSize - 1, 1, 1));
			}
			span1 += subSize;
		}
		int serialno = 1;
		int rowIndex = 1;
		myStyle = new MyCellStyle();
		CellStyle dataStyle = myStyle.confCellStyle(workbook, false, true, true, true).getStyle();
		myStyle = new MyCellStyle();
		CellStyle redFontStyle = myStyle.confCellStyle(workbook, false, true, true, true).setFontStyle(workbook, (short)10, HSSFColorPredefined.RED.getIndex(), "宋体", false).getStyle();
		
		Row dataRow;
		for (AppReformStatBean b1 : list) {
			String appName = b1.getApp_name();

			List<AppOsReformBean> l2 = b1.getChildren();
			for (AppOsReformBean b2 : l2) {
				String os = b2.getSystem();
				int times = b2.getSubmit_times();
				List<AppCheckInfoBean> l3 = b2.getChildren();
				for (AppCheckInfoBean b3 : l3) {
					int colIndex = 0;
					String version = b3.getVersion();
					String order = b3.getSubmit_order();
					String time = b3.getSubmit_time();
					String result = b3.getCheck_result();
					String reform = b3.getReform();
					dataRow = sheet.createRow(rowIndex++);
					setCellValue(setStyle(dataRow.createCell(colIndex++), dataStyle), serialno);
					setCellValue(setStyle(dataRow.createCell(colIndex++), dataStyle), appName);
					setCellValue(setStyle(dataRow.createCell(colIndex++), dataStyle), os);
					setCellValue(setStyle(dataRow.createCell(colIndex++), dataStyle), times);

					setCellValue(setStyle(dataRow.createCell(colIndex++), dataStyle), version);
					setCellValue(setStyle(dataRow.createCell(colIndex++), dataStyle), order);
					setCellValue(setStyle(dataRow.createCell(colIndex++), dataStyle), time);
					setCellValue(setStyle(dataRow.createCell(colIndex++), dataStyle), result);
					if("整改中".equals(reform)){
						setCellValue(setStyle(dataRow.createCell(colIndex++), redFontStyle), reform);
					}else{
						setCellValue(setStyle(dataRow.createCell(colIndex++), dataStyle), reform);
					}
				}
			}
			serialno++;
		}
		sheet.setColumnWidth(0, 2000);
		sheet.setColumnWidth(1, 5000);
		sheet.setColumnWidth(2, 3000);
		sheet.setColumnWidth(3, 3000);
		sheet.setColumnWidth(4, 4000);
		sheet.setColumnWidth(5, 3000);
		sheet.setColumnWidth(6, 4000);
		sheet.setColumnWidth(7, 3000);
		sheet.setColumnWidth(8, 5000);
		
		return workbook;
	}

	/**
	 * 风险统计导出Excel
	 * 
	 * @param list
	 * @return
	 * @throws Exception
	 */
	public HSSFWorkbook writeRiskStat(List<AppRiskStatBean> list) throws Exception {
		HSSFWorkbook workbook = (HSSFWorkbook) getWorkbook("xls");
		HSSFSheet sheet = workbook.createSheet("app风险统计");

		// 表头
		MyCellStyle myStyle = new MyCellStyle();
		CellStyle headStyle = myStyle.confCellStyle(workbook, true, true, true, false).getStyle();
		HSSFRow title = sheet.createRow(0);
		setCellValue(setStyle(title.createCell(0), headStyle), "序号");
		setCellValue(setStyle(title.createCell(1), headStyle), "应用名称");
		setCellValue(setStyle(title.createCell(2), headStyle), "系统");
		setCellValue(setStyle(title.createCell(3), headStyle), "版本号");
		setCellValue(setStyle(title.createCell(4), headStyle), "高危风险");
		setCellValue(setStyle(title.createCell(5), headStyle), "中危风险");
		setCellValue(setStyle(title.createCell(6), headStyle), "低危风险");
		setCellValue(setStyle(title.createCell(7), headStyle), "备注");
		// 合并单元格
		int span1 = 1;// 记录前下标 为0和1列当前位置
		int span2 = 1;// 记录前下标 为2和3列当前位置
		for (AppRiskStatBean b1 : list) {
			List<AppRiskSystemBean> l2 = b1.getChildren();
			int subSize = 0;
			for (AppRiskSystemBean b2 : l2) {
				List<AppRiskProBean> l3 = b2.getChildren();
				// 下标 为2和3列从span2位置开始合并l3.size()个单元格
				if (l3.size() >= 2) {
					sheet.addMergedRegion(new CellRangeAddress(span2, span2 + l3.size() - 1, 2, 2));
					sheet.addMergedRegion(new CellRangeAddress(span2, span2 + l3.size() - 1, 3, 3));
				}
				span2 += l3.size();
				subSize += l3.size();
			}
			// 下标 为0和1列从span1位置开始合并subSize个单元格
			if (subSize >= 2) {
				sheet.addMergedRegion(new CellRangeAddress(span1, span1 + subSize - 1, 0, 0));
				sheet.addMergedRegion(new CellRangeAddress(span1, span1 + subSize - 1, 1, 1));
			}
			span1 += subSize;
		}
		int serialno = 1;
		int rowIndex = 1;

		// 设置样式
		myStyle = new MyCellStyle();
		CellStyle dataStyle = myStyle.confCellStyle(workbook, false, true, true,true).getStyle();
		
		Row dataRow;
		// 循环设置单元格值
		for (AppRiskStatBean b1 : list) {
			// app应用名称
			String appName = b1.getApp_name();
			List<AppRiskSystemBean> l2 = b1.getChildren();
			for (AppRiskSystemBean b2 : l2) {
				// 系统
				String os = b2.getSystem();
				// 版本号
				String version = b2.getVersion();
				List<AppRiskProBean> l3 = b2.getChildren();
				for (AppRiskProBean b3 : l3) {
					int colIndex = 0;
					// 高、中、低风险
					String highrisk = b3.getHighrisk();
					String middlerisk = b3.getMiddlerisk();
					String lowrisk = b3.getLowrisk();
					// 备注 空着
					String remark = "";

					dataRow = sheet.createRow(rowIndex++);
					setCellValue(setStyle(dataRow.createCell(colIndex++), dataStyle), serialno);
					setCellValue(setStyle(dataRow.createCell(colIndex++), dataStyle), appName);

					setCellValue(setStyle(dataRow.createCell(colIndex++), dataStyle), os);
					setCellValue(setStyle(dataRow.createCell(colIndex++), dataStyle), version);

					setCellValue(setStyle(dataRow.createCell(colIndex++), dataStyle), highrisk);
					setCellValue(setStyle(dataRow.createCell(colIndex++), dataStyle), middlerisk);
					setCellValue(setStyle(dataRow.createCell(colIndex++), dataStyle), lowrisk);
					setCellValue(setStyle(dataRow.createCell(colIndex++), dataStyle), remark);
				}
			}
			serialno++;
		}
		// 设置列宽
		sheet.setColumnWidth(0, 2000);
		sheet.setColumnWidth(1, 5000);
		sheet.setColumnWidth(2, 5000);
		sheet.setColumnWidth(3, 3000);
		sheet.setColumnWidth(4, 8000);
		sheet.setColumnWidth(5, 8000);
		sheet.setColumnWidth(6, 8000);
		return workbook;
	}

	public HSSFWorkbook writeProblemStat(List<Map<String, String>> proList, List<Map<String, String>> typList,
			byte[] barBuffer, byte[] pieBuffer) throws Exception {

		HSSFWorkbook workbook = (HSSFWorkbook) getWorkbook("xls");
		HSSFSheet sheet = workbook.createSheet("app检测汇总");

		// 表头
		MyCellStyle myStyle = new MyCellStyle();
		CellStyle headStyle = myStyle.confCellStyle(workbook, true, true, true, false).getStyle();
		HSSFRow title = sheet.createRow(0);
		setCellValue(setStyle(title.createCell(0), headStyle), "时间");
		setCellValue(setStyle(title.createCell(1), headStyle), "提交版本数");
		setCellValue(setStyle(title.createCell(2), headStyle), "发现问题数");
		
		sheet.setColumnWidth(0, 5000);
		sheet.setColumnWidth(1, 3000);
		sheet.setColumnWidth(2, 3000);
		myStyle = new MyCellStyle();
		CellStyle dataStyle = myStyle.confCellStyle(workbook, false, true, true,true).getStyle();
		Row dataRow;
		int rowIndex = 1;
		for (Map<String, String> map : proList) {
			dataRow = sheet.createRow(rowIndex++);
			int colIndex = 0;
			setCellValue(setStyle(dataRow.createCell(colIndex++), dataStyle), map.get("SUBMIT_TIME"));
			setCellValue(setStyle(dataRow.createCell(colIndex++), dataStyle), map.get("VER_COUNT"));
			setCellValue(setStyle(dataRow.createCell(colIndex++), dataStyle), map.get("PRO_COUNT"));
		}
		
		putImgByte2Sheet(workbook, 0, barBuffer, (short)5, 2, (short)19, 18);
		
		/** ---------------------------------------------------------------------------*/
		
		HSSFSheet sheet2 = workbook.createSheet("app问题分类");

		// 表头
		HSSFRow title2 = sheet2.createRow(0);
		setCellValue(setStyle(title2.createCell(0), headStyle), "问题分类");
		setCellValue(setStyle(title2.createCell(1), headStyle), "问题数量");
		
		sheet2.setColumnWidth(0, 5000);
		sheet2.setColumnWidth(1, 5000);

		rowIndex = 1;
		
		for (Map<String, String> map : typList) {
			int colIndex = 0;
			dataRow = sheet2.createRow(rowIndex++);
			setCellValue(setStyle(dataRow.createCell(colIndex++), dataStyle), map.get("name"));
			setCellValue(setStyle(dataRow.createCell(colIndex++), dataStyle), map.get("value"));
		}
		
		putImgByte2Sheet(workbook, 1, pieBuffer, (short)4, 2, (short)10, 18);
		
		return workbook;
	}

	public HSSFWorkbook writeIntegrated(String[] t1,String[] colName1, String[] t2,String[] colName2, List<Map<String, Object>> proList, List<Map<String, Object>> typList,
			byte[] barBuffer) throws Exception {

		HSSFWorkbook workbook = (HSSFWorkbook) getWorkbook("xls");
		HSSFSheet sheet = workbook.createSheet("sheet1");

		// 表头
		HSSFRow title = sheet.createRow(0);
		MyCellStyle myStyle = new MyCellStyle();
		CellStyle headStyle = myStyle.confCellStyle(workbook, false, true, true, false).getStyle();
		int hColIndex = 0;
		for(String s : t1){
			setCellValue(setStyle(title.createCell(hColIndex++), headStyle), s);
		}

		myStyle = new MyCellStyle();
		CellStyle dataStyle = myStyle.confCellStyle(workbook, false, true, true,true).getStyle();
		Row dataRow;
		int rowIndex = 1;
		for (Map<String, Object> map : proList) {
			dataRow = sheet.createRow(rowIndex++);
			int colIndex = 0;
			for(String s:colName1){
				setCellValue(setStyle(dataRow.createCell(colIndex), dataStyle), map.get(s));
				sheet.autoSizeColumn(colIndex);
				colIndex++;
			}
			
		}
		
		/** ---------------------------------------------------------------------------*/
		
		HSSFSheet sheet2 = workbook.createSheet("sheet2");

		// 表头
		HSSFRow title2 = sheet2.createRow(0);
		hColIndex = 0;
		for(String s : t2){
			setCellValue(setStyle(title2.createCell(hColIndex++), headStyle), s);
		}

		rowIndex = 1;
		
		for (Map<String, Object> map : typList) {
			int colIndex = 0;
			dataRow = sheet2.createRow(rowIndex++);
			for(String s:colName2){
				setCellValue(setStyle(dataRow.createCell(colIndex), dataStyle), map.get(s));
				sheet.autoSizeColumn(colIndex);
				colIndex++;
			}
		}
		//picture
		putImgByte2Sheet(workbook, 1, barBuffer, (short)5, 2, (short)13, 18);
		
		return workbook;
	}
	/**
	 * Excel 插入图片
	 * 
	 * @param wb
	 *            workbook
	 * @param index
	 *            sheet index
	 * @param buffer
	 *            byte[]
	 */
	public void putImgByte2Sheet(HSSFWorkbook wb, int index, byte[] buffer, short col1, int row1, short col2,
			int row2) {
		HSSFSheet sheet = wb.getSheetAt(index);
		HSSFPatriarch patri = sheet.createDrawingPatriarch();
		HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0, col1, row1, col2, row2);
		patri.createPicture(anchor, wb.addPicture(buffer, HSSFWorkbook.PICTURE_TYPE_PNG));
	}
	
	/**
	 * 总数Total
	 * @param wb
	 * @param index
	 * @param result
	 * @return
	 */
	public HSSFWorkbook putCountSheet(HSSFWorkbook wb, int index,List<Map<String, Object>> result) {
		HSSFSheet sheet = wb.getSheetAt(index);
		HSSFWorkbook workbook = wb;

		// 表头
		MyCellStyle myStyle = new MyCellStyle();
		CellStyle headStyle = myStyle.confCellStyle(wb, true, true, true, false).getStyle();
		HSSFRow title = sheet.createRow(sheet.getLastRowNum()+1);
		setCellValue(setStyle(title.createCell(0), headStyle ), "应用总数");
		setCellValue(setStyle(title.createCell(1), headStyle), "android数量");
		setCellValue(setStyle(title.createCell(2), headStyle), "ios数量");
		
		sheet.setColumnWidth(0, 5000);
		sheet.setColumnWidth(1, 5000);
		sheet.setColumnWidth(2, 5000);
		myStyle = new MyCellStyle();
		CellStyle dataStyle = myStyle.confCellStyle(wb, false, true, true,true).getStyle();
		Row dataRow;
		int rowIndex =sheet.getLastRowNum()+1;
		for (Map<String, Object> map : result) {
			dataRow = sheet.createRow(rowIndex++);
			int colIndex = 0;
			setCellValue(setStyle(dataRow.createCell(colIndex++), dataStyle), map.get("num"));
			setCellValue(setStyle(dataRow.createCell(colIndex++), dataStyle), map.get("android"));
			setCellValue(setStyle(dataRow.createCell(colIndex++), dataStyle), map.get("ios"));
		}
		return workbook;
	}

	/**
	 * 总数-漏洞
	 * @param wb
	 * @param index
	 * @param result
	 * @return
	 */
	public HSSFWorkbook putCountSheetHole(HSSFWorkbook wb, int index,List<Map<String, Object>> result) {
		HSSFSheet sheet = wb.getSheetAt(index);
		HSSFWorkbook workbook = wb;

		// 表头
		MyCellStyle myStyle = new MyCellStyle();
		CellStyle headStyle = myStyle.confCellStyle(wb, true, true, true, false).getStyle();
		HSSFRow title = sheet.createRow(sheet.getLastRowNum()+1);
		setCellValue(setStyle(title.createCell(0), headStyle ), "应用总数");
	
		sheet.setColumnWidth(0, 5000);
		
		myStyle = new MyCellStyle();
		CellStyle dataStyle = myStyle.confCellStyle(wb, false, true, true,true).getStyle();
		Row dataRow;
		int rowIndex =sheet.getLastRowNum()+1;
		for (Map<String, Object> map : result) {
			dataRow = sheet.createRow(rowIndex++);
			int colIndex = 0;
			setCellValue(setStyle(dataRow.createCell(colIndex++), dataStyle), map.get("Total"));
		}
		return workbook;
	}
	
	/**
	 * 加入样式
	 * 
	 * @param cell
	 * @param style
	 */
	public Cell setStyle(Cell cell, CellStyle style) {
		cell.setCellStyle(style);
		return cell;
	}

	/**
	 * 合并单元格
	 * 
	 * @param sheet
	 * @param startRow
	 * @param endRow
	 * @param startCol
	 * @param endCol
	 */
	public void mergedRegion(Sheet sheet, int startRow, int endRow, int startCol, int endCol) {
		sheet.addMergedRegion(new CellRangeAddress(startRow, endRow, startCol, endCol));
	}

	public CellStyle confCellBg(CellStyle cellStyle, short color) {
		cellStyle.setFillBackgroundColor(color);
		return cellStyle;
	}

	public static void setCellValue(Cell cell, Object obj) {
		if (obj instanceof Double) {
			Double value = (Double) obj;
			cell.setCellType(CellType.NUMERIC);
			cell.setCellValue(value);
		} else if (obj instanceof Integer) {
			Integer value = (Integer) obj;
			cell.setCellType(CellType.NUMERIC);
			cell.setCellValue(value);
		} else if (obj instanceof Date) {
			Date value = (Date) obj;
			cell.setCellType(CellType.STRING);
			cell.setCellValue(value);
		} else if (obj instanceof Calendar) {
			Calendar value = (Calendar) obj;
			cell.setCellType(CellType.STRING);
			cell.setCellValue(value);
		} else {
			String value = String.valueOf(obj);
			cell.setCellType(CellType.STRING);
			cell.setCellValue(value);
		}
	}
	
	private void setSizeColumn(HSSFSheet sheet, int i) {
	    for (int columnNum = 0; columnNum < i; columnNum++) {
	        int columnWidth = 0;
	        for (int rowNum = 0; rowNum < sheet.getLastRowNum(); rowNum++) {
	            HSSFRow currentRow;
	            //当前行未被使用过
	            if (sheet.getRow(rowNum) == null) {
	                currentRow = sheet.createRow(rowNum);
	            } else {
	                currentRow = sheet.getRow(rowNum);
	            }

	            if (currentRow.getCell(columnNum) != null) {
	                HSSFCell currentCell = currentRow.getCell(columnNum);
	                if (currentCell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
	                    int length = currentCell.getStringCellValue().getBytes().length;
	                    if (columnWidth < length) {
	                        columnWidth = length;
	                    }
	                }
	            }
	        }
	        if (columnWidth > 255) {
	        	sheet.setColumnWidth(columnNum, 254 * 256);
	        } else{
	        	sheet.setColumnWidth(columnNum, (columnWidth + 1) * 256);
	        }
	    }
	}

}