springBoot读取excel并存入数据库

springBoot读取excel并存入数据库

代码块如下(适用与xls2003及xlsx2007版本)：
吾日三省吾身，高否，富否，帅否，否，学习去

maven依赖

<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
	<dependency>
		<groupId>org.apache.poi</groupId>
		<artifactId>poi</artifactId>
		<version>4.1.0</version>
	</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
	<dependency>
		<groupId>org.apache.poi</groupId>
		<artifactId>poi-ooxml</artifactId>
		<version>4.1.0</version>
	</dependency>

Mysql创建表语句

/*
 Navicat Premium Data Transfer

 Source Server         : 127.0.0.1
 Source Server Type    : MySQL
 Source Server Version : 50709
 Source Host           : localhost:3306
 Source Schema         : data

 Target Server Type    : MySQL
 Target Server Version : 50709
 File Encoding         : 65001

 Date: 04/05/2020 16:29:25
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for workers_data_df
-- ----------------------------
DROP TABLE IF EXISTS `workers_data_df`;
CREATE TABLE `workers_data_df`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `card_num` int(11) NOT NULL,
  `worker_name` varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL,
  `age` int(30) NULL DEFAULT NULL,
  `sex` varchar(30) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL,
  `address` varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL,
  `position` varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL,
  `work_date` date NULL DEFAULT NULL,
  `add_data_time` datetime(0) NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`, `card_num`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 91 CHARACTER SET = utf8 COLLATE = utf8_unicode_ci ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

excel示例样板

读取excel工具类

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
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.springframework.web.multipart.MultipartFile;

import com.project.main.excel.upload.entity.WorkersPO;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.util.*;
import java.util.logging.Logger;


/**
 * 读取Excel内容
 * @author Administrator
 *
 */

public class ExcelReader {

    private static Logger logger = Logger.getLogger(ExcelReader.class.getName()); // 日志打印类
    //Map<String,Object> map = new HashMap<String, Object>();

    private static final String XLS = "xls";
    private static final String XLSX = "xlsx";

    /**
               * 根据文件后缀名类型获取对应的工作簿对象
     * @param inputStream 读取文件的输入流
     * @param fileType 文件后缀名类型（xls或xlsx）
     * @return 包含文件数据的工作簿对象
     * @throws IOException
     */
    public static Workbook getWorkbook(InputStream inputStream, String fileType) throws IOException {
        Workbook workbook = null;
        if (fileType.equalsIgnoreCase(XLS)) {
            workbook = new HSSFWorkbook(inputStream);
        } else if (fileType.equalsIgnoreCase(XLSX)) {
            workbook = new XSSFWorkbook(inputStream);
        }
        return workbook;
    }

    /**
               * 读取Excel文件内容
     * @param fileName 要读取的Excel文件所在路径
     * @return 读取结果列表，读取失败时返回null
     */
    public static List<WorkersPO> readExcel(String fileName) {

        Workbook workbook = null;
        FileInputStream inputStream = null;

        try {
            // 获取Excel后缀名
            String fileType = fileName.substring(fileName.lastIndexOf(".") + 1, fileName.length());
            // 获取Excel文件
            File excelFile = new File(fileName);
            if (!excelFile.exists()) {
                logger.warning("指定的Excel文件不存在！");
            }

            // 获取Excel工作簿
            inputStream = new FileInputStream(excelFile);
            workbook = getWorkbook(inputStream, fileType);

            // 读取excel中的数据
            List<WorkersPO> resultDataList = parseExcel(workbook);

            return resultDataList;
        } catch (Exception e) {
            logger.warning("解析Excel失败，文件名：" + fileName + " 错误信息：" + e.getMessage());
            return null;
        } finally {
            try {
                if (null != workbook) {
                    workbook.close();
                }
                if (null != inputStream) {
                    inputStream.close();
                }
            } catch (Exception e) {
                logger.warning("关闭数据流出错！错误信息：" + e.getMessage());
                return null;
            }
        }
    }

