spingboot+layui+poi实现excel导入功能，并存入数据库

好好607

已于 2022-03-06 10:36:07 修改

阅读量865

点赞数 2

文章标签： java

于 2021-05-21 14:45:53 首次发布

原文链接：https://blog.csdn.net/bigsimpleton/article/details/105193056

版权

第一步：引入依赖

        <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>

第二步：设置前端按钮，并给后端传值（感谢@bigsimpleton/https://blog.csdn.net/bigsimpleton/article/details/105193056）

<button type="button" class="layui-btn layui-btn-primary" id="uploadExcel">批量导入</button>

layui.use('upload', function() {
      var upload = layui.upload;
      upload.render({
          elem: '#uploadExcel'
          , url:'importExcel'//路径这看你怎么配置的怎么写
          , field:"file"
          , exts: 'xls|xlsx|xlsm|xlt|xltx|xltm'
          , done: function (res) {
              if(res.success){
                  parent.layer.msg('导入成功！', {icon: 1,time:2000,shade:0.2});
              }
          }
      });
  });

第三步：运用工具类，进行文档内容的获取以及批量插入方法的实现（感谢班级大佬教学）

    @ResponseBody
    @RequestMapping("/importExcel")
    public CURDResult importExcel(@RequestParam("file") MultipartFile file) throws IOException {
        CURDResult curdResult=new CURDResult();

        file.getInputStream();
        ExcelRead excelRead=new ExcelRead();
        String[] files = file.getOriginalFilename().split("\\.");//分隔符
        List<Employee> list=null;
        if (files[files.length-1].equals("xls")){//对后缀经许比对
            list = excelRead.ReadXls((FileInputStream) file.getInputStream());//获取输入流
        }else {
            list=excelRead.ReadXlsx((FileInputStream) file.getInputStream());
        }

        //进行批量插入
        System.out.println(list);
        employeeService.insertBatch(list);

        return curdResult;
    }

package com.gyf.szcrm.util;


import com.gyf.szcrm.domain.Employee;
import org.apache.poi.hssf.usermodel.HSSFCell;
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 java.io.FileInputStream;
import java.text.DecimalFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * @Author Bamboo
 * @Date 2021/5/7 14:56
 * @Version 1.0
 */
public class ExcelRead<T> {

    /**
     * 传入流
     * @param inputStream
     */
    public List<Employee> ReadXls(FileInputStream inputStream){
        List<Employee> list=new ArrayList<>();
        try{

            Workbook xwb = new HSSFWorkbook(inputStream);
            // 循环工作表sheet
            for(int numSheet = 0; numSheet < xwb.getNumberOfSheets(); numSheet++) {
                Sheet sheet = xwb.getSheetAt(numSheet);
                if(sheet == null) {
                    continue;
                }
                // 循环row，如果第一行是字段，则 numRow = 1
                for(int numRow = 0; numRow <= sheet.getLastRowNum(); numRow++) {
                    Row row = sheet.getRow(numRow);
                    if(row == null) {
                        continue;
                    }


                    Employee employee=new Employee();
                    employee.setName(getValue(row.getCell(1)));
                    employee.setPassword(getValue(row.getCell(2)));
                    employee.setEmail(getValue(row.getCell(5)));

                    list.add(employee);
                }
            }
        }catch(Exception e) {
            e.printStackTrace();
        }
        return list;
    }

