springboot实现将excel数据导入数据库

• 前言
• 一、pandas是什么？
• 二、使用步骤
• • maven
  • 1.实体类代码
  • 2.mapper代码
  • xml文件
  • service 代码
  • controller
• 总结

提示：这里可以添加本文要记录的大概内容：

例如：随着人工智能的不断发展，机器学习这门技术也越来越重要，很多人都开启了学习机器学习，本文就介绍了机器学习的基础内容。

提示：以下是本篇文章正文内容，下面案例可供参考

            org.apache.poi
            poi-ooxml
            4.1.1
            compile
        
        
            cn.afterturn
            easypoi-annotation
            4.2.0
        
        
            org.apache.poi
            poi
            4.1.1
        

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

import java.util.Date;


@Data
@AllArgsConstructor
@NoArgsConstructor
public class WxAuthPersonnelInfo {
    //主键
    private String id;
    //分公司
 private String branch;
   
    //注册时间
    private Date registertime;


}

代码如下（示例）：

import com.suorui.wx.model.WxAuthPersonnelInfo;
import org.apache.ibatis.annotations.Mapper;


@Mapper
public interface WxAuthPersonnelInfoDao {

    //将excel数据插入到数据库
    void addUser(WxAuthPersonnelInfo wxAuthPersonnelInfo);

}

    
        insert into WX_AUTH_PERSONNEL_INFO
            (ID,BRANCH，REGISTERTIME)
        values(
               #{id,jdbcType=VARCHAR},
               #{BRANCH,jdbcType=VARCHAR}, 
               #{registertime,jdbcType=TIMESTAMP}
        )
    

import com.suorui.common.utils.EncryptPropUtil;
import com.suorui.common.utils.UuidUtil;
import com.suorui.wx.dao.WxAuthPersonnelInfoDao;
import com.suorui.wx.model.WxAuthPersonnelInfo;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;

import java.io.InputStream;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;


@Service
@Transactional(readOnly = true)
@Slf4j
public class WxAuthPersonnelInfoService {
    @Autowired
    private WxAuthPersonnelInfoDao wxAuthPersonnelInfoDao;
    @Autowired
    private EncryptPropUtil encryptPropUtil;

    @Transactional(readOnly = false,rollbackFor = Exception.class)
    public boolean batchImport(String fileName, MultipartFile file) throws Exception {

        boolean notNull = false;
        List userList = new ArrayList();
        if (!fileName.matches("^.+\.(?i)(xls)$") && !fileName.matches("^.+\.(?i)(xlsx)$")) {
          throw   new RuntimeException("选择文件格式不正确，请下载模板上传");
        }
        boolean isExcel2003 = true;
        if (fileName.matches("^.+\.(?i)(xlsx)$")) {
            isExcel2003 = false;
        }
        InputStream is = file.getInputStream();
        Workbook wb = null;

        if (isExcel2003) {
            wb = new HSSFWorkbook(is);
        } else {
            wb = new XSSFWorkbook(is);
        }
        // 获取excel的sheet页数
        int numberOfSheets = wb.getNumberOfSheets();
        //获取excel字段名称进行比较
        Sheet sheetAt = wb.getSheetAt(0);
        Row row1 = sheetAt.getRow(1);
        String xuhao = row1.getCell(0).getStringCellValue();
        String fengongsi = row1.getCell(1).getStringCellValue();
        String suoshuxian = row1.getCell(2).getStringCellValue();
        String zhijumingcheng = row1.getCell(3).getStringCellValue();
        String wanggemingcheng = row1.getCell(4).getStringCellValue();
        String kehujingli = row1.getCell(5).getStringCellValue();
        String lianxidianhua = row1.getCell(6).getStringCellValue();
        String gonghao = row1.getCell(7).getStringCellValue();
        String shuoming = row1.getCell(8).getStringCellValue();

        if ("序号".equals(xuhao) && "分公司".equals(fengongsi) && "所属县".equals(suoshuxian)
                && "支局名称".equals(zhijumingcheng) && "网格名称".equals(wanggemingcheng)
                && "客户经理".equals(kehujingli) && "联系电话".equals(lianxidianhua)
                && "工号".equals(gonghao) && "说明".equals(shuoming)){
            log.info("格式正确");

        }else {
            throw new RuntimeException("表字段格式错误，请下载模板");
        }

        for (int i = 0; i < numberOfSheets; i++) {
            Sheet sheet = wb.getSheetAt(i);
            if(sheet!=null){
                notNull = true;
            }
            WxAuthPersonnelInfo wxAuthPersonnelInfo;
            // 循环行数
            for (int r = 2; r <= sheet.getLastRowNum(); r++) {
                // 获取sheet的第r行的数据
                Row row = sheet.getRow(r);
                if (row == null){
                    continue;
                }

                wxAuthPersonnelInfo = new WxAuthPersonnelInfo();
                String uuid = UuidUtil.getUuid();
                wxAuthPersonnelInfo.setId(uuid);
                wxAuthPersonnelInfo.setStaffType("10005");
                wxAuthPersonnelInfo.setStaffSign("用户至上，用心服务");
                wxAuthPersonnelInfo.setStaffSex("1");
                wxAuthPersonnelInfo.setAuthStatus("0");
                wxAuthPersonnelInfo.setRegistertime(new Date());



                String areaCode = row.getCell(1).getStringCellValue();
                wxAuthPersonnelInfo.setAreaCode(areaCode);

                String county = row.getCell(2).getStringCellValue();
                wxAuthPersonnelInfo.setCounty(county);

                String branchName = row.getCell(3).getStringCellValue();
                wxAuthPersonnelInfo.setBranchName(branchName);

                String gridName = row.getCell(4).getStringCellValue();
                wxAuthPersonnelInfo.setGridName(gridName);

                String account_manager = row.getCell(5).getStringCellValue();
                //姓名加密
                String name = encryptPropUtil.encrPublic3des(account_manager);
                wxAuthPersonnelInfo.setStaffName(name);

                Cell cell = row.getCell(6);
                cell.setCellType(CellType.STRING);
                String phoneNum = row.getCell(6).getStringCellValue();
                wxAuthPersonnelInfo.setPhonenum(phoneNum);

                Cell cell1 = row.getCell(7);
                cell1.setCellType(CellType.STRING);
                String staffCode = row.getCell(7).getStringCellValue();
                wxAuthPersonnelInfo.setStaffCode(staffCode);

                String remarks = row.getCell(8).getStringCellValue();
                wxAuthPersonnelInfo.setRemarks(remarks);

                userList.add(wxAuthPersonnelInfo);

            }
        }

        for (WxAuthPersonnelInfo userResord : userList) {
                //数据插入到数据库
                wxAuthPersonnelInfoDao.addUser(userResord);
                //控制台输出插入的数据
                System.out.println(" 插入 "+userResord);
        }
        System.out.println(" 插入了 "+userList.size()+"条数据");
        return notNull;
    }

}

