springBoot读取excel并存入数据库

用户734402819342
4 阅读6分钟

本文介绍如何使用SpringBoot结合Apache POI库读取Excel文件，并将数据存储到MySQL数据库中。涵盖Maven依赖配置、创建数据库表、读取Excel的工具类、实体类、Mapper、Service及Controller的实现。 springBoot读取excel并存入数据库 代码块如下(适用与xls2003及xlsx2007版本)： 吾日三省吾身，高否，富否，帅否，否，学习去

maven依赖

<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi</artifactId>
	<version>4.1.0</version>
</dependency>

<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见本博主其他文章有 到这里流程已经整体走完 更多好玩的代码及工具，可微信公众号 搜索关注“小逸分享君”领取哦 原文链接：blog.csdn.net/weixin_4464…

avatar
文章
阅读
粉丝