Java实现excel导出功能的几种方法——poi、easyExcel、easypoi、jxl

用户123123

已于 2023-02-17 22:29:22 修改

阅读量3.3k

点赞数 1

文章标签： java html poi excel

于 2021-09-15 21:55:48 首次发布

本文链接：https://blog.csdn.net/ordinarybird/article/details/120286017

版权

推荐使用easyExcel，简单好用
对于稍微复杂一点的表格，个人建议用jxl

easypoi

以下代码中包含的操作：

合并单元格
设置字体格式：加粗，字体大小，颜色
设置单元格格式：居中，边框，背景颜色
填充数据

一、jxl

jxl jxl只支持导出xls文件
导入依赖：

<dependency>
    <groupId>net.sourceforge.jexcelapi</groupId>
    <artifactId>jxl</artifactId>
    <version>2.6.12</version>
</dependency>

利用jxl最后生成的表格：
在这里插入图片描述

@GetMapping("/jxl")
public void jxlExport(HttpServletResponse response) throws Exception {
    String fileName = "jxl_test.xls";
    response.reset();
    response.setContentType("application/octet-stream; charset=utf-8");
    response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(fileName, "UTF-8"));
    //不能是ServletOutputStream
    OutputStream outputStream = response.getOutputStream();

    //创建工作簿
    WritableWorkbook workbook = Workbook.createWorkbook(outputStream);
    //创建sheet
    WritableSheet sheet = workbook.createSheet("意向院校", 0);


    //设置表头1
    String header1 = "院校信息汇总表";
    WritableFont writableFont = new WritableFont(WritableFont.createFont("黑体"),17);
    WritableCellFormat format = new WritableCellFormat(writableFont);
    //居中
    format.setAlignment(Alignment.CENTRE);
    //合并单元格，从(0,0)到(0,6)
    sheet.mergeCells(0, 0, 6, 0);
    sheet.addCell(new Label(0,0,header1,format));


    //设置表头2
    String[] headers2 = {"序号", "学校名称", "是否985", "是否211", "是否双一流", "地域", "学校类型"};
    //为表头2单元格 设置字体格式：11号宋体，加粗，无下划线，红色字体
    WritableFont headerFont = new WritableFont(WritableFont.createFont("宋体"), 11, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.RED);
    WritableCellFormat headerFormat = new WritableCellFormat(headerFont);
    //为表头2设置单元格格式
    headerFormat.setAlignment(Alignment.CENTRE); //设置居中
    headerFormat.setBorder(Border.ALL, BorderLineStyle.THIN); //设置边框线
    headerFormat.setBackground(Colour.GOLD); //设置单元格的背景颜色
    //在第1行创建表头2的单元格
    for (int i = 0; i < headers2.length; i++) {
        //c：列 r：行
        Label label = new Label(i, 1, headers2[i], headerFormat);
        sheet.addCell(label);
    }


    //设置数据单元格字体：11号宋体
    WritableFont font = new WritableFont(WritableFont.createFont("宋体"),11);
    WritableCellFormat cellFormat = new WritableCellFormat(font);

    //原始数据
    List<RecSchool> recSchools = recSchoolMapper.findAll();
    //从第2行开始创建数据
    for (int i = 2; i < recSchools.size()+2; i++) {
        sheet.addCell(new Label(0,i, "" + (i-1),cellFormat));
        sheet.addCell(new Label(1,i,recSchools.get(i-2).getSchoolName(),cellFormat));
        sheet.addCell(new Label(2,i,recSchools.get(i-2).getF985(),cellFormat));
        sheet.addCell(new Label(3,i,recSchools.get(i-2).getF211(),cellFormat));
        sheet.addCell(new Label(4,i,recSchools.get(i-2).getDualClass(),cellFormat));
        sheet.addCell(new Label(5,i,recSchools.get(i-2).getSchoolProvince(),cellFormat));
        sheet.addCell(new Label(6,i,recSchools.get(i-2).getSchoolType(),cellFormat));
    }

    //写入数据
    workbook.write();
    //关闭工作簿
    workbook.close();
}

在浏览器中执行该接口即可生成excel

二、poi

导入依赖：

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.15</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.15</version>
</dependency>

在这里插入图片描述

