java通过POI和easypoi实现Excel的导出

蔚落 2022-02-23 12:08 589阅读 0赞

### 前言 ###

在工作经常会遇到excel导出报表的功能，自己也做过一些，然后在项目里看到同事封装的一个excel导出工具类，着实不错，拿来分享一下。然后，又在网上看到一个使用easypoi实现cxcel导出的博客，于是自己也仿着搞了一下，在这也分享一下。

### 使用POI实现excel导出 ###

*  首先，引入jar包，这是POI需要的jar包。

<dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi</artifactId>
        <version>3.17</version>
    </dependency>

*  下面是主要的实现方法。

定义一个函数式接口，用于自定义格式。

package com.mz.util;
    
    import java.io.IOException;
    
    /**
     * @version V1.0
     * @Description:
     * @date 2018/10/31 17:16
     */
    @FunctionalInterface
    public interface ExportConsumer<ByteArrayOutputStream, Workbook, List> {
        /**
         *自定义导出数据拼装
         * @param fileOut 输出流
         * @param wb workbook对象
         * @param listData 数据集
         * @throws IOException
         */
        void accept(ByteArrayOutputStream fileOut, Workbook wb, List listData) throws IOException;
    }

*  工具类主要方法

package com.mz.util;
    
    import org.apache.poi.ss.usermodel.Workbook;
    import org.springframework.http.HttpHeaders;
    import org.springframework.http.HttpStatus;
    import org.springframework.http.MediaType;
    import org.springframework.http.ResponseEntity;
    
    import java.io.ByteArrayOutputStream;
    import java.io.IOException;
    import java.nio.charset.StandardCharsets;
    import java.util.List;
    
    public class POIExportUtil {
    
     /**
      * 导出excel
      *
      * @param fileOut    输出流
      * @param wb         excel workbook对象
      * @param listData 需要导出的数据
      * @param consumer   自定义导出excel的格式
      * @param fileName   文件名
      * @return ResponseEntity
      * @throws IOException
      */
     public static <T> ResponseEntity<byte[]> exportExcel(ByteArrayOutputStream fileOut,
                                                            Workbook wb,
                                                            List<T> listData,
                                                            String fileName,
                                                            ExportConsumer<ByteArrayOutputStream, Workbook, List<T>> consumer) throws IOException {
          consumer.accept(fileOut, wb, listData);
          HttpHeaders headers = new HttpHeaders();
          headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);
    
          headers.setContentDispositionFormData("attachment", new String((fileName + ".xls").getBytes(StandardCharsets.UTF_8), StandardCharsets.ISO_8859_1));
          return new ResponseEntity<>(fileOut.toByteArray(), headers, HttpStatus.OK);
        }
    }

*  使用案例，页面通过<a>标签访即可。

