web表格数据导出excel表格

这里使用的是后端导出表格的形式。

一整套流程简单的修改一下前台查询参数，和controller中的数据格式就能适应不同的需求。

/**
 * 工具类
 */
public abstract class AssembleExcel {
    private int columnSize;
    private String sheetName = "Excel";
    private HSSFWorkbook workbook;
    // 列标题字体样式
    private HSSFFont columnHeadFont;
    // 列标题样式
    private HSSFCellStyle columnHeadStyle;
    // 普通单元格字体
    private HSSFFont font;
    // 普通单元格样式
    private HSSFCellStyle style;
    //创建sheet
    private HSSFSheet sheet;

    private HSSFRow row;
    //单元格
    private HSSFCell cell;

    private void initCellStyle() {
        workbook = new HSSFWorkbook();
        // 列标题字体样式
        columnHeadFont = workbook.createFont();
        columnHeadFont.setFontName("微软雅黑");
        columnHeadFont.setFontHeightInPoints((short) 10);
        columnHeadFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

        // 列标题样式
        columnHeadStyle = workbook.createCellStyle();
        columnHeadStyle.setFont(columnHeadFont);
        // 左右居中
        columnHeadStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        // 上下居中
        columnHeadStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        columnHeadStyle.setLocked(true);
        columnHeadStyle.setWrapText(true);
        // 左边框的颜色
        columnHeadStyle.setLeftBorderColor(HSSFColor.BLACK.index);
        // 边框的大小
        columnHeadStyle.setBorderLeft((short) 1);
        // 右边框的颜色
        columnHeadStyle.setRightBorderColor(HSSFColor.BLACK.index);
        // 边框的大小
        columnHeadStyle.setBorderRight((short) 1);
        // 设置单元格的边框为粗体
        columnHeadStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        // 设置单元格的边框颜色
        columnHeadStyle.setBottomBorderColor(HSSFColor.BLACK.index);
        // 设置单元格的背景颜色（单元格的样式会覆盖列或行的样式）
        columnHeadStyle.setFillForegroundColor(HSSFColor.WHITE.index);

        // 普通单元格字体
        font = workbook.createFont();
        font.setFontName("微软雅黑");
        font.setFontHeightInPoints((short) 9);

        // 普通单元格样式
        style = workbook.createCellStyle();
        style.setFont(font);
        style.setAlignment(HSSFCellStyle.ALIGN_LEFT); // 左右居中
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 上下居中
        style.setWrapText(false);
        style.setLeftBorderColor(HSSFColor.BLACK.index);
        style.setBorderLeft((short) 1);
        style.setRightBorderColor(HSSFColor.BLACK.index);
        style.setBorderRight((short) 1);
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 设置单元格的边框为粗体
        style.setBottomBorderColor(HSSFColor.BLACK.index); // 设置单元格的边框颜色．
        style.setFillForegroundColor(HSSFColor.WHITE.index); // 设置单元格的背景颜色．

        //创建sheet
        sheet = workbook.createSheet(sheetName);
        sheet.createFreezePane(1, 1); // 冻结

        row = sheet.createRow(0);
        row.setHeight((short) 300);
    }

    private void initCell() {
        String[] cellValue = setCellValue();
        int[] columnWidth = setColumnWidth();
        columnSize = cellValue.length;

        for (int i = 0; i < cellValue.length; i++) {
            cell = row.createCell(i);
            cell.setCellValue(new HSSFRichTextString(cellValue[i]));
            cell.setCellStyle(columnHeadStyle);
            sheet.setColumnWidth(i, columnWidth[i]);
        }
    }

    public HSSFWorkbook assembleExcel(int dataSize) {
        for (int i = 0; i < dataSize; i++) {
            row = sheet.createRow(i + 1);
            row.setHeight((short) 300);// 设置列标题行高
//			row.setRowStyle(style);

            for (int j = 0; j < columnSize; j++) {
                cell = row.createCell(j);
                cell.setCellStyle(style);
                cell.setCellValue(CellSwitch(i, j));
            }
        }
        return workbook;
    }

