java实现导入Excel数据入库

java实现导入Excel数据入库

引入依赖

        <dependency>
            <groupId>jexcelapi</groupId>
            <artifactId>jxl</artifactId>
            <version>2.6</version>
        </dependency>

将Excel表格数据通过java导入到mysql数据库，废话不多说，直接上代码

如果导入失败，需要检查文件，另存为 97-2003版本的.xls

数据导入之后可以直接做其他业务操作

如果有错误的地方请指出，便于及时改正

package com.yihu.jw.base.endpoint.patient;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import com.alibaba.fastjson.serializer.SerializerFeature;
import com.yihu.jw.entity.base.patient.BasePatientDO;
import com.yihu.jw.patient.dao.BasePatientDao;
import com.yihu.jw.restmodel.web.ObjEnvelop;
import com.yihu.jw.restmodel.web.endpoint.EnvelopRestEndpoint;
import com.yihu.jw.rm.base.BaseRequestMapping;
import com.yihu.utils.security.MD5;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiParam;
import jxl.Sheet;
import jxl.Workbook;
import org.apache.commons.lang.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletRequest;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.*;

/***
 * @ClassName: ImportExcelDemo
 * @Description:
 * @Auther:
 * @Date: 2021/9/14 13:45
 */
@RestController
@RequestMapping(value = BaseRequestMapping.BasePatient.PREFIX)
@Api(value = "数据导入", description = "数据导入", tags = {"数据导入"})
public class ImportExcelDemo extends EnvelopRestEndpoint {

    @Autowired
    private BasePatientDao basePatientDao;

    /**
     * 将表格数据导入到 base_patient 表
     *  如果添加失败，将Excel改成97-2003 xls版本
     * @param request
     * @param file
     * @return
     */
    @RequestMapping(value = "/importPatientFromExcel", produces = "application/json;charset=UTF-8",method = RequestMethod.POST)
    @ResponseBody
    public ObjEnvelop importPatientFromExcel(HttpServletRequest request, @ApiParam(value = "文件", required = true)
    @RequestParam(value = "file", required = true) MultipartFile file) {
        List errorLs = new ArrayList<>();
        List correctLs = new ArrayList<>();
        List idcardList = new ArrayList<>();
        Map<String, String> errorMsgMap = new HashMap<>();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        List<String> deviceCodes = new ArrayList<>();
        try {
            request.setCharacterEncoding("UTF-8");
            InputStream inputStream = file.getInputStream();
            Workbook rwb = Workbook.getWorkbook(inputStream);
            Sheet[] sheets = rwb.getSheets();
            int rows;
            int row;
            String name=null;//用户姓名
            String sex; //性别
            String age; //年龄
            String idcard;  //身份证号
            String mobile;  //联系方式
            String residentialArea; //居住小区
            String address; //居住地址
            String signTeam; //签约团队
            String label; //能力类型
            Sheet sheet = sheets[0];    //第一张表
            rows = sheet.getRows();
            for (int j = 1; j < rows; j++) {
                if (sheet.getRow(j).length == 0) {
                    continue;
                }
                BasePatientDO basePatientVO = new BasePatientDO();
                JSONObject infoMap = new JSONObject();
                row = j;
                name = sheet.getCell(0, row).getContents().trim();    //0 用户姓名
                sex = sheet.getCell(1, row).getContents().trim();  //1 性别
                age = sheet.getCell(2, row).getContents().trim();   //2 年龄
                idcard = sheet.getCell(3, row).getContents().trim(); //3 身份证号
                mobile = sheet.getCell(4, row).getContents().trim();  //4 联系方式
                residentialArea = sheet.getCell(5, row).getContents().trim(); //5 居住小区
                address = sheet.getCell(6, row).getContents().trim(); //6 居住地址
                signTeam = sheet.getCell(7, row).getContents().trim();; //签约团队
                label = sheet.getCell(8, row).getContents().trim();; //能力类型
                if (StringUtils.isBlank(idcard)){
                    continue;
                }
                if (StringUtils.isBlank(name)){
                    errorMsgMap.put(idcard,"姓名不能为空");
                    continue;
                }
                if (StringUtils.isBlank(sex)){
                    sex = "3";
                } else {
                    if (sex.equals("男")) {
                        sex = "1";
                    } else {
                        sex = "2";
                    }
                }
                if (StringUtils.isBlank(signTeam)){
                    errorMsgMap.put(idcard,"签约团队不能为空");
                    continue;
                }

                if (StringUtils.isBlank(label)){
                    errorMsgMap.put(idcard,"能力类型不能为空");
                    continue;
                }
                basePatientVO.setAddress(address);
                basePatientVO.setDel("1");
                basePatientVO.setIdcard(idcard);
                basePatientVO.setSex(new Integer(sex));
                basePatientVO.setName(name);
                basePatientVO.setMobile(mobile);
                basePatientVO.setArchiveStatus(3);
                basePatientVO.setArchiveType(1); //默认添加老人
                String pw = idcard.substring(idcard.length()-6);
                String salt = UUID.randomUUID().toString().substring(0,5);
                basePatientVO.setPassword(MD5.md5Hex(pw + "{" + salt + "}"));
                basePatientVO.setSalt(salt);
                basePatientVO.setId(getUID());
                infoMap.put("idcard",idcard);
                infoMap.put("signTeam",signTeam);
                infoMap.put("label",label);
                infoMap.put("patient",getUID());
                correctLs.add(basePatientVO);
                idcardList.add(infoMap);
            }

            basePatientDao.save(correctLs);

            //包装导入结果（导入成功数量、错误对象集合）
            Map<String, Object> map = new HashMap<>();
            map.put("successNum", correctLs.size());
            map.put("failedNum", rows-1 - correctLs.size() );
            map.put("errorData", JSON.toJSONString(errorMsgMap, SerializerFeature.WriteMapNullValue));
            System.out.println(map);
            return ObjEnvelop.getSuccess("获取成功",map);
        } catch (Exception e) {
            e.printStackTrace();
            return failedObjEnvelopException(e);
        }
    }



}

实体类代码就没必要上代码了，一张图

测试方式：postman进行测试