【SpringBoot高级篇】SpringBoot整合POI和EasyExcel报表技术

Excel说明

在企业级应用开发中，Excel报表是一种最常见的报表需求。Excel报表开发一般分为两种形式：
1、为了方便操作，基于Excel的报表批量上传数据，也就是把Excel中的数据导入到系统中。
2、通过java代码生成Excel报表。也就是把系统中的数据导出到Excel中，方便查阅。

Excel的两种版本

目前世面上的Excel分为两个大的版本Excel2003和Excel2007及以上两个版本；
两者之间的区别如下：

Excel2003 是一个特有的二进制格式，其核心结构是复合文档类型的结构，存储数据量较小；

Excel2007 的核心结构是 XML 类型的结构，采用的是基于 XML 的压缩方式，使其占用的空间更小， 操作效率更高

常见的Excel操作工具

Java中常见的用来操作Excel的方式一般有2种：JXL和POI。

1、JXL只能对Excel进行操作,属于比较老的框架，它只支持到Excel 95-2000的版本。现在已经停止更新和
维护

2、POI是apache的项目,可对微软的Word,Excel,PPT进行操作,包括office2003和2007,Excle2003和2007。

Apache POI是Apache软件基金会的开源项目，由Java编写的免费开源的跨平台的 Java API，Apache
POI提供API给Java语言操作Microsoft Office的功能。

POI API 对象介绍

excel名称	JAVA对象	对应版本
工作簿	WorkBook	（HSSFWordBook ： 2003版本，XSSFWorkBook ： 2007级以上）
工作表	Sheet	（HSSFWordBook ： 2003版本，XSSFWorkBook ： 2007级以上）
行	Row	（HSSFRow ： 2003版本，XSSFRow ： 2007级以上）
单元格	Cell	（HSSFCell ： 2003版本，XSSFCell ： 2007级以上）

1、HSSFWorkbook : 这个实现类是我们早期使用最多的对象，它可以操作Excel2003以前（包含2003）的所有Excel版本。在2003以前Excel的版本后缀还是.xls

它是POI版本中最常用的方式,不过: 它的缺点是 最多只能导出 65535行,也就是导出的数据函数超过这个数据就会报错; 它的优点是 不会报内存溢出。（因为数据量还不到7w所以内存一般都够用，首先你得明确知道这种方式是将数据先读取到内存中，然后再操作）

2、XSSFWorkbook : 这个实现类现在在很多公司都可以发现还在使用，它是操作的Excel2003–Excel2007之间的版本，Excel的扩展名是.xlsx

优点：这种形式的出现是为了突破HSSFWorkbook的65535行局限，是为了针对Excel2007版本的1048576行，16384列，最多可以导出104w条数据；
缺点：伴随的问题来了，虽然导出数据行数增加了好多倍，但是随之而来的内存溢出问题也成了噩梦。因为你所创建的book，Sheet，row，cell等在写入到Excel之前，都是存放在内存中的（这还没有算Excel的一些样式格式等等），可想而知，内存不溢出就有点不科学了！

3、SXSSFWorkbook : 这个实现类是POI3.8之后的版本才有的,它可以操作Excel2007以后的所有版本Excel,扩展名是.xlsx

从POI 3.8版本开始，提供了一种基于XSSF的低内存占用的SXSSF方式;
优点：
这种方式不会一般不会出现内存溢出（它使用了硬盘来换取内存空间，也就是当内存中数据达到一定程度这些数据会被持久化到硬盘中存储起来，而内存中存的都是最新的数据），并且支持大型Excel文件的创建（存储百万条数据绰绰有余）。
缺点：
既然一部分数据持久化到了硬盘中，且不能被查看和访问那么就会导致，在同一时间点我们只能访问一定数量的数据，也就是内存中存储的数据;
sheet.clone()方法将不再支持,还是因为持久化的原因;
不再支持对公式的求值，还是因为持久化的原因，在硬盘中的数据没法读取到内存中进行计算；
在使用模板方式下载数据的时候，不能改动表头，还是因为持久化的问题，写到了硬盘里就不能改变了；

POI简单操作excel

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

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>4.0.1</version>
</dependency>

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml-schemas</artifactId>
    <version>4.0.1</version>
</dependency>

在POI包中有如下几个主要对象和excel的几个对象对应：

对应excel名称	低版本中的类名	高版本中的类名
工作簿	HSSFWorkbook	XSSFWorkbook
工作表	HSSFSheet	XSSFSheet
行	HSSFRow	XSSFRow
单元格	HSSFCell	XSSFCell
单元格样式	HSSFCellStyle	XSSFCellStyle

操作低版本