    /**
               * 读取Excel文件内容
     * @param file 上传的Excel文件
     * @return 读取结果列表，读取失败时返回null
     */
    public static List<WorkersPO> readExcel(MultipartFile file) {

        Workbook workbook = null;

        try {
            // 获取Excel后缀名
            String fileName = file.getOriginalFilename();
            if (fileName == null || fileName.isEmpty() || fileName.lastIndexOf(".") < 0) {
                logger.warning("解析Excel失败，因为获取到的Excel文件名非法！");
                return null;
            }
            String fileType = fileName.substring(fileName.lastIndexOf(".") + 1, fileName.length());

            // 获取Excel工作簿
            workbook = getWorkbook(file.getInputStream(), fileType);

            // 读取excel中的数据
            List<WorkersPO> resultDataList = parseExcel(workbook);

            return resultDataList;
        } catch (Exception e) {
            logger.warning("解析Excel失败，文件名：" + file.getOriginalFilename() + " 错误信息：" + e.getMessage());
            return null;
        } finally {
            try {
                if (null != workbook) {
                    workbook.close();
                }
            } catch (Exception e) {
                logger.warning("关闭数据流出错！错误信息：" + e.getMessage());
                return null;
            }
        }
    }


    /**
               * 解析Excel数据
     * @param workbook Excel工作簿对象
     * @return 解析结果
     */
    private static List<WorkersPO> parseExcel(Workbook workbook) {
       List<WorkersPO> resultDataList = new ArrayList<>();
        // 解析sheet
        for (int sheetNum = 0; sheetNum < workbook.getNumberOfSheets(); sheetNum++) {
            Sheet sheet = workbook.getSheetAt(sheetNum);

            // 校验sheet是否合法
            if (sheet == null) {
                continue;
            }

            // 获取第一行数据
            int firstRowNum = sheet.getFirstRowNum();
            Row firstRow = sheet.getRow(firstRowNum);
            //if (null == firstRow) {
                //logger.warning("解析Excel失败，在第一行没有读取到任何数据！");
            //}

            // 解析每一行的数据，构造数据对象
            int rowStart = firstRowNum + 2; //获取第几行
            int rowEnd = sheet.getPhysicalNumberOfRows();
            for (int rowNum = rowStart; rowNum < rowEnd; rowNum++) {
                Row row = sheet.getRow(rowNum);

                if (null == row) {
                    continue;
                }

                WorkersPO resultData = convertRowToData(row);
                if (null == resultData) {
                    logger.warning("第 " + row.getRowNum() + "行数据不合法，已忽略！");
                    continue;
                }
                resultDataList.add(resultData);
            }
        }

        return resultDataList;
    }

    /**
     * 将单元格内容转换为字符串
     * @param cell
     * @return
     */
    private static String convertCellValueToString(Cell cell) {
        if(cell==null){
            return null;
        }
        String returnValue = null;
        switch (cell.getCellType()) {
            case NUMERIC:   //数字
                Double doubleValue = cell.getNumericCellValue();

                // 格式化科学计数法，取一位整数
                DecimalFormat df = new DecimalFormat("0");
                returnValue = df.format(doubleValue);
                break;
            case STRING:    //字符串
                returnValue = cell.getStringCellValue();
                break;
            case BOOLEAN:   //布尔
                Boolean booleanValue = cell.getBooleanCellValue();
                returnValue = booleanValue.toString();
                break;
            case BLANK:     // 空值
                break;
            case FORMULA:   // 公式
                returnValue = cell.getCellFormula();
                break;
            case ERROR:     // 故障
                break;
            default:
                break;
        }
        return returnValue;
    }