    // 初始化操作
    public void init() {
        initCellStyle();
        initCell();
    }

    // 实现类 根据 行和列 进行数据的查找 返回String
    public abstract String CellSwitch(int row, int col);

    // 实现类只需要返回 String数组即可 不必进行其他操作
    public abstract String[] setCellValue();

    public  abstract int[] setColumnWidth();

    public void setSheetName(String sheetName) {
        this.sheetName = sheetName;
    }
}

后端Controller代码

@RequestMapping("/exportCityInfo")
    public void exportCityInfo(HttpServletRequest req, HttpServletResponse resp)throws Exception {
        try {
            String[] params = {"province","city","hasLevel1Partner"};
            Map<String,Object> map = CtrlUtils.getParameterMap(req,params);
            final List<Map<String,Object>> list = cityInfoService.getCityInfoList(map);
            
            AssembleExcel assembleExcel = new AssembleExcel() {
                @Override
                public String CellSwitch(int row, int col) {
                    String s = "";
                    Map<String, Object> map = list.get(row);
                    switch (col) {
                        case 0:
                            if (map.get("province") != null) {
                                s = map.get("province").toString();
                            }
                            break;
                        case 1:
                            if (map.get("city") != null) {
                                s = map.get("city").toString();
                            }
                            break;
                        case 2:
                            if (map.get("level1PartnerName") != null) {
                                s = map.get("level1PartnerName").toString();
                            }
                            break;
                        case 3:
                            if (map.get("subPartnerNum") != null) {
                                s = map.get("subPartnerNum").toString();
                            }
                            break;
                        case 4:
                            if (map.get("vipNum") != null) {
                                s = map.get("vipNum").toString();
                            }
                            break;
                        case 5:
                            if (map.get("vipWithCardNum") != null) {
                                s = map.get("vipWithCardNum").toString();
                            }
                            break;
                        case 6:
                            if (map.get("buyCount") != null) {
                                s = map.get("buyCount").toString();
                            }
                            break;
                    }
                    return s;
                }

                @Override
                public String[] setCellValue() {
                    return new String[]{"省份", "城市", "一级合伙人", "二级合伙人数", "会员数", "持卡会员数"};
                }

                @Override
                public int[] setColumnWidth() {
                    return new int[]{5000, 5000, 5000, 5000, 5000, 5000};
                }
            };
            assembleExcel.setSheetName("订单信息");
            assembleExcel.init();
            HSSFWorkbook workbook = assembleExcel.assembleExcel(list.size());
            SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss");
            String filename = "订单信息" + sdf.format(new java.util.Date()) + ".xls";
            resp.setContentType("application/vnd.ms-excel");
            String agent = req.getHeader("USER-AGENT").toLowerCase();
            String codedFileName = java.net.URLEncoder.encode(filename, "UTF-8");
            if (agent.contains("firefox")) {
                resp.setCharacterEncoding("utf-8");
                resp.setHeader("content-disposition", "attachment;filename=" + new String(filename.getBytes(), "ISO8859-1"));
            } else {
                resp.setHeader("content-disposition", "attachment;filename=" + codedFileName);
            }

            OutputStream ouPutStream = resp.getOutputStream();
            workbook.write(ouPutStream);
            ouPutStream.flush();
            ouPutStream.close();
        } catch (Exception e) {
            e.printStackTrace();
            CtrlUtils.putJsonResult(false, "查询失败!", resp);
        }
    }

前台代码:

	let params = {
	    "province": $("#province").val(),
	     "city": $("#city").val(),
	     "hasLevel1Partner": $("#hasLevel1Partner").val()
	 };
	 let g = [];
	 for (let h in params) g.push(h + "=" + params[h]);
	 location.href = basePath.value + "/cityInfo/exportCityInfo.json?" + g.join("&");