springboot实现将Excel导入数据库

springboot实现将Excel导入数据库

只展示核心代码

1.service实现类

package com.tuanzi.service.impl;


import com.tuanzi.beans.HttpResponseEntity;
import com.tuanzi.common.Constans;
import com.tuanzi.dao.InformationMapper;
import com.tuanzi.entity.Information;
import com.tuanzi.service.InformationService;
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.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

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


@Service
public class InformationServiceImpl implements InformationService {

    private final Logger logger = LoggerFactory.getLogger(InformationServiceImpl.class);

    @Autowired
    private InformationMapper informationMapper;
    @Override
    public HttpResponseEntity getExcelInfo(String fileName, MultipartFile file)throws Exception{

        HttpResponseEntity httpResponseEntity = new HttpResponseEntity();

        int[] resultCell = new int[]{0,1,2,3,4};
        List<Information> resultList = new ArrayList<>();
        if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")){
            logger.error("上传文件不正确");
        }
        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);
        }
        Sheet sheet = wb.getSheetAt(0);
        resultList = getSheetVal(sheet,resultCell);
        System.out.println("结果是--->"+resultList);
        try{
            informationMapper.addUser(resultList);
            httpResponseEntity.setCode(Constans.SUCCESS_CODE);
            httpResponseEntity.setMessage("数据导入成功");
        }catch (Exception e){
            e.printStackTrace();
            httpResponseEntity.setCode(Constans.ADD_EXIST_CODE);
            httpResponseEntity.setMessage("数据导入失败");
        }
        return httpResponseEntity;
    }

    public List getSheetVal(Sheet sheet,int[] resultCell){
        List<Information> informationList = new ArrayList<>();
        int [] resultIndex = new int[resultCell.length];
        Information information;
        for (int r = 1;r <= sheet.getLastRowNum();r++){
            Row row = sheet.getRow(r);
            if (row == null){
                continue;
            }
            information = new Information();
            for (int i =  0;i<row.getPhysicalNumberOfCells();i++){
                String temp = getCellVal(row.getCell(i)).toString().trim();
                for (int j = 0;j<resultCell.length;j++){
                    if (i==resultCell[j]){
                        switch (i){
                            case 0:
                                information.setName(temp);
                                break;
                            case 1:
                                information.setAge(temp);
                                break;
                            case 2:
                                information.setPhone(temp);
                                break;
                            case 3:
                                information.setAddress(temp);
                                break;
                            case 4:
                                information.setEmail(temp);
                                break;
                            default:
                                break;
                        }
                    }else {
                        continue;
                    }
                }
            }
            informationList.add(information);

        }
        return informationList;
    }

    public Object getCellVal(Cell cell){
        Object obj  =  null;
        switch (cell.getCellTypeEnum()){
            case BOOLEAN:
                obj = cell.getBooleanCellValue();
                break;
            case ERROR:
                obj = cell.getErrorCellValue();
                break;
            case NUMERIC:
                obj = cell.getNumericCellValue();
                break;
            case STRING:
                obj = cell.getStringCellValue();
                break;
            default:
                break;
        }
        return obj;
    }
}

2.mapper

 void addUser(List<Information> resultList);

3.mapper.xml

<insert id="addUser" parameterType="java.util.List">
    insert into information (name,age,phone,address,email)
    values
    <foreach collection="list" item="item" index="index" separator=",">
      (#{item.name,jdbcType=VARCHAR}, #{item.age,jdbcType=VARCHAR},
      #{item.phone,jdbcType=VARCHAR}, #{item.address,jdbcType=VARCHAR}, #{item.email,jdbcType=VARCHAR}
      )
    </foreach>
  </insert>

4.效果图:




最后附赠完整源码

demo地址

博客地址

博客地址

码云代码地址

代码地址