poi导出excel，表头数据动态拼装
/* * 第一步：拼装表头和数据 */ // 放多个sheet的集合 List<Map<String,Object>> datas = new ArrayList<Map<String,Object>>(); //字典项 String typeCode = Tool.getTypeCodeByCategory(belongsCategory); List<DataDictVO> dataDicts = dataDictService.getDataDictListByTypeCode(typeCode); List<Map<String,String>> purchaseMethodData = (List<Map<String,String>>) getDataInfoBusiness.getDataSourceList2(params, "TradeGetInfo", "category", belongsCategory, statisticalTime); //动态表头 List<List<CellInfo>> headerTwo = new LinkedList<List<CellInfo>>(); List<CellInfo> firstTwo = new LinkedList<CellInfo>(); firstTwo.add(new CellInfo("来源系统",2,1)); if(!CollectionUtils.isEmpty(dataDicts)){ for(DataDictVO dict:dataDicts){ firstTwo.add(new CellInfo(dict.getName(),1,1)); } } headerTwo.add(firstTwo); List<CellInfo> second = new LinkedList<CellInfo>(); int colNum = 1; second.add(new CellInfo("来源系统")); if(!CollectionUtils.isEmpty(dataDicts)){ for(int i=0;i<dataDicts.size();i++){ second.add(new CellInfo("入库")); colNum += 1; } } headerTwo.add(second); //表数据 List<List<CellInfo>> dataTwo = new LinkedList<List<CellInfo>>(); for(Map<String,String> typeData:purchaseMethodData){ List<CellInfo> row = new LinkedList<CellInfo>(); row.add(new CellInfo(typeData.get("DATA_SOURCE_CODE_NAME"))); for(DataDictVO dict:dataDicts){ String code = dict.getCode() + "_ACCESS_NUM"; row.add(new CellInfo(typeData.get(code))); } dataTwo.add(row); } Map<String, Object> mapTwo = new HashMap<String, Object>(); mapTwo.put("sheetName", "按类别统计"); mapTwo.put("title", "按类别统计"); mapTwo.put("unitInfo", ""); mapTwo.put("headerLength", colNum); mapTwo.put("header", headerTwo); mapTwo.put("data", dataTwo); datas.add(mapTwo); /* * 第二步：调用工具类 */ /** * 导出成Excel表格 * @param sheetName sheet名称 该类多Sheet * @param title 表格的title。eg:政府采购 * @param unitInfo 表格单位信息。eg:金额单位:万元 * @param headerLength 表头的最大列数（按未合并前的单元格算） * @param header 表头信息（见CellInfo）如果跨行则被合并的行不再需要说明跨行信息 即：跨行信息放在第一行出现该信息的CellInfo里。后面的每行只需要输入相同的内容即可。 * @param data 同表头 * @param out 输出流 将生成的Excel数据流传输到该输出流 * @throws IOException */ @SuppressWarnings("unchecked") public static HSSFWorkbook exportExcel(List<Map<String,Object>> datas) throws IOException{ //创建工作簿 HSSFWorkbook workbook = new HSSFWorkbook(); //表头的样式 HSSFCellStyle titlestyle = workbook.createCellStyle();// 创建样式对象 titlestyle.setAlignment(HSSFCellStyle.ALIGN_CENTER_SELECTION);// 水平居中 titlestyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直居中 titlestyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); titlestyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); titlestyle.setBorderRight(HSSFCellStyle.BORDER_THIN); titlestyle.setBorderTop(HSSFCellStyle.BORDER_THIN); // titlestyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); // titlestyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); //字体 HSSFFont titleFont = workbook.createFont(); // 创建字体对象 titleFont.setFontHeightInPoints((short) 11); // 设置字体大小 titleFont.setFontName("微软雅黑"); // 设置为黑体字 // titleFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//粗体 titlestyle.setFont(titleFont); //指定当单元格内容显示不下时自动换行 titlestyle.setWrapText(true); //表数据的样式 HSSFCellStyle style = workbook.createCellStyle();// 创建样式对象 style.setAlignment(HSSFCellStyle.ALIGN_CENTER_SELECTION);// 水平居中 style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直居中 style.setBorderBottom(HSSFCellStyle.BORDER_THIN); style.setBorderLeft(HSSFCellStyle.BORDER_THIN); style.setBorderRight(HSSFCellStyle.BORDER_THIN); style.setBorderTop(HSSFCellStyle.BORDER_THIN); //字体 HSSFFont font = workbook.createFont(); // 创建字体对象 font.setFontHeightInPoints((short) 11); // 设置字体大小 font.setFontName("微软雅黑"); // 设置为黑体字 style.setFont(font); //指定当单元格内容显示不下时自动换行 style.setWrapText(true); for(Map<String,Object> map:datas){ String sheetName = map.get("sheetName").toString(); String title = map.get("title").toString(); String unitInfo = map.get("unitInfo").toString(); int headerLength = (int)map.get("headerLength"); List<List<CellInfo>> header = (List<List<CellInfo>>)map.get("header"); List<List<CellInfo>> data = (List<List<CellInfo>>)map.get("data"); //创建Sheet HSSFSheet sheet = workbook.createSheet(sheetName); //设置表格默认列宽度为20个字节 sheet.setDefaultColumnWidth(20); //设置第一行 （单位信息，定制） int rowNum = 0; if(null!=unitInfo){ sheet.addMergedRegion(new CellRangeAddress(rowNum,1,0,headerLength-1)); HSSFRow rowUnit = sheet.createRow(rowNum); HSSFCell cellUnit = rowUnit.createCell(0); HSSFCellStyle unitStyle = workbook.createCellStyle();// 创建样式对象 unitStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直居中 unitStyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT); unitStyle.setFont(font); cellUnit.setCellStyle(unitStyle); cellUnit.setCellValue(unitInfo); rowNum =1; } //标题 if(null!=title){ rowNum++; HSSFRow rowHeader = sheet.createRow(rowNum); for(int i=0;i<headerLength;i++){ HSSFCell cellHeader = rowHeader.createCell(i); cellHeader.setCellStyle(titlestyle); cellHeader.setCellValue(title); } rowNum++; HSSFRow title2 = sheet.createRow(rowNum); for(int i=0;i<headerLength;i++){ HSSFCell cellHeader = title2.createCell(i); cellHeader.setCellStyle(titlestyle); cellHeader.setCellValue(title); } sheet.addMergedRegion(new CellRangeAddress(rowNum-1,rowNum,0,headerLength-1)); } //表头 for (int i = 0; i < header.size(); i++) { //行 rowNum ++; HSSFRow row = sheet.createRow(rowNum); List<CellInfo> cols = header.get(i); //创建列 int colNum = 0; for(int j=0;j<cols.size();j++){ HSSFCell cell = row.createCell(colNum); cell.setCellStyle(titlestyle); cell.setCellValue(cols.get(j).getContent()); int firstRow = rowNum; int lastRow =rowNum; int firstCol = colNum; int lastCol = colNum; boolean merge = false; if(cols.get(j).getRowSpan()>1){ lastRow += cols.get(j).getRowSpan()-1; merge = true; } //如果跨行则先创建被合并的单元格（主要是不创建的话，合并后的样式引用有问题） if(cols.get(j).getColSpan()>1){ for(int k=0;k<cols.get(j).getColSpan()-1;k++){ colNum++; HSSFCell tmpCell = row.createCell(colNum); tmpCell.setCellStyle(titlestyle); tmpCell.setCellValue(cols.get(j).getContent()); } lastCol = colNum; merge = true; } colNum++; if(merge){ sheet.addMergedRegion(new CellRangeAddress(firstRow,lastRow,firstCol,lastCol)); } } } //表格数据 for (int i = 0; i < data.size(); i++) { //行 rowNum ++; HSSFRow row = sheet.createRow(rowNum); List<CellInfo> cols = data.get(i); //创建列 int colNum = 0; for(int j=0;j<cols.size();j++){ HSSFCell cell = row.createCell(colNum); cell.setCellStyle(style); cell.setCellValue(cols.get(j).getContent()); int firstRow = rowNum; int lastRow =rowNum; int firstCol = colNum; int lastCol = colNum; boolean merge = false; if(cols.get(j).getRowSpan()>1){ lastRow += cols.get(j).getRowSpan()-1; merge = true; } //如果跨行则先创建被合并的单元格（主要是不创建的话，合并后的样式引用有问题） if(cols.get(j).getColSpan()>1){ for(int k=0;k<cols.get(j).getColSpan()-1;k++){ colNum++; HSSFCell tmpCell = row.createCell(colNum); tmpCell.setCellStyle(style); tmpCell.setCellValue(cols.get(j).getContent()); } lastCol = colNum; merge = true; } colNum++; if(merge){ sheet.addMergedRegion(new CellRangeAddress(firstRow,lastRow,firstCol,lastCol)); } } } rowNum++; //页底样式 HSSFCellStyle footerStyle = workbook.createCellStyle();// 创建样式对象 footerStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直居中 footerStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT); footerStyle.setFont(font); int n = headerLength/4; int m = headerLength%4; int start = 0; int end = n; HSSFRow footer = sheet.createRow(rowNum); HSSFCell unitFooter = footer.createCell(0); unitFooter.setCellStyle(footerStyle); unitFooter.setCellValue("填报单位："); if(m>0){ end += 1; } sheet.addMergedRegion(new CellRangeAddress(rowNum,rowNum+1,start,end-1)); start = end; HSSFCell timeFooter = footer.createCell(start); timeFooter.setCellStyle(footerStyle); timeFooter.setCellValue("填报时间："); if(m>1){ end += n+1; }else{ end += n; } sheet.addMergedRegion(new CellRangeAddress(rowNum,rowNum+1,start,end-1)); start = end; HSSFCell personFooter = footer.createCell(start); personFooter.setCellStyle(footerStyle); personFooter.setCellValue("填报人："); if(m>2){ end += n+1; }else{ end += n; } sheet.addMergedRegion(new CellRangeAddress(rowNum,rowNum+1,start,end-1)); start = end; HSSFCell telFooter = footer.createCell(start); telFooter.setCellStyle(footerStyle); telFooter.setCellValue("联系电话："); sheet.addMergedRegion(new CellRangeAddress(rowNum,rowNum+1,start,headerLength-1)); } return workbook; } /** * 解决 导出excel 文件名 为中文时乱码的问题 * @param fileName 文件名 * @return */ public static String toUtf8String(String fileName) { try { return new String(fileName.getBytes("GBK"), "ISO8859-1"); } catch (UnsupportedEncodingException e) { e.printStackTrace(); } return fileName; }
/** * * 表格内容信息 * <p> * 表格类每个单元格信息 * </p> * @author qinwb * @since jdk1.7 * 2017年3月21日 * */ public class CellInfo { /** * 内容:默认为空 */ private String content = ""; /** * 跨列:默认跨1列 */ private int colSpan = 1; /** * 跨行:默认跨1行 */ private int rowSpan = 1; public String getContent() { return content; } public void setContent(String content) { this.content = content; } public int getColSpan() { return colSpan; } public void setColSpan(int colSpan) { this.colSpan = colSpan; } public int getRowSpan() { return rowSpan; } public void setRowSpan(int rowSpan) { this.rowSpan = rowSpan; } public CellInfo() { } public CellInfo(String content, int rowSpan, int colSpan) { super(); this.content = content; this.rowSpan = rowSpan; this.colSpan = colSpan; } public CellInfo(String content) { super(); this.content = content; } }
版权声明：本文为liyang19910805原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。