# excelDemo

**Repository Path**: somedaymail/excelDemo

## Basic Information

- **Project Name**: excelDemo
- **Description**: 使用easypoi导出导入excel(springboot) easypoi
- **Primary Language**: Java
- **License**: Not specified
- **Default Branch**: master
- **Homepage**: None
- **GVP Project**: No

## Statistics

- **Stars**: 0
- **Forks**: 3
- **Created**: 2019-07-16
- **Last Updated**: 2020-12-19

## Categories & Tags

**Categories**: Uncategorized

**Tags**: None

## README

SpringBoot整合EasyPoi实现excel导出
原文参考这个哥们改造的代码：https://gitee.com/ladit/excelDemo

#步骤如下：
1. 导入依赖
```aidl
   <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-base</artifactId>
            <version>4.0.0</version>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-web</artifactId>
            <version>4.0.0</version>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-annotation</artifactId>
            <version>4.0.0</version>
        </dependency>
```
2.  Excel导出主要使用这个方法

 ```aidl
    private static void downLoadExcel(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"));

            workbook.write(response.getOutputStream());
        } catch (IOException e) {
//            throw new BusinessException(e.getMessage(), false);
        }
    }
```

3. 改造为我自己的方法
```aidl
   @PostMapping("excelExport")
    @ApiOperation("excel导出")
    public void excelExport(@RequestBody SearchInputDTOArgs searchInputDTOArgs, HttpServletResponse response) throws Exception {
        FileOutputStream fos = null;

        String fileGenerateName = UUID.randomUUID().toString();
        String suffix = ".xlsx";
        Workbook workbook = sacwjbxxService.excelExport(searchInputDTOArgs);
        try {
            fos = new FileOutputStream(new File(fileGenerateName + suffix));
            workbook.write(fos);
            response.setCharacterEncoding("UTF-8");
            response.setHeader("content-Type", "application/vnd.ms-excel");
            response.setHeader("Content-Disposition",
                    "attachment;filename=" + URLEncoder.encode(fileGenerateName, "UTF-8"));
            workbook.write(response.getOutputStream());
        } catch (Exception e) {
            log.error("excel导出失败，错误信息为：" + e.getMessage());
            throw new ExcelExportException("文件导出失败");

        } finally {
            fos.close();
            Files.deleteIfExists(Paths.get(fileGenerateName + suffix));

        }
    }
    
   // service:
    
      @Override
        public Workbook excelExport(SearchInputDTOArgs searchInputDTOArgs) {
    
            SearchOutDTOList search = this.search(searchInputDTOArgs);
            List<ListDTO> result = search.getResult();
    
            Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams("计算机一班学生", "学生"),
                    ListDTO.class, result);
            return workbook;
        }
    
    
```
#  注意：集合中ListDTO对象中的元素添加`@Excel`注解,不然会报错参数不合法，调试源码才知道的。耽误了一下午的时间调试。
    ```
        @Excel(name = "分类编码", height = 20, width = 30, isImportField = "true_st")
        private String flbm;
        
    ```

#  ------------------------------------------------------------------------------------------------------

作者的方式对我来说不可用，有需要的朋友可以借鉴我的方法：

``` controller
@PostMapping("excelExport")
    @ApiOperation("涉案财物列表Excel导出")
    public void excelExport( @Validated @RequestBody SearchInputDTOArgs searchInputDTOArgs, 
    HttpServletResponse response) throws Exception {
        FileInputStream inputStream = null;
        String fileGenerateName = searchInputDTOArgs.getBmsah() + " "
                + searchInputDTOArgs.getTysah() + " 涉案财物登记表";
        String suffix = ".xls";
        String globalPath = fileGenerateName + suffix;
        //生成excel
        Workbook workbook = sacwjbxxService.excelExport(searchInputDTOArgs);

        try {
        //excel写出到response中
            workbook.write(response.getOutputStream());
            //设置文件名编码格式
            formatEncodingUtils.setDownloadFileHeader(response, globalPath);
            //输入流转为输出流，转为一个url
            IOUtils.copy(inputStream, response.getOutputStream());
        } catch (Exception e) {
            e.printStackTrace();
            log.error("Excel文件导出失败,请联系管理员！错误信息为：" + e.getMessage());
            throw new ExcelExportException("Excel文件导出失败,请联系管理员！" + e.getMessage());
        } finally {
        //流关闭
            inputStream.close();
        }
    }

```

```service
 @Override
    public Workbook excelExport(SearchInputDTOArgs searchInputDTOArgs) {

        SearchOutDTOList search = this.search(searchInputDTOArgs);
        List<ListDTO> result = search.getResult();

        Workbook workbook = ExcelExportUtil.exportExcel(
                new ExportParams(searchInputDTOArgs.getBmsah() + ":"
                        + searchInputDTOArgs.getTysah() + ": 涉案财物登记表", ""),
                ListDTO.class, result);
        return workbook;
    }
    //处理response乱码
        public static void setDownloadFileHeader(HttpServletResponse response, String fileName) {
        try {
            String enc = StandardCharsets.UTF_8.name();
            fileName = URLEncoder.encode(fileName, enc).replaceAll("\\+", "%20");
        } catch (UnsupportedEncodingException ignored) {
        }

        response.setHeader("Content-Disposition",
                "attachment;" +
                        " filename=\"" +
                        fileName +
                        "\";" +
                        " filename*=utf-8''" +
                        fileName);
    }
```

#  注意：集合中ListDTO对象中的元素添加@Excel注解,不然会报错参数不合法，调试源码才知道的。耽误了一下午的时间调试。

@Excel(name = "分类编码", height = 20, width = 30, isImportField = "true_st") private String flbm;