Spring boot使用POI导出EXCEL的简单教程

为什么写这笔记

• 导出excel在web管理系统中是一个经典的功能。
• 项目已经有新的解决方案，即让前端基于获取的json数据通过js插件来生成excel。好处在于任何的页面上的json数据都可以这样导出Excel，方便很多。
• 所以，后端已经不需要提供这个功能，删除无用代码，把自制的工具代码搬到笔记这边

pom.xml

首先是jar问题，至少baidu搜索出来的前一页教程都没提及这个问题，也可能是本人使用的版本问题，这里使用的是poi4.0.0

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>4.0.0</version>
</dependency>
<!-- 操作xlsx格式的excel文件 -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-excelant</artifactId>
    <version>4.0.0</version>
</dependency>

poi有两种操作excel的包，一个是poi-ooxml-schemas用于操作xls格式的Excel表-2003版，自Excel2007版文件格式都为xlsx，所以这里导入poi-excelant的包。

生成Excel表并导入数据

下面的操作搬运自 – poi官网教程

创建Excel文件

XSSFWorkbook需要导入poi-excelant的jar包，HSSFWorkbook需要导入poi-ooxml-schemas的jar包。

// 创建xls格式的excel文件
Workbook wb = new HSSFWorkbook();
try  (OutputStream fileOut = new FileOutputStream("workbook.xls")) {
wb.write(fileOut);
}
// 创建xlsx格式的excel文件
Workbook wb = new XSSFWorkbook();
try (OutputStream fileOut = new FileOutputStream("workbook.xlsx")) {
wb.write(fileOut);
}

创建表

一个Excel文件可以包含多个表，不清楚的话，桌面新建一个Excel文件，看左下角的sheet1、sheet2、sheet3就知道了

Workbook wb = new XSSFWorkbook();  // or new HSSFWorkbook();
Sheet sheet1 = wb.createSheet("sheetName");
Sheet sheet2 = wb.createSheet("second sheet");

sheet的名字有如下规则要求：

• 命名不允许有：(空字符)0x0000、(全角空格)0x0003、冒号(:)、斜杠(\)、星号(*)、问号(?)、反斜杠(/)、中括号([、])
• 命名不能以单引号(‘)开头或结尾

apache也提供了检验修正命名的工具

//默认将不符合规定的字符替换为半角空格
String safeName = WorkbookUtil.createSafeSheetName("[O'Brien's sales*?]"); // returns " O'Brien's sales   "
//也可以自定义替换字符
String safeName2 = WorkbookUtil.createSafeSheetName("[O'Brien's sales*?]",'z');// returns "zO'Brien's saleszzz"
Sheet sheet3 = wb.createSheet(safeName);
Sheet sheet4 = wb.createSheet(safeName2);

替换名字的源码的实现很简单,这里看看apache的校验修正方式。

public static String createSafeSheetName(final String nameProposal, char replaceChar) {
    if (nameProposal == null) {
        return "null";
    }
    if (nameProposal.length() < 1) {
        return "empty";
    }
    final int length = Math.min(31, nameProposal.length());
    final String shortenname = nameProposal.substring(0, length);
    final StringBuilder result = new StringBuilder(shortenname);
    for (int i=0; i<length; i++) {
        char ch = result.charAt(i);
        switch (ch) {
            case '\u0000':
            case '\u0003':
            case ':':
            case '/':
            case '\\':
            case '?':
            case '*':
            case ']':
            case '[':
                result.setCharAt(i, replaceChar);
                break;
            case '\'':
                if (i==0 || i==length-1) {
                    result.setCharAt(i, replaceChar);
                }
                break;
            default:
                // all other chars OK
        }
    }
    return result.toString();
}

创建阵列(行、列)

Sheet sheet = wb.createSheet("new sheet");
// 使用sheet对象创建行对象，从零开始为第一行
Row row = sheet.createRow(0);
// 使用row对象创建列对象，从零开始为第一列
Cell cell = row.createCell(0);
// 为该列赋值
cell.setCellValue(1);
row.createCell(1).setCellValue(1.2);
row.createCell(2).setCellValue("hello wolrd");
// 通过指定的行和列来确认一个具体的单元格
sheet.createRow(1).createCell(0).setCellValue(111);

完整的导出数据过程

//使用lombok生成getter/setter方法
@Data
public class UserInfo implements Serializable {

    private static final long serialVersionUID = 1L;

    private Integer id;

    private String username;

    private Integer age;
}

