Excel导出数据

package com; import java.io.FileNotFoundException; import java.io.FileOutputStream; import java.io.IOException; import java.math.BigDecimal; import java.text.NumberFormat; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.Date; import java.util.List; import org.apache.commons.beanutils.PropertyUtils; 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.Region; public class ExportDemo { private static SimpleDateFormat DATEFORMAT2 = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss:SSS"); private static SimpleDateFormat DATEFORMAT = new SimpleDateFormat("yyyy-MM-dd"); /** * 功能: 导出到excel,创建XLS的行和列 * <p> * 作者 曾人元 2012-8-20 下午04:19:06 * * @param workBook workBook * @param sheet sheet * @param listObjData 需要导出到excel的数据 * @param strList 表头数据 */ public void writeGatherXLS(HSSFWorkbook workBook, HSSFSheet sheet, List<?> listObjData, List<String> objExcelData) { HSSFRow row = null; int rowNo = 1; row = sheet.createRow(0); sheet.createFreezePane(0, 1, 1, 1); /* * 冻结第一个参数表示要冻结的列数；第二个参数表示要冻结的行数；第三个参数表示右边区域可见的首列序号，从1开始计算； * 第四个参数表示下边区域可见的首行序号，从1开始计算； */ NumberFormat numberFormat = NumberFormat.getInstance(); numberFormat.setMaximumFractionDigits(2); numberFormat.setMinimumFractionDigits(2); HSSFCell cell = null; HSSFRichTextString cellValue = null; for(int i = 0; i < objExcelData.size(); i++) { cell = row.createCell((short)i); cellValue = new HSSFRichTextString(objExcelData.get(i).split("#")[0].toString()); cell.setCellValue(cellValue); cell.setCellStyle(setCellStyle(workBook, sheet, i)); } // 创建excel数据行 createExcelData(row, sheet, cell, listObjData, objExcelData, rowNo); } /** * 创建excel2行和跨列 * * @param workBook * @param sheet * @param listObjData 数据list * @param theFirstLineData 第一行表头信息 * @param theSecondLineData 第二行表头信息 * @param theThirdLineData 第三行表头信息 * @param objExcelData 需要导出的excel数据 * @param totalCol 总列数 */ public void writeExcelDataInfo(HSSFWorkbook workBook, HSSFSheet sheet, List<?> listObjData, List<String> theFirstLineData, List<String> theSecondLineData, List<String> theThirdLineData, List<String> objExcelData) { /* * 冻结第一个参数表示要冻结的列数；第二个参数表示要冻结的行数；第三个参数表示右边区域可见的首列序号，从1开始计算； * 第四个参数表示下边区域可见的首行序号，从1开始计算； */ HSSFCell cell = null; HSSFRichTextString cellValue = null; int rowNo = 0; Short rowFrom = 0; Short colFrom = 0; Short rowTo = 0; Short colTo = 0; HSSFRow row = null; // 判断当前需要创建第一行的数据有没有数据,如果有则开始创建 if (theFirstLineData != null && !theFirstLineData.isEmpty()) { // 导出excel数据开始的行数 rowNo = 1; // 创建excel第一行数据 row = sheet.createRow(0); sheet.createFreezePane(0, 1, 1, 1); createMultiLineOrMultiColumn(row, sheet, theFirstLineData, cell, cellValue, workBook, rowFrom, colFrom, rowTo, colTo); } // 判断当前需要创建第二行的数据有没有数据,如果有则开始创建 if (theSecondLineData != null && !theSecondLineData.isEmpty()) { // 导出excel数据开始的行数 rowNo = 2; // 创建excel第二行数据 row = sheet.createRow(1); sheet.createFreezePane(0, 2, 1, 2); createMultiLineOrMultiColumn(row, sheet, theSecondLineData, cell, cellValue, workBook, rowFrom, colFrom, rowTo, colTo); } // 判断当前需要创建第三行的数据有没有数据,如果有则开始创建 if (theThirdLineData != null && !theThirdLineData.isEmpty()) { // 导出excel数据开始的行数 rowNo = 3; row = sheet.createRow(2); sheet.createFreezePane(0, 3, 1, 3); createMultiLineOrMultiColumn(row, sheet, theThirdLineData, cell, cellValue, workBook, rowFrom, colFrom, rowTo, colTo); } // 创建excel数据行 createExcelData(row, sheet, cell, listObjData, objExcelData, rowNo); } /** * 功能: 生成多行多列的方法 * * @param row * @param sheet * @param strList * @param cell * @param cellValue * @param workBook * @param rowFrom * @param colFrom * @param rowTo * @param colTo */ private void createMultiLineOrMultiColumn(HSSFRow row, HSSFSheet sheet, List<String> strList, HSSFCell cell, HSSFRichTextString cellValue, HSSFWorkbook workBook, Short rowFrom, Short colFrom, Short rowTo, Short colTo) { for(int i = 0; i < strList.size(); i++) { rowFrom = Short.parseShort(strList.get(i).split("#")[1].toString()); colFrom = Short.parseShort(strList.get(i).split("#")[2].toString()); rowTo = Short.parseShort(strList.get(i).split("#")[3].toString()); colTo = Short.parseShort(strList.get(i).split("#")[4].toString()); sheet.addMergedRegion(new Region(rowFrom, colFrom, rowTo, colTo)); cell = row.createCell(colFrom); cellValue = new HSSFRichTextString(strList.get(i).split("#")[0].toString()); cell.setCellValue(cellValue); cell.setCellStyle(setCellStyle(workBook, sheet, i)); } } /** * 功能: 创建excel数据行 * * @param row * @param sheet * @param cell * @param listObjData * @param objExcelData */ private void createExcelData(HSSFRow row, HSSFSheet sheet, HSSFCell cell, List<?> listObjData, List<String> objExcelData, int rowNo) { NumberFormat numberFormat = NumberFormat.getInstance(); numberFormat.setMaximumFractionDigits(2); numberFormat.setMinimumFractionDigits(2); for(Object yearProfit : listObjData) { row = sheet.createRow(rowNo); for(int i = 0; i < objExcelData.size(); i++) { cell = row.createCell((short)i); try { // String类型处理 if (PropertyUtils.getProperty(yearProfit, objExcelData.get(i).split("#")[1]) instanceof String) { String cellStr = (String)PropertyUtils.getProperty(yearProfit, objExcelData.get(i).split("#")[1]); if (null != cellStr) { cell.setCellValue(cellStr); } else { cell.setCellValue(""); } } // Integer类型处理 if (PropertyUtils.getProperty(yearProfit, objExcelData.get(i).split("#")[1]) instanceof Integer) { Integer cellInteger = (Integer)PropertyUtils.getProperty(yearProfit, objExcelData.get(i).split("#")[1]); if (null != cellInteger) { cell.setCellValue(cellInteger); } else { cell.setCellValue(""); } } // Long类型处理 if (PropertyUtils.getProperty(yearProfit, objExcelData.get(i).split("#")[1]) instanceof Long) { Long cellLong = (Long)PropertyUtils.getProperty(yearProfit, objExcelData.get(i).split("#")[1]); if (null != cellLong) { cell.setCellValue(cellLong); } else { cell.setCellValue(""); } } // Double类型处理 if (PropertyUtils.getProperty(yearProfit, objExcelData.get(i).split("#")[1]) instanceof Double) { Double cellDouble = (Double)PropertyUtils.getProperty(yearProfit, objExcelData.get(i).split("#")[1]); if (null != cellDouble) { cell.setCellValue(numberFormat.format(cellDouble) + "%"); } else { cell.setCellValue(""); } } // Float类型处理 if (PropertyUtils.getProperty(yearProfit, objExcelData.get(i).split("#")[1]) instanceof Float) { Float cellFloat = (Float)PropertyUtils.getProperty(yearProfit, objExcelData.get(i).split("#")[1]); if (n