springboot+apache poi 3.17读取Excel文件数据

要做一个前端上传Excel文件，后端接收到后读取Excel文件数据后保存到Mysql的功能。

非常感谢这位博主的 https://blog.csdn.net/phil_jing/article/details/78307819，虽然完全复制上去有报错，但是问题不大。

主要使用SpringBoot快速搭建项目，Apache poi 3.17解析Excel文件。

maven导包

<dependencies>

        <dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-collections4</artifactId>
            <version>4.1</version>
        </dependency>

        <dependency>
            <groupId>commons-io</groupId>
            <artifactId>commons-io</artifactId>
            <version>2.4</version>
        </dependency>

        <dependency>
            <groupId>org.apache.xmlbeans</groupId>
            <artifactId>xmlbeans</artifactId>
            <version>2.6.0</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.13</version>
        </dependency>

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

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </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.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
    </dependencies>

这第一个坑是需要多导入个jar ，poi-ooxml-schemas，这个jar在poi-pooxml有引用，还有关于poi的三个jar引入顺序有讲究，

poi-ooxml必须在其他两个poi后面引入，不然会导包会报错。

ExcelUtil工具类

import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.Objects;
import java.util.regex.Pattern;

import com.example.demo.annotation.ExcelColumn;
import org.apache.commons.io.IOUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.springframework.util.StringUtils;
import org.springframework.web.multipart.MultipartFile;


public class ExcelUtil {

    private static final DecimalFormat DECIMAL_FORMAT = new DecimalFormat("0");// 格式化 number为整

    private static final DecimalFormat DECIMAL_FORMAT_PERCENT = new DecimalFormat("##.00%");//格式化分比格式，后面不足2位的用0补齐

    private static final SimpleDateFormat FAST_DATE_FORMAT = new SimpleDateFormat("yyyy/MM/dd hh:mm:ss");

    private static final DecimalFormat DECIMAL_FORMAT_NUMBER  = new DecimalFormat("0.00E000"); //格式化科学计数器

    private static final Pattern POINTS_PATTERN = Pattern.compile("0.0+_*[^/s]+"); //小数匹配

    /**
     * 读取excel
     * @param file    spring封装的文件
     * @return         二维数据列表
     * @throws IOException
     */
    public static List<List<Object>> readExcel(MultipartFile file) throws IOException {
        String extension = file.getOriginalFilename().substring(file.getOriginalFilename().lastIndexOf(".") + 1).toLowerCase();
        if(Objects.equals("xls", extension) || Objects.equals("xlsx", extension)) {
            return readExcel(file.getInputStream());
        } else {
            throw new IOException("不支持的文件类型");
        }
    }

    /**
     * 读取excel
     * @param file      spring封装的文件
     * @param cls       返回泛型类型
     * @return          返回对象列表
     * @throws IOException
     */
    public static <T> List<T> readExcel(MultipartFile file, Class<T> cls) throws IOException {
        String extension = file.getOriginalFilename().substring(file.getOriginalFilename().lastIndexOf(".") + 1).toLowerCase();
        if(Objects.equals("xls", extension) || Objects.equals("xlsx", extension)) {
            return readExcel(file.getInputStream(), cls);
        } else {
            throw new IOException("不支持的文件类型");
        }
    }

