Excel文件导入工具（读取excel并转化为对象数组）

Excel文件导入工具（读取excel并转化为对象数组）

控制器类

	@PostMapping("/batchAddStudent")
	public void batchAddStudentByExcel(@RequestParam("file") MultipartFile file){
		try {
			String[] firstRow = {
					"gh", "xm", "xbm", "csrq", "schoolName",
					"className", "rxn", "dhhm", "sfzjh", "jg",
					"mzm", "jjlxr", "parentName", "jtzz"};
			List<EcAtApplicationStudent> studentList = ExcelToolUtils.ExcelReadToObjectList(file, EcAtApplicationStudent.class, firstRow);
			writerToPageByJsonByNoNull(studentService.BatchAddStudent(studentList));
		}catch (CustomException e) {
			log.error("文件解析错误！", e);
			writerToPageByJsonByNoNull(actionResultService.callBackResult(false, "-1", e.getErrorMsg()));
		}catch (Exception e){
			log.error("系统内部异常！",e);
			writerToPageByJsonByNoNull(actionResultService.callBackResult(false, "-1", "系统内部异常！"));
		}
		return ;

注解：firstRow 数组中存放excel文件的表头对应的需要存放到类中的属性字段。
工具类

import com.alibaba.fastjson.JSONObject;
import com.chinaunicom.digit.country.capacity.center.user.common.exception.custom.CustomException;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.io.InputStream;
import java.math.BigInteger;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;

/**
 * @author XGJ
 * @apiNote Excel文件处理工具
 * @date 2022/9/26 9:25
 */
public class ExcelToolUtils {

    /**
     * 将excel文件读取为指定的java对象数组
     * @param file      文件
     * @param t         对象类
     * @param firstRow  对象类中的字符串，对应excel标题栏
     *                  以学生类为例：
     *        String [] firstRow= {
     *        "gh","xm","xbm","csrq","schoolName",
     *        "className","rxn","dhhm","sfzjh","jg",
     *        "mzm","jjlxr","parentName","jtzz"};
     *
     *
     * @param <T>       泛型
     * @return          返回对象数组
     * @throws IOException
     */
    public static<T> List<T> ExcelReadToObjectList(MultipartFile file,Class<T> t,String [] firstRow) throws IOException {
        String fileDir = file.getOriginalFilename();
        InputStream inputStream = null;
        List<T> list =new ArrayList<>();
        try{
        //判定后缀
        String suffix = fileDir.substring(fileDir.lastIndexOf(".")+1);
        if(!"xls".equals(suffix.toLowerCase())&&!"xlsx".equals(suffix.toLowerCase())){

            throw new CustomException("请上传excel格式文件！");
        }
            inputStream = file.getInputStream();
            Workbook workbook = null;
            if ("xls".equals(suffix)){
                workbook = new HSSFWorkbook(inputStream);
            }else if ("xlsx".equals(suffix)){
                workbook = new XSSFWorkbook(inputStream);
            }

            Sheet sheet;
            Row row;
            Cell cell;
            //默认获取Sheet1
            sheet=workbook.getSheetAt(0);
            for(int j=sheet.getFirstRowNum();j<=sheet.getLastRowNum();j++){
                row=sheet.getRow(j);
                if(row!=null&&row.getFirstCellNum()!=j){
                    T tempObject = rowToObject(row,firstRow,t);
                    list.add(tempObject);
                    }
                }
            // todo 解析Excel中数据，并保存到List对象中
        }catch (IllegalStateException e){
            throw new CustomException("单元格非法传参！");
        }
        catch (Exception e){
            throw new CustomException("文件解析错误！");
        }
        finally {
            inputStream.close();
        }
        return list;
    }

    /**
     * 将excel文件中的一行转化为java对象
     *
     * @param row       excel表格中的一行数据
     * @param firstRow  对象字符串数组
     * @param t         对象类型
     * @param <T>       泛型
     * @return
     */


    public static<T> T rowToObject(Row row,String [] firstRow,Class<T> t){
        Cell cell =null;
        String cellString = null;
        JSONObject studentJSON = new JSONObject();
        for(int k = row.getFirstCellNum();k<row.getLastCellNum()&&k<firstRow.length;k++){cell =row.getCell(k);
            switch (cell.getCellType()){
                case _NONE: cellString = String.valueOf(cell.getStringCellValue());break;
                case NUMERIC:
                    if (DateUtil.isCellDateFormatted(cell)){
                        SimpleDateFormat sdf = null;
                        if (cell.getCellStyle().getDataFormat()==14){
                            sdf = new SimpleDateFormat("yyyy/MM/dd");
                        }else{
                            throw new CustomException("日期格式错误！");
                        }
                        Date date = cell.getDateCellValue();
                        cellString =sdf.format(date);
                    }else if (cell.getCellStyle().getDataFormat()==0){
                        cell.setCellType(CellType.STRING);
                        cellString = String.valueOf(cell.getRichStringCellValue().getString());
                    }
                    break;
                case STRING :cellString = String.valueOf( cell.getStringCellValue());break;
                case FORMULA :cellString = String.valueOf( cell.getCellFormula());break;
                case BLANK :cellString = "";break;
                case BOOLEAN :cellString = String.valueOf(cell.getBooleanCellValue());break;
                case ERROR :cellString = "非法字符串";break;
                default: throw new CustomException("未知类型");
            }
            studentJSON.put(firstRow[k],cellString);
        }
        T student = JSONObject.toJavaObject(studentJSON,t);
        return student;
    }
}

注解：ExcelReadToObjectList（）用来读取Excel文件；rowToObject（）用来将excel中的某一行行转化为一个对象。

其他
这个封装依赖也可以实现excel的导入功能，转化为对象数组，详情参考其他文档

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