JavaWeb导出excel

PIO导出复杂的excel

   在一次项目开发中偶然遇到了需要导出设计复杂的excel，如果用普通的方式合并单元格，然后填充数据想必会累死，当作者面对这样的难题时，突然看见隔壁格子泡茶喝的前段妹妹，一不留神计上心头。
   总体的思路是在POI的基础上用流读取上传好的excel文件，然后将数字填充在所需要的位置，遇到复杂的运算时，还可以用excel的运算公式解决，无需自己在程序中运算。

    /**
     * @author songhao
     * @Title: exportBankRegulatoryCommissionDetails
     * @Description: TODO(导出报表)
     * @param @param
     *            request
     * @param @param
     *            response
     * @param @throws
     *            IOException 设定文件
     * @return void 返回类型
     * @throws @date
     *             2017年8月7日 下午5:50:34
     */
    @ResponseBody
    @RequestMapping("exportBankRegulatoryCommissionDetails")
    public void exportBankRegulatoryCommissionDetails(HttpServletRequest request, HttpServletResponse response)
            throws IOException {
        String yearmounth = request.getParameter("year");
        FileInputStream stream1 = new FileInputStream(UtilPath.getRootPath() + "WEB-INF/classes/银监会报表.xlsx");
        String fileName = "银监会报表";
        response.reset();
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        response.setHeader("Content-Disposition",
                "attachment;filename=" + new String((fileName + ".xlsx").getBytes(), "iso-8859-1"));
        XSSFWorkbook workbook1 = new XSSFWorkbook(stream1);
        String[] arr = yearmounth.split(";");
        for (int i = 0; i < arr.length; i++) {
            String year = arr[i].substring(0, 4);
            String month = arr[i].substring(7, 9);
            System.out.println(year + month);
            if (i != 0) {
                workbook1.cloneSheet(0);
                workbook1.setSheetName(i, year + "年" + month + "月");
            } else {
                workbook1.setSheetName(0, year + "年" + month + "月");
            }
        }

        for (int i = 0; i < arr.length; i++) {
            String year = arr[i].substring(0, 4);
            String month = arr[i].substring(7, 9);
            System.out.println(year + month);
            XSSFSheet sheet1 = workbook1.getSheetAt(i);
            sheet1.setForceFormulaRecalculation(true);
            XSSFCellStyle style = workbook1.createCellStyle();
            style.setLocked(false);
            style.setAlignment(XSSFCellStyle.ALIGN_CENTER);// 水平布局：居中
            style.setVerticalAlignment(XSSFCellStyle.VERTICAL_TOP);// 指定单元格垂直居中对齐
            style.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 下边框
            style.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左边框
            style.setBorderTop(HSSFCellStyle.BORDER_THIN);// 上边框
            style.setBorderRight(HSSFCellStyle.BORDER_THIN);// 右边框
            Map<String, Object> map = BankRegulatoryCommissionService.findbankRegulatoryCommission(year, month);
            for (Entry<String, Object> entry : map.entrySet()) {
                if (!entry.getKey().equals("yearmonth")) {
                    String row = entry.getKey().split("_")[1];
                    String cel = entry.getKey().split("_")[2];
                    int realrow = Integer.parseInt(entry.getKey().split("_")[1]) + 1;
                    // System.out.println("Key = " +
                    // entry.getKey().split("_")[1]+"行"+entry.getKey().split("_")[2]+"列"
                    // + ", Value = " + entry.getValue());
                    sheet1.getRow(Integer.parseInt(row)).getCell(Integer.parseInt(cel)).setCellStyle(style);
                    sheet1.getRow(Integer.parseInt(row)).getCell(Integer.parseInt(cel))
                            .setCellValue(entry.getValue() == null ? "" : entry.getValue() + "");
                    sheet1.getRow(Integer.parseInt(row)).getCell(7).setCellStyle(style);
                    sheet1.getRow(Integer.parseInt(row)).getCell(7).setCellType(HSSFCell.CELL_TYPE_FORMULA);
                    sheet1.getRow(Integer.parseInt(row)).getCell(7)
                            .setCellFormula("if(or(G" + realrow + "=" + "\"\"" + ",I" + realrow + "=" + "\"\"" + "),"
                                    + "\"\"" + ",SUM(-G" + realrow + "+I" + realrow + "))   ");
                }
            }
        }
        OutputStream out = response.getOutputStream();
        workbook1.write(out);
        stream1.close();
        workbook1.close();
    }