    /**
     * 提取每一行中需要的数据，构造成为一个结果数据对象
     *
     * 当该行中有单元格的数据为空或不合法时，忽略该行的数据
     *
     * @param row 行数据
     * @return 解析后的行数据对象，行数据错误时返回null
     */
    private static WorkersPO convertRowToData(Row row) {
    	WorkersPO resultData = new WorkersPO();

        Cell cell;
        int cellNum = 0;
        
        //获取工号
        cell = row.getCell(cellNum++);
        String cardNum = convertCellValueToString(cell);
        if (null == cardNum || "".equals(cardNum)) {
            // 年龄为空
            resultData.setCardNum(cardNum);
        } else {
            resultData.setCardNum(cardNum);
        }
        
        // 获取姓名
        cell = row.getCell(cellNum++);
        String workerName = convertCellValueToString(cell);
        if (null == workerName || "".equals(workerName)) {
            // 年龄为空
            resultData.setWorkerName(workerName);
        } else {
            resultData.setWorkerName(workerName);
        }
        
        // 获取年龄
        cell = row.getCell(cellNum++);
        String age = convertCellValueToString(cell);
        if (null == age || "".equals(age)) {
            // 年龄为空
            resultData.setAge(null);
        } else {
            resultData.setAge(age);
        }
        
        // 获取性别
        cell = row.getCell(cellNum++);
        String sex = convertCellValueToString(cell);
        resultData.setSex(sex);
        
        // 获取居住地
        cell = row.getCell(cellNum++);
        String address = convertCellValueToString(cell);
        resultData.setAddress(address);
        
        // 获取部门
        cell = row.getCell(cellNum++);
        String position = convertCellValueToString(cell);
        resultData.setPosition(position);
        
        // 获取入职时间
        cell = row.getCell(cellNum++);
        String workDate = convertCellValueToString(cell);
        resultData.setWorkDate(workDate);

        return resultData;
    }

}

实体类(推荐试用lombok插件，本文没用)

import java.sql.Date;

/**
 * 职工信息表实体
 * @author Administrator
 *
 */
public class WorkersPO {
	private String cardNum;
	private String workerName;
	private String age;
	private String sex;
	private String address;
	private String position;
	private String workDate;
	public String getCardNum() {
		return cardNum;
	}
	public void setCardNum(String cardNum) {
		this.cardNum = cardNum;
	}
	public String getWorkerName() {
		return workerName;
	}
	public void setWorkerName(String workerName) {
		this.workerName = workerName;
	}
	public String getAge() {
		return age;
	}
	public void setAge(String age) {
		this.age = age;
	}
	public String getSex() {
		return sex;
	}
	public void setSex(String sex) {
		this.sex = sex;
	}
	public String getAddress() {
		return address;
	}
	public void setAddress(String address) {
		this.address = address;
	}
	public String getPosition() {
		return position;
	}
	public void setPosition(String position) {
		this.position = position;
	}
	public String getWorkDate() {
		return workDate;
	}
	public void setWorkDate(String workDate) {
		this.workDate = workDate;
	}
	public WorkersPO(String cardNum, String workerName, String age, String sex, String address, String position,
			String workDate) {
		super();
		this.cardNum = cardNum;
		this.workerName = workerName;
		this.age = age;
		this.sex = sex;
		this.address = address;
		this.position = position;
		this.workDate = workDate;
	}
	public WorkersPO() {
		super();
	}
	@Override
	public String toString() {
		return "WorkersPO [cardNum=" + cardNum + ", workerName=" + workerName + ", age=" + age + ", sex=" + sex
				+ ", address=" + address + ", position=" + position + ", workDate=" + workDate + "]";
	}
}

Mapper类

import java.util.List;
import java.util.Map;

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

@Mapper
public interface WorkersMapper {
	/**
	 * 插入数据(读取excel入库)
	 * @param workersPO
	 * @return
	 */
	int insertWorkers(Map<String,Object> map);
	
	@Select("SELECT card_num FROM data.workers_data_df")
	List<String> getCardNum();
}

