poi导出excel实例

收藏夹

前台

function exportExcel() {
        var month = mini.get("month").getValue();

        if (month === "") {
            mini.get("month").setIsValid(false);
            ciic.miniAlert("期间不能为空！", "提示", "", "fail");
            return;
        }
        var filter = { month: month};

        var fields = new Array();
        var columns = grid.columns;
        //循环获取field，以便后台直接取值
        for (var i = 0; i < columns.length; i++) {
            var column = columns[i];
            if (column.field == 'ID' || column.name == 'operation' || column.type == "indexcolumn" || column.type == "checkcolumn" || column.field == null || column.visible == false || column.enabled == false) continue;
            var field = column.displayField != null && column.displayField != "" ? column.displayField : column.field;
            fields.push(field);
        }
        exportFile(contextRoot + "/mvc/eport/export", {
            fileName: "测试测试测试.xlsx",
            fieldStr: fields.toString(),
            filterStr: JSON.stringify(filter).toString(),
        });

    }


function exportFile(url, fields) {
    //创建Form
    var submitfrm = document.createElement("form");
    submitfrm.action = url;
    submitfrm.style = "display:none";
    submitfrm.method = "post";
    submitfrm.target = "_self";
    document.body.appendChild(submitfrm);

    if (fields) {

        for (var p in fields) {
            var input = mini.append(submitfrm, "<input type='hidden' name='" + p + "'>");
            var v = fields[p];
            if (typeof v != "string") v = mini.encode(v);
            input.value = v;
        }
    }
    ciic.mask({
        el: document.body,
        cls: 'mini-mask-loading',
        html: '下载中...'
    });
    submitfrm.submit();
    setTimeout(function () {
        submitfrm.parentNode.removeChild(submitfrm);
        ciic.unmask(document.body);
    }, 1000);
}

controller

    @RequestMapping({"/export"})
    public void export(String fileName, String fieldStr, String filterStr, HttpServletResponse res) throws BizException {
        try {
            URL xmlPath = this.getClass().getClassLoader().getResource("");
            String rootPath = xmlPath.getPath();
            String filePath = rootPath + "/templates/exportTemplate.xlsx";
            Workbook workbook = WorkbookFactory.create(new FileInputStream(new File(filePath)));
//            Workbook workbook = null;

            ParmMap filterMap = JSONUtils.fromJson(filterStr, ParmMap.class);
            workbook = reportService.export(fieldStr, filterMap, workbook);

            ExcelExportUtil.writeFile(workbook, fileName, res);
        } catch (Exception e) {
            throw new BizException(e, "文件下载失败！", new String[0]);
        }
    }

service

    public Workbook export(String fieldStr, ParmMap filterMap, Workbook workbook) {
        if (workbook == null) {
            workbook = new XSSFWorkbook();
        }
        XSSFSheet sheet = (XSSFSheet) workbook.getSheetAt(0);
        short defaultHeight = ((short) (300 * 2));

        //查询需要导出的数据
        List<Map> mapList = queryList(filterMap);

        int dataStartRowNum = sheet.getLastRowNum() + 1;
        String[] fieldStr_arr = fieldStr.split(",");

        //设置字体
        Font font = workbook.createFont();
        font.setFontName("宋体");
        font.setFontHeightInPoints((short) 10);//设置字体大小

        //设置样式
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
        cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        cellStyle.setBorderBottom(CellStyle.BORDER_THIN);
        cellStyle.setBorderLeft(CellStyle.BORDER_THIN);
        cellStyle.setBorderRight(CellStyle.BORDER_THIN);
        cellStyle.setBorderTop(CellStyle.BORDER_THIN);
        cellStyle.setFont(font);

        //循环遍历数据，对单元格进行赋值
        for (int i = 0; i < mapList.size(); i++) {
            Map map = mapList.get(i);

            sheet.createRow(dataStartRowNum + i);
            Row row = sheet.getRow(dataStartRowNum + i);
            row.setHeight(defaultHeight);

            Cell cell_index = row.createCell(0);
            cell_index.setCellValue(i + 1);
            cell_index.setCellStyle(cellStyle);
            for (int j = 0; j < fieldStr_arr.length; j++) {
                Cell cell = row.createCell(j + 1);
                String field = fieldStr_arr[j];
                String value = map.get(field) == null ? "" : String.valueOf(map.get(field));

                cell.setCellValue(value);
                cell.setCellStyle(cellStyle);
            }

//            Cell cell_end = row.createCell(fieldStr_arr.length + 1);//备注
//            cell_end.setCellStyle(cellStyle);
        }


		//合并单元格
        CellRangeAddress region2 = new CellRangeAddress(dataStartRowNum + list.size() - 3, dataStartRowNum + list.size() - 3, (short) 1, (short) 3);
        CellRangeAddress region3 = new CellRangeAddress(dataStartRowNum + list.size() - 2, dataStartRowNum + list.size() - 2, (short) 1, (short) 3);
        CellRangeAddress region4 = new CellRangeAddress(dataStartRowNum + list.size() - 1, dataStartRowNum + list.size() - 1, (short) 1, (short) 3);
        sheet.addMergedRegion(region2);
        sheet.addMergedRegion(region3);
        sheet.addMergedRegion(region4);

        return workbook;
    }