public class POIDemo01 {
    public static void main(String[] args) throws Exception{
        Workbook workbook = new HSSFWorkbook(); //创建了一个全新（里面什么都没有）的工作薄
        Sheet sheet = workbook.createSheet("demo测试");  //创建了一个全新（里面什么都没有）的工作表
        Row row = sheet.createRow(0);  //创建了第一行（空的）
        Cell cell = row.createCell(0);//创建的是第一行的第一个单元格
        cell.setCellValue("这是我第一次玩POI");
        //        把工作薄输出到本地磁盘
        workbook.write(new FileOutputStream("d://test.xls"));
    }
}

操作高版本

public class POIDemo02 {
    public static void main(String[] args) throws Exception{
        Workbook workbook = new XSSFWorkbook(); //创建了一个全新（里面什么都没有）的工作薄
        Sheet sheet = workbook.createSheet("demo测试");  //创建了一个全新（里面什么都没有）的工作表
        Row row = sheet.createRow(0);  //创建了第一行（空的）
        Cell cell = row.createCell(0);//创建的是第一行的第一个单元格
        cell.setCellValue("这是我第一次玩POI");
        //        把工作薄输出到本地磁盘
        workbook.write(new FileOutputStream("d://test.xlsx"));
    }
}

POI实现用户数据的导入

  <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>
        
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
        </dependency>

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

        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.0.1</version>
        </dependency>

        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml-schemas</artifactId>
            <version>4.0.1</version>
        </dependency>
    </dependencies>

@RestController
@RequestMapping("/poi")
public class PoiController {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @PostMapping(value = "/upload", name = "POI实现用户数据的导入")
    public String upload(@RequestParam("file") MultipartFile file) throws IOException, ParseException {

        uploadExcel(file);

        return "ok";
    }

    void uploadExcel(MultipartFile file) throws IOException, ParseException {
        Workbook workbook = new XSSFWorkbook(file.getInputStream()); //根据上传的输入流创建workbook
        Sheet sheet = workbook.getSheetAt(0); //获取工作薄中的第一个工作表
        int lastRowIndex = sheet.getLastRowNum(); //获取这个sheet中最后一行数据，为了循环遍历

        // 以下三个为了节省栈内存，所以提到循环的外面

        ArrayList<User> userList = new ArrayList<>();
        Row row = null;
        Cell cell = null;

        for (int i = 1; i < lastRowIndex; i++) {
            row = sheet.getRow(i);
            User user = new User();
            user.setId(UUID.randomUUID().toString().replace("-",""));
            // 因为第一个列单元格中是字符串，可以直接使用getStringCellValue方法
            String userName = row.getCell(0).getStringCellValue(); //用户名
            user.setUserName(userName);

            String phone = null; //手机号
            try {
                phone = row.getCell(1).getStringCellValue();
            } catch (IllegalStateException e) {
                phone = row.getCell(1).getNumericCellValue()+"";
            }
            user.setPhone(phone);
            String province = row.getCell(2).getStringCellValue(); //省份
            user.setProvince(province);

            String city = row.getCell(3).getStringCellValue(); //城市
            user.setCity(city);

            // 因为在填写excel中的数据时就可以约定这个列只能填写数值，所以可以直接用getNumericCellValue方法
            Integer salary = ((Double)row.getCell(4).getNumericCellValue()).intValue(); //工资
            user.setSalary(salary);
            String hireDateStr = row.getCell(5).getStringCellValue(); //入职日期
            SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
            Date hireDate = simpleDateFormat.parse(hireDateStr);
            user.setHireDate(hireDate);
            String birthdayStr = row.getCell(6).getStringCellValue(); //出生日期
            Date birthday = simpleDateFormat.parse(birthdayStr);
            user.setBirthday(birthday);

            String address = row.getCell(7).getStringCellValue(); //现住地址
            user.setAddress(address);
            userList.add(user);
        }
        String insertSql = "insert into tb_user3(id,user_name,phone,province,city,salary,hire_date,birthday,address) values(?,?,?,?,?,?,?,?,?)";
        jdbcTemplate.batchUpdate(insertSql, new BatchPreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                User user = userList.get(i);
                ps.setString(1,user.getId());
                ps.setString(2,user.getUserName());
                ps.setString(3,user.getPhone());
                ps.setString(4,user.getProvince());
                ps.setString(5,user.getCity());
                ps.setLong(6,user.getSalary());
                ps.setDate(7,new java.sql.Date(user.getHireDate().getTime()));
                ps.setDate(8,new java.sql.Date(user.getBirthday().getTime()));
                ps.setString(9,user.getAddress());
            }

            @Override
            public int getBatchSize() {
                return userList.size();
            }
        });
    }
}

POI实现用户数据的导出

@GetMapping(value = "/export", name = "POI实现用户数据的导出")
public String export(HttpServletResponse response) throws IOException {
    exportExcel(response);
    return "ok";
}

void exportExcel(HttpServletResponse response) throws IOException {
    // 创建一个空的工作薄
    Workbook workbook = new XSSFWorkbook();
    // 在工作薄中创建一个工作表
    Sheet sheet = workbook.createSheet("user");
    // 设置列宽
    sheet.setColumnWidth(0,5*256);
    sheet.setColumnWidth(1,8*256);
    sheet.setColumnWidth(2,15*256);
    sheet.setColumnWidth(3,15*256);
    sheet.setColumnWidth(4,30*256);

    //  处理标题
    String[] titles = new String[]{"编号","姓名","手机号","入职日期","现住址"};

    //  创建标题行
    Row titleRow = sheet.createRow(0);
    Cell cell = null;
    for (int i = 0; i < titles.length; i++) {
        cell = titleRow.createCell(i);
        cell.setCellValue(titles[i]);
    }

    String querySQL = "select id, user_name userName,phone,hire_date hireDate,address from tb_user3";

    //  处理内容
    List<User> userList = jdbcTemplate.query(querySQL, new BeanPropertyRowMapper<>(User.class));
    int rowIndex = 1;
    Row row = null;
    for (User user : userList) {
        row = sheet.createRow(rowIndex);
        cell = row.createCell(0);
        cell.setCellValue(user.getId());

        cell = row.createCell(1);
        cell.setCellValue(user.getUserName());

        cell = row.createCell(2);
        cell.setCellValue(user.getPhone());

        cell = row.createCell(3);
        cell.setCellValue(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(user.getHireDate()));

        cell = row.createCell(4);
        cell.setCellValue(user.getAddress());

        rowIndex++;
    }
    // 导出的文件名称
    String filename="员工数据.xlsx";
    // 设置文件的打开方式和mime类型
    ServletOutputStream outputStream = response.getOutputStream();
    response.setHeader( "Content-Disposition", "attachment;filename="  + new String(filename.getBytes(),"ISO8859-1"));
    response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
    workbook.write(outputStream);
}

POI 封装工具类

pom

<dependencies>
   <dependency>
       <groupId>org.springframework.boot</groupId>
       <artifactId>spring-boot-starter-web</artifactId>
   </dependency>

   <!-- POI start-->
   <dependency>
       <groupId>org.apache.poi</groupId>
       <artifactId>poi</artifactId>
       <version>4.0.1</version>
   </dependency>

   <dependency>
       <groupId>org.apache.poi</groupId>
       <artifactId>poi-ooxml</artifactId>
       <version>4.0.1</version>
   </dependency>

   <dependency>
       <groupId>org.apache.poi</groupId>
       <artifactId>poi-ooxml-schemas</artifactId>
       <version>4.0.1</version>
   </dependency>
   <!-- POI end-->

   <dependency>
       <groupId>mysql</groupId>
       <artifactId>mysql-connector-java</artifactId>
   </dependency>

   <dependency>
       <groupId>com.baomidou</groupId>
       <artifactId>mybatis-plus-boot-starter</artifactId>
       <version>3.4.3</version>
   </dependency>

   <dependency>
       <groupId>com.alibaba</groupId>
       <artifactId>druid-spring-boot-starter</artifactId>
       <version>1.2.14</version>
   </dependency>

   <dependency>
       <groupId>org.apache.commons</groupId>
       <artifactId>commons-lang3</artifactId>
   </dependency>
   <dependency>
       <groupId>org.projectlombok</groupId>
       <artifactId>lombok</artifactId>
   </dependency>
</dependencies>

application.yml

# DataSource Config
spring:
  datasource:
    #   数据源基本配置
    url: jdbc:mysql://localhost:3306/study_db?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&nullCatalogMeansCurrent=true
    username: root
    password: root
    driver-class-name: com.mysql.cj.jdbc.Driver
    initialization-mode: always #表示始终都要执行初始化，2.x以上版本需要加上这行配置
    type: com.alibaba.druid.pool.DruidDataSource
    #   数据源其他配置
    initialSize: 5
    minIdle: 5
    maxActive: 20
    maxWait: 60000
    timeBetweenEvictionRunsMillis: 60000
    minEvictableIdleTimeMillis: 300000
    validationQuery: SELECT 1 FROM DUAL
    testWhileIdle: true
    testOnBorrow: false
    testOnReturn: false
    poolPreparedStatements: true
    #   配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙
    filters: stat,wall,log4j
    maxPoolPreparedStatementPerConnectionSize: 20
    useGlobalDataSourceStat: true
    connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=500

# Logger Config
logging:
  level:
    cn.zysheep.mapper: debug

业务代码

1、UserMapper

public interface UserMapper extends BaseMapper<User> {

}

2、UserService

public interface UserService extends IService<User> {

}

3、UserServiceImpl

@Service
@Transactional
public class UserServiceImpl extends ServiceImpl<UserMapper, User> implements UserService{
}

User

@TableName(value ="tb_user")
@Data
public class User implements Serializable {
    /**
     * 用户ID
     */
    @ExcelAttribute(sort = 0)
    @TableId(type = IdType.AUTO)
    private Long id;

    /**
     * 姓名
     */
    @ExcelAttribute(sort = 1)
    private String userName;

    /**
     * 手机号
     */
    @ExcelAttribute(sort = 2)
    private String phone;

    /**
     * 省份
     */
    @ExcelAttribute(sort = 3)
    private String province;

    /**
     * 城市
     */
    @ExcelAttribute(sort = 4)
    private String city;

    /**
     *
     */
    @ExcelAttribute(sort = 5)
    private Integer salary;

    /**
     * 入职日期
     */
    @ExcelAttribute(sort = 6)
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss",timezone = "GMT+8")
    private Date hireDate;

    /**
     * 部门编号
     */
    @ExcelAttribute(sort = 7)
    private Long deptId;

    /**
     * 出生日期
     */
    @ExcelAttribute(sort = 8)
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss",timezone = "GMT+8")
    private Date birthday;

    /**
     * 照片路径
     */
    @ExcelAttribute(sort = 9)
    private String photo;

    /**
     * 现在住址
     */
    @ExcelAttribute(sort = 10)
    private String address;
}

@ExcelAttribute

@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.FIELD)
public @interface ExcelAttribute {
    /** 对应的列名称 */
    String name() default "";

    /** 列序号 */
    int sort();

    /** 字段类型对应的格式 */
    String format() default "";
}

ExcelExportUtil

/**
 * @ClassName: ExcelExportUtil
 * @Description: POI导出工具类
 */
@Getter
@Setter
public class ExcelExportUtil<T> {

    private int rowIndex;
    private int styleIndex;
    private String templatePath;
    private Class clazz;
    private Field fields[];

	 /**
     * @param clazz 解析对应实体的class类型
     * @param rowIndex excel第几行开始写信息,从0开始
     * @param styleIndex excel第几行样式,从0开始
     */
    public ExcelExportUtil(Class clazz,int rowIndex,int styleIndex) {
        this.clazz = clazz;
        this.rowIndex = rowIndex;
        this.styleIndex = styleIndex;
        fields = clazz.getDeclaredFields();
    }

    /**
     * 基于注解导出
     */
    public void export(HttpServletResponse response, InputStream is, List<T> objs, String fileName) throws Exception {

        XSSFWorkbook workbook = new XSSFWorkbook(is);
        Sheet sheet = workbook.getSheetAt(0);

        CellStyle[] styles = getTemplateStyles(sheet.getRow(styleIndex));

        AtomicInteger datasAi = new AtomicInteger(rowIndex);
        for (T t : objs) {
            Row row = sheet.createRow(datasAi.getAndIncrement());
            for(int i=0;i<styles.length;i++) {
                Cell cell = row.createCell(i);
                cell.setCellStyle(styles[i]);
                for (Field field : fields) {
                    if(field.isAnnotationPresent(ExcelAttribute.class)){
                        field.setAccessible(true);
                        ExcelAttribute ea = field.getAnnotation(ExcelAttribute.class);
                        if(i == ea.sort()) {
                            if (field.get(t) != null){
                                typeConvert(field, t, cell);
                            }
                        }
                    }
                }
            }
        }
        fileName = URLEncoder.encode(fileName, "UTF-8");
        response.setContentType("application/octet-stream");
        response.setHeader("content-disposition", "attachment;filename=" + new String(fileName.getBytes("ISO8859-1")));
        response.setHeader("filename", fileName);
        workbook.write(response.getOutputStream());
    }

    private void typeConvert(Field field, T t, Cell cell) throws IllegalAccessException {
        String type = field.getType().getName();
        if (StringUtils.equals(type, "java.util.Date")) {
            cell.setCellValue(DateFormatUtils.format((Date)field.get(t), "yyyy-MM-dd hh:mm:ss"));
        } else {
            cell.setCellValue(field.get(t).toString());
        }
    }


    public CellStyle[] getTemplateStyles(Row row) {
        CellStyle [] styles = new CellStyle[row.getLastCellNum()];
        for(int i=0; i<row.getLastCellNum(); i++) {
            styles[i] = row.getCell(i).getCellStyle();
        }
        return styles;
    }
}

ExcelImportUtil

/**
 * @ClassName: ExcelImportUtil
 * @Description: POI导入工具类
 */
public class ExcelImportUtil<T> {

    private Class clazz;
    private Field fields[];

    public ExcelImportUtil(Class clazz) {
        this.clazz = clazz;
        fields = clazz.getDeclaredFields();
    }

    /**
     * 基于注解读取excel
     *
     * @param file      文件输入流
     * @param rowIndex  excel第几行开始解析,从0开始
     * @param cellIndex excel第几列单元格开始解析,从0开始
     * @return
     */
    public List<T> readExcel(MultipartFile file, int rowIndex, int cellIndex) {
        List<T> list = new ArrayList<T>();
        T entity = null;
        try {
            Workbook workbook = createWorkbookFactory(file);
            Sheet sheet = workbook.getSheetAt(0);
            // 不准确
            int rowLength = sheet.getLastRowNum();

            for (int rowNum = rowIndex; rowNum <= sheet.getLastRowNum(); rowNum++) {
                Row row = sheet.getRow(rowNum);
                entity = (T) clazz.newInstance();
 				// 不准确
 				// short firstCellNum = row.getFirstCellNum();
                // short lastCellNum = row.getLastCellNum();
                for (int j = cellIndex; j < row.getLastCellNum(); j++) {
                    Cell cell = row.getCell(j);
                    for (Field field : fields) {
                        if (field.isAnnotationPresent(ExcelAttribute.class)) {
                            field.setAccessible(true);
                            ExcelAttribute ea = field.getAnnotation(ExcelAttribute.class);
                            if (j == ea.sort()) {
                                field.set(entity, covertAttrType(field, cell));
                            }
                        }
                    }
                }
                list.add(entity);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }


    /**
     * 类型转换 将cell 单元格格式转为 字段类型
     */
    private Object covertAttrType(Field field, Cell cell) throws ParseException {
        String fieldType = field.getType().getSimpleName();
        if ("String".equals(fieldType)) {
            return getValue(cell);
        } else if ("Date".equals(fieldType)) {
            return new SimpleDateFormat("yyyy-MM-dd hh:mm:ss").parse(getValue(cell));
        } else if ("int".equals(fieldType) || "Integer".equals(fieldType)) {
            return Integer.parseInt(getValue(cell));
        } else if ("double".equals(fieldType) || "Double".equals(fieldType)) {
            return Double.parseDouble(getValue(cell));
        } else if ("long".equals(fieldType) || "Long".equals(fieldType)) {
            return Long.parseLong(getValue(cell));
        } else if ("BigDecimal".equals(fieldType)) {
            return BigDecimal.valueOf(Double.parseDouble(getValue(cell)));
        } else {
            return null;
        }
    }


    /**
     * 格式转为String
     *
     * @param cell
     * @return
     */
    public String getValue(Cell cell) {
        if (cell == null) {
            return "";
        }
        switch (cell.getCellType()) {
            case STRING:
                return cell.getRichStringCellValue().getString().trim();
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    Date dt = DateUtil.getJavaDate(cell.getNumericCellValue());
                    return new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(dt);
                } else {
                    // 防止数值变成科学计数法
                    String strCell = "";
                    Double num = cell.getNumericCellValue();
                    BigDecimal bd = new BigDecimal(num.toString());
                    if (bd != null) {
                        strCell = bd.toPlainString();
                    }
                    // 去除 浮点型 自动加的 .0
                    if (strCell.endsWith(".0")) {
                        strCell = strCell.substring(0, strCell.indexOf("."));
                    }
                    return strCell;
                }
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            default:
                return "";
        }
    }

    /**
     * 根据文件名，解析后缀返回不同的Workbook对象
     *
     * @return Excel文档对象Workbook
     */
    private Workbook createWorkbookFactory(MultipartFile file) throws Exception {
        String originalFilename = file.getOriginalFilename();
        //得到文件后缀
        String suffix = originalFilename.substring(originalFilename.lastIndexOf("."));

        InputStream is = file.getInputStream();
        if (".xls".equals(suffix)) {
            return new HSSFWorkbook(is);
        } else if (".xlsx".equals(suffix)) {
            return new XSSFWorkbook(is);
        } else {
            throw new Exception("文件类型错误！");
        }
    }
}

short firstCellNum = row.getFirstCellNum();
short lastCellNum = row.getLastCellNum();

• row.getFirstCellNum() 方法返回的是该行中第一个单元格的索引值，即第一个实际存在的单元格的索引值。它表示该行中第一个非空单元格的位置。如excel的第一列为空，则row.getFirstCellNum()为1，默认为0

• row.getLastCellNum() 方法返回的是该行中最后一个单元格的索引值，它并不准确表示该行中的实际单元格数量。因为 getLastCellNum() 方法只计算最后一个非空单元格的索引值，并不会计算中间空的单元格。 如excel的最后一列为空，模板列为5，则row.getLastCellNum()为4

所以用row.getFirstCellNum()做为起始列索引，row.getLastCellNum()做为最终列索引，如果excel的前列不填或者后列不填的情况下，可能会出现数组下标越界异常。

DownloadUtils

/**
 * @ClassName: DownloadUtils
 * @Description: 下载工具类
 */
public class DownloadUtils {
    public void download(ByteArrayOutputStream byteArrayOutputStream, HttpServletResponse response, String returnName) throws IOException {
        response.setContentType("application/octet-stream");
        returnName = response.encodeURL(new String(returnName.getBytes(),"iso8859-1"));			//保存的文件名,必须和页面编码一致,否则乱码
        response.addHeader("Content-Disposition","attachment;filename=" + returnName);
        response.setContentLength(byteArrayOutputStream.size());
        ServletOutputStream outputstream = response.getOutputStream();	//取得输出流
        byteArrayOutputStream.writeTo(outputstream);					//写到输出流
        byteArrayOutputStream.close();									//关闭
        outputstream.flush();											//刷数据
    }
}

UserController

@RestController
@RequestMapping(value = "/api")
public class UserController {

    private static final Logger log = LoggerFactory.getLogger(UserController.class);

    @Autowired
    private UserService userService;

    /**
     * 导入Excel,添加用户
     */
    @PostMapping(value = "/user/import")
    public ResponseEntity importUser(@RequestParam(name = "file") MultipartFile file) throws Exception {
        List<User> list = new ExcelImportUtil<User>(User.class).readExcel(file, 1, 0);
        //3.批量保存用户
        list.forEach(System.out::println);
        return ResponseEntity.ok().build();
    }


    /**
     *  采用模版打印的形式完成报表导出,sxssf不支持模版导出
     *  工具类导出
     */
    @GetMapping(value = "/exportUtil")
    public void exportUtil(HttpServletResponse response) throws Exception {
        //1.构造报表数据
        List<User> list = userService.list();

        //2.加载模版
        Resource resource = new ClassPathResource("excel-template/用户信息.xlsx");
        FileInputStream fis = new FileInputStream(resource.getFile());

        //3.通过工具类下载文件
        new ExcelExportUtil<User>(User.class , 2, 2).export(response , fis , list ,   "用户信息报表.xlsx");
    }

    /**
     *  采用模版打印的形式完成报表导出
     *  编码导出
     */
    @GetMapping(value = "/export")
    public void export(HttpServletResponse response) throws Exception {
        //1.构造报表数据
        List<User> list = userService.list();

        //2.加载模版
        Resource resource = new ClassPathResource("excel-template/用户信息.xlsx");
        FileInputStream fis = new FileInputStream(resource.getFile());

        // 3.根据模板创建工作簿
        Workbook wb = new XSSFWorkbook(fis);
        //4.读取工作表
        Sheet sheet = wb.getSheetAt(0);
        //5.抽取公共样式
        Row row = sheet.getRow(2);
        CellStyle styles[] = new CellStyle[row.getLastCellNum()];
        for (int i = 0;i < row.getLastCellNum(); i++){
            Cell cell = row.getCell(i);
            styles[i] = cell.getCellStyle();
        }
        //6.构造单元格
        int rowIndex = 2;
        Cell cell = null;

        for (User user : list) {
            row = sheet.createRow(rowIndex++);
            //编号,
            cell = row.createCell(0);
            cell.setCellValue(user.getId());
            cell.setCellStyle(styles[0]);
            //姓名,
            cell = row.createCell(1);
            cell.setCellValue(user.getUserName());
            cell.setCellStyle(styles[1]);
            //手机,
            cell = row.createCell(2);
            cell.setCellValue(user.getPhone());
            cell.setCellStyle(styles[2]);
            //省份,
            cell = row.createCell(3);
            cell.setCellValue(user.getProvince());
            cell.setCellStyle(styles[3]);
            //城市,
            cell = row.createCell(4);
            cell.setCellValue(user.getCity());
            cell.setCellStyle(styles[4]);
            //工资,
            cell = row.createCell(5);
            cell.setCellValue(user.getSalary());
            cell.setCellStyle(styles[5]);
            //入职日期,
            cell = row.createCell(6);
            cell.setCellValue(DateFormatUtils.format(user.getHireDate(), "yyyy-MM-dd hh:mm:ss"));
            cell.setCellStyle(styles[6]);
            //部门编号,
            cell = row.createCell(7);
            cell.setCellValue(Objects.isNull(user.getDeptId())? "" : user.getDeptId().toString());
            cell.setCellStyle(styles[7]);
            //出生日期,
            cell = row.createCell(8);
            cell.setCellValue(DateFormatUtils.format(user.getBirthday(), "yyyy-MM-dd hh:mm:ss"));
            cell.setCellStyle(styles[8]);
            //照片路径,
            cell = row.createCell(9);
            cell.setCellValue(Objects.isNull(user.getPhoto())? "" : user.getPhoto().toString());
            cell.setCellStyle(styles[9]);
            //现在住址,
            cell = row.createCell(10);
            cell.setCellValue(user.getAddress());
            cell.setCellStyle(styles[10]);
        }

        //7.下载
        ByteArrayOutputStream baos = new ByteArrayOutputStream();
        wb.write(baos);
        new DownloadUtils().download(baos , response ,   "用户信息报表.xlsx");
    }
}

导出测试

excel-template/用户信息.xlsx模板

导出数据