import com.suorui.wx.service.WxAuthPersonnelInfoService;
import com.suorui.wx.vo.CommRetVo;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.core.io.ClassPathResource;
import org.springframework.core.io.Resource;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.servlet.ModelAndView;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLEncoder;


@RestController
@RequestMapping("/excel")
@Slf4j
public class WxAuthPersonnelInfoController {
    @Autowired
   private WxAuthPersonnelInfoService wxAuthPersonnelInfoService;


    @GetMapping("/toexcel")
    public ModelAndView toexcel(){
        ModelAndView model = new ModelAndView();
        model.setViewName("/excel/upload");

        return model;
    }



    @PostMapping("/import")
    public CommRetVo addUser(@RequestParam("file") MultipartFile file) {
        CommRetVo commRetVo = new CommRetVo();
        boolean a = false;
        String fileName = file.getOriginalFilename();
        try {
            a = wxAuthPersonnelInfoService.batchImport(fileName, file);
            commRetVo.setCode("1");
            commRetVo.setMsg("成功");
        } catch (Exception e) {
            commRetVo.setCode("0");
            log.error("失败", e);
            log.info("{}",e.getMessage());
            String message = e.getMessage();
            commRetVo.setMsg(message);
        }
        return commRetVo;
    }

    @RequestMapping("/download")
    public CommRetVo downloadWorkHourRecordTemplate(HttpServletResponse response) {
        CommRetVo commRetVo = new CommRetVo();
        try {
            Resource resource = new ClassPathResource("static/excelTemplate/模板.xlsx");
            File file = resource.getFile();
            String filename = resource.getFilename();
            InputStream inputStream = new FileInputStream(file);
            //强制下载不打开
            response.setContentType("application/force-download");
            OutputStream out = response.getOutputStream();
            //使用URLEncoder来防止文件名乱码或者读取错误
            response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(filename, "UTF-8"));
            int b = 0;
            byte[] buffer = new byte[1000000];
            while (b != -1) {
                b = inputStream.read(buffer);
                if (b != -1) {
                    out.write(buffer, 0, b);
                }
            }
            inputStream.close();
            out.close();
            out.flush();
            commRetVo.setCode("1");
            commRetVo.setMsg("下载成功");

        } catch (IOException e) {

            e.printStackTrace();
            commRetVo.setCode("0");
            commRetVo.setMsg("失败");
        }
        return commRetVo;
    }


}