关于springboot将Excel表导入至数据库以及数据回显范例

我们需要使用到workbook类，首先要导入相对应的maven

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

创建工具类，用于读取Excel表内容

package edu.gdlgxy.newexaminationsystem.utils;

import edu.gdlgxy.newexaminationsystem.bean.inputExcel;
import lombok.extern.slf4j.Slf4j;
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.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.List;

@Repository
@Slf4j
public class ReadExcel {
    @Autowired
    JdbcTemplate jdbcTemplate;

    /**
     * 读取excel文件内容
     *
     * @throws Exception
     */
    @Transactional
    public void readExcel(String filePathByString, String fileName) throws Exception {
        String realPath = filePathByString + "/" + fileName;
        InputStream is = new FileInputStream(new File(realPath));
        Workbook hssfWorkbook = null;
        if (fileName.endsWith("xlsx")) {
            hssfWorkbook = new XSSFWorkbook(is);//Excel 2007
        } else if (fileName.endsWith("xls")) {
            hssfWorkbook = new HSSFWorkbook(is);//Excel 2003
        }
        inputExcel inputExcel = null;
        List<inputExcel> list = new ArrayList<>();
        // 循环工作表Sheet
        for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
            //HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
            Sheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
            if (hssfSheet == null) {
                continue;
            }
            // 循环行Row
            for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
                Row hssfRow = hssfSheet.getRow(rowNum);
                if (hssfRow != null) {
                    inputExcel = new inputExcel();
                    Cell id = hssfRow.getCell(0);
                    Cell name = hssfRow.getCell(1);
                    Cell sex = hssfRow.getCell(2);
                    Cell identityCode = hssfRow.getCell(3);
                    Cell stuNumber = hssfRow.getCell(4);
                    Cell eduBackground = hssfRow.getCell(5);
                    Cell enrollmentYear = hssfRow.getCell(6);
                    Cell subjectCode = hssfRow.getCell(7);
                    Cell examTitle = hssfRow.getCell(8);
                    Cell examLevel = hssfRow.getCell(9);
                    Cell telNumber = hssfRow.getCell(10);
                    Cell subordinateDepartments = hssfRow.getCell(11);
                    Cell major = hssfRow.getCell(12);
                    Cell classNumber = hssfRow.getCell(13);

                    //这里是自己的逻辑
                    //把得到Cell类型的id转换成String=》double=》int
                    //id
                    String s1 = String.valueOf(id);
                    double v1 = Double.parseDouble(s1);
                    BigDecimal realId= BigDecimal.valueOf(v1);
//                    inputExcel.setId(realId);

                   //名称
                    inputExcel.setName(name.toString());
                   //性别
                   inputExcel.setSex(sex.toString());

                   //把得到Cell类型的num转换成String=》double=》int
                   //身份证号码
                    inputExcel.setIdentityCode(identityCode.toString());
                    //学号
                    String s3 = String.valueOf(stuNumber);
                    double v3 = Double.parseDouble(s3);
                    BigDecimal realstuNumber = BigDecimal.valueOf(v3);
                    inputExcel.setStuNumber(realstuNumber);
                    //学历
                    inputExcel.setEduBackground(eduBackground.toString());
                    //入学年份
                    //目前的enrollmentYear是Cell类型的
                    inputExcel.setEnrollmentYear(enrollmentYear.toString());

                    //报考科目
                    inputExcel.setExamTitle(examTitle.toString());
                    //科目代码
                   inputExcel.setSubjectCode(subjectCode.toString());
                    //等级
                   inputExcel.setExamLevel(examLevel.toString());
                    //手机号码
                    String s5 = String.valueOf(telNumber);
                   double v5 = Double.parseDouble(s5);
                    BigDecimal realTelNumber = BigDecimal.valueOf(v5);
                    inputExcel.setTelNumber(realTelNumber);
                    //所属院系
                    inputExcel.setSubordinateDepartments(subordinateDepartments.toString());
                   //所属专业
                   inputExcel.setMajor(major.toString());
                    //班级
                    inputExcel.setClassNumber(classNumber.toString());
                    list.add(inputExcel);
                    }
                }
        }



        //先把原表的数据导进备份表（留痕）
        String backupSQL= "INSERT INTO BACKUP SELECT * FROM inputExcel";
        jdbcTemplate.update(backupSQL);

        //再次插入数据时，先把原先的数据删掉，再重新更新新的数据
        Long count = jdbcTemplate.queryForObject("select count(*) from inputExcel",Long.class);
        if(count>0){
            jdbcTemplate.update("TRUNCATE TABLE inputexcel;");
        }
        //循环插入list集合里面的Student对象属性
        for (int i = 0; i < list.size(); i++) {
            inputExcel ie = list.get(i);
            String sql="insert into inputExcel(id,name,sex,identityCode,stuNumber,eduBackground,enrollmentYear,subjectCode," +
                    "examTitle,examLevel,telNumber,subordinateDepartments,major,classNumber)values (?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
            Object[] args={ie.getId(),ie.getName(),ie.getSex(),ie.getIdentityCode(),ie.getStuNumber(),ie.getEduBackground(),
                            ie.getEnrollmentYear(),ie.getSubjectCode(),ie.getExamTitle(),ie.getExamLevel(),ie.getTelNumber(),
                            ie.getSubordinateDepartments(),ie.getMajor(),ie.getClassNumber()};
            jdbcTemplate.update(sql,args);
        }

        }


    }

使用范例

/**
     * 接收管理端web传过来的excel表
     *
     * @param uploadExcel
     * @return
     * @throws IOException
     */
    @PostMapping("/uploadExcel")
    public String uploadExcel(@RequestPart("certificate") MultipartFile uploadExcel) throws Exception {
        if (!uploadExcel.isEmpty()) {
            //保存到文件服务器，OSS服务器
            String originalFilename = uploadExcel.getOriginalFilename();//获取excel表的名字
            log.info("导入的excel的表格名字是：" + originalFilename);//eg：学分导入模板.xlsx
            File filepath = new File("D:\\saveCertificateExcel\\");
            String filePathByString = filepath.toString();//file类型文件路径转string类型
            log.info(filepath.toString());//打印路径
            if (!filepath.exists()) {
                filepath.mkdir();
            }
            uploadExcel.transferTo(new File("D:\\saveCertificateExcel\\" + originalFilename));
            read.readExcel(filePathByString, originalFilename);//这里调用读取excel文件内容方法
        }
        return "success";
    }

    //  选择用缓冲区来实现这个转换即使用java 创建的临时文件 使用 MultipartFile.transferto()方法
    private File transferToFile(MultipartFile multipartFile) {
        File file = null;
        try {
            String originalFilename = multipartFile.getOriginalFilename();
            String[] filename = originalFilename.split(".");
            file = File.createTempFile(filename[0], filename[1]);
            multipartFile.transferTo(file);
            file.deleteOnExit();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return file;
    }

前端页面，这里使用了Thymeleaf模板引擎

<html lang="en" xmlns:th="http://www.thymeleaf.org">

<!--上传-->
        <div >
            <div class="row">
                <div class="col-lg-12">

                    <form role="form" th:action="@{/uploadExcel}" method="post" enctype="multipart/form-data">
                        <div class="form-group">
                            <label for="exampleInputFile">选择excel表</label>
                            <input type="file" name="certificate" id="exampleInputFile">
                        </div>
                        <button type="submit" class="btn btn-primary">上传</button>
                    </form>
                    <br><br><br><br>

                </div>
            </div>
        </div>

    <!--上传-->

至此结束！