easyexcel导出Excel表格

柔情只为你懂 2021-12-01 14:04 663阅读 0赞

easyexcel相比较poi不是一套成熟的框架，但是开发快捷方便，不耗内存，速度快，是alibaba的开源框架，用起来十分简单：

整个excel表是一个workbook，下面有多个sheet，一个sheet有多行row，一行有多个cell

### 效果 ###

![excel][]

### 第一步 引入maven依赖 ###

<dependency>
                <groupId>com.alibaba</groupId>
                <artifactId>easyexcel</artifactId>
                <version>1.1.1</version>
    </dependency>

非正式版本目前更新有1.1.5beat版，生产中还是选正式版1.1.1，api 没有那么丰富。

### 第二步 封装对应实体 ###

a、 需要继承BaseRowModel类，此类map字段可以自定义excel样式

@Data
    public class UserVO extends BaseRowModel implements Serializable {
    
        @ExcelProperty(value = "姓名", index = 0)
        private String name;
    
        @ExcelProperty(value = "昵称", index = 1)
        private String nickName;
    
        @ExcelProperty(value = "幸运数字", index = 2)
        private String password;
    
        @ExcelProperty(value = "生日", index = 3, format = "yyyy/MM/dd")
        private Date birthday;
    }

index 代表第几列，value表头

========================================================

b、封装一个sheet表数据对应实体：

@Data
    public class MultipleSheelPropety implements Serializable {
    
        private List<? extends BaseRowModel> data;
    
        private Sheet sheet;
       }

data字段 为sheet表数据，一行一行的，sheet 字段可以设置顺序，名字等

========================================================

c 、编写excel工具类

@Slf4j
    public class ExcelUtil {
        private static Sheet initSheet;
    
        /**
         * 如果 sheet为空，默认创建一个
         */
        static {
            initSheet = new Sheet(1, 0);
            initSheet.setSheetName("mysheet");
            initSheet.setAutoWidth(Boolean.TRUE);
        }
    
        /**
         * 生成多Sheet的excle
         *
         * @param response              response
         * @param multipleSheelPropetys multipleSheelPropetys
         */
        public static void writeWithMultipleSheel(HttpServletResponse response, List<MultipleSheelPropety> multipleSheelPropetys) {
            if (CollectionUtils.isEmpty(multipleSheelPropetys)) {
                log.error("export data is null");
                return;
            }
            OutputStream outputStream = null;
            ExcelWriter writer = null;
            try {
                outputStream = response.getOutputStream();
                writer = new ExcelWriter(outputStream, ExcelTypeEnum.XLSX, true);
                for (MultipleSheelPropety multipleSheelPropety : multipleSheelPropetys) {
                    Sheet sheet = multipleSheelPropety.getSheet() != null ? multipleSheelPropety.getSheet() : initSheet;
                    if (!CollectionUtils.isEmpty(multipleSheelPropety.getData())) {
                        sheet.setClazz(multipleSheelPropety.getData().get(0).getClass());
                    }
                    writer.write(multipleSheelPropety.getData(), sheet);
                }
            } catch (IOException e) {
                log.error("error la");
            } finally {
                try {
                    Objects.requireNonNull(writer).finish();
                    Objects.requireNonNull(outputStream).close();
                } catch (IOException e) {
                    log.error("excel文件导出失败, 失败原因：{}", e);
                }
            }
    
        }
    }

### 编写controller，service ###

controller：

@RestController
    public class ExcelController {
    
        @Autowired
        private ExcelService excelService;
    
        @RequestMapping(value = "/export", method = RequestMethod.GET)
        public void export(HttpServletResponse response){
            excelService.export(response);
        }
    }

service：

public interface ExcelService {
    
        /**
         * export
         *
         * @param response res
         */
        void export(HttpServletResponse response);
    }

@Service  
public class ExcelServiceImpl implements ExcelService \{

@Autowired
    private UserMapper userMapper;
    
    @Override
    public void export(HttpServletResponse response) {
    
        List list = getExportData();
        response.setCharacterEncoding("utf-8");
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        try {
            response.setHeader("Content-Disposition", "attachment;filename=" + new String(("test.xlsx").getBytes(), "ISO8859-1"));
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        }
    
        ExcelUtil.writeWithMultipleSheel(response, list);
    }
    
    private List getExportData() {
        List list = Lists.newArrayList();
    
        List<UserDto> allUser = userMapper.getAllUser();
    
        MultipleSheelPropety multipleSheelPropety = new MultipleSheelPropety();
        multipleSheelPropety.setData(allUser);
        Sheet sheet = new Sheet(1, 0);
        sheet.setSheetName("sheet1");
        multipleSheelPropety.setSheet(sheet);
    
        list.add(multipleSheelPropety);
    
        MultipleSheelPropety multipleSheelPropety1 = new MultipleSheelPropety();
        Sheet sheet1 = new Sheet(2, 0);
        sheet1.setSheetName("sheet2");
        multipleSheelPropety1.setSheet(sheet1);
        multipleSheelPropety1.setData(allUser);
    
        list.add(multipleSheelPropety1);
        return list;
    }

数据随意填写

### 测试导出接口 ###

![导出成功][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTQ0OTI2NjU_size_16_color_FFFFFF_t_70]

[excel]: /images/20211201/30bcf082abcf4660aa48539eef1fd1cf.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTQ0OTI2NjU_size_16_color_FFFFFF_t_70]: /images/20211201/7bf03c63c22d40168db0650e2568008c.png