Excel导出POI-白红宇

Excel导出POI

阅读量：4613 次

发布时间：2019-06-09

本文共 17214 字，大约阅读时间需要 57 分钟。

CwReportExcleUtils.java 类 　1 package com.bigdata.campus.report.utils;  2   3 import java.util.List;  4 import java.util.Map;  5   6 import org.apache.poi.hssf.usermodel.HSSFWorkbook;  7 import org.apache.poi.ss.usermodel.Cell;  8 import org.apache.poi.ss.usermodel.CellStyle;  9 import org.apache.poi.ss.usermodel.DataFormat; 10 import org.apache.poi.ss.usermodel.Font; 11 import org.apache.poi.ss.usermodel.IndexedColors; 12 import org.apache.poi.ss.usermodel.Row; 13 import org.apache.poi.ss.usermodel.Sheet; 14 import org.apache.poi.ss.usermodel.Workbook; 15 import org.apache.poi.ss.util.CellRangeAddress; 16  17 public class CwReportExcleUtils { 18         /** 19          * Excel的导出（自定义表头） 20          * @describe list 为数据 keys 为需要显示在excel中的参数对应的值，因为是用map存的，放的都是对应的key
 21          * headRange 合并表头的参数 如new String[] { "0,0,0,2", "0,0,0,2", "3,3,4,4","3,3,5,5" }; 起始行, 终止行, 起始列, 终止列
 22          * headRowParam 合并表头的参数（数量与headRowNum对应） 23          * intRow存放那些行不需要添加背景填充和数据行格式一样如果不需要则置为空list 24          * @param list 25          * @param keys 26          * @param columnNames 27          * @param headRowNum 28          * @param headRange 29          * @param rowParam 30          * @return 31          */ 32          public static Workbook createCustomWorkBook( 33                                                      List
      
       > list, 34                                                      List
       
         keys, 35                                                      List
        
          headRange, 36                                                      List
         
          
           > ListStr, 37 List
           
             intRow 38 ) { 39 // 创建excel工作簿 40 Workbook wb = new HSSFWorkbook(); 41 // 创建第一个sheet（页），并命名 42 Sheet sheet = wb.createSheet(list.get(0).get("sheetName").toString()); 43 // 手动设置列宽。第一个参数表示要为第几列设；，第二个参数表示列的宽度，n为列高的像素数。 44 for(int i=0;i
            
             0) {107 cell.setCellValue(d);108 cell.setCellStyle(cs2_Double_red);109 }else {110 cell.setCellValue(d);111 cell.setCellStyle(cs2_Double);112 }113 }else {114 if(list.get(i).get(keys.get(j)) instanceof Double){115 Double d=Double.parseDouble(list.get(i).get(keys.get(j)) == null?"0": list.get(i).get(keys.get(j)).toString());116 cell.setCellValue(d);117 cell.setCellStyle(cs2_Double);118 }else if(list.get(i).get(keys.get(j)) instanceof Integer){119 Integer it=Integer.parseInt(list.get(i).get(keys.get(j)) == null?"0": list.get(i).get(keys.get(j)).toString());120 cell.setCellValue(it);121 cell.setCellStyle(cs4_Int);122 }else{123 cell.setCellValue(list.get(i).get(keys.get(j)) == null?" ": list.get(i).get(keys.get(j)).toString());124 cell.setCellType(Cell.CELL_TYPE_STRING);125 cell.setCellStyle(cs3_String);126 }127 }128 129 }130 }131 return wb;132 }133 /**134 * 居左样式135 * @param wb136 * @return137 */138 private static CellStyle createCellStyle_left(Workbook wb) {139 // 创建单元格格式140 CellStyle csleft = wb.createCellStyle();141 // 创建字体142 Font f = wb.createFont();143 // 创建字体样式（用于列名）144 f.setFontHeightInPoints((short) 12);145 f.setColor(IndexedColors.BLACK.getIndex());146 f.setBoldweight(Font.BOLDWEIGHT_BOLD);147 // 设置单元格的样式（用于列名）148 csleft.setFont(f);149 csleft.setBorderLeft(CellStyle.BORDER_THIN);150 csleft.setBorderRight(CellStyle.BORDER_THIN);151 csleft.setBorderTop(CellStyle.BORDER_THIN);152 csleft.setBorderBottom(CellStyle.BORDER_THIN);153 csleft.setAlignment(CellStyle.ALIGN_LEFT);//水平居左154 csleft.setVerticalAlignment(CellStyle.VERTICAL_CENTER);//垂直居中155 return csleft;156 }157 /**158 * 标题样式159 * @param wb160 * @return161 */162 private static CellStyle createCellStyle_title(Workbook wb) {163 // 创建单元格格式164 CellStyle csTitle = wb.createCellStyle();165 // 创建字体166 Font f = wb.createFont();167 // 创建字体样式（用于列名）168 f.setFontHeightInPoints((short) 18);169 f.setColor(IndexedColors.BLACK.getIndex());170 f.setBoldweight(Font.BOLDWEIGHT_BOLD);171 // 设置单元格的样式（用于列名）172 csTitle.setFont(f);173 csTitle.setBorderLeft(CellStyle.BORDER_THIN);174 csTitle.setBorderRight(CellStyle.BORDER_THIN);175 csTitle.setBorderTop(CellStyle.BORDER_THIN);176 csTitle.setBorderBottom(CellStyle.BORDER_THIN);177 csTitle.setAlignment(CellStyle.ALIGN_CENTER);//水平居中178 csTitle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);//垂直居中179 return csTitle;180 }181 /**182 * 整型格式样式183 * @param wb184 * @return185 */186 private static CellStyle createCellStyle_Int(Workbook wb) {187 // 创建单元格格式188 CellStyle cs4 = wb.createCellStyle();189 // 创建字体190 Font f4 = wb.createFont();191 // 创建第二种字体样式（用于值）192 f4.setFontHeightInPoints((short) 12);193 f4.setColor(IndexedColors.BLACK.getIndex());194 // 设置第二种单元格的样式（用于值）195 cs4.setFont(f4);196 cs4.setBorderLeft(CellStyle.BORDER_THIN);197 cs4.setBorderRight(CellStyle.BORDER_THIN);198 cs4.setBorderTop(CellStyle.BORDER_THIN);199 cs4.setBorderBottom(CellStyle.BORDER_THIN);200 cs4.setAlignment(CellStyle.ALIGN_CENTER);201 cs4.setVerticalAlignment(CellStyle.VERTICAL_CENTER);//垂直居中202 DataFormat df = wb.createDataFormat(); //此处设置数据格式 203 cs4.setDataFormat(df.getFormat("#0")); //数据格式只显示整数，如果是小数点后保留两位，可以写contentStyle.setDataFormat(df.getFormat("#,#0.00")); 204 return cs4;205 }206 /**207 * 普通文本格式208 * @param wb209 * @return210 */211 private static CellStyle createCellStyle_String(Workbook wb) {212 // 创建单元格格式213 CellStyle cs3 = wb.createCellStyle();214 // 创建字体215 Font f2 = wb.createFont();216 // 创建第二种字体样式（用于值）217 f2.setFontHeightInPoints((short) 12);218 f2.setColor(IndexedColors.BLACK.getIndex());219 // 设置第二种单元格的样式（用于值）220 cs3.setFont(f2);221 cs3.setBorderLeft(CellStyle.BORDER_THIN);222 cs3.setBorderRight(CellStyle.BORDER_THIN);223 cs3.setBorderTop(CellStyle.BORDER_THIN);224 cs3.setBorderBottom(CellStyle.BORDER_THIN);225 cs3.setAlignment(CellStyle.ALIGN_CENTER);226 cs3.setVerticalAlignment(CellStyle.VERTICAL_CENTER);//垂直居中227 DataFormat df = wb.createDataFormat(); //此处设置数据格式 228 cs3.setDataFormat(df.getFormat("@")); //数据格式只显示整数，如果是小数点后保留两位，可以写contentStyle.setDataFormat(df.getFormat("#,#0.00")); 229 return cs3;230 }231 /**232 * 数字格式（保留两位小数）233 * @param wb234 * @return235 */236 private static CellStyle createCellStyle_Double(Workbook wb) {237 // 创建单元格格式238 CellStyle cs2 = wb.createCellStyle();239 // 创建字体240 Font f2 = wb.createFont();241 // 创建第二种字体样式（用于值）242 f2.setFontHeightInPoints((short) 12);243 f2.setColor(IndexedColors.BLACK.getIndex());244 // 设置第二种单元格的样式（用于值）245 cs2.setFont(f2);246 cs2.setBorderLeft(CellStyle.BORDER_THIN);247 cs2.setBorderRight(CellStyle.BORDER_THIN);248 cs2.setBorderTop(CellStyle.BORDER_THIN);249 cs2.setBorderBottom(CellStyle.BORDER_THIN);250 cs2.setAlignment(CellStyle.ALIGN_CENTER);251 cs2.setVerticalAlignment(CellStyle.VERTICAL_CENTER);//垂直居中252 DataFormat df = wb.createDataFormat(); //此处设置数据格式 253 cs2.setDataFormat(df.getFormat("#,#0.00")); //数据格式只显示整数，如果是小数点后保留两位，可以写contentStyle.setDataFormat(df.getFormat("#,#0.00")); 254 return cs2;255 }256 /**257 * 数字格式（保留两位小数）红色背景258 * @param wb259 * @return260 */261 private static CellStyle createCellStyle_Double_red(Workbook wb) {262 // 创建单元格格式263 CellStyle cs2 = wb.createCellStyle();264 // 创建字体265 Font f2 = wb.createFont();266 // 创建第二种字体样式（用于值）267 f2.setFontHeightInPoints((short) 12);268 f2.setColor(IndexedColors.BLACK.getIndex());269 // 设置第二种单元格的样式（用于值）270 cs2.setFont(f2);271 cs2.setBorderLeft(CellStyle.BORDER_THIN);272 cs2.setBorderRight(CellStyle.BORDER_THIN);273 cs2.setBorderTop(CellStyle.BORDER_THIN);274 cs2.setBorderBottom(CellStyle.BORDER_THIN);275 cs2.setAlignment(CellStyle.ALIGN_CENTER);276 cs2.setVerticalAlignment(CellStyle.VERTICAL_CENTER);//垂直居中277 cs2.setFillForegroundColor(IndexedColors.RED.getIndex());278 cs2.setFillPattern(CellStyle.SOLID_FOREGROUND);279 DataFormat df = wb.createDataFormat(); //此处设置数据格式 280 cs2.setDataFormat(df.getFormat("#,#0.00")); //数据格式只显示整数，如果是小数点后保留两位，可以写contentStyle.setDataFormat(df.getFormat("#,#0.00")); 281 return cs2;282 }283 /**284 * 表头样式285 * @param wb286 * @return287 */288 private static CellStyle createCellStyle_Head(Workbook wb) {289 // 创建单元格格式290 CellStyle cs = wb.createCellStyle();291 // 创建字体292 Font f = wb.createFont();293 // 创建字体样式（用于列名）294 f.setFontHeightInPoints((short) 12);295 f.setColor(IndexedColors.BLACK.getIndex());296 f.setBoldweight(Font.BOLDWEIGHT_BOLD);297 // 设置单元格的样式（用于列名）298 cs.setFont(f);299 cs.setBorderLeft(CellStyle.BORDER_THIN);300 cs.setBorderRight(CellStyle.BORDER_THIN);301 cs.setBorderTop(CellStyle.BORDER_THIN);302 cs.setBorderBottom(CellStyle.BORDER_THIN);303 cs.setAlignment(CellStyle.ALIGN_CENTER);//水平居中304 cs.setVerticalAlignment(CellStyle.VERTICAL_CENTER);//垂直居中305 cs.setFillForegroundColor(IndexedColors.PALE_BLUE.index);306 cs.setFillPattern(CellStyle.SOLID_FOREGROUND);307 return cs;308 }309 }

export方法

/**    * headRowNum 有多少个headRowParam 就必须要有多少组  {"1,2,3,4"} // 1起始行, 2终止行, 3起始列, 4终止列    * @param response    * @param fileName    * @param list    * @param keys    * @param headRange {"1,2,3,4"} // 1起始行, 2终止行, 3起始列, 4终止列    * @param headRowNum    * @param headRowParam    * @throws IOException    */    private void export(HttpServletResponse response, String fileName, List
      
       > list,List
       
         keys,List
        
          headRange,List
         
          
           > headRowParam,List
           
             intRow) throws IOException { ByteArrayOutputStream os = new ByteArrayOutputStream(); try { CwReportExcleUtils.createCustomWorkBook(list,keys,headRange,headRowParam,intRow).write(os); } catch (IOException e) { e.printStackTrace(); } byte[] content = os.toByteArray(); InputStream is = new ByteArrayInputStream(content); // 设置response参数，可以打开下载页面 response.reset(); response.setContentType("application/vnd.ms-excel;charset=utf-8"); response.setHeader("Content-Disposition", "attachment;filename="+ new String((fileName + ".xls").getBytes(), "iso-8859-1")); ServletOutputStream out = response.getOutputStream(); BufferedInputStream bis = null; BufferedOutputStream bos = null; try { bis = new BufferedInputStream(is); bos = new BufferedOutputStream(out); byte[] buff = new byte[2048]; int bytesRead; // Simple read/write loop. while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) { bos.write(buff, 0, bytesRead); } } catch (final IOException e) { throw e; } finally { if (bis != null) bis.close(); if (bos != null) bos.close(); } }

exportClassStudentsFee 类

/**     * 导出班级缴费学生缴费明细统计数据     * @param response     * @param redirectAttributes     * @param cwreport     */    public Workbook exportClassStudentsFee(HttpServletResponse response, RedirectAttributes redirectAttributes,CWReport cwreport, Boolean isBatch) {        Workbook workbook = null;        try {            String fileName="班级学生缴费明细报表"+DateUtils.getDate("yyyy年MM月dd日 HH时mm分ss秒");               //填充projects数据制作模板               List
     
       classStuDetailslist = classStuDetailsExport(cwreport);//查询整体数据               CWReport classStuCount =sumClassStuDetails(cwreport);//查询数据求和总计               List
      
       
        > list=createExcelclassStuDetails(classStuDetailslist,classStuCount);//关联修改导出内容               List< List
        
         > listStr=Lists.newArrayList();//表头的List集合               List
         
           intRow=Lists.newArrayList();//存放那些行不需要添加背景填充和数据行格式一样如果不需要则置为空list               intRow.add(1);               //设置表头数据               List
          
            li0=Lists.newArrayList(); List
           
             li1=Lists.newArrayList(); List
            
              li2=Lists.newArrayList(); List
             
               li3=Lists.newArrayList(); //设置需要取值的键值 List
              
                liKey=Lists.newArrayList(); //设置需要合并的单元格 List
               
                 rli=Lists.newArrayList(); li0.add("班级学生缴费明细报表"); li1.add(cwreport.getAllsDetail()); li2.add("序号"); li2.add("缴费日期"); li2.add("学号"); li2.add("姓名"); li2.add("姓别"); li2.add("学籍状态信息"); li2.add("联系方式"); li2.add("身份证号"); li2.add("应缴费用/元"); li2.add("其中/元"); li2.add(""); li2.add(""); li3.add("");li3.add("");li3.add("");li3.add("");li3.add("");li3.add("");li3.add("");li3.add("");li3.add(""); li3.add("贷款金额"); li3.add("缓交金额"); li3.add("户口减免"); liKey.add("orderNumber"); liKey.add("createTime"); liKey.add("stuNumber"); liKey.add("name"); liKey.add("sex"); liKey.add("status"); liKey.add("phone"); liKey.add("idCard"); liKey.add("needFee"); liKey.add("dkFee"); liKey.add("delayFee"); liKey.add("jmFee"); rli.add("2,3,0,0"); rli.add("2,3,1,1"); rli.add("2,3,2,2"); rli.add("2,3,3,3"); rli.add("2,3,4,4"); rli.add("2,3,5,5"); rli.add("2,3,6,6"); rli.add("2,3,7,7"); rli.add("2,3,8,8"); rli.add("2,2,9,11"); int row=0;//记录多少个明细项，0开始 Integer row1 = 0; if(!classStuDetailslist.isEmpty()){ li2.add("实缴费用明细/元");//如果查出的数据不为空则显示明细项列，为空则不显示缴费明细项列 if(!classStuDetailslist.get(0).getFeeDetail().isEmpty()){ //循环明细项添加到list List
                
                  cw=classStuDetailslist.get(0).getFeeDetail(); for (CWReportFeeDetails c :cw) { row++; if(row != 1){ li2.add(""); } li3.add(c.getFeeTypeName()); liKey.add(c.getFeeTypeName()); } } rli.add("2,2,12,"+(12+row-1)+""); li2.add("缴费方式/元"); if (!classStuDetailslist.get(0).getFeesWayList().isEmpty()) { List
                 
                   feesWayList = classStuDetailslist.get(0).getFeesWayList(); for (ChargeWaySumEntity chargeWaySumEntity : feesWayList) { row1 ++; if (row1 != 1) { li2.add(""); } li3.add(chargeWaySumEntity.getName()); liKey.add(chargeWaySumEntity.getKeyValue()); } } rli.add("2,2," + (12 + row) + "," + (12 + row + row1 - 1)); }// li2.add("");li2.add(""); li2.add("实缴合计/元"); li2.add("收款人"); li2.add("备注");// li3.add("现金");// li3.add("POS机");// li3.add("预缴费用"); li3.add(""); li3.add(""); li3.add(""); // liKey.add("cash");// liKey.add("pos");// liKey.add("yjFee"); liKey.add("isFee"); liKey.add("receivUserName"); liKey.add("remark"); rli.add("0,0,0,"+(15+row1+row-1)+""); rli.add("1,1,0,"+(15+row1+row-1)+"");// rli.add("2,2,"+(13+row-1)+","+(15+row-1)+""); rli.add("2,3,"+(13+row1+row-1)+","+(13+row1+row-1)+""); rli.add("2,3,"+(14+row1+row-1)+","+(14+row1+row-1)+""); rli.add("2,3,"+(15+row1+row-1)+","+(15+row1+row-1)+""); listStr.add(li0); listStr.add(li1); listStr.add(li2); listStr.add(li3); if (isBatch) { workbook = exportBatch(fileName, list, liKey, rli, listStr, intRow); } else { export(response, fileName, list, liKey, rli,listStr,intRow); } } catch (Exception e) { e.printStackTrace(); System.out.println("哈哈 失败了"); } return workbook; }

createExcelclassStuDetails 方法

private List
      
       > createExcelclassStuDetails(List
       
         classStuDetailslist,CWReport classStuCount) {        List
        
         
          > listmap = new ArrayList
          
           
            >(); Map
            
              map = new HashMap
             
              (); map.put("sheetName", "sheet1"); listmap.add(map); for (int j = 0; j < classStuDetailslist.size(); j++) { CWReport cwrep=classStuDetailslist.get(j); if(cwrep != null){ Map
              
                mapValue = new HashMap
               
                (); mapValue.put("orderNumber",j+1); mapValue.put("createTime",cwrep.getCreateTime().substring(0, 19)); mapValue.put("stuNumber",cwrep.getStuNumber()); mapValue.put("name",cwrep.getName()); mapValue.put("sex",cwrep.getSex()); mapValue.put("status",cwrep.getStatus()); mapValue.put("phone",cwrep.getPhone()); mapValue.put("idCard",cwrep.getIdCard()); mapValue.put("needFee",cwrep.getNeedFee()); mapValue.put("dkFee",cwrep.getDkFee()); mapValue.put("delayFee",cwrep.getDelayFee()); mapValue.put("jmFee",cwrep.getJmFee()); for (CWReportFeeDetails fee : cwrep.getFeeDetail()) { mapValue.put(fee.getFeeTypeName(),fee.getFeeMoney()); } List
                
                  feesWayList = cwrep.getFeesWayList(); for (ChargeWaySumEntity chargeWaySumEntity : feesWayList) { mapValue.put(chargeWaySumEntity.getKeyValue(), chargeWaySumEntity.getMoney()); if (chargeWaySumEntity.getKeyValue() == null || "".equals(chargeWaySumEntity.getKeyValue())) { mapValue.put("isFee", chargeWaySumEntity.getMoney()); } }// mapValue.put("cash",cwrep.getCash());// mapValue.put("pos",cwrep.getPOS());// mapValue.put("yjFee",cwrep.getYjFee()); mapValue.put("isFee",cwrep.getIsFee()); mapValue.put("receivUserName",cwrep.getReceivUserName()); if ("退学".equals(cwrep.getStatus()) || "休学".equals(cwrep.getStatus())) { mapValue.put("remark", cwrep.getStatus()); } else { mapValue.put("remark", "无"); } listmap.add(mapValue); } } if(classStuCount !=null && null != classStuCount.getStuCode() && !"".equals(classStuCount.getStuCode())){ Map
                 
                   mapValue = new HashMap
                  
                   (); mapValue.put("orderNumber","合计"); mapValue.put("createTime",""); mapValue.put("stuNumber",""); mapValue.put("name",""); mapValue.put("sex",""); mapValue.put("status",""); mapValue.put("phone",""); mapValue.put("idCard",""); mapValue.put("needFee",classStuCount.getNeedFee()); mapValue.put("dkFee",classStuCount.getDkFee()); mapValue.put("delayFee",classStuCount.getDelayFee()); mapValue.put("jmFee",classStuCount.getJmFee()); for (CWReportFeeDetails fee : classStuCount.getFeeDetail()) { mapValue.put(fee.getFeeTypeName(),fee.getFeeMoney()); } List
                   
                     feesWayList = classStuCount.getFeesWayList(); for (ChargeWaySumEntity chargeWaySumEntity : feesWayList) { mapValue.put(chargeWaySumEntity.getKeyValue(), chargeWaySumEntity.getMoney()); if (chargeWaySumEntity.getKeyValue() == null || "".equals(chargeWaySumEntity.getKeyValue())) { mapValue.put("isFee", chargeWaySumEntity.getMoney()); } }// mapValue.put("cash",classStuCount.getCash());// mapValue.put("pos",classStuCount.getPOS());// mapValue.put("yjFee",classStuCount.getYjFee());// mapValue.put("isFee",classStuCount.getIsFee()); mapValue.put("receivUserName",""); listmap.add(mapValue); } return listmap; }