    /**
     *  读取excel
     * @param inputStream   excel文件流
     * @return                二维列表数据
     * @throws IOException
     */
    private static List<List<Object>> readExcel(InputStream inputStream) throws IOException {
        List<List<Object>> list = new LinkedList<>();
        Workbook workbook = null;
        try {
            workbook = WorkbookFactory.create(inputStream);
            int sheetsNumber = workbook.getNumberOfSheets();
            for (int n = 0; n < sheetsNumber; n++) {
                Sheet sheet = workbook.getSheetAt(n);
                Object value = null;
                Row row = null;
                Cell cell = null;
                for (int i = sheet.getFirstRowNum() + 1; i <= sheet.getPhysicalNumberOfRows(); i++) { // 从第二行开始读取
                    row = sheet.getRow(i);
                    if (StringUtils.isEmpty(row)) {
                        continue;
                    }
                    List<Object> linked = new LinkedList<>();
                    for (int j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) {
                        cell = row.getCell(j);
                        if (StringUtils.isEmpty(cell)) {
                            continue;
                        }
                        value = getCellValue(cell);
                        linked.add(value);
                    }
                    list.add(linked);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            IOUtils.closeQuietly(workbook);
            IOUtils.closeQuietly(inputStream);
        }
        return list;
    }

    /**
     * 获取excel数据 将之转换成bean
     * @param inputStream      excel文件输入流
     * @param cls               需要转换的实体类
     * @param <T>               可以返回任意类型的列表
     * @return
     */
    private static <T> List<T> readExcel(InputStream inputStream, Class<T> cls) {
        List<T> dataList = new LinkedList<T>();
        Workbook workbook = null;
        try {
            workbook = WorkbookFactory.create(inputStream);
            Map<String, List<Field>> classMap = new HashMap<String, List<Field>>();// 保存字段名及对
            Field[] fields = cls.getDeclaredFields();// 获取实体类所有字段
            for (Field field : fields) {
                ExcelColumn annotation = field.getAnnotation(ExcelColumn.class);
                if (annotation != null) {
                    String value = annotation.value();
                    if (!classMap.containsKey(value)) {
                        classMap.put(value, new ArrayList<Field>());
                    }
                    field.setAccessible(true);
                    classMap.get(value).add(field);
                }
            }
            Map<Integer, List<Field>> reflectionMap = new HashMap<Integer, List<Field>>();
            int sheetsNumber = workbook.getNumberOfSheets(); // 获取excel工作单页数
            //获取每一个表单
            for (int n = 0; n < sheetsNumber; n++) {
                Sheet sheet = workbook.getSheetAt(n);

                // 一行中没有数据将会返回null
                if(sheet.getRow(0) == null){
                    return dataList;
                }
                int firstCellNum = sheet.getRow(0).getFirstCellNum();
                int lastCellNum = sheet.getRow(0).getLastCellNum();
                ;
                if(firstCellNum == lastCellNum ){       // 进入说明后面工作表单是空的，可以直接返回了
                    return dataList;
                }
                for (int j = firstCellNum; j < lastCellNum; j++) {  // 获取首行的所有列名
                    Object cellValue = getCellValue(sheet.getRow(0).getCell(j));
                    if (classMap.containsKey(cellValue)) {
                        reflectionMap.put(j, classMap.get(cellValue));
                    }
                }
                Row row = null;
                Cell cell = null;
                for (int i = sheet.getFirstRowNum() + 1; i < sheet.getPhysicalNumberOfRows(); i++) {
                    row = sheet.getRow(i);
                    T t = cls.newInstance();
                    for (int j = row.getFirstCellNum(); j < row.getLastCellNum(); j++) {
                        cell = row.getCell(j);
                        if (reflectionMap.containsKey(j)) {
                            Object cellValue = getCellValue(cell);
                            List<Field> fieldList = reflectionMap.get(j);
                            for (Field field : fieldList) {
                                try {
                                    field.set(t, cellValue);
                                } catch (Exception e) {
                                    e.printStackTrace();
                                }
                            }
                        }
                    }
                    dataList.add(t);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            IOUtils.closeQuietly(workbook);
            IOUtils.closeQuietly(inputStream);
        }
        return dataList;
    }

    /**
     * 获取excel 单元格数据
     * @param cell      单元格
     * @return           转换后的单元格数据
     */
    private static Object getCellValue(Cell cell) {
        Object value = null;
        if(cell == null){
            return value = "";
        }
        switch (cell.getCellType()) {
            case Cell.CELL_TYPE_STRING:
                value = cell.getStringCellValue();
                break;
            case Cell.CELL_TYPE_NUMERIC:
                if(DateUtil.isCellDateFormatted(cell)){ //日期
                    value = FAST_DATE_FORMAT.format(DateUtil.getJavaDate(cell.getNumericCellValue()));//转成同统一日期格式
                } else if("@".equals(cell.getCellStyle().getDataFormatString())
                        || "General".equals(cell.getCellStyle().getDataFormatString())
                        || "0_ ".equals(cell.getCellStyle().getDataFormatString())){
                    //文本  or 常规 or 整型数值
                    value = DECIMAL_FORMAT.format(cell.getNumericCellValue());
                } else if(POINTS_PATTERN.matcher(cell.getCellStyle().getDataFormatString()).matches()){ //正则匹配小数类型
                    value = cell.getNumericCellValue();  //直接显示
                } else if("0.00E+00".equals(cell.getCellStyle().getDataFormatString())){//科学计数
                    value = cell.getNumericCellValue();	//待完善
                    value = DECIMAL_FORMAT_NUMBER.format(value);
                } else if("0.00%".equals(cell.getCellStyle().getDataFormatString())){//百分比
                    value = cell.getNumericCellValue(); //待完善
                    value = DECIMAL_FORMAT_PERCENT.format(value);
                } else if("# ?/?".equals(cell.getCellStyle().getDataFormatString())){//分数
                    value = cell.getNumericCellValue(); 待完善
                } else { //货币
                    value = cell.getNumericCellValue();
                    value = DecimalFormat.getCurrencyInstance().format(value);
                }
                break;
            case Cell.CELL_TYPE_BOOLEAN:
                value = cell.getBooleanCellValue();
                break;
            case Cell.CELL_TYPE_BLANK:// 空白区域
                value = "";
                break;
            default:
                value = cell.toString();
        }
        return value;
    }



}

跟上面提到的博主的代码差不太多吧，主要修改了两个地方

第一个

                // 一行中没有数据将会返回null
                if(sheet.getRow(0) == null){
                    return dataList;
                }
                int firstCellNum = sheet.getRow(0).getFirstCellNum();
                int lastCellNum = sheet.getRow(0).getLastCellNum();
                ;
                if(firstCellNum == lastCellNum ){       // 进入说明后面工作表单是空的，可以直接返回了
                    return dataList;
                }

这段代码需要加上，因为Excel表是可以有多个工作表单的，在遍历表单，然后获取第一行数据时若没有数据就会返回一个null，

那时在调用就会报空指针异常，要不就你就将没有数据的工作表单删掉。

第二个

/**
     * 获取excel 单元格数据
     * @param cell      单元格
     * @return           转换后的单元格数据
     */
    private static Object getCellValue(Cell cell) {
        Object value = null;
        if(cell == null){
            return value = "";
        }
        switch (cell.getCellType()) {
            case Cell.CELL_TYPE_STRING:
                value = cell.getStringCellValue();
                break;
            case Cell.CELL_TYPE_NUMERIC:
                if(DateUtil.isCellDateFormatted(cell)){ //日期
                    value = FAST_DATE_FORMAT.format(DateUtil.getJavaDate(cell.getNumericCellValue()));//转成同统一日期格式
                } else if("@".equals(cell.getCellStyle().getDataFormatString())
                        || "General".equals(cell.getCellStyle().getDataFormatString())
                        || "0_ ".equals(cell.getCellStyle().getDataFormatString())){
                    //文本  or 常规 or 整型数值
                    value = DECIMAL_FORMAT.format(cell.getNumericCellValue());
                } else if(POINTS_PATTERN.matcher(cell.getCellStyle().getDataFormatString()).matches()){ //正则匹配小数类型
                    value = cell.getNumericCellValue();  //直接显示
                } else if("0.00E+00".equals(cell.getCellStyle().getDataFormatString())){//科学计数
                    value = cell.getNumericCellValue();	//待完善
                    value = DECIMAL_FORMAT_NUMBER.format(value);
                } else if("0.00%".equals(cell.getCellStyle().getDataFormatString())){//百分比
                    value = cell.getNumericCellValue(); //待完善
                    value = DECIMAL_FORMAT_PERCENT.format(value);
                } else if("# ?/?".equals(cell.getCellStyle().getDataFormatString())){//分数
                    value = cell.getNumericCellValue(); 待完善
                } else { //货币
                    value = cell.getNumericCellValue();
                    value = DecimalFormat.getCurrencyInstance().format(value);
                }
                break;
            case Cell.CELL_TYPE_BOOLEAN:
                value = cell.getBooleanCellValue();
                break;
            case Cell.CELL_TYPE_BLANK:// 空白区域
                value = "";
                break;
            default:
                value = cell.toString();
        }
        return value;
    }

在进入这个方法的第二行代码需要判断cell是否为空，这是一个单元数据，就是excel每一格里面的数据，有时候我们的excel数据不一定是全的，所以需要判断是否为空，当然返回一个“”不一定适应所有的情况，需要根据情况看吧，还有Cell的常量值也是修改的，表明当前Cell获取到的值样式。

还有个注解类，没有任何修改，可以直接拿来用

import java.lang.annotation.Documented;
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;

@Target({ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface ExcelColumn{

    public String value() default "";

}

接下来是测试的实体类

import com.example.demo.annotation.ExcelColumn;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

import javax.persistence.Column;
import javax.persistence.Entity;

@Data
@AllArgsConstructor
@NoArgsConstructor
@Entity
public class Student extends BaseEntity {

    @Column(columnDefinition = "timestamp null comment '创建时间'", updatable = false)
    @ExcelColumn("时间")
    protected String createTime;

    @Column(columnDefinition = "varchar(50) null default '' comment '姓名'")
    @ExcelColumn("姓名")
    private String name;

    @Column(columnDefinition = "varchar(50) null default '' comment '学号'")
    @ExcelColumn("学号")
    private String number;

    @Column(columnDefinition = "varchar(50) null default '' comment '班级'")
    @ExcelColumn("班级")
    private String clazz;

}

控制器

import com.example.demo.entity.Student;
import com.example.demo.service.StudentService;
import com.example.demo.util.ExcelUtil;
import com.example.demo.iterable.Students;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;

import java.util.List;

@RestController
public class StudentController {

    @Autowired
    private StudentService studentService;

    @RequestMapping("up")
    public String up(MultipartFile file) throws Exception {
        if (file.isEmpty()) {
            return "文件不能为空";
        }
        //判断是否是excel文件
        List<Student> studentList = ExcelUtil.readExcel(file,Student.class);

        Students students = new Students(studentList);

        studentService.saveMulti(students);

        return "上传成功";
    }
}

服务类

import com.example.demo.entity.Student;
import com.example.demo.repositories.StudentRepositories;
import com.example.demo.service.StudentService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import java.util.List;

@Service
public class StudentServiceImpl implements StudentService {

    @Autowired
    private StudentRepositories studentRepositories;

    @Override
    public boolean saveMulti(Iterable<Student> students) {

        studentRepositories.saveAll(students);
        return false;
    }
}

Dao层的就在正常不过了，就不放出来了，因为saveAll()方法参数只能是实现Iterable<T>的类，所以参考网上写了个Students类

import com.example.demo.entity.Student;

import java.util.Arrays;
import java.util.Iterator;
import java.util.List;
import java.util.Spliterator;
import java.util.function.Consumer;

public class Students implements Iterable<Student> {
    private Student[] students;

    public Students(List<Student> students){

        this.students = new Student[students.size()];
        for(int i = 0; i < students.size(); i++){
            this.students[i] = students.get(i);
        }
    }

    @Override
    public Iterator<Student> iterator() {
        return new StudentIterator();
    }

    // 实现Iterator接口的私有内部类，外界无法直接访问
    private class StudentIterator implements Iterator<Student> {
        // 当前迭代元素的下标
        private int index = 0;

        // 判断是否还有下一个元素，如果迭代到最后一个元素就返回false
        public boolean hasNext() {
            return index != students.length;
        }

        @Override
        public Student next() {
            return students[index++];
        }

        // 这里不支持，抛出不支持操作异常
        public void remove() {
            throw new UnsupportedOperationException();
        }
    }

}

excel数据放一张

基本就这样了，唯一缺点是不能让时间为空，看实体类知道时间设计是时间戳，这个感觉不太会弄，还没怎么写过关于时间的功能。