【SpringBoot+poi实现excel文档导出（一）】

SpringBoot+poi实现excel文档导出（一）

使用SpringBoot+poi实现excel文档导出，打算分为三篇书写，第一篇基于if-else导出，第二篇提取部分方法作为抽象方法导出，第三篇为全抽象导出。

一、基于if-else导出

众所周知，行业内有一句黑话：“没有什么代码不能用if-else开发，如果不行，再套一层！！！”，使用该方式开发简单的程序没有什么问题，但是随着版本的迭代，单纯的if-else开发已经无法满足复杂的业务需求，如果坚持继续往下走，会导致系统卡顿，代码无法渗透等一系列弊端，这时候需要修改一个需求，需要理清全局代码，具体请看这篇博客：为什么我不建议你用if-else。本章将使用if-else编程方式，完成对excel表格导出，废话不多说，直接上代码！！！

1.接口

import com.example.export.vo.ScheduleVo;

import javax.servlet.http.HttpServletResponse;

public interface IExportService {

    /**
     * @Description 导出
     */
    void export(ScheduleVo scheduleVo, HttpServletResponse response) throws IllegalAccessException;
}

2.实现方法

import cn.hutool.core.io.resource.ClassPathResource;
import com.example.export.service.IExportService;
import com.example.export.util.DataExportUtil;
import com.example.export.vo.ResultVo;
import com.example.export.vo.ScheduleVo;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Service;

import javax.servlet.http.HttpServletResponse;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.util.*;

@Service
public class ExportServiceImpl implements IExportService {
   public void export(ScheduleVo scheduleVo, HttpServletResponse response) throws IllegalAccessException {
       //新行的索引
       int i = 1;

       List<ResultVo> scheduleByConditions = findScheduleByConditions(scheduleVo);

       String path = "excel/user.xlsx";

       InputStream inputStream = null;
       SXSSFSheet sheet = null;
       XSSFSheet xssfSheet = null;
       SXSSFWorkbook sworkbook = null;
       XSSFWorkbook workbook = null;
       int cells = 0;
       try {
           ClassPathResource resource = new ClassPathResource(path);
           inputStream = resource.getStream();
           workbook = new XSSFWorkbook(inputStream);
           sworkbook = new SXSSFWorkbook(workbook, -1);
           //获取sheet列数
           int sheets = sworkbook.getNumberOfSheets();

           XSSFSheet sheetAt = sworkbook.getXSSFWorkbook().getSheetAt(0);
           //获取sheet
           sheet = sworkbook.getSheetAt(0);
//            xssfSheet = xssfWorkbook.getSheetAt(0);
           XSSFRow row1 = sheetAt.getRow(0);
           cells = row1.getPhysicalNumberOfCells();
       } catch (Exception e) {
           e.printStackTrace();
       }

       for (int k = 0; k < scheduleByConditions.size(); k++) {

           //创建新行
           SXSSFRow row = sheet.createRow(i);

           List<String> valueList = new ArrayList<>();
           ResultVo resultVo = scheduleByConditions.get(k);

           // 将实体中的数据提取出来放到list容器中
           Class<? extends ResultVo> aClass = resultVo.getClass();
           Field[] declaredFields = aClass.getDeclaredFields();
           for (int o = 1; o < declaredFields.length; o++) {
               Field declaredField = declaredFields[o];
               declaredField.setAccessible(true);
               Object value = null;
               try {
                   value = declaredField.get(resultVo);
               } catch (IllegalAccessException e) {
                   e.printStackTrace();
               }
               if (null != value){
                   String s = value.toString();
                   valueList.add(s);
               }else {
                   valueList.add("");
               }
           }
           //插入序号
           int xh = k+1;
           valueList.add(0, String.valueOf(xh));
           // 将数据写入到单元格
           for (int j = 0; j <= cells; j++) {
               SXSSFCell cell = row.createCell(j);
               for (int u = 0; u < valueList.size(); u++) {
                   if (j == u) {
                       cell.setCellValue(valueList.get(u));
                       break;
                   }
               }
           }
           i++;
       }

       DataExportUtil.sxssfWriteAndClose(response, inputStream, workbook, sworkbook);
   }

