Excel导出之多工作表导出

导出是我们工作中长遇到的一个问题，我分享记录一下我最近做的excel 多个工作表导出，

  /**
     * 课题五 导出详情
     * @param response
     * @return
     */
    @RequestMapping(value = "exportFiveDetail", method = RequestMethod.GET)
    public Json exportFiveDetail(HttpServletResponse response) {

        Map<String,Object> map = uploadExclBiz.getFiveDetail();

        ExcelUtil excelUtil =new ExcelUtil();
        ByteArrayOutputStream out = new ByteArrayOutputStream();

        List<ExcelParmDto> parm = getListExcelParm(map,out);
        excelUtil.exportExcelMany(parm);
        String title ="课题五评分详情";
        try {
            byte[] content = out.toByteArray();

            InputStream is = new ByteArrayInputStream(content);
            response.reset();
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
            response.setHeader("Content-Disposition", "attachment;filename=" + new String((title + ".xlsx").getBytes(), "ISO-8859-1"));
            ServletOutputStream outputStream = response.getOutputStream();
            BufferedInputStream bis = new BufferedInputStream(is);
            BufferedOutputStream bos = new BufferedOutputStream(outputStream);
            byte[] buff = new byte[2048];
            int bytesRead;
            while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
                bos.write(buff, 0, bytesRead);
            }
            bis.close();
            bos.close();
            outputStream.flush();
            outputStream.close();

        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        } catch (IOException ee) {
            ee.printStackTrace();
        }


        return Json.SUCCESS().setMessage("导出成功");

    }

 

Map是获取四个对象列表，对应我要导出的四个工作表数据

下面是excel 工具类

 /**
     * 导出excel

     */
    public void exportExcelMany(List<ExcelParmDto> list) {
        if (list.get(0).getDatePattern() == null) {
            String datePattern = DEFAULT_DATE_PATTERN;
            //工作簿   指定最多访问多少行。  创建excel
            SXSSFWorkbook workbook = new SXSSFWorkbook(10000);
            //设置临时文件是否被压缩
            workbook.setCompressTempFiles(true);
            //创建一个新的单元格样式并添加到工作薄的样式表中
            CellStyle titleStyle = workbook.createCellStyle();
            //设置单元格的水平对齐方式 。
            titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            //单元格内容的中对齐
            titleStyle.setAlignment(HSSFCellStyle.VERTICAL_CENTER);

            //创建一个新的字体并添加 到工作薄的字体样式表中；
            Font font = workbook.createFont();
            font.setFontHeightInPoints((short) 20);
            font.setBoldweight((short) 70);
            titleStyle.setFont(font);

            CellStyle cellStyle = workbook.createCellStyle();
            cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
            cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
            cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
            cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
            cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
            cellStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);
            Font headerFont = workbook.createFont();
            headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
            cellStyle.setFont(headerFont);

            for(ExcelParmDto epd : list){

                //创建工作表
                SXSSFSheet sheet = (SXSSFSheet) workbook.createSheet();

                // 设置工作表名称
                workbook.setSheetName(epd.getSheetNum(),epd.getSheetTitle());
                logger.info("工作表个数 workbook.getNumberOfSheets() :" + workbook.getNumberOfSheets());
                logger.info("工作表dang当前位置 :" + workbook.getSheetIndex(sheet));



                int minBytes = epd.getColWidth() < DEFAULT_COLOUMN_WIDTH ? DEFAULT_COLOUMN_WIDTH : epd.getColWidth();
                int[] arrColWidth = new int[epd.getHeadMap().size()];
                String[] properties = new String[epd.getHeadMap().size()];
                String[] headers = new String[epd.getHeadMap().size()];
                int ii = 0;
                for (Iterator<String> iter = epd.getHeadMap().keySet().iterator(); iter.hasNext(); ) {
                    String filedName = iter.next();
                    properties[ii] = filedName;
                    headers[ii] = epd.getHeadMap().get(filedName);
                    int bytes = filedName.getBytes().length;
                    arrColWidth[ii] = bytes < minBytes ? minBytes : bytes;
                    sheet.setColumnWidth(ii, arrColWidth[ii] * 200);
                    // sheet.setColumnWidth(ii,120);
                    ii++;
                }
                int rowIndex = 0;
                for (Object obj : epd.getJsonArray()) {
                    if (rowIndex == 65535 || rowIndex == 0) {
                        if (rowIndex != 0)
                            sheet = (SXSSFSheet) workbook.createSheet();
                        SXSSFRow titleRow = (SXSSFRow) sheet.createRow(0);
                        titleRow.createCell(0).setCellValue(epd.getTitle());
                        titleRow.getCell(0).setCellStyle(titleStyle);
                        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, epd.getHeadMap().size() - 1));
                        // 第三步 创建行
                        SXSSFRow headerRow = (SXSSFRow) sheet.createRow(1);
                        for (int i = 0; i < headers.length; i++) {
                            //第四步 创建列
                            headerRow.createCell(i).setCellValue(headers[i]);
                            if (i > 3) {
                                sheet.setColumnWidth(i, arrColWidth[i] * 500);
                            }
                            // headerRow.getCell(i).setCellStyle(cellStyle);}
                            rowIndex = 2;
                        }
                        if (headers.length == 8) {
                            for (int i = 0; i < headers.length; i++) {
                                headerRow.createCell(i).setCellValue(headers[i]);
                                sheet.setColumnWidth(i, arrColWidth[i] * 512);
                                rowIndex = 2;
                            }
                        }
                    }
                    JSONObject jo = (JSONObject) JSONObject.toJSON(obj);
                    SXSSFRow dataRow = (SXSSFRow) sheet.createRow(rowIndex);
                    // 第五步 设置值
                    for (int i = 0; i < properties.length; i++) {
                        SXSSFCell newCell = (SXSSFCell) dataRow.createCell(i);
                        Object o = jo.get(properties[i]);
                        String cellValue = "";
                        if (o == null) {
                            cellValue = "";
                        } else if (o instanceof Date) {
                            cellValue = new SimpleDateFormat(datePattern).format(o);
                        } else {
                            cellValue = o.toString();
                        }
                        newCell.setCellValue(cellValue);
                        // newCell.setCellStyle(cellStyle);
                    }
                    rowIndex++;
                }


            }
            try {
                 workbook.write(list.get(0).getOut());
                // workbook.write(list.get(1).getOut());

            } catch (IOException e) {
                e.printStackTrace();
            }


        }


    }

 

导出效果：