	/**
	 *  采用模版打印的形式完成报表导出,sxssf不支持模版导出
	 *  工具类导出
	 */
	@GetMapping(value = "/exportUtil")
	public void exportUtil(HttpServletResponse response) throws Exception {
	    //1.构造报表数据
	    List<User> list = userService.list();
	
	    //2.加载模版
	    Resource resource = new ClassPathResource("excel-template/用户信息.xlsx");
	    FileInputStream fis = new FileInputStream(resource.getFile());
	
	    //3.通过工具类下载文件
	    new ExcelExportUtil<User>(User.class , 2, 2).export(response , fis , list ,   "用户信息报表.xlsx");
	}

导入测试

导入数据

  /**
     * 导入Excel,添加用户
     */
    @PostMapping(value = "/user/import")
    public ResponseEntity importUser(@RequestParam(name = "file") MultipartFile file) throws Exception {
        List<User> list = new ExcelImportUtil<User>(User.class).readExcel(file.getInputStream(), 1, 0);
        //3.批量保存用户
        list.forEach(System.out::println);
        return ResponseEntity.ok().build();
    }

EasyExcel介绍

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

EasyExcel是一个基于Java的简单、省内存的读写Excel的开源项目。在尽可能节约内存的情况下支持读写百M的Excel。

文档地址

导出示例

写Excel 示例链接

导入示例

读Excel 示例链接

EasyExcel集成

• springboot
• mp
• easyexcel

添加依赖

<dependencies>
   <!-- https://mvnrepository.com/artifact/com.alibaba/easyexcel -->
   <dependency>
       <groupId>com.alibaba</groupId>
       <artifactId>easyexcel</artifactId>
       <version>2.1.1</version>
   </dependency>
</dependencies>

写操作

本地写

UserData实体类

@Data
public class UserData {

    @ExcelProperty(value = "用户编号",index = 0)
    private int uid;

    @ExcelProperty(value = "用户名称",index = 1)
    private String username;

}

public class  TestWrite {

    public static void main(String[] args) {
        //构建数据list集合
        List<UserData> list = new ArrayList();
        for (int i=0;i<10;i++) {
            UserData data = new UserData();
            data.setUid(i);
            data.setUsername("lucy"+i);
            list.add(data);
        }

        //设置excel文件路径和文件名称
        String fileName = "E:\\excel\\";
        File file = new File(fileName);
        if (!file.exists()) {
           file.mkdirs();
        }

        //调用方法实现写操作
        EasyExcel.write(fileName+"01.xlsx", UserData.class).sheet("用户信息")
                .doWrite(list);
    }
}

web中的写

DictEeVo 实体对象

@Data
public class DictEeVo {

@ExcelProperty(value = "id",index = 0)
private Long id;

@ExcelProperty(value = "上级id",index = 1)
private Long parentId;

@ExcelProperty(value = "名称",index = 2)
private String name;

@ExcelProperty(value = "值",index = 3)
private String value;

@ExcelProperty(value = "编码",index = 4)
private String dictCode;

}

/**
 * 文件下载（失败了会返回一个有部分数据的Excel）
 * <p>
 * 1. 创建excel对应的实体对象 参照{@link DownloadData}
 * <p>
 * 2. 设置返回的 参数
 * <p>
 * 3. 直接写，这里注意，finish的时候会自动关闭OutputStream,当然你外面再关闭流问题不大
 */
@GetMapping("download")
public void download(HttpServletResponse response) throws IOException {
    // 这里注意 有同学反应使用swagger 会导致各种问题，请直接用浏览器或者用postman
   response.setContentType("application/vnd.ms-excel");
      response.setCharacterEncoding("utf-8");
// 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
String fileName = URLEncoder.encode("数据字典", "UTF-8");
      response.setHeader("Content-disposition", "attachment;filename="+ fileName + ".xlsx");

      List<Dict> dictList = dictMapper.selectList(null);
      List<DictEeVo> dictVoList = new ArrayList<>(dictList.size());
	 for(Dict dict : dictList) {
         DictEeVo dictVo = new DictEeVo();
         // 将前者的属性copy给后者，属性名要一样
         BeanUtils.copyBean(dict, dictVo, DictEeVo.class);
         dictVoList.add(dictVo);
      }

      EasyExcel.write(response.getOutputStream(), DictEeVo.class).sheet("数据字典").doWrite(dictVoList);
   } catch (IOException e) {
      e.printStackTrace();
   }
}

