欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

POI导出excel

程序员文章站 2022-03-06 22:26:11
...
  @Override
    public BaseResult ListExport(ListReqVO reqVO, HttpServletResponse response) throws Exception {

        List<ListRepVO> ListRepVOs = selectList(reqVO);

        // excel标题 22
        LinkedHashMap<String, String>  title =  setExcelColums();
        // sheet名
        String sheetName = "列表";
        
        List<Map<String, Object>> dataListMap = BeanMapUtils.toMapList(ListRepVOs);
        
        DateTimeFormatter dateTimeFormatter = DateTimeFormatter.ofPattern("yyyy-MM-dd");
        //数据处理
        int i = 1;
        for (Iterator it = dataListMap.iterator(); it.hasNext(); ) {
            Map<String, Object> dataMap = (Map<String, Object>) it.next();
            dataMap.put("num", i);
            //日期格式化
            dataMap.put("coveragePeriodValue", DateDisposeUtils.datetimeToString(Long.valueOf(dataMap.get("coveragePeriodValue").toString()), dateTimeFormatter));             
            dataMap.put("validateDate",  DateDisposeUtils.datetimeToString(Long.valueOf(dataMap.get("validateDate").toString()), dateTimeFormatter));
            dataMap.put("dueDate", DateDisposeUtils.datetimeToString(Long.valueOf(dataMap.get("dueDate").toString()), dateTimeFormatter));
            //不同列共用一个key时处理
            dataMap.put("payablePrem", dataMap.get("prem"));
            dataMap.put("payPrem", dataMap.get("prem"));
            dataMap.put("accountNoWithin", dataMap.get("accountNo"));
            dataMap.put("accountNoOuter", dataMap.get("accountNo"));
            i++;
        }
      
        // 创建HSSFWorkbook
        XSSFWorkbook wb = ExcelExportUtils.getXSSFWorkbook(sheetName, title, dataListMap);

        // excel文件名
        String fileName = sheetName;
        ExcelExportUtils.setResponseHeader(response, fileName, wb);

        return new BaseResult();
    }





 /**
     * <b>方法说明:Excel导出</b>
     * @param sheetName sheet名称
     * @param map 		标题
     * @param list 		内容
     * @return
     */
    public static XSSFWorkbook getXSSFWorkbook(String sheetName,LinkedHashMap<String, String> map, List<?> list) {
    
    	// 第一步,创建一个HSSFWorkbook,对应一个Excel文件
        XSSFWorkbook  wb = new XSSFWorkbook();
        
        // 第二步,在workbook中添加一个sheet,对应Excel文件中的sheet
        XSSFSheet sheet = wb.createSheet(sheetName);
        
        // 合并标题单元格
        // 参数说明:1:开始行 2:结束行  3:开始列 4:结束列
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, map.size() - 1));
        // 第三步,在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制
        XSSFRow row = sheet.createRow(0);
        // 第四步,创建单元格,并设置值表头 设置表头居中
        XSSFCellStyle style = wb.createCellStyle();
        // 创建一个居中格式
        style.setAlignment(XSSFCellStyle.ALIGN_CENTER);
        // 声明列对象
        XSSFCell cell = null;
        // 创建居中标题
        cell = row.createCell(0);
        cell.setCellValue(sheetName);
        cell.setCellStyle(style);
        // 创建标题
        row = sheet.createRow(1);
    	//字段集合
    	Object[] fieldsName = map.keySet().toArray();      
    	//标题集合
        Object[] rowsName = map.values().toArray();       
        
        for (int i = 0; i < map.size(); i++) {
            cell = row.createCell(i);
            cell.setCellValue(rowsName[i].toString());
            cell.setCellStyle(style);
            sheet.setColumnWidth(i, rowsName[i].toString().getBytes().length * 200);
        }
        // 创建内容
        if (list == null || list.size() == 0) {
            return wb;
        }

        // 第五步,创建单元格,并设置值
        for (int i = 0; i < list.size(); i++) {
            row = sheet.createRow((int) i + 2);
            for (int j = 0; j < fieldsName.length; j++) {
                cell = row.createCell(j);
                Object obj = list.get(i);
                if (obj == null) continue;
                String jsonString = JSONObject.toJSONString(obj);
                JSONObject jo = JSONObject.parseObject(jsonString);
                if (jo.get(fieldsName[j].toString()) == null) {
                    cell.setCellValue("");
                } else {
                    cell.setCellValue(jo.get(fieldsName[j].toString()).toString());
                }
                cell.setCellStyle(style);
            }
        }
        return wb;
    }



 /**
     * 发送响应流方法
     *
     * @param response
     * @param fileName
     * @throws IOException
     */
    public static void setResponseHeader(HttpServletResponse response, String fileName, XSSFWorkbook wb) {

        try {
          //定义输出类型
         response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");            
            
            fileName = new String(fileName.getBytes("gbk"), "iso8859-1");
            response.setHeader("Content-Disposition", "attachment;filename=" + fileName + ".xlsx");
            response.flushBuffer();
            wb.write(response.getOutputStream());

        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }