Springboot 整合导入Excel文件读取数据，支持windows和linux

参考博客：https://blog.csdn.net/rexueqingchun/article/details/82586950

备注：基于参考博客调整方法在windows和linux都可使用

1.导入的excel所在目录为D:/cs/test.xls，测试数据如下图所示：

2.创建与excel表头对应的xml模版，文件名import_job_number.xml

<?xml version="1.0" encoding="UTF-8"?>

<!-- 导入的模版校验文件 -->
<execelImport>
    <field>姓名</field>
    <field>工号</field>
</execelImport>

3.目录结构

 

创建导入数据对应实体类

package com.gandong.etcm.manager.controller.params;


public class SevBankUserParams {


    /**
     * 用户名
     */
    private String realName;
    /**
     * 工号
     */
    private String username;



    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getRealName() {
        return realName;
    }

    public void setRealName(String realName) {
        this.realName = realName;
    }

    public void setValues(String[] values) throws Exception{
         //必须与配置文件对应
        this.realName = values[0] ==null?"":values[0].trim();
        this.username = values[1] ==null?"":values[1].trim();

    }




}

 

控制层

 /**
     * 批量导入工号
     * @return
     * @throws Exception
     */
    @PostMapping("/recordJobNumber")
    public Result recordJobNumber(@RequestParam("file")MultipartFile file)throws Exception{
        log.info("==========================");
        return sevBankUserService.recordJobNumber(file);
    }

业务层

  Result recordJobNumber(MultipartFile file)throws Exception;

 @Override
    public Result recordJobNumber(MultipartFile file) throws Exception{
       InputStream is = file.getInputStream() ;
       //读取数据
        ExecelUtil eu = new ExecelUtil(is, "import_job_number.xml", SevBankUserParams.class,".xls",env);
        SevBankUserParams cp = null;
        List<SevBankUser>   userList=new ArrayList<>();
     //循环遍历
        while (eu.hasNext()) {
            if (eu.validate()) {
                cp = (SevBankUserParams) eu.getObjectBean();
                //取到数据后需要处理的业务逻辑 
                SevBankUser user=SevBankUser
                        .builder()
                        .id( UUID.randomUUID().toString().replaceAll("-", ""))
                        .username(cp.getUsername())
                        .userPwd(commonUtil.sha1Encrypt("233688"))
                        .bankCode(cp.getUsername().substring(0,2))
                        .realName(cp.getRealName())
                        .createTime(new Date())
                        .build();
                //查询是否有重复工号
                Integer  number=    sevBankUserMapper.selectSevBankUserByUsernameNumber(cp.getUsername());
                if (number>0){
                    userList.add(user);
                }else {
                    //插入数据库
                    sevBankUserMapper.saveSevBankUser(user);

                }
                log.info(cp.getUsername()+"/"+cp.getRealName());
            }
        }
        //返回前台工号重复数据
    return Result.success(userList);
    }

工具类

package com.gandong.etcm.manager.util.common.excelUtil;

import java.io.*;
import java.net.URLDecoder;
import java.text.DateFormat;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Set;

import javax.validation.ConstraintViolation;
import javax.validation.Validation;
import javax.validation.Validator;
import javax.validation.ValidatorFactory;

import com.gandong.etcm.manager.controller.params.SevBankUserParams;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.CellReference;
import org.apache.poi.ss.usermodel.Cell;
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.xssf.usermodel.XSSFWorkbook;
import org.dom4j.Document;
import org.dom4j.DocumentException;
import org.dom4j.Element;
import org.dom4j.io.SAXReader;
import org.springframework.core.io.ClassPathResource;
import org.springframework.expression.EvaluationException;
import org.springframework.expression.Expression;
import org.springframework.expression.ExpressionParser;
import org.springframework.expression.spel.standard.SpelExpressionParser;

@Slf4j
public class ExecelUtil {

    private String description = "";// 如果校验失败，将会给出详细提示信息
    private Sheet sheet;// execel 对象
    private List<String> fieldList;//从xml读取到的execel表格信息
    private int rowIndex = 0;//当前操作行
    private Object objectBean;//每一行数据封装
    private Cell cellStart;// 数据的开始单元格
    private Class clazz; //需要封装的类
    private Validator validator; //hibernate 的校验器
    private String[] fieldVals ; //从execel读到的某一行的数据
    private int fieldSize = 0;  //有效数据的列数
    private DateFormat format = new SimpleDateFormat("yyyy-MM-dd"); // 针对日期的默认转换形式
    private Expression exp ;//EL 解析器
    private ExpressionParser parser;