//表头字段名
        private static final String[] COLUMN_NAMES = {"序号", "单号/流水号", "PNR", "业务类", "发生时间", "付款科目", "总金额",
                "预存款期初金额", "预存款发生金额", "预存款期末金额", "授信期初金额", "授信发生金额", "授信期末金额",
                "协议欠款期初金额", "协议欠款发生金额", "协议欠款期末金额", "分销商", "操作员"};
    
    
    
     /**
         * 导出流水报表
         * @param request
         * @return
         */
        @Override
        public ResponseEntity exportExcel(TradDetailRequest request) {
            ByteArrayOutputStream fileout = new ByteArrayOutputStream();
            try {
                //数据库的数据
                List<B2bTradedetail> tradeDetailForExport = tradedetailMapper.getTradeDetailForExport(request);
                //创建workbook对象
                Workbook wb = new HSSFWorkbook();
                return POIExportUtil.exportExcel(fileout, wb, tradeDetailForExport, "流水记录报表",
                        (out, workbook, data) -> createCell(wb, data).write(fileout));
            } catch (Exception e) {
                LOGGER.error("B2bTradedetailServiceImpl.exportExcel:" + e.getMessage());
                return new ResponseEntity<>("导出错误！\\n" + e.getMessage(), HttpStatus.INTERNAL_SERVER_ERROR);
            } finally {
                try {
                    fileout.close();
                } catch (IOException e) {
                    LOGGER.error("B2bTradedetailServiceImpl.exportExcel 输出流关闭错误" + e.getMessage());
                }
            }
        }
    
        /**
         * 设置excel标题和数据
         * @param wb
         * @param data
         * @return
         */
        private Workbook createCell(Workbook wb, List<B2bTradedetail> data) {
            String safeName = WorkbookUtil.createSafeSheetName("sheet1");
            Sheet sheet = wb.createSheet(safeName);
            //第一行标题
            Row title = sheet.createRow(0);
            for (int i = 0; i < COLUMN_NAMES.length; i++) {
                title.createCell(i).setCellValue(COLUMN_NAMES[i]);
            }
            //插入数据
            data.forEach(b2bTradedetail -> {
                Row row = sheet.createRow(data.indexOf(b2bTradedetail) + 1);
                row.createCell(0).setCellValue(data.indexOf(b2bTradedetail) + 1);
                row.createCell(1).setCellValue(b2bTradedetail.getDealno());
                row.createCell(2).setCellValue(b2bTradedetail.getPnrno());
                row.createCell(3).setCellValue(b2bTradedetail.getOperatetypeno());
                row.createCell(4).setCellValue(b2bTradedetail.getCreatetime());
                row.createCell(5).setCellValue(b2bTradedetail.getPaytypeno());
                row.createCell(6).setCellValue(b2bTradedetail.getTotalmoney());
                row.createCell(7).setCellValue(b2bTradedetail.getAccountbefore());
                row.createCell(8).setCellValue(b2bTradedetail.getAccountmoney());
                row.createCell(9).setCellValue(b2bTradedetail.getAccountafter());
                row.createCell(10).setCellValue(b2bTradedetail.getCreditbefore());
                row.createCell(11).setCellValue(b2bTradedetail.getCreditmoney());
                row.createCell(12).setCellValue(b2bTradedetail.getCreditafter());
                row.createCell(13).setCellValue(b2bTradedetail.getProtocoldebtbefore());
                row.createCell(14).setCellValue(b2bTradedetail.getProtocoldebtmoney());
                row.createCell(15).setCellValue(b2bTradedetail.getProtocoldebtafter());
                row.createCell(16).setCellValue(b2bTradedetail.getCompid());
                row.createCell(17).setCellValue(b2bTradedetail.getOperatorid());
            });
            return wb;
        }

使用easypoi实现，这里直接是一个工具类和使用案例，关于这个教程可以参考[easypoi教程][easypoi]

*  先导入jar包

<dependency>
        <groupId>cn.afterturn</groupId>
        <artifactId>easypoi-base</artifactId>
        <version>3.2.0</version>
    </dependency>
    
    <dependency>
        <groupId>cn.afterturn</groupId>
        <artifactId>easypoi-web</artifactId>
        <version>3.2.0</version>
    </dependency>
    
    <dependency>
        <groupId>cn.afterturn</groupId>
        <artifactId>easypoi-annotation</artifactId>
        <version>3.2.0</version>
    </dependency>

*  一个实体类，报表的主要数据。

