JavaWeb导出excel

来源：互联网发布：黑马程序员薪资编辑：程序博客网时间：2024/05/22 15:55

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();    }

阅读全文

2 0