读操作

读取本地文件

创建监听器

public class ExcelListener extends AnalysisEventListener<UserData> {

    //一行一行读取excel内容，从第二行读取
    @Override
    public void invoke(UserData userData, AnalysisContext analysisContext) {
        System.out.println(userData);
    }

    @Override
    public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
        System.out.println("表头信息："+headMap);
    }

    //读取之后执行
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {

    }
}

public class TestRead {

    public static void main(String[] args) {
        // 读取文件路径
        String fileName = "E:\\excel\\01.xlsx";
        //调用方法实现读取操作
        EasyExcel.read(fileName, UserData.class,new ExcelListener()).sheet().doRead();
    }
}

读取web上传文件写到库中

创建监听器

public interface DictMapper extends BaseMapper<Dict> {

}



public class DictListener extends AnalysisEventListener<DictEeVo> {

    private DictMapper dictMapper;
    public DictListener(DictMapper dictMapper) {
        this.dictMapper = dictMapper;
    }

    //一行一行读取
    @Override
    public void invoke(DictEeVo dictEeVo, AnalysisContext analysisContext) {
        //调用方法添加数据库
        Dict dict = new Dict();
        BeanUtils.copyProperties(dictEeVo,dict);
        dictMapper.insert(dict);
    }
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {

    }
}

@PostMapping("upload")
@ResponseBody
public String upload(MultipartFile file) throws IOException {
      EasyExcel.read(file.getInputStream(),DictEeVo.class,new DictListener(baseMapper)).sheet().doRead();
    return "success";
}

百万数据导出

1.异步处理
2.使用easyexcel
3.分页查询
4.多个sheet
5.计算limit的起始位置
6.文件上传到OSS
7.通过WebSocket推送通知
8.总条数可配置
9.order by商品编号

异步处理

做一个MySQL百万数据级别的excel导出功能，如果走接口同步导出，该接口肯定会非常容易超时。

因此，我们在做系统设计的时候，第一选择应该是接口走异步处理。

说起异步处理，其实有很多种，比如：使用开启一个线程，或者使用线程池，或者使用job，或者使用mq等。

为了防止服务重启时数据的丢失问题，我们大多数情况下，会使用job或者mq来实现异步功能。

使用job

如果使用job的话，需要增加一张执行任务表，记录每次的导出任务。

用户点击全部导出按钮，会调用一个后端接口，该接口会向表中写入一条记录，该记录的状态为：待执行。

有个job，每隔一段时间（比如：5分钟），扫描一次执行任务表，查出所有状态是待执行的记录。

然后遍历这些记录，挨个执行。

需要注意的是：如果用job的话，要避免重复执行的情况。比如job每隔5分钟执行一次，但如果数据导出的功能所花费的时间超过了5分钟，在一个job周期内执行不完，就会被下一个job执行周期执行。

所以使用job时可能会出现重复执行的情况。

为了防止job重复执行的情况，该执行任务需要增加一个执行中的状态。

具体的状态变化如下：

1. 执行任务被刚记录到执行任务表，是待执行状态。
2. 当job第一次执行该执行任务时，该记录再数据库中的状态改为：执行中。
3. 当job跑完了，该记录的状态变成：完成或失败。
   这样导出数据的功能，在第一个job周期内执行不完，在第二次job执行时，查询待处理状态，并不会查询出执行中状态的数据，也就是说不会重复执行。

此外，使用job还有一个硬伤即：它不是立马执行的，有一定的延迟。

如果对时间不太敏感的业务场景，可以考虑使用该方案。

使用mq

用户点击全部导出按钮，会调用一个后端接口，该接口会向mq服务端，发送一条mq消息。

有个专门的mq消费者，消费该消息，然后就可以实现excel的数据导出了。

相较于job方案，使用mq方案的话，实时性更好一些。

对于mq消费者处理失败的情况，可以增加补偿机制，自动发起重试。

RocketMQ自带了失败重试功能，如果失败次数超过了一定的阀值，则会将该消息自动放入死信队列。