报错原因

由于easyexcel导入导出时如果存在null会报错，跟踪源码com.alibaba.excel.analysis.v07.XlsxSaxAnalyser#parseXmlSource，查看xmlReader.parse，发现这里try了没啥卵用，第一次启动导入或者导出时，有null值一样报错。因为excel有空值，不能直接用，所以我自定义了一个excel的导入导出

自定义excel导入导出

导入pom依赖，easyexcel包含了apache.poi的依赖

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

自定义注解ExcelColumn 用来处理excel的导入导出：

package com.xxx.core.anno;
import org.springframework.core.annotation.AliasFor;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;

import java.lang.annotation.*;

/**
 * excel导出表格注解
 *
 * @author ：seagull
 * @date ：Created in 2022/7/12
 * @description：
 * @modified By：
 */
@Target({ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface ExcelColumn {

    String name() default "";

    @AliasFor("name")
    String value() default "";

}

一个model类，使用自定义注解提供表格标题信息

@Data
@AllArgsConstructor
@NoArgsConstructor
@Builder
@TableName("sys_dictionary")
public class Dictionary implements Serializable {
    /**
     * ID
     */
    @TableId(value = "id", type = IdType.AUTO)
    @ExcelColumn("ID")
    private Integer id;

    /**
     * 字典名称
     */
    @TableField(value = "name")
    @ExcelColumn("字典名称")
    private String name;

    /**
     * 父id
     */
    @TableField(value = "pid")
    @ExcelColumn("父id")
    private Integer pid;

    /**
     * 排序
     */
    @TableField(value = "num")
    @ExcelColumn("排序")
    private Integer num;

    /**
     * 提示
     */
    @TableField(value = "tips")
    @ExcelColumn("提示")
    private String tips;

    @TableField(exist = false)
    private List<Dictionary> dictionaryList;

}

创建一个excel工具类

package com.xxx.core.utils;

import com.xxx.core.anno.ExcelColumn;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.net.URLEncoder;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

/**
 * excel工具类
 *
 * @author ：seagull
 * @date ：Created in 2022/7/12
 * @description：
 * @modified By：
 */
public class ExcelUtil {

    /**
     * 导入excel
     *
     * @param file
     * @param <T>
     * @return
     */
    public static <T> List<T> importExcel(MultipartFile file, Class<?> clazz) throws IOException, InstantiationException, IllegalAccessException, ParseException {
        List<T> list = new ArrayList<>();
        XSSFWorkbook sheets = new XSSFWorkbook(file.getInputStream());//读取文件
        //获取sheet
        XSSFSheet sheet = sheets.getSheetAt(0);
        XSSFRow firstRow = sheet.getRow(0);
        int firstRowLength = firstRow.getPhysicalNumberOfCells();
        /*首行名称列表*/
        List<String> firstRowCellList = new ArrayList<>();
        for (int i = 0; i < firstRowLength; i++) {
            String firstRowCellValue = firstRow.getCell(i).toString();
            firstRowCellList.add(firstRowCellValue);
        }

        //获取行数
        int rows = sheet.getPhysicalNumberOfRows();
        for (int i = 1; i < rows; i++) {
            //获取列数
            XSSFRow row = sheet.getRow(i);
            int columns = row.getPhysicalNumberOfCells();
            Field[] fields = clazz.getDeclaredFields();
            Object newInstance = clazz.newInstance();

            for (int j = 0; j < firstRowCellList.size(); j++) {
                String titleCellValue = firstRowCellList.get(j);
                /*根据行单元格名称获取带有指定注解的字段*/
                Field field = getCustomField(fields, titleCellValue);
                if (field == null) {
                    continue;
                }
                XSSFCell cell = row.getCell(j);
                if (cell == null) {
                    continue;
                }
                String cellValue = cell.toString();
                field.setAccessible(true);
                switch (field.getType().getName()) {
                    case "java.lang.Integer":
                        Integer num = 0;
                        try {
                            num = Integer.parseInt(cellValue.substring(0, cellValue.contains(".") ? cellValue.indexOf(".") : cellValue.length()));
                        } catch (NumberFormatException e) {
                            e.printStackTrace();
                        }
                        field.set(newInstance, num);
                        break;
                    case "java.lang.String":
                        field.set(newInstance, cellValue);
                        break;
                    case "java.util.Date":
                        field.set(newInstance, new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").parse(cellValue));
                        break;
                }
                System.out.print(cellValue + "   ");
            }
            System.out.println();
            list.add((T) newInstance);
        }
        return list;
    }

    /**
     * 根据行单元格名称获取带有指定注解的字段
     *
     * @param fields
     * @param cellName
     * @return
     */
    private static Field getCustomField(Field[] fields, String cellName) {
        for (int i = 0; i < fields.length; i++) {
            ExcelColumn annotation = fields[i].getAnnotation(ExcelColumn.class);
            if (annotation != null) {
                if (cellName.equals(annotation.value())) {
                    return fields[i];
                }
            }
        }
        return null;
    }

    /**
     * 直接将数据写到客户端
     *
     * @param response
     * @param dataList
     * @param clazz
     * @param <T>
     * @throws Exception
     */
    public static <T> void exportExcel(HttpServletResponse response, List<T> dataList, Class<T> clazz) throws Exception {
        Field[] fields = clazz.getDeclaredFields();
        Map<String, Object> filedMap = new LinkedHashMap<>();
        for (Field field : fields) {
            Class<?> type = field.getType();
            ExcelColumn annotation = field.getAnnotation(ExcelColumn.class);
            if (annotation != null) {
                filedMap.put(field.getName(), annotation.value());
            }
        }

        XSSFWorkbook workbook = new XSSFWorkbook();

        //创建行
        String[] columnNames = filedMap.values().toArray(new String[0]);

        Sheet sheet = workbook.createSheet();
        Font titleFont = workbook.createFont();
        titleFont.setFontName("simsun");
        titleFont.setBold(true);
        titleFont.setColor(IndexedColors.BLACK.index);

        XSSFCellStyle titleStyle = workbook.createCellStyle();
        titleStyle.setAlignment(HorizontalAlignment.CENTER);
        titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        titleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        titleStyle.setFillForegroundColor(IndexedColors.YELLOW.index);
        titleStyle.setFont(titleFont);

        Row titleRow = sheet.createRow(0);

        for (int i = 0; i < columnNames.length; i++) {
            Cell cell = titleRow.createCell(i);
            cell.setCellValue(columnNames[i]);
            cell.setCellStyle(titleStyle);
        }
        //创建数据行并写入值
        for (T obj : dataList) {
            int lastRowNum = sheet.getLastRowNum();
            Row dataRow = sheet.createRow(lastRowNum + 1);
            int index = 0;
            for (String filedName : filedMap.keySet()) {
                String fieldTypeName = filedMap.get(filedName).toString();
                Class<?> aClass = obj.getClass();
                Field field = aClass.getDeclaredField(filedName);
                field.setAccessible(true);
                Object value = field.get(obj);
                dataRow.createCell(index++).setCellValue(value == null ? null : value.toString());
            }
        }
        response.setContentType("application/vnd.ms-excel");
        response.setHeader("content-Disposition", "attachment;filename=" + URLEncoder.encode("easyexcel.xlsx", "utf-8"));
        response.setHeader("Access-Control-Expose-Headers", "content-Disposition");
        OutputStream outputStream = response.getOutputStream();
        workbook.write(outputStream);
        outputStream.flush();
        outputStream.close();
    }

}

使用方式

@Test
    public void testExcel() {
        List<A> list = ExcelUtil.importExcel(file, A.class);//假如传了file
        
        // 导出用js创建a标签下载
        ExcelUtil.exportExcel(response, list, A.class);
    }