package com.mz.entity;
    
    import cn.afterturn.easypoi.excel.annotation.Excel;
    
    import java.io.Serializable;
    import java.util.Date;
    
    /**
     * @version V1.0
     * @Description:
     * @date 2018/10/31 15:31
     */
    public class StudentEntity implements Serializable {
    
        /**
         * id
         */
        private String id;
        /**
         * 学生姓名
         */
        @Excel(name = "姓名", isImportField = "true_st")
        private String name;
        /**
         * 学生性别
         */
        @Excel(name = "性别", replace = { "男_1", "女_2" }, isImportField = "true_st")
        private int sex;
    
        @Excel(name = "出生日期", databaseFormat = "yyyyMMddHHmmss", format = "yyyy-MM-dd", isImportField = "true_st")
        private Date birthday;
    
        @Excel(name = "进校日期", databaseFormat = "yyyyMMddHHmmss", format = "yyyy-MM-dd")
        private Date registrationDate;
    
        public StudentEntity(String id, String name, int sex, Date birthday, Date registrationDate) {
            this.id = id;
            this.name = name;
            this.sex = sex;
            this.birthday = birthday;
            this.registrationDate = registrationDate;
        }
    
        //省略setter getter方法

*  工具类方法，包含两个excel的方法，没有测试过。

package com.mz.util;
    
    import cn.afterturn.easypoi.excel.ExcelExportUtil;
    import cn.afterturn.easypoi.excel.ExcelImportUtil;
    import cn.afterturn.easypoi.excel.entity.ExportParams;
    import cn.afterturn.easypoi.excel.entity.ImportParams;
    import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
    import org.apache.commons.lang3.StringUtils;
    import org.apache.poi.ss.usermodel.Workbook;
    import org.springframework.web.multipart.MultipartFile;
    
    import javax.servlet.http.HttpServletResponse;
    import java.io.File;
    import java.io.IOException;
    import java.net.URLEncoder;
    import java.util.List;
    import java.util.Map;
    import java.util.NoSuchElementException;
    
    /**
     * @version V1.0
     * @Description: Excel导出工具类
     * @date 2018/10/31 19:16
     */
    public class ExportExcelUtil {
        /**
         * 
         * @param list 数据集合
         * @param title 内容标题
         * @param sheetName excel名称
         * @param pojoClass 实体类
         * @param fileName 导出的文件名
         * @param isCreateHeader 是否创建excel表头
         * @param response 响应
         */
        public static void exportExcel(List<?> list,
                                       String title,
                                       String sheetName,
                                       Class<?> pojoClass,
                                       String fileName,
                                       boolean isCreateHeader,
                                       HttpServletResponse response) {
            ExportParams exportParams = new ExportParams(title, sheetName);
            exportParams.setCreateHeadRows(isCreateHeader);
            defaultExport(list, pojoClass, fileName, response, exportParams);
        }
    
        /**
         * 导出
         * @param list 数据集合
         * @param title 内容标题
         * @param sheetName excel名称
         * @param pojoClass 实体类
         * @param fileName 导出的文件名
         * @param response 响应
         */
        public static void exportExcel(List<?> list,
                                       String title,
                                       String sheetName,
                                       Class<?> pojoClass,
                                       String fileName,
                                       HttpServletResponse response){
            defaultExport(list, pojoClass, fileName, response, new ExportParams(title, sheetName));
        }
    
        /**
         * 导出 无内容标题和excel表名
         * @param list
         * @param pojoClass
         * @param fileName
         * @param response
         */
        public static void exportExcel(List<?> list,
                                       Class<?> pojoClass,
                                       String fileName,
                                       HttpServletResponse response) {
            defaultExport(list, pojoClass, fileName, response, new ExportParams());
        }
    
        /**
         * 导出
         * @param list 数据集合
         * @param fileName 导出的文件名
         * @param response 响应
         */
        public static void exportExcel(List<Map<String, Object>> list,
                                       String fileName,
                                       HttpServletResponse response){
            defaultExport(list, fileName, response);
        }
    
        /**
         *
         * @param list 数据集合
         * @param pojoClass  实体类
         * @param fileName  导出的文件名
         * @param response 响应
         * @param exportParams
         */
        private static void defaultExport(List<?> list,
                                          Class<?> pojoClass,
                                          String fileName,
                                          HttpServletResponse response,
                                          ExportParams exportParams) {
            Workbook workbook = ExcelExportUtil.exportExcel(exportParams,pojoClass,list);
            if (workbook != null) {
                doExport(fileName, response, workbook);
            }
        }
    
        /**
         *
         * @param list 数据集合
         * @param fileName 导出的文件名
         * @param response 响应
         */
        private static void defaultExport(List<Map<String, Object>> list,
                                          String fileName,
                                          HttpServletResponse response) {
            Workbook workbook = ExcelExportUtil.exportExcel(list, ExcelType.HSSF);
            if (workbook != null){
                doExport(fileName, response, workbook);
            }
        }
    
        /**
         * 导出
         * @param fileName  导出的文件名
         * @param response 响应
         * @param workbook 工作表
         */
        private static void doExport(String fileName,
                                          HttpServletResponse response,
                                          Workbook workbook) {
            try {
                response.setCharacterEncoding("UTF-8");
                response.setHeader("content-Type", "application/vnd.ms-excel");
                response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8") + ".xls");
                workbook.write(response.getOutputStream());
            } catch (IOException e) {
                throw new NormalException(e.getMessage());
            }
        }
    
    
        /**
         *
         * @param filePath
         * @param titleRows
         * @param headerRows
         * @param pojoClass
         * @param <T>
         * @return
         */
        public static <T> List<T> importExcel(String filePath,
                                              Integer titleRows,
                                              Integer headerRows,
                                              Class<T> pojoClass) {
            if (StringUtils.isBlank(filePath)){
                return null;
            }
            ImportParams params = new ImportParams();
            params.setTitleRows(titleRows);
            params.setHeadRows(headerRows);
            List<T> list = null;
            try {
                list = ExcelImportUtil.importExcel(new File(filePath), pojoClass, params);
            }catch (NoSuchElementException e){
                throw new NormalException("模板不能为空");
            } catch (Exception e) {
                e.printStackTrace();
                throw new NormalException(e.getMessage());
            }
            return list;
        }
    
        /**
         *
         * @param file
         * @param titleRows
         * @param headerRows
         * @param pojoClass
         * @param <T>
         * @return
         */
        public static <T> List<T> importExcel(MultipartFile file,
                                              Integer titleRows,
                                              Integer headerRows,
                                              Class<T> pojoClass){
            if (file == null){
                return null;
            }
            ImportParams params = new ImportParams();
            params.setTitleRows(titleRows);
            params.setHeadRows(headerRows);
            List<T> list = null;
            try {
                list = ExcelImportUtil.importExcel(file.getInputStream(), pojoClass, params);
            }catch (NoSuchElementException e){
                throw new NormalException("excel文件不能为空");
            } catch (Exception e) {
                throw new NormalException(e.getMessage());
            }
            return list;
        }
    
    }

*  使用案例，可以写一个Controller直接通过浏览器访问。

@RequestMapping(value = "/")
        public void getname(HttpServletResponse response) {
            List<StudentEntity> list = new ArrayList<>();
            StudentEntity studentEntity = new StudentEntity("a","路飞",1,new Date(),new Date());
            StudentEntity studentEntity1 = new StudentEntity("a","路飞",1,new Date(),new Date());
            StudentEntity studentEntity2 = new StudentEntity("a","路飞",1,new Date(),new Date());
            StudentEntity studentEntity3 = new StudentEntity("a","路飞",1,new Date(),new Date());
            StudentEntity studentEntity4 = new StudentEntity("a","路飞",1,new Date(),new Date());
            list.add(studentEntity);
            list.add(studentEntity1);
            list.add(studentEntity2);
            list.add(studentEntity3);
            list.add(studentEntity4);
    
                    ExportExcelUtil.exportExcel(list, StudentEntity.class, "学生报表", response);
    
    
    
    //      response.setHeader("content-Type", "application/vnd.ms-excel");
    //      response.setHeader("Content-Disposition", "attachment;filename=" + System.currentTimeMillis() + ".xls");
    //      response.setCharacterEncoding("UTF-8");
    
    //      Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams("","学生"),
    //              StudentEntity.class, list);
    
    //      ExportExcelUtil.exportExcel(list,"学生信息", "学生",StudentEntity.class,
    //              "学生报表", true, response);
    
    //      if (workbook != null) {
    //          try {
    //              workbook.write(response.getOutputStream());
    //          } catch (IOException e) {
    //              e.printStackTrace();
    //          }
    //      }
            //return "Hello Spring Boot";
        }

\*导入报表使用

<form id="loginForm" method="post" action="/import" enctype="multipart/form-data">
            <ul>
                <li>
                    <span>上       传：</span>
                    <span class="input">
                   <input type="file" id="file" name="file"/>
                    </span>
                    <input type="submit" placeholder="提交">
                </li>
            </ul>
        </form> 
    
    @RequestMapping(value = "/import", method = RequestMethod.POST)
        public void importExcel(@RequestParam("file") MultipartFile file) {
            System.out.println("进来了");
            System.out.println("名字：" + file.getName());
            List<StudentEntity> personList = ExportExcelUtil.importExcel(file, 0,1, StudentEntity.class);
            System.out.println(personList.size());
            personList.forEach(stu -> {
                System.out.println(stu.getName() + " " + stu.getRegistrationDate() + " " + stu.getSex());
            });
            System.out.println("导入数据一共【"+personList.size()+"】行");
        }

![1240732-20181128111056203-1832679341.png][]

小结

报表在工作中遇到的还算多，总结一下方便以后使用。

[https://www.cnblogs.com/black-spike/p/9884468.html][https_www.cnblogs.com_black-spike_p_9884468.html]

[http://easypoi.mydoc.io/\#text\_228244][http_easypoi.mydoc.io_text_228244]

[easypoi]: http://easypoi.mydoc.io/#text_186905
[1240732-20181128111056203-1832679341.png]: /images/20220223/7cd6aad61d0e44d1bfe012c1eb22f3b6.png
[https_www.cnblogs.com_black-spike_p_9884468.html]: https://www.cnblogs.com/black-spike/p/9884468.html
[http_easypoi.mydoc.io_text_228244]: http://easypoi.mydoc.io/#text_228244