//直接写controller了，
@RequestMapping("/test")
public class TestController {

@Autowired
private IUserInfoService userInfoService;

@GetMapping("/downLoadUserInfoExcel")
public void getExcel(HttpServletResponse response){
    //告诉浏览器下载excel文件，并通过response写回文件
    response.setContentType("application/vnd.ms-excel;charset=UTF-8");
    response.setHeader("Content-Disposition", "attachment;filename=UserInfo.xlsx");
    response.setHeader("Pragma", "no-cache");
    response.setHeader("Cache-Control", "no-cache");

    Workbook workbook = new XSSFWorkbook();
    Sheet sheet = workbook.createSheet();
    List<UserInfo> userInfos = userInfoService.getListUserInfo();
    for (int i = 0; i < userInfos.size(); i++) {
        Row row = sheet.createRow(i);
        row.createCell(0).setCellValue(userInfos.get(i).getId());
        row.createCell(1).setCellValue(userInfos.get(i).getUserName());
        row.createCell(2).setCellValue(userInfos.get(i).getAge());
    }
    try(OutputStream outputStream = response.getOutputStream()){
        workbook.write(outputStream);
    }catch (IOException ex){
        ex.printStackTrace();
    }
}

小工具

用于任意一种类型的数据excel表导出的小工具，先来看看使用方式

Workbook workbook = new XSSFWorkbook();
new SheetDataUtil<UserInfo>(workbook.createSheet(safeName))
        //设置列标题 如果不设置列标题，默认使用字段名
        .setHeaders("用户id","用户名","年龄")
        // 也可以 关闭列标题
        //.enableHeaders(false)
        .setData(userInfoService.getListUserInfo())
        //需要忽略的字段
        .setIgnoredFields("serialVersionUID")
        .exportData();
try(OutputStream outputStream = response.getOutputStream()){
    workbook.write(outputStream);
}catch (IOException ex){
    ex.printStackTrace();
}

以下是源码：

import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;

import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collection;

/**
 * Description:
 * 基于POI封装的导表工具
 * 用于只对任意一种数据格式导入Excel表的数据导入工具
 * TODO 基本上可以使用，但是需要细节完善。缺点在于，当字段是引用类型时……
 *
 * @author zcolder
 * @date 2018-10-19
 */
@Deprecated
public class SheetDataUtil<T> {
    /**
     * 用于自增的行号
     */
    private int lineNumber;

    /**
     * 自定义每列的标题
     */
    private ArrayList<String> customHeaders;

    /**
     * 默认每列的标题为属性名
     */
    private ArrayList<String> defaultHeaders;

    /**
     * 表对象
     */
    private Sheet sheet;

    /**
     * 用于标记是否存在自定义每列的标题
     */
    private boolean isExistHeaders;

    /**
     * 用于标记是否关闭导入标题
     */
    private boolean isImportHeaders;

    /**
     * 数据
     */
    private Collection<T> data;

    /**
     * 泛型T对象的所有属性的Getter方法
     */
    private ArrayList<String> getterMethodNames = new ArrayList<>();

    /**
     * 需要忽略的字段
     */
    private ArrayList<String> ignoredFields;

    /**
     * 存在要忽略的字段
     */
    private boolean isExistIgnoredFields;

    /**
     * 行 对象
     */
    private Row row;

    public SheetDataUtil(Sheet sheet) {
        this.sheet = sheet;
    }

    public SheetDataUtil<T> setData(Collection<T> data) {
        this.data = data;
        return this;
    }

    public SheetDataUtil<T> enableHeaders(boolean isImportHeadersFlag) {
        this.isImportHeaders = isImportHeadersFlag;
        return this;
    }

    public SheetDataUtil<T> setHeaders(String... headers) {
        return setHeaders(new ArrayList<>(Arrays.asList(headers)));
    }

    public SheetDataUtil<T> setHeaders(ArrayList<String> headers) {
        this.customHeaders = headers;
        this.isExistHeaders = true;
        return this;
    }

    public SheetDataUtil<T> setIgnoredFields(String... fieldName) {
        return setIgnoredFields(new ArrayList<>(Arrays.asList(fieldName)));
    }

    public SheetDataUtil<T> setIgnoredFields(ArrayList<String> fieldName) {
        this.ignoredFields = fieldName;
        this.isExistIgnoredFields = true;
        return this;
    }

    public void exportData() throws NoSuchMethodException, InvocationTargetException, IllegalAccessException {
        // 通过过反射获取所有字段，并组装Getter方法名
        setGetterMethodNames(data.iterator().next().getClass());
        // 导入每列的标题，如果用户有自定义
        if (isImportHeaders) {
            importHeaders();
        }
        // 循环将数据填充入excel表
        for (T t : data) {
            row = sheet.createRow(lineNumber++);
            int i = 0;
            for (String getterMethodName : getterMethodNames) {
                // 通过获取get方法，来获取实例的属性值
                Method getterMethod = t.getClass().getMethod(getterMethodName);
                Object value = getterMethod.invoke(t);
                // 此处的判断顺序 按照类型的使用概率
                if (value instanceof Number) {
                    if (value instanceof Integer) {
                        row.createCell(i).setCellValue((Integer) value);
                    } else if (value instanceof Long) {
                        row.createCell(i).setCellValue((Long) value);
                    } else if (value instanceof Float) {
                        row.createCell(i).setCellValue((Float) value);
                    } else if (value instanceof Double) {
                        row.createCell(i).setCellValue((Double) value);
                    } else if (value instanceof Byte) {
                        row.createCell(i).setCellValue((Byte) value);
                    } else if (value instanceof Short) {
                        row.createCell(i).setCellValue((Short) value);
                    }
                }
                // 除了数字类型之外，其他都转为文本
                row.createCell(i).setCellValue(value.toString());
                i++;
            }
        }
    }

    private void importHeaders() {
        ArrayList<String> headers = isExistHeaders ? customHeaders : defaultHeaders;
        row = sheet.createRow(lineNumber++);
        for (int i = 0; i < headers.size(); i++) {
            row.createCell(i).setCellValue(headers.get(i));
        }
    }

    private void setGetterMethodNames(Class clazz) {
        defaultHeaders = new ArrayList<>();
        for (Field field : clazz.getDeclaredFields()) {
            String fieldName = field.getName();
            // 忽略不需要的字段
            if (isExistIgnoredFields && ignoredFields.contains(fieldName)) {
                continue;
            }
            //拼接getter方法名，并添加
            getterMethodNames.add("get"
                    + fieldName.substring(0, 1).toUpperCase()
                    + fieldName.substring(1));
            defaultHeaders.add(fieldName);
        }
    }
}