MyBatis

	<insert id="insertWorkers">
        INSERT INTO data.workers_data_df
        <trim prefix="(" suffix=")" suffixOverrides="," >
            <if test='cardNum != null and cardNum !=""'>
                card_num,
            </if>
            <if test='workerName != null and workerName !=""'>
                worker_name,
            </if>
            <if test='age != null and age !=""'>
                age,
            </if>
            <if test='sex != null and sex !=""'>
                sex,
            </if>
            <if test='address != null and address !=""'>
                address,
            </if>
            <if test='position != null and position !=""'>
                position,
            </if>
            <if test='workDate != null'>
                work_date
            </if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
             <if test='cardNum != null and cardNum !=""'>
             	#{cardNum,jdbcType=INTEGER},
            </if>
            <if test='workerName != null and workerName !=""'>
            	#{workerName,jdbcType=VARCHAR},
            </if>
            <if test='age != null and age !=""'>
            	#{age,jdbcType=INTEGER},
            </if>
            <if test='sex != null and sex !=""'>
            	#{sex,jdbcType=VARCHAR},
            </if>
            <if test='address != null and address !=""'>
            	#{address,jdbcType=VARCHAR},
            </if>
            <if test='position != null and position !=""'>
            	#{position,jdbcType=VARCHAR},
            </if>	
            <if test='workDate != null'>
            	#{workDate,jdbcType=DATE}
            </if>
        </trim>
    </insert>

Service类

import java.util.List;
import java.util.Map;


public interface WorkersService {
	/**
	 * 插入数据(读取excel入库)
	 * @param workersPO
	 * @return
	 */
	int insertWorkers(Map<String,Object> map);
	
	List<String> getCardNum();
}

Service实现类

import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import com.project.main.excel.upload.mapper.WorkersMapper;
import com.project.main.excel.upload.service.WorkersService;

@Service
public class WorkersServiceImpl implements WorkersService{
	
	@Autowired
	private WorkersMapper workersMapper;

	@Override
	public int insertWorkers(Map<String,Object> map) {
		return workersMapper.insertWorkers(map);
	}

	@Override
	public List<String> getCardNum() {
		return workersMapper.getCardNum();
	}
}

Controller类

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.CrossOrigin;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;

import com.project.main.excel.upload.entity.WorkersPO;
import com.project.main.excel.upload.service.WorkersService;
import com.project.main.excel.upload.utils.ExcelReader;
import com.project.main.utils.R;

@RestController
@CrossOrigin
public class WorkersController {
	
	@Autowired
	private WorkersService workersService;
	
	@GetMapping("/workersExcelUpload")
	public R workersExcelUpload(@RequestParam String excelFileName) {
		try {
			Map<String,Object> map = new HashMap<String, Object>();
			List<String> cardNumList = workersService.getCardNum(); //查询库里已有卡号
	        List<WorkersPO> readResult = ExcelReader.readExcel(excelFileName); //excel读取到的数据
	        System.out.println(readResult);
	        for (int i=0;i<readResult.size();i++){
	        	String cardNum = readResult.get(i).getCardNum();
	        	if(cardNumList.contains(cardNum)) { //只添加库中卡号没有数据进行判定
	        		continue;
	        	}else {
	        		map.put("cardNum", cardNum);
	        		map.put("workerName", readResult.get(i).getWorkerName());
	        		map.put("age", readResult.get(i).getAge());
	        		map.put("sex", readResult.get(i).getSex());
	        		map.put("address", readResult.get(i).getAddress());
	        		map.put("position", readResult.get(i).getPosition());
	        		map.put("workDate", readResult.get(i).getWorkDate());
	        		workersService.insertWorkers(map); //添加入库
	        	}
	        }
			return R.ok();
		} catch (Exception e) {
			e.printStackTrace();
			return R.error("数据导入失败，请规范导入模板");
		}
	}
}

返回示例工具类R.java见本博主其他文章有
到这里流程已经整体走完
更多好玩的代码及工具，可微信公众号搜索关注“小逸分享君”领取哦