    private DecimalFormat df = new DecimalFormat("#");

    public String getDescription() {
        return description;
    }

    public Object getObjectBean() {
        return objectBean;
    }



    /**
     *
     * @param
     *
     * @param xmlFilename
     *
     * @param env : dev-本地  test-测试  prod-生产
     */
    public ExecelUtil(InputStream execelIS,String xmlFilename,Class clazz,String suffix,String env) throws Exception{
        // 打开execel工作簿
        Workbook wb = null;
        try {
            if(suffix.equals(".xls")){
                wb = new HSSFWorkbook(execelIS);
            }else if(suffix.equals(".xlsx")){
                wb = new XSSFWorkbook(execelIS);
            }
            execelIS.close();
        } catch (IOException e) {
            log.error(""+e);
            throw new Exception("加载文件失败,请确保是否是Execel表格");
        }
        sheet = wb.getSheetAt(0);// 默认取第一个工作簿
        //读配置文件，获取所有的属性列描述
        if (env.equals("dev")){

            fieldList = this.readFieldsFromXML(getAbsolutePath(xmlFilename),env);
        }else {

            fieldList = this.readFieldsFromXML(xmlFilename,env);
        }
        //个数
        fieldSize = fieldList.size();

        //找到有效数据的开始单元格
        cellStart = this.findStartCell();
        if(cellStart == null){
            throw new Exception(this.description);
        }

        //每次读取一行execel数据，rowIndex每次增1
        rowIndex = cellStart.getRowIndex()+1;

        //需要封装的对象类
        this.clazz = clazz;
        //初始化校验器
        ValidatorFactory factory = Validation.buildDefaultValidatorFactory();
        validator = factory.getValidator();
        //初始化EL解析器
        parser = new SpelExpressionParser();
        exp = parser.parseExpression("values");
    }

    //是否还有数据
    public boolean hasNext(){
        Row row = sheet.getRow(rowIndex++);
        if(row == null)
            return false;
        fieldVals = this.getRowValues(row, cellStart.getColumnIndex());
        if(Arrays.asList(fieldVals).indexOf("") != -1){
            for(String s :fieldVals)//如果每个字段都是空的，则返回false 否则true
                if(!s.equals(""))
                    return true;
            return false;
        }
        return true;
    }
    //校验
    public boolean validate(){
        try {
            objectBean = Class.forName(clazz.getName()).newInstance();
        } catch (Exception e) {
            log.error(""+e);
        }

        try{
            exp.setValue(objectBean, fieldVals);// 给objectBean的属性赋值

        }catch(EvaluationException e){//由于所有的数据类型转换都有objectBean里面来处理，故可能有异常，需要进行相应的处理
            System.out.println(e);
            List exList = Arrays.asList("ParseException","NumberFormatException");//一般可能发生的异常
            Throwable t = e.getCause();
            while(t!=null){
                String causeClazz = t.getClass().getSimpleName();
                if(exList.contains(causeClazz)){
                    this.description = "第" +rowIndex+"行，类型转换失败："+t.getMessage();
                    return false;
                }else if(causeClazz.equals("Exception")){//自定义异常
                    this.description = "第" +rowIndex+"行,"+t.getMessage();
                    return false;
                }else
                    t = t.getCause();
            }
            //this.description = parser.parseExpression("sales").getValue(objectBean,String.class);
            this.description = "数据错误";
            return false;
        }
        //校验，校验规则是配置在objectBean对象里面
        Set<ConstraintViolation<Object>> constraintViolations = validator.validate(objectBean);

        if(constraintViolations.size() >0){//校验失败时，提示相应信息
            this.description = "第" +rowIndex+"行，校验出错：";
            for (ConstraintViolation<Object> vl : constraintViolations) {
                this.description = this.description + vl.getMessage()+" ;  ";
            }
            return false;
        }
        return true;
    }

    private String[] getRowValues(Row row,int columnStartIndex){
        String[] values = new String[fieldSize];
        for(int j = columnStartIndex,t=0;t<fieldSize;j++,t++){
            Cell c = row.getCell(j);
            if(c==null){
                values[t] = "";
                continue;
            }
            switch(c.getCellType()){
                case Cell.CELL_TYPE_BLANK:
                    values[t] = "";
                    break;
                case Cell.CELL_TYPE_BOOLEAN:
                    values[t] = String.valueOf(c.getBooleanCellValue());
                    break;
                case Cell.CELL_TYPE_NUMERIC:

                    if (HSSFDateUtil.isCellDateFormatted(c)) {
                        values[t] = format.format(c.getDateCellValue());
                    } else if(c.getCellStyle().getDataFormat() == 58) {
                        // 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58)
                        SimpleDateFormat sdf = new SimpleDateFormat("MM/dd");
                        double value = c.getNumericCellValue();
                        Date date = org.apache.poi.ss.usermodel.DateUtil
                                .getJavaDate(value);

                        values[t] = sdf.format(date);
                    }
                    else {
                        values[t] = new DecimalFormat("#").format(c.getNumericCellValue());
                    }
                    break;
                case Cell.CELL_TYPE_STRING:
                    values[t] = String.valueOf(c.getStringCellValue());
                    break;

                default:
                    values[t] = "";
                    break;
            }
        }
        return values;
    }
    // 根据某一个单元格，得到更人性化的显示,例如“A4”
    private String getCellRef(Cell cell) {
        return CellReference.convertNumToColString(cell.getColumnIndex()) + (cell.getRowIndex() + 1);
    }
    private  String getAbsolutePath(String file) throws Exception {
        try {
            file="src/main/resources/"+file;
          log.info("file==========="+file);
        } catch (NullPointerException e) {
            throw new Exception(file+", 文件不存在");
        }
        try {
            // 解决当出现中文路径时不能解析的bug
            file = URLDecoder.decode(file, "UTF-8");
        } catch (UnsupportedEncodingException e) {
            throw new Exception(file+",解码失败");
        }
        return file;
    }
    private List<String> readFieldsFromXML(String xmlFilename,String env)throws Exception{
        SAXReader reader = new SAXReader();
        Document document = null;

        try {

            if (env.equals("dev")){
                document = reader.read(new File(xmlFilename));// 加载配置文件
            }else {
                ClassPathResource classPathResource = new ClassPathResource(xmlFilename);
                document = reader.read(classPathResource.getInputStream());// 加载配置文件
            }
        } catch (DocumentException e) {
            e.printStackTrace();
            this.description = "IO 异常，读取配置文件失败";
            throw new Exception(xmlFilename+",IO 异常，读取配置文件失败");
        }

        Element root = document.getRootElement();
        List<String> fields = new ArrayList<String>();
        for (Iterator iter = root.elementIterator("field"); iter.hasNext();) {
            Element field = (Element) iter.next();
            fields.add(field.getTextTrim());
        }

        return fields;
    }
    /**
     * 从execel表中找到数据开始的单元格
     * @return
     */
    private  Cell findStartCell(){
        String firstFieldDesc = this.fieldList.get(0);
        int endRow = sheet.getLastRowNum()>100?100:sheet.getLastRowNum();
        for(int i = 0;i<=endRow;i++){
            Row r = sheet.getRow(i);
            if (r == null)
                continue;
            for(int j = 0;j < r.getLastCellNum();j++){
                Cell c = r.getCell(j);
                if( c == null)
                    continue;
                if(c.getCellType() == Cell.CELL_TYPE_STRING){
                    if(c.getStringCellValue().trim().equals(firstFieldDesc)){//找到第一个符合要求的字段，接下来判断它相邻的字段是否都符合要求
                        if(fieldList.size()>r.getLastCellNum()-j){
                            this.description = "execel表格与所给配置描述不符，请下载模板文件";
                            return null;
                        }
                        for(int k=j+1,t=1;k<=j+fieldList.size()-1;k++,t++){
                            Cell c2 = r.getCell(k);
                            if(c2 == null){
                                this.description = "请确保单元格"+this.getCellRef(c2)+"内容是\""+fieldList.get(t)+"\"";
                                return null;
                            }
                            if(c2.getCellType() == Cell.CELL_TYPE_STRING){
                                if(c2.getStringCellValue().contains(fieldList.get(t)))
                                    continue;
                                else{
                                    this.description = "请确保单元格"+this.getCellRef(c2)+"内容是\""+fieldList.get(t)+"\"";
                                    return null;
                                }
                            }

                        }
                        return c;
                    }else
                        continue;
                }else
                    continue;
            }
        }
        this.description = "找不到\""+fieldList.get(0)+"\"这一列";
        return null;
    }

    public int getRowIndex() {
        return rowIndex;
    }

    public DateFormat getFormat() {
        return format;
    }


    public String createExcelIncludeFailReason(InputStream execelIS,String path,String newExcelName, HashMap<Integer,String> errHash) {
        FileOutputStream file;
        try {
            file = new FileOutputStream(path+"/"+newExcelName);
            Workbook workbook=this.getWorkbook(execelIS);
            Sheet s=workbook.getSheetAt(0);

            //int endRow = sheet.getLastRowNum()>100?100:sheet.getLastRowNum();
            int endRow = sheet.getLastRowNum();
            for(int i=1;i<=endRow;i++){
                if(errHash.get(i)!=null){
                    Row rowkk = s.getRow(i);
                    Cell error_cell = rowkk.createCell(fieldList.size());
                    error_cell.setCellValue(errHash.get(i));
                }
            }
            workbook.write(file);
            file.close();
        } catch (FileNotFoundException e) {

            e.printStackTrace();
        } catch (IOException e) {

            e.printStackTrace();
        } catch (Exception e) {

            e.printStackTrace();
        }

        return newExcelName;
    }


    public String getFile(InputStream execelIS,String path,String appPath, List<Integer> listF, boolean b) {
        FileOutputStream file;
        try {
            file = new FileOutputStream(path+"/"+appPath);
            Workbook workbook=this.getWorkbook(execelIS);
            Sheet s=workbook.getSheetAt(0);
            Row row=null;
            //int endRow = sheet.getLastRowNum()>100?100:sheet.getLastRowNum();
            int endRow = sheet.getLastRowNum();
            for(int i=1;i<=endRow;i++){

                Row rowkk = s.getRow(i);
                Cell info_cell = rowkk.createCell(fieldList.size());
                info_cell.setCellValue("sss");

                //删除错误行
                if(b&& listF.contains(i)){
                    row=s.getRow(i);
                    //System.out.println("错误行删除"+i);
                    if(row!=null)s.removeRow(row);
                }
                //删除正确行
                if(!b && !listF.contains(i)){
                    row=s.getRow(i);
                    //System.out.println("正确行删除"+i);
                    if(row!=null)s.removeRow(row);
                }
            }
            workbook.write(file);
            file.close();
        } catch (FileNotFoundException e) {

            e.printStackTrace();
        } catch (IOException e) {

            e.printStackTrace();
        } catch (Exception e) {

            e.printStackTrace();
        }

        return appPath;
    }
    public  Workbook  getWorkbook(InputStream execelIS) throws Exception{
        Workbook wb = null;
        try {
            wb = new HSSFWorkbook(execelIS);
        } catch (IOException e) {
            log.error(""+e);
            throw new Exception("加载文件失败,请确保是否是Execel表格");
        }
        return wb;
    }

    /**
     * 测试
     * @param args
     * @throws Exception
     */
/*    public static void main(String[] args) throws Exception {
        InputStream is = new FileInputStream("C:\\Users\\Administrator\\Desktop\\test.xls");
        ExecelUtil eu = new ExecelUtil(is, "/import_job_number.xml", Persion.class,".xls");
        Persion cp = null;
        List<Persion> list=new ArrayList<>();
        while (eu.hasNext()) {
            if (eu.validate()) {
                cp = (Persion) eu.getObjectBean();
                list.add(cp);
                System.out.println(cp.getName()+"/"+cp.getSex()+"/"+cp.getPhone()+"/"+cp.getEmail()+"/"+cp.getAddress());
            }
        }
        System.out.println(list.get(0).getName());

    }*/


   /**
     * 测试 
     * @param args
     * @throws Exception
     */
    public static void main(String[] args) throws Exception {
        InputStream is = new FileInputStream("C:\\Users\\Administrator\\Desktop\\test.xls");
        ExecelUtil eu = new ExecelUtil(is, "/import_job_number.xml", SevBankUserParams.class,".xls","dev");
        SevBankUserParams cp = null;
        List<SevBankUserParams> list=new ArrayList<>();
        while (eu.hasNext()) {
            if (eu.validate()) {
                cp = (SevBankUserParams) eu.getObjectBean();
                list.add(cp);
                System.out.println(cp.getUsername()+"/"+cp.getRealName());
            }
        }
        System.out.println(list.get(0).getRealName());

    }
}