@GetMapping("/poi")
public void poiExport(HttpServletResponse response) throws IOException {
    String fileName = "poi_test.xlsx";
    SXSSFWorkbook workbook = new SXSSFWorkbook();
    //创建sheet表格
    SXSSFSheet sheet = workbook.createSheet();


    //设置表头1字体格式
    Font font1 = workbook.createFont();
    font1.setFontName("黑体");
    font1.setBold(true); //加粗
    font1.setFontHeightInPoints((short) 17); //字体大小

    //设置表头1单元格格式
    CellStyle cellStyle1 = workbook.createCellStyle();
    cellStyle1.setAlignment(HorizontalAlignment.CENTER); //居中
    cellStyle1.setFont(font1);

    //设置表头2字体格式
    Font font2 = workbook.createFont();
    font2.setFontName("宋体");
    font2.setBold(true);
    font2.setFontHeightInPoints((short) 11);
    font2.setColor(Font.COLOR_RED);

    //设置表头2单元格格式
    CellStyle cellStyle2 = workbook.createCellStyle();
    cellStyle2.setAlignment(HorizontalAlignment.CENTER);
    //设置单元格背景颜色
    cellStyle2.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
    cellStyle2.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    //设置上下左右边框
    cellStyle2.setBorderBottom(BorderStyle.valueOf(String.valueOf(BorderStyle.THIN)));
    cellStyle2.setBorderTop(BorderStyle.valueOf(String.valueOf(BorderStyle.THIN)));
    cellStyle2.setBorderLeft(BorderStyle.valueOf(String.valueOf(BorderStyle.THIN)));
    cellStyle2.setBorderRight(BorderStyle.valueOf(String.valueOf(BorderStyle.THIN)));
    cellStyle2.setFont(font2);

    //设置数据字体格式
    CellStyle cellStyle3 = workbook.createCellStyle();
    Font font3 = workbook.createFont();
    font3.setFontName("宋体");
    font3.setBold(false);
    font3.setFontHeightInPoints((short) 11);
    cellStyle3.setFont(font3);

    //合并单元格(0,0)到(0,6)
    CellRangeAddress region = new CellRangeAddress(0, 0, 0, 6);
    sheet.addMergedRegion(region);
    //填充表头1数据
    SXSSFRow row1 = sheet.createRow(0);
    SXSSFCell cell1 = row1.createCell(0);
    cell1.setCellStyle(cellStyle1);
    cell1.setCellValue("院校信息汇总表");
    
    //填充表头2数据
    String[] headers2 = {"序号", "学校名称", "是否985", "是否211", "是否双一流", "地域", "学校类型"};
    SXSSFRow row2 = sheet.createRow(1);
    for (int i = 0; i < headers2.length; i++) {
        SXSSFCell cell = row2.createCell(i);
        cell.setCellStyle(cellStyle2);
        cell.setCellValue(headers2[i]);
    }

    //填充具体数据
    List<RecSchool> recSchools = recSchoolMapper.findAll();
    for (int i = 0; i < recSchools.size(); i++) {
        SXSSFCell cell;
        //从第三行开始
        SXSSFRow row = sheet.createRow(i + 2);
        cell = row.createCell(0);
        cell.setCellValue(""+(i+1));
        cell.setCellStyle(cellStyle3);

        cell = row.createCell(1);
        cell.setCellValue(recSchools.get(i).getSchoolName());
        cell.setCellStyle(cellStyle3);

        cell = row.createCell(2);
        cell.setCellValue(recSchools.get(i).getF985());
        cell.setCellStyle(cellStyle3);

        cell = row.createCell(3);
        cell.setCellValue(recSchools.get(i).getF211());
        cell.setCellStyle(cellStyle3);

        cell = row.createCell(4);
        cell.setCellValue(recSchools.get(i).getDualClass());
        cell.setCellStyle(cellStyle3);

        cell = row.createCell(5);
        cell.setCellValue(recSchools.get(i).getSchoolProvince());
        cell.setCellStyle(cellStyle3);

        cell = row.createCell(6);
        cell.setCellValue(recSchools.get(i).getSchoolType());
        cell.setCellStyle(cellStyle3);

    }

    response.reset();
    response.setContentType("application/octet-stream; charset=utf-8");
    response.setHeader("Content-Disposition", "attachment; filename="+ URLEncoder.encode(fileName ,"UTF-8"));
    workbook.write(response.getOutputStream());

}