导出复合表头excel

 

Controller层代码：前端将id拼接成“idList”，传入后台。

@RequestMapping("settle/provinceSettlement/downloadProvince.do") public void downloadProvince(HttpServletRequest request, HttpServletResponse response, ModelMap model) { setRequestModelMap(request, model); String idString = request.getParameter("idList"); Map<String, Object> resultMap = new HashMap<String, Object>(); List<Integer> idList = new ArrayList<Integer>(); // 接收数据库的查询出的数据 List<ProvinceSettlementVO> provinceSettlementVOList = new ArrayList<ProvinceSettlementVO>(); if(idString != null && idString != "") { List<String> idListString = Arrays.asList(idString.split(",")); try { for (String id : idListString) { if (id != null) { idList.add(Integer.valueOf(id)); } } } catch (Exception e) { log.error("downloadProvince", e); resultMap.put("msg", "idConversionError"); AjaxResponseUtil.returnData(response, "json", resultMap); } // 查询出的list provinceSettlementVOList = provinceSettlementService.selectListById(idList); } String folderName = DateUtil.date2Str(new java.util.Date(), "yyyyMMddHHmmss"); // 任意fileDir名字，此处用到ParamInfoCache String fileDir = ParamInfoCache.get("BMS@SETTLEGROUP_UP_DIR")+folderName; HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = null; sheet = wb.createSheet("集团结算报表（省结算表）"); ProvinceSettlementExportExcel.getProvinceSettlementExportExcel(wb,sheet,provinceSettlementVOList); String excelName = DateUtil.date2Str(new java.util.Date(), "yyyyMMddHHmmss"); // 生成excel文件 SettleGroupUtil.outputExcel(wb, fileDir+"/"+excelName+".xls"); SettleGroupUtil.exportExcel(wb, response, request, excelName); }

dataUtil中的date2Str

public static String date2Str(Date date, String format) { SimpleDateFormat sdf = new SimpleDateFormat(format); return sdf.format(date); }

 

ProvinceSettlementExportExcel类 package xxx.utils; import xxx.entity.ProvinceSettlementVO; import org.apache.poi.hssf.usermodel.*; import org.apache.poi.hssf.util.CellRangeAddress; import org.apache.poi.ss.usermodel.CellStyle; import java.util.ArrayList; import java.util.List; public class ProvinceSettlementExportExcel { static final float COMPARE_ZREO = 0.000001F; public static void getProvinceSettlementExportExcel(HSSFWorkbook wb, HSSFSheet sheet, List<ProvinceSettlementVO> list) { PoolSettleExportExcelUtils exportExcel = new PoolSettleExportExcelUtils( wb, sheet); // 创建列标头LIST List<String> fialList = new ArrayList<String>(); fialList.add("序号"); fialList.add("省份"); fialList.add("结算账期"); fialList.add("应结算金额"); fialList.add("调整金额"); fialList.add("实际结算金额"); fialList.add(""); fialList.add(""); // 计算该报表的列数 int number = fialList.size(); List<String> fialList2 = new ArrayList<String>(); fialList2.add(""); fialList2.add(""); fialList2.add(""); fialList2.add(""); fialList2.add(""); fialList2.add("价款"); fialList2.add("税款"); fialList2.add("价税合计"); // 给工作表列定义列宽(实际应用自己更改列数) for (int i = 0; i < number; i++) { sheet.setColumnWidth(i, 4000); } // 创建单元格样式 HSSFCellStyle cellStyle = wb.createCellStyle(); // 指定单元格居中对齐 cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 指定单元格垂直居中对齐 cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 指定当单元格内容显示不下时自动换行 cellStyle.setWrapText(true); cellStyle.setBorderLeft(CellStyle.BORDER_MEDIUM); cellStyle.setBorderBottom(CellStyle.BORDER_MEDIUM); cellStyle.setBorderRight(CellStyle.BORDER_MEDIUM); cellStyle.setBorderTop(CellStyle.BORDER_MEDIUM); // 设置单元格字体 HSSFFont font = wb.createFont(); // font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); font.setFontName("仿宋_GB2312"); font.setFontHeight((short) 240); cellStyle.setFont(font); HSSFRow row2 = sheet.createRow(0); // 设置行高 row2.setHeight((short) 1500); HSSFCell row2Cell = null; // 创建不同的LIST的列标题 for (int i = 0; i < number; i++) { row2Cell = row2.createCell(i); row2Cell.setCellStyle(cellStyle); row2Cell.setCellValue(new HSSFRichTextString(fialList.get(i) .toString())); } // 合并单元格 sheet.addMergedRegion(new CellRangeAddress(0, 0, 5, 7)); HSSFCell row3Cell = null; HSSFRow row3 = sheet.createRow(1); row3.setHeight((short) 800); for (int i = 0; i < number; i++) { row3Cell = row3.createCell(i); row3Cell.setCellStyle(cellStyle); row3Cell.setCellValue(new HSSFRichTextString(fialList2.get(i) .toString())); } // 合并单元格 sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, 0)); sheet.addMergedRegion(new CellRangeAddress(0, 1, 1, 1)); sheet.addMergedRegion(new CellRangeAddress(0, 1, 2, 2)); sheet.addMergedRegion(new CellRangeAddress(0, 1, 3, 3)); sheet.addMergedRegion(new CellRangeAddress(0, 1, 4, 4)); List<List<Object>> list2 = new ArrayList<List<Object>>(); // DecimalFormat df = new DecimalFormat("0.00"); for (ProvinceSettlementVO provinceSettlementVO : list) { List<Object> p = new ArrayList<Object>(); p.add(provinceSettlementVO.getId()); p.add(provinceSettlementVO.getProvinceName()); p.add(provinceSettlementVO.getPaymentDays()); p.add(provinceSettlementVO.getSettlementAmount()); p.add(provinceSettlementVO.getAdjustmentAmount()); p.add(provinceSettlementVO.getCostAmount()); p.add(provinceSettlementVO.getTaxAmount()); p.add(provinceSettlementVO.getFactSettlementAmount()); list2.add(p); } HSSFCell rowkCell = null; int k = 1; for (List<Object> list3 : list2) { HSSFRow rowk = sheet.createRow(1 + k); rowk.setHeight((short) 450); // 其他的略低 for (int i = 0; i < number; i++) { rowkCell = rowk.createCell(i); // rowkCell.setCellType(HSSFCell.CELL_TYPE_STRING); rowkCell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); HSSFDataFormat hSSFDataFormat = wb.createDataFormat(); cellStyle.setDataFormat(hSSFDataFormat.getFormat("0.00")); // 把数字格式化为 // 带两位小数的模式 if (list3.get(i) instanceof Double) { rowkCell.setCellValue((Double) list3.get(i)); } else { rowkCell.setCellValue(new HSSFRichTextString(String .valueOf(list3.get(i)))); } rowkCell.setCellStyle(cellStyle); } k++; } } }

 PoolSettleExportExcelUtils 类

package xxx.common.utils; 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; import org.apache.poi.hssf.util.Region; //font.setFontHeight((short) 320); // 20大小 excel 20倍 @SuppressWarnings("deprecation") public class PoolSettleExportExcelUtils { private HSSFWorkbook wb = null; private HSSFSheet sheet = null; public PoolSettleExportExcelUtils(HSSFWorkbook wb, HSSFSheet sheet) { super(); this.wb = wb; this.sheet = sheet; } public HSSFSheet getSheet() { return sheet; } public void setSheet(HSSFSheet sheet) { this.sheet = sheet; } public HSSFWorkbook getWb() { return wb; } public void setWb(HSSFWorkbook wb) { this.wb = wb; } /** * 创建通用EXCEL头部 * * @param headString * 头部显示的字符 * @param colSum * 该报表的列数 */ public void createNormalHead(String headString, int colSum) { HSSFRow row = sheet.createRow(0); // 设置第一行 HSSFCell cell = row.createCell(0); row.setHeight((short) 800); // 定义单元格为字符串类型 cell.setCellType(HSSFCell.ENCODING_UTF_16); cell.setCellValue(new HSSFRichTextString(headString)); // 指定合并区域 sheet.addMergedRegion(new Region(0, (short) 0, 0, (short) colSum)); HSSFCellStyle cellStyle = wb.createCellStyle(); cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 指定单元格居中对齐 cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 指定单元格垂直居中对齐 cellStyle.setWrapText(false);// 指定单元格自动换行 // 设置单元格字体 HSSFFont font = wb.createFont(); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); font.setFontName("仿宋_GB2312"); font.setFontHeight((short) 320); // 20大小 excel 20倍 cellStyle.setFont(font); cellStyle.setWrapText(false);// 指定单元格自动换行 cell.setCellStyle(cellStyle); } /** * 创建通用报表第二行 * * @param params * 统计条件数组 * @param colSum * 需要合并到的列索引 */ public void createNormalTwoRow(String params, int colSum) { HSSFRow row1 = sheet.createRow(1); row1.setHeight((short) 500); HSSFCell cell2 = row1.createCell(0); cell2.setCellType(HSSFCell.ENCODING_UTF_16); cell2.setCellValue(new HSSFRichTextString(params)); // 指定合并区域 sheet.addMergedRegion(new Region(1, (short) 0, 1, (short) colSum)); HSSFCellStyle cellStyle = wb.createCellStyle(); cellStyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT); // 指定单元格居中对齐 cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 指定单元格垂直居中对齐 cellStyle.setWrapText(false);// 指定单元格自动换行 // 设置单元格字体 HSSFFont font = wb.createFont(); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); font.setFontName("仿宋_GB2312"); font.setFontHeight((short) 240); cellStyle.setFont(font); cell2.setCellStyle(cellStyle); } /** * 设置报表标题 * * @param columHeader * 标题字符串数组 */ public void createColumHeader(String[] columHeader) { // 设置列头 HSSFRow row2 = sheet.createRow(2); // 指定行高 row2.setHeight((short) 600); HSSFCellStyle cellStyle = wb.createCellStyle(); cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 指定单元格居中对齐 cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 指定单元格垂直居中对齐 cellStyle.setWrapText(true);// 指定单元格自动换行 // 单元格字体 HSSFFont font = wb.createFont(); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); font.setFontName("仿宋_GB2312"); font.setFontHeight((short) 250); cellStyle.setFont(font); /* * cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 设置单无格的边框为粗体 * cellStyle.setBottomBorderColor(HSSFColor.BLACK.index); // 设置单元格的边框颜色． * cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); * cellStyle.setLeftBorderColor(HSSFColor.BLACK.index); * cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); * cellStyle.setRightBorderColor(HSSFColor.BLACK.index); * cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); * cellStyle.setTopBorderColor(HSSFColor.BLACK.index); */ // 设置单元格背景色 cellStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); HSSFCell cell3 = null; for (int i = 0; i < columHeader.length; i++) { cell3 = row2.createCell(i); cell3.setCellType(HSSFCell.ENCODING_UTF_16); cell3.setCellStyle(cellStyle); cell3.setCellValue(new HSSFRichTextString(columHeader[i])); } } /** * 创建内容单元格 * * @param wb * HSSFWorkbook * @param row * HSSFRow * @param col * short型的列索引 * @param align * 对齐方式 * @param val * 列值 */ public void cteateCell(HSSFWorkbook wb, HSSFRow row, int col, short align, String val) { HSSFCell cell = row.createCell(col); cell.setCellType(HSSFCell.ENCODING_UTF_16); cell.setCellValue(new HSSFRichTextString(val)); HSSFCellStyle cellstyle = wb.createCellStyle(); cellstyle.setAlignment(align); cell.setCellStyle(cellstyle); } /** * 创建合计行 * * @param colSum * 需要合并到的列索引 * @param cellValue */ public void createLastSumRow(int colSum, String[] cellValue) { HSSFCellStyle cellStyle = wb.createCellStyle(); cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 指定单元格居中对齐 cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 指定单元格垂直居中对齐 cellStyle.setWrapText(true);// 指定单元格自动换行 // 单元格字体 HSSFFont font = wb.createFont(); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); font.setFontName("仿宋_GB2312"); font.setFontHeight((short) 250); cellStyle.setFont(font); HSSFRow lastRow = sheet.createRow((short) (sheet.getLastRowNum() + 1)); HSSFCell sumCell = lastRow.createCell(0); sumCell.setCellValue(new HSSFRichTextString("合计")); sumCell.setCellStyle(cellStyle); sheet.addMergedRegion(new Region(sheet.getLastRowNum(), (short) 0, sheet.getLastRowNum(), (short) colSum));// 指定合并区域 for (int i = 2; i < (cellValue.length + 2); i++) { sumCell = lastRow.createCell(i); sumCell.setCellStyle(cellStyle); sumCell.setCellValue(new HSSFRichTextString(cellValue[i - 2])); } } }

 

// 生成excel文件 SettleGroupUtil.outputExcel package xxx.common.utils; import java.io.BufferedInputStream; import java.io.BufferedOutputStream; import java.io.BufferedReader; import java.io.BufferedWriter; import java.io.File; import java.io.FileInputStream; import java.io.FileNotFoundException; import java.io.FileOutputStream; import java.io.FileReader; import java.io.FileWriter; import java.io.IOException; import java.io.InputStream; import java.io.OutputStream; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.Date; import java.util.List; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; //import java.util.zip.ZipEntry; //import java.util.zip.ZipOutputStream; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.tools.zip.ZipEntry; import org.apache.tools.zip.ZipOutputStream; import org.apache.log4j.Logger; import com.opensymphony.oscache.util.StringUtil; /** * @Descrption: 集团报表类 * @author turner * @time 2014-1-5 下午3:11:45 * @version: * @Copyright: */ public final class SettleGroupUtil { static final float COMPARE_ZERO = 0.000001F; private static Logger log = Logger.getLogger(SettleGroupUtil.class); /** * 将存放在sourceFilePath目录下的源文件,打包成fileName名称的ZIP文件,并存放到zipFilePath。 * * @param sourceFilePath * 待压缩的文件路径 * @param zipFilePath * 压缩后存放路径 * @param fileName * 压缩后文件的名称 * @return flag */ public static boolean fileToZip(String sourceFilePath, String zipFilePath, String fileName) { boolean flag = false; File sourceFile = new File(sourceFilePath); FileInputStream fis = null; BufferedInputStream bis = null; FileOutputStream fos = null; ZipOutputStream zos = null; if (sourceFile.exists() == false) { log.info(sourceFilePath + "is not exist"); } else { try { File zipFile = new File(zipFilePath + "/" + fileName + ".zip"); if (zipFile.exists()) { log.info(zipFilePath + " exists：" + fileName + ".zip" + " file."); } else { File[] sourceFiles = sourceFile.listFiles(); if (null == sourceFiles || sourceFiles.length < 1) { log.info(sourceFilePath + " not exists file. no need to uncompress."); } else { fos = new FileOutputStream(zipFile); zos = new ZipOutputStream(new BufferedOutputStream(fos)); zos.setEncoding("GBK"); byte[] bufs = new byte[1024 * 10]; for (int i = 0; i < sourceFiles.length; i++) { // 创建ZIP实体,并添加进压缩包 ZipEntry zipEntry = new ZipEntry( sourceFiles[i].getName()); zipEntry.setUnixMode(644); zos.putNextEntry(zipEntry); // 读取待压缩的文件并写进压缩包里 fis = new FileInputStream(sourceFiles[i]); bis = new BufferedInputStream(fis, 1024 * 10); int read = 0; while ((read = bis.read(bufs, 0, 1024 * 10)) != -1) { zos.write(bufs, 0, read); } } flag = true; } } } catch (FileNotFoundException e) { log.error("", e); } catch (IOException e) { log.error("", e); } finally { // 关闭流 try { if (null != bis) bis.close(); if (null != zos) zos.close(); } catch (IOException e) { log.error("", e); } } } return flag; } public static String date2String(Date date) { SimpleDateFormat sf = new SimpleDateFormat("yyyyMMddHHmmss"); return sf.format(date); } /** * @desc: copy file from here to there. * @param src * @param dst */ public static void copyFile(String src, String dst) { BufferedWriter bufferedWriter = null; BufferedReader bufferedReader = null; try { bufferedWriter = new BufferedWriter(new FileWriter(dst)); bufferedReader = new BufferedReader(new FileReader(src)); String read = new String(); while ((read = bufferedReader.readLine()) != null) { bufferedWriter.write(read + "\r\n"); // bufferedWriter.newLine(); } bufferedWriter.flush(); } catch (IOException e) { e.printStackTrace(); } finally { if (bufferedWriter != null) { try { bufferedWriter.close(); } catch (Exception e1) { log.error("", e1); } } if (bufferedReader != null) { try { bufferedReader.close(); } catch (Exception e2) { log.error("", e2); } } } } public static void copyFileNew(String oldPath, String newPath) { InputStream inStream = null; FileOutputStream fs = null; try { int bytesum = 0; int byteread = 0; File oldfile = new File(oldPath); if (oldfile.exists()) { // 文件存在时 inStream = new FileInputStream(oldPath); // 读入原文件 fs = new FileOutputStream(newPath); byte[] buffer = new byte[1444]; int length; while ((byteread = inStream.read(buffer)) != -1) { bytesum += byteread; // 字节数 文件大小 // System.out.println(bytesum); fs.write(buffer, 0, byteread); } fs.flush(); } } catch (Exception e) { log.error(e.getMessage(), e); } finally { try { if (inStream != null) inStream.close(); } catch (IOException e) { log.error(e.getMessage(), e); } try { if (fs != null) fs.close(); } catch (IOException e) { log.error(e.getMessage(), e); } } } /** * 递归查找文件 * * @param baseDirName * 查找的文件夹路径 * @param targetFileName * 需要查找的文件名 * @param fileList * 查找到的文件集合 */ public static void findFiles(String baseDirName, String targetFileName, List fileList) { /** * 算法简述： 从某个给定的需查找的文件夹出发，搜索该文件夹的所有子文件夹及文件， * 若为文件，则进行匹配，匹配成功则加入结果集，若为子文件夹，则进队列。 队列不空，重复上述操作，队列为空，程序结束，返回结果。 */ String tempName = null; // 判断目录是否存在 File baseDir = new File(baseDirName); if (!baseDir.exists() || !baseDir.isDirectory()) { log.info("find file fail:" + baseDirName + " is not the directory."); } else { String[] filelist = baseDir.list(); for (int i = 0; i < filelist.length; i++) { File readfile = new File(baseDirName + "/" + filelist[i]); // WINDOWS // 为\\ // System.out.println(readfile.getAbsolutePath()); if (!readfile.isDirectory()) { tempName = readfile.getName(); if (SettleGroupUtil.wildcardMatch(targetFileName, tempName)) { // 匹配成功，将文件名添加到结果集 fileList.add(readfile.getAbsoluteFile()); } } else if (readfile.isDirectory()) { findFiles(baseDirName + "/" + filelist[i], targetFileName, fileList); } } } } /** * 通配符匹配 * * @param pattern * 通配符模式 * @param str * 待匹配的字符串 * @return 匹配成功则返回true，否则返回false */ private static boolean wildcardMatch(String pattern, String str) { int patternLength = pattern.length(); int strLength = str.length(); int strIndex = 0; char ch; for (int patternIndex = 0; patternIndex < patternLength; patternIndex++) { ch = pattern.charAt(patternIndex); if (ch == '*') { // 通配符星号*表示可以匹配任意多个字符 while (strIndex < strLength) { if (wildcardMatch(pattern.substring(patternIndex + 1), str.substring(strIndex))) { return true; } strIndex++; } } else if (ch == '?') { // 通配符问号?表示匹配任意一个字符 strIndex++; if (strIndex > strLength) { // 表示str中已经没有字符匹配?了。 return false; } } else { if ((strIndex >= strLength) || (ch != str.charAt(strIndex))) { return false; } strIndex++; } } return (strIndex == strLength); } /** * 生成excel文件 * * @param wb * @param fileName */ public static void outputExcel(HSSFWorkbook wb, String fileName) { FileOutputStream fos = null; try { fos = new FileOutputStream(new File(fileName)); wb.write(fos); fos.close(); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } } /** * 流方式输入EXCEL文件 * * @param fileName * 文件名 */ public static void exportExcel(HSSFWorkbook wb, HttpServletResponse response, HttpServletRequest request, String excelName) { OutputStream fOut = null; try { response.setCharacterEncoding("UTF-8"); response.setContentType("application/vnd.ms-excel"); String agent = request.getHeader("User-Agent"); boolean isMSIE = (agent != null && agent.indexOf("MSIE") != -1); if (!StringUtil.isEmpty(excelName)) { if (isMSIE) { excelName = java.net.URLEncoder.encode(excelName, "UTF8"); } else { excelName = new String(excelName.getBytes("UTF-8"), "ISO-8859-1"); } } response.setHeader("content-disposition", "attachment;filename=" + excelName + ".xls"); fOut = response.getOutputStream(); wb.write(fOut); fOut.flush(); fOut.close(); } catch (IOException e) { e.printStackTrace(); } } }

最后生成的表格如下: