EasyExcel数据导出功能的封装及上传到云服务器（OSS）

缺乏、安全感 2022-11-12 04:27 1191阅读 0赞

## 1. 工具类 ##

#### 1.1 EasyExcel封装类 ####

package com.example.utils.easyexcel;
    
    import com.alibaba.excel.metadata.CellData;
    import com.alibaba.excel.metadata.Head;
    import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
    import com.alibaba.excel.write.metadata.style.WriteCellStyle;
    import com.alibaba.excel.write.metadata.style.WriteFont;
    import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
    import com.alibaba.excel.write.style.column.AbstractColumnWidthStyleStrategy;
    import lombok.extern.slf4j.Slf4j;
    import org.apache.poi.ss.usermodel.*;
    
    import javax.servlet.http.HttpServletResponse;
    import java.io.UnsupportedEncodingException;
    import java.net.URLEncoder;
    import java.util.List;
    
    /** * 描述: * EasyExcel的基础封装 * * @author XueGuCheng * @create 2021-03-19 23:01 */
    @Slf4j
    public class EasyExcel { 
    
    
    
        /** * 设置响应头 * * @param response 回应的请求数据 * @param fileName 文件名字 */
        public static void setHead(HttpServletResponse response, String fileName) { 
    
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf-8");
            // 这里URLEncoder.encode可以防止中文乱码
            try { 
                fileName = URLEncoder.encode(fileName, "UTF-8");
            } catch (UnsupportedEncodingException e) { 
                e.printStackTrace();
                log.error("编码异常");
            }
            response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
    
        }
    
        /** * 设置Excel的格式 * * @return 格式化后的Excel */
        public static HorizontalCellStyleStrategy formatExcel() { 
            WriteCellStyle headWriteCellStyle = new WriteCellStyle();
            headWriteCellStyle.setFillBackgroundColor(IndexedColors.WHITE.getIndex());
            WriteFont headWriteFont = new WriteFont();
            headWriteFont.setFontHeightInPoints((short) 10);
            headWriteCellStyle.setWriteFont(headWriteFont);
            // 内容的策略
            WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
            WriteFont contentWriteFont = new WriteFont();
            // 字体大小
            contentWriteFont.setFontHeightInPoints((short) 10);
            contentWriteCellStyle.setWriteFont(contentWriteFont);
            // 设置自动换行
            contentWriteCellStyle.setWrapped(true);
            // 设置垂直居中
            contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            // 设置水平居中
            contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
    
            return new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
    
        }
    
        /** * 设置头部单元格宽度 */
        public static class ExcelWidthStyleStrategy extends AbstractColumnWidthStyleStrategy { 
    
            @Override
            protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<CellData> list, Cell cell, Head head, Integer integer, Boolean aBoolean) { 
                // 设置宽度
                Sheet sheet = writeSheetHolder.getSheet();
                sheet.setColumnWidth(cell.getColumnIndex(), 5000);
            }
        }
    
    
    
    }

#### 1.2 EasyExcelUtils封装 ####

#### 注意： 博主并非采用了浏览器访问url直接下载文件的方式实现导出功能，所以采用了ByteArrayOutputStream转换数据直接上传至OSS。如果需要浏览器访问url直接下载文件，使用HttpServletResponse参数的getOutputStream()方法获取输出流，传入ExcelWriter即可，见注释代码。 ####

package com.example.utils.easyexcel;
    
    import com.alibaba.excel.EasyExcel;
    import com.alibaba.excel.ExcelWriter;
    import com.alibaba.excel.metadata.CellData;
    import com.alibaba.excel.metadata.Head;
    import com.alibaba.excel.write.builder.ExcelWriterSheetBuilder;
    import com.alibaba.excel.write.metadata.WriteSheet;
    import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
    import com.alibaba.excel.write.metadata.style.WriteCellStyle;
    import com.alibaba.excel.write.metadata.style.WriteFont;
    import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
    import com.alibaba.excel.write.style.column.AbstractColumnWidthStyleStrategy;
    import lombok.Cleanup;
    import lombok.extern.slf4j.Slf4j;
    import org.apache.poi.ss.usermodel.*;
    
    
    import javax.servlet.ServletOutputStream;
    import javax.servlet.http.HttpServletResponse;
    import java.io.*;
    import java.net.URLEncoder;
    import java.util.Date;
    import java.util.List;
    
    /** * @ClassName: EasyExcelUtils * @Description: EasyExcel工具类(替换com.example.utils.easyexcel) * @Author: jiangbeiping * @Data: 2021/3/18 16:46 * @Version: 1.0 */
    @Slf4j
    public class EasyExcelUtils { 
    
        /** * 每个sheet的容量，即超过60000时就会把数据分sheet */
        private static final int PAGE_SIZE = 60000;
    
    
        /** * 导出报表(使用注解方式设置Excel头数据) * * @param response 响应请求 * @param data 报表数据 * @param fileName 文件名字 * @param excelClass 报表实体类的Class（根据该Class的属性来设置Excel的头属性） */
        public static void exportByExcel(HttpServletResponse response, List<?> data, String fileName, Class<?> excelClass) throws IOException { 
            @Cleanup ByteArrayOutputStream os = new ByteArrayOutputStream();
    // ServletOutputStream os = response.getOutputStream();
            long exportStartTime = System.currentTimeMillis();;
            log.info("报表导出Size: " + data.size() + "条。");
    
            // 把查询到的数据按设置的sheet的容量进行切割
            List<? extends List<?>> lists = SplitList.splitList(data, PAGE_SIZE);
            // 设置响应头
            com.example.utils.easyexcel.EasyExcel.setHead(response, fileName);
            // 格式化Excel数据
            // EasyExcelBasic.formatExcel()：设置Excel的格式
            // EasyExcelBasic.ExcelWidthStyleStrategy():设置头部单元格宽度
            ExcelWriter excelWriter = EasyExcel.write(os, excelClass).registerWriteHandler(com.example.utils.easyexcel.EasyExcel.formatExcel()).registerWriteHandler(new com.example.utils.easyexcel.EasyExcel.ExcelWidthStyleStrategy()).build();
    		
    		// 浏览器访问url直接下载文件的方式
     		//ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream(), excelClass).registerWriteHandler(com.example.utils.easyexcel.EasyExcel.formatExcel()).registerWriteHandler(new com.example.utils.easyexcel.EasyExcel.ExcelWidthStyleStrategy()).build();
    
            ExcelWriterSheetBuilder excelWriterSheetBuilder;
            WriteSheet writeSheet;
            for (int i = 1; i <= lists.size(); ++i) { 
                excelWriterSheetBuilder = new ExcelWriterSheetBuilder(excelWriter);
                excelWriterSheetBuilder.sheetNo(i).sheetName("sheet" + i);
                writeSheet = excelWriterSheetBuilder.build();
                excelWriter.write(lists.get(i - 1), writeSheet);
            }
            // 必须要finish才会写入，不finish只会创建empty的文件
            excelWriter.finish();
    
            byte[] content = os.toByteArray();
            @Cleanup InputStream is = new ByteArrayInputStream(content);
    
            // 文件落地，用来测试文件的格式和数据的完整性
            // @Cleanup:lombok关流注解
            //FileOutputStream fileOutputStream = new FileOutputStream("/data/logs/" + fileName + ".xlsx");
            //@Cleanup BufferedInputStream bis = new BufferedInputStream(is);
            //@Cleanup BufferedOutputStream bos = new BufferedOutputStream(fileOutputStream);
            //byte[] buff = new byte[2048];
            //int bytesRead;
            //while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) { 
            // bos.write(buff, 0, bytesRead);
           // }
            //log.info("文件落地磁盘");
    
    
            // 文件上传到OSS
            FileUploadUtil_Test.upLocalFileToOss(is, fileName);
    
    
            System.out.println("报表导出结束时间:" + new Date() + ";导出耗时: " + (System.currentTimeMillis() - exportStartTime) + "ms");
    
        }
    
    
    }

#### 1.3 SplitList封装类 ####

package com.example.utils.easyexcel;
    
    import java.util.ArrayList;
    import java.util.Collections;
    import java.util.List;
    
    /** * @ClassName: SplitList * @Description: EasyExcelUtils类的辅助类（切割查询的数据） * @Author: jiangbeiping * @Data: 2021/3/19 17:38 * @Version: 1.0 */
    public class SplitList { 
    
        /** * 切割查询的数据 * @param list 需要切割的数据 * @param len 按照什么长度切割 * @param <T> * @return */
        public static <T> List<List<T>> splitList(List<T> list, int len) { 
            if (list == null || list.size() == 0 || len < 1) { 
                return null;
            }
            List<List<T>> result = new ArrayList<List<T>>();
            int size = list.size();
            int count = (size + len - 1) / len;
            for (int i = 0; i < count; i++) { 
                List<T> subList = list.subList(i * len, (Math.min((i + 1) * len, size)));
                result.add(subList);
            }
            return result;
        }
    
        /** * 集合平均分组 * @param source 源集合 * @param n 分成n个集合 * @param <T> 集合类型 * @return 平均分组后的集合 */
        public static <T> List<List<T>> groupList(List<T> source, int n) { 
            if (source == null || source.size() == 0 || n < 1) { 
                return null;
            }
            if (source.size() < n) { 
                return Collections.singletonList(source);
            }
            List<List<T>> result = new ArrayList<List<T>>();
            int number = source.size() / n;
            int remaider = source.size() % n;
            // 偏移量，每有一个余数分配，就要往右偏移一位
            int offset = 0;
            for (int i = 0; i < n;i++) { 
                List<T> list1 = null;
                if (remaider > 0){ 
                    list1 = source.subList(i * number + offset,(i + 1) * number + offset + 1);
                    remaider--;
                    offset++;
                }else { 
                    list1 = source.subList(i * number + offset, (i+1) * number + offset);
                }
                result.add(list1);
            }
            return result;
        }
    
    }

## 2. 测试类 ##

#### 2.1 实体类 ####

package com.example.vo;
    
    
    import com.alibaba.excel.annotation.ExcelProperty;
    import com.alibaba.excel.annotation.format.DateTimeFormat;
    import lombok.Data;
    
    @Data
    public class User { 
    
      @ExcelProperty(value = "ID",index = 2)
      private long id;
    
      @ExcelProperty(value = "用户名",index = 3)
      private String name;
    
      @ExcelProperty(value = "居住城市",index = 4)
      private String city;
    
      @ExcelProperty(value = "性别",index = 5)
      private String sex;
    
      @ExcelProperty(value = "序号",index = 1)
      private Integer num;
    
      @ExcelProperty(value = "时间",index = 6)
      @DateTimeFormat("yyyy-MM-dd hh:mm:ss")
      private String time;
    
    }

#### 2.2 Controller ####

package com.example.controller;
    
    import com.example.service.UserService;
    import com.example.utils.easyexcel.EasyExcel;
    import com.example.utils.easyexcel.EasyExcelUtils;
    import com.example.vo.User;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.web.bind.annotation.GetMapping;
    import org.springframework.web.bind.annotation.RequestMapping;
    import org.springframework.web.bind.annotation.RestController;
    
    import javax.servlet.http.HttpServletResponse;
    import java.io.IOException;
    import java.util.List;
    
    /** * 描述: * Excel测试 * * @author XueGuCheng * @create 2021-03-20 22:27 */
    @RestController
    @RequestMapping("Excel")
    public class ExcelController { 
    
        @Autowired
        private UserService userService;
    
        // http://localhost:8081/Excel/EasyExce
        @GetMapping("/EasyExce")
        public void doExport(HttpServletResponse response) throws IOException { 
            List<User> users = userService.ListUser();
            EasyExcelUtils.exportByExcel(response,users,"EasyExcel测试",User.class);
        }
    
    
    }

## 3. 效果图 ##

第一列是空，因为@ExcelProperty注解的index是从o开始的

部分数据没有，是因为数据库就没有  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h1ZWd1Y2hlbg_size_16_color_FFFFFF_t_70]

## 4. 总结 ##

1.  实体类需要标明注释
2.  时间类字段转换（可以自定义转换器）
3.  ExcelWriter必须要finish才会写入，不finish只会创建empty的文件
4.  jar包版本冲突问题，报某些方法或者类找不到（升级版本即可）

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h1ZWd1Y2hlbg_size_16_color_FFFFFF_t_70]: /images/20221022/ef1a7ed0ec324551badd51800012c540.png