   private List<ResultVo> findScheduleByConditions(ScheduleVo scheduleVo) {
       ResultVo resultVo1 = new ResultVo();
       ResultVo resultVo2 = new ResultVo();
       ResultVo resultVo3 = new ResultVo();
       ResultVo resultVo4 = new ResultVo();
       ResultVo resultVo5 = new ResultVo();
       List<ResultVo> resultVos = new ArrayList<>();
       resultVo1.setName("账伞").setAge("17").setSex("男").setAddress("北京市朝阳区上雕刻技法").setContactInformation("2342893472374");
       resultVo2.setName("里斯").setAge("43").setSex("女").setAddress("上海市黄浦区的发射点").setContactInformation("34534453");
       resultVo3.setName("汪芜").setAge("23").setSex("男").setAddress("陕西省西安市瓦基尔克").setContactInformation("3131231");
       resultVo4.setName("马尔扎哈").setAge("534").setSex("男").setAddress("山西省太原市昆仑山地方").setContactInformation("23544343");
       resultVo5.setName("古力娜扎").setAge("43").setSex("女").setAddress("广东省广州市的快速减肥").setContactInformation("1213124242");
       resultVos.add(resultVo1);
       resultVos.add(resultVo2);
       resultVos.add(resultVo3);
       resultVos.add(resultVo4);
       resultVos.add(resultVo5);
       return resultVos;
   }

3. 工具类

import cn.hutool.poi.excel.ExcelWriter;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;

/**
 * @auther WP
 * @date 2021年10月20日
 */
public class DataExportUtil {

    public static void sxssfWriteAndClose(HttpServletResponse response, InputStream inputStream, XSSFWorkbook workbook, SXSSFWorkbook sworkbook) {
        ServletOutputStream out = null;
        ExcelWriter writer = null;
        try {

            ByteArrayOutputStream os = new ByteArrayOutputStream();
            sworkbook.write(os);
            byte[] bytes = os.toByteArray();
            response.addHeader("Content-Length", String.valueOf(bytes.length));
            out = changeOutputStream(os, response);

        } catch (IOException exception) {
            exception.printStackTrace();
        }finally {
            try {
                inputStream.close();
                out.close();
                workbook.close();
                sworkbook.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 转换输出流
     * @param os
     * @param response
     * @return
     * @throws IOException
     */
    public static ServletOutputStream changeOutputStream(ByteArrayOutputStream os, HttpServletResponse response) throws IOException {
        ServletOutputStream outputStream = response.getOutputStream();
        os.writeTo(outputStream);
        os.close();
        return outputStream;
    }
}

4.实体，顺序和文件字段顺序保持一致

import lombok.Data;
import lombok.experimental.Accessors;

@Data
@Accessors(chain = true)
public class ResultVo {

    private String id;
    private String name;
    private String age;
    private String sex;
    private String address;
    private String contactInformation;
}

5.Controller层

import com.example.export.service.IExportService;
import com.example.export.vo.ScheduleVo;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.http.HttpServletResponse;

@RestController
@RequestMapping("/request")
public class ExportController {

    @Autowired
    private IExportService exportService;

    @GetMapping("/export")
    public void export(ScheduleVo scheduleVo, HttpServletResponse response) throws IllegalAccessException {
        exportService.export(scheduleVo, response);
    }
}

6.代码层级和配置文件!

7.pom路径

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.6.2</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.example</groupId>
    <artifactId>export</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>export</name>
    <description>Demo project for Spring Boot</description>
    <properties>
        <java.version>1.8</java.version>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.2.0</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>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>5.0.0</version>
        </dependency>
        <dependency>
            <groupId>cn.hutool</groupId>
            <artifactId>hutool-all</artifactId>
            <version>5.7.18</version>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.20</version>
            <scope>provided</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-web</artifactId>
            <version>5.3.13</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-devtools</artifactId>
            <scope>runtime</scope>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-actuator</artifactId>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
                <configuration>
                    <excludes>
                        <exclude>
                            <groupId>org.projectlombok</groupId>
                            <artifactId>lombok</artifactId>
                        </exclude>
                    </excludes>
                </configuration>
            </plugin>
        </plugins>
    </build>

</project>

8.使用postman请求接口

9.生成结果

&这样就简单的生成一个excel文档，思路很简单：根据inputstream()序列化excel模板，保存在内存中，再根据vo.getClass()反射获取已经拿到的数据，保存在valueList中，再通过行数跟valueList对比，逐条根据行和列的坐标定位某个单元格，再把数据set进去。对于单个需求，也是比较方便；今天完成了需求，回家美美的睡了一觉，第二天产品过来对你说：“我觉得表里字段好像有点不对，我需要加某某某几个字段，第一列序号换个颜色，再给表添加一个表头等等等等…”，
类型少了可以在代码中去改，但是违反java的开闭原则，也不方便。这个时候你会想，如果我有一个通用方法，我只改一个地方，所有类型都生效那该多好，下一章我将根据现在的模板将一部分方法作为抽象方法抽取出来
纯手打，技术有限，请各位大佬批评指正，谢谢！