SpringBoot使用EasyExcel导出Excel

古城微笑少年丶 2022-01-07 06:09 434阅读 0赞

## 源码 ##

**GitHub：** https://github.com/291685399/springboot-learning/tree/master/springboot-easyexcel01

## EasyExcel是什么？ ##

Java解析、生成Excel比较有名的框架有Apache poi、jxl。但他们都存在一个严重的问题就是非常的耗内存，poi有一套SAX模式的API可以一定程度的解决一些内存溢出的问题，但POI还是有一些缺陷，比如07版Excel解压缩以及解压后存储都是在内存中完成的，内存消耗依然很大。easyexcel重写了poi对07版Excel的解析，能够原本一个3M的excel用POI sax依然需要100M左右内存降低到KB级别，并且再大的excel不会出现内存溢出，03版依赖POI的sax模式。在上层做了模型转换的封装，让使用者更加简单方便

## EasyExcel和POI的对比 ##

1、 相比于POI，EasyExcel简化了开发量，能够用更少的代码实现更多的功能  
2、 相比于POI，EasyExcel使用简单  
3、 相比于POI，EasyExcel能够使用更少的内存占用

## 使用EasyExcel导出或下载Excel ##

**pom.xml：**

<dependencies>
        
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-devtools</artifactId>
            <scope>runtime</scope>
            <optional>true</optional>
        </dependency>
    
        
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.0.1</version>
        </dependency>
    
        
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
    
        
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
    
        
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>1.1.2-beat1</version>
        </dependency>
    </dependencies>

**UserController：**

@RestController
    public class UserController { 
    
        @Autowired
        private UserService userService;
    
        /** * 导出Excel */
        @RequestMapping(value = "/exportExcel")
        public void exportExcel() { 
            ExcelWriter writer = null;
            OutputStream out = null;
            try { 
                List userList = userService.findAll();
                String fileName = "用户信息表格";
                out = new FileOutputStream("C:/Users/EDZ/Desktop/" + fileName + ".xlsx");
                writer = new ExcelWriter(out, ExcelTypeEnum.XLSX);
                Sheet sheet = new Sheet(1, 0, User.class);
                sheet.setSheetName("用户信息");
                writer.write(userList, sheet);
                out.flush();
            } catch (Exception e) { 
                e.printStackTrace();
            } finally { 
                if (writer != null) { 
                    writer.finish();
                }
                if (out != null) { 
                    try { 
                        out.close();
                    } catch (IOException e) { 
                        e.printStackTrace();
                    }
                }
            }
        }
    
        /** * 下载Excel * * @param response */
        @RequestMapping(value = "/uploadExcel")
        public void uploadExcel(HttpServletResponse response) { 
            ExcelWriter writer = null;
            OutputStream out = null;
            try { 
                List userList = userService.findAll();
                out = response.getOutputStream();
                writer = new ExcelWriter(out, ExcelTypeEnum.XLSX);
                String fileName = "用户信息表格";
                Sheet sheet = new Sheet(1, 0, User.class);
                sheet.setSheetName("用户信息");
                writer.write(userList, sheet);
                response.setCharacterEncoding("utf-8");
                response.setContentType("application/vnd.ms-excel;charset=utf-8");
                response.setHeader("Content-Disposition", "attachment;filename=" + new String((fileName + ".xlsx").getBytes(), "ISO8859-1"));
                out.flush();
            } catch (Exception e) { 
                e.printStackTrace();
            } finally { 
                if (writer != null) { 
                    writer.finish();
                }
                if (out != null) { 
                    try { 
                        out.close();
                    } catch (IOException e) { 
                        e.printStackTrace();
                    }
                }
            }
        }
    }

**UserService：**

public interface UserService { 
    
        /** * 查询所有用户 */
        public List<User> findAll();
    
    }

**UserServiceImpl：**

@Service
    public class UserServiceImpl implements UserService { 
    
        @Autowired
        private UserMapper userMapper;
    
        @Override
        public List<User> findAll() { 
            return userMapper.findAll();
        }
    
    }

**UserMapper：**

@Mapper
    public interface UserMapper { 
    
        /** * 查询所有用户 */
        public List<User> findAll();
    
    }

**UserMapper.xml：**

<?xml version="1.0" encoding="UTF-8"?>
    <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
    <mapper namespace="com.wyj.mapper.UserMapper">
    
        <select id="findAll" resultType="com.wyj.entity.po.User">
            select * from user
        </select>
    
    </mapper>

**User：**

@Data
    public class User extends BaseRowModel implements Serializable { 
    
        //通过 @ExcelProperty 注解与 index 变量可以标注成员变量所映射的列
        @ExcelProperty(value = "序号", index = 0)
        private int id;
        @ExcelProperty(value = "姓名", index = 1)
        private String name;
        @ExcelProperty(value = "性别", index = 2)
        private String sex;
        @ExcelProperty(value = "年龄", index = 3)
        private int age;
        @ExcelProperty(value = "地址", index = 4)
        private String address;
        @ExcelProperty(value = "联系方式", index = 5)
        private String phone;
    
    }

**application.properties：**

#tomcat port
    server.port=8080
    #datasource
    spring.datasource.driver-class-name=com.mysql.jdbc.Driver
    spring.datasource.url=jdbc:mysql://127.0.0.1:3306/springboot-easyexcel01?allowMultiQueries=true&useUnicode=true&useSSL=false&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull
    spring.datasource.username=root
    spring.datasource.password=root
    #mybatis
    mybatis.mapper-locations=classpath*:mapper/*.xml mybatis.type-aliases-package=com.wyj.entity