    /**
     * 传入流
     * @param inputStream
     */
    public List<Employee> ReadXlsx(FileInputStream inputStream){
        List<Employee> list=new ArrayList<>();
        try{

            Workbook hwb = new XSSFWorkbook(inputStream);
            // 循环工作表sheet
            for(int numSheet = 0; numSheet < hwb.getNumberOfSheets(); numSheet++) {
                Sheet sheet = hwb.getSheetAt(numSheet);
                if(sheet == null) {
                    continue;
                }
                // 循环row，如果第一行是字段，则 numRow = 1
                for(int numRow = 1; numRow <= sheet.getLastRowNum(); numRow++) {
                    Row row = sheet.getRow(numRow);
                    if(row == null) {
                        continue;
                    }
                    System.out.println(row.getCell(2));
                    //xls表格中读取的数据=====>201842311
                    //xls表格中读取的数据=====>张三14
                    //xls表格中读取的数据=====>女
                    //xls表格中读取的数据=====>57
                    //xls表格中读取的数据=====>13458332381
                    //xls表格中读取的数据=====>19765664@qq.com
                    //xls表格中读取的数据=====>测试部
                    //xls表格中读取的数据=====>2021-03-25
                    Employee employee=new Employee();
                      employee.setName(getValue(row.getCell(1)));
                      employee.setPassword(getValue(row.getCell(2)));
                      employee.setEmail(getValue(row.getCell(3)));
                      employee.setAge(Long.valueOf(getValue(row.getCell(2))));
//                      employee.setDept_id();
//                      employee.setHireDate(DateChange(getValue(row.getCell(8))));
//                      employee.setState();
//                      employee.setAdmin();
                      employee.setRole(getValue(row.getCell(9)));
//                    employee.setUsername(getValue(row.getCell(1)));
//                    employee.setRealname(getValue(row.getCell(2)));
//                    employee.setTel(getValue(row.getCell(5)));
//                    employee.setEmail(getValue(row.getCell(6)));
//                    employee.setInputtime(DateChange(getValue(row.getCell(8))));
//                    employee.setDept(getValue(row.getCell(7)));
//                    //初始化密码为12345
//                    employee.setPassword(PasswordUtil.encodePwd("123456"));
//                    employee.setBirthDay(DateChange(getValue(row.getCell(4))));
//                    employee.setSex(getValue(row.getCell(3)));
//                    employee.setState(1);
                    list.add(employee);
//
                }
            }
        }catch(Exception e) {
            e.printStackTrace();
        }
        return list;
    }

    /**
     * CELL_TYPE_NUMERIC	数值型	    0
     * CELL_TYPE_STRING	    字符串型	    1
     * CELL_TYPE_FORMULA	公式型	    2
     * CELL_TYPE_BLANK	    空值	        3
     * CELL_TYPE_BOOLEAN	布尔型	    4
     * CELL_TYPE_ERROR	    错误  	    5
     */
    public String getValue(Cell cell){
        String value = null;
        if (cell==null){
            return null;
        }
        switch(cell.getCellType()){
            case HSSFCell.CELL_TYPE_NUMERIC: // 数字
                //如果为时间格式的内容
                if (HSSFDateUtil.isCellDateFormatted(cell)) {
                    //注：format格式 yyyy-MM-dd hh:mm:ss 中小时为12小时制，若要24小时制，则把小h变为H即可，yyyy-MM-dd HH:mm:ss
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                    value=sdf.format(HSSFDateUtil.getJavaDate(cell.
                            getNumericCellValue())).toString();
                    break;
                } else {
                    value = new DecimalFormat("0").format(cell.getNumericCellValue());
                }
                break;
            case HSSFCell.CELL_TYPE_STRING: // 字符串
                value = cell.getStringCellValue();
                break;
            case HSSFCell.CELL_TYPE_BOOLEAN: // Boolean
                value = cell.getBooleanCellValue() + "";
                break;
            case HSSFCell.CELL_TYPE_FORMULA: // 公式
                value = cell.getCellFormula() + "";
                break;
            case HSSFCell.CELL_TYPE_BLANK: // 空值
                value = "";
                break;
            case HSSFCell.CELL_TYPE_ERROR: // 故障
                value = "非法字符";
                break;
            default:
                value = "未知类型";
                break;
        }
        return value;
    }

    public Date DateChange(String date){
        Date  dateTime=null;
        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
        try {
            dateTime= simpleDateFormat.parse(date);

        } catch (ParseException e) {
            e.printStackTrace();
        }
        return dateTime;
    }
}

    <insert id="insertBatch">
        insert into employee (name,password,email,age,dept_id,hireDate,state,admin,role)
        values
        <foreach collection="list" item="item" separator=",">
            (#{item.name},#{item.password},#{item.email},#{item.age},#{item.dept_id},#{item.hireDate},#{item.state},#{item.admin},#{item.role})
        </foreach>
    </insert>

CURDResult.java
下面展示一些 内联代码片。

package com.gyf.szcrm.domain;

public class CURDResult {
    int success = 1;
    String msg = "";

    public int getSuccess() {
        return success;
    }

    public void setSuccess(int success) {
        this.success = success;
    }

    public String getMsg() {
        return msg;
    }

    public void setMsg(String msg) {
        this.msg = msg;
    }
}