记一次Excel导入数据

写在 前面

使用Excel导入过程比较简单,就是由于POI的版本,所以之前的一些方法可能不能使用了,特此开一篇文章记录.

使用POI依赖

<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi</artifactId>
			<version>3.17</version>
		</dependency>

主要代码

 @RequestMapping(value = "/upload", method = RequestMethod.POST)
    public OperaResult upload(@ApiParam(value = "上传的文件", required = true) MultipartFile file) {
        OperaResult operaResult = new OperaResult();
        try {
            if (file.isEmpty()) {
                operaResult.setResultCode(OperaResult.OPT_RESULT_CODE_FAIL);
                operaResult.setResultDesc("上传文件为空");
                operaResult.setMessageType(OperaResult.Error);
            }
            InputStream inputStream = file.getInputStream();
            Workbook workbook = null;
            //判断excel版本号
            //xls是excel2003及以前版本生成的文件格式
            if (file.getOriginalFilename().endsWith("xls")) {
                workbook = new HSSFWorkbook(inputStream);
                //xlsx是excel2007及以后版本生成的文件格式。
            } else if (file.getOriginalFilename().endsWith("xlsx")) {
                workbook = new XSSFWorkbook(inputStream);
            }
            for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
                //获得excel中的工作表
                Sheet sheet = workbook.getSheetAt(i);
                //判断sheet是否有数据
                if (sheet.getPhysicalNumberOfRows() <= 0) {
                    continue;
                }
                //存放集合
                List<TupleTxpyUserInfo> list = new ArrayList<>();
                //第一行头部信息是固定的,我不需读取第一行数据
                //从第二行数据开始,读取数据,生成对象
                for(int j = 1; j <= sheet.getLastRowNum(); j++){
                    Row row = sheet.getRow(j);
                    //判断row是否有数据
                    if (row.getPhysicalNumberOfCells() <= 0) {
                        continue;
                    }

               
                    TupleTxpyUserInfo tupleTxpyUserInfo = new TupleTxpyUserInfo();

                    //生成user对象基本属性
                    tupleTxpyUserInfo.setMobile(XlsxDataUtil.getStringXlsx(row.getCell(0)));


                    tupleTxpyUserInfo.setNickname(row.getCell(1).getStringCellValue());
                    tupleTxpyUserInfo.setEmail(XlsxDataUtil.getStringXlsx(row.getCell(6)));

                    //获取userInfo的基本属性
                    SysUserInfo userInfo = new SysUserInfo();

                    userInfo.setSex(XlsxDataUtil.getStringXlsx(row.getCell(2))=="男"?"1":"0");
                    SimpleDateFormat dff = new SimpleDateFormat("yyyy-MM-dd");

                    userInfo.setBirthday(dff.parse(XlsxDataUtil.getStringXlsx(row.getCell(3))));
                    userInfo.setNation(XlsxDataUtil.getStringXlsx(row.getCell(4)));
                    userInfo.setNationPlace(XlsxDataUtil.getStringXlsx(row.getCell(5)));
                    userInfo.setEducation(XlsxDataUtil.getStringXlsx(row.getCell(7)));
                    userInfo.setUnit(XlsxDataUtil.getStringXlsx(row.getCell(8)));
                    userInfo.setUnitAddr(XlsxDataUtil.getStringXlsx(row.getCell(9)));

                    //此处的职称是字符串,需要在单点中进行拆分,查询
                    userInfo.setTitle(XlsxDataUtil.getStringXlsx(row.getCell(11)));

                    //首先将数据存储到自定义职称上面,到单点中进行拆分
                    SysUserSubject sysUserSubject = new SysUserSubject();
                    sysUserSubject.setCustom(XlsxDataUtil.getStringXlsx(row.getCell(10)));
                    List<SysUserSubject> subjects = new ArrayList<>();
                    subjects.add(sysUserSubject);

                    //开始封装TupleTxpyUserInfo
                    tupleTxpyUserInfo.setSysUserInfo(userInfo);
                    tupleTxpyUserInfo.setSysUserSubjects(subjects);
                    list.add(tupleTxpyUserInfo);


                }
                ....
                //省略数据库CRUD逻辑
            }

        } catch (IOException  e) {
            log.info("excel文件上传文件出错");
            e.printStackTrace();
            operaResult.setResultCode(OperaResult.OPT_RESULT_CODE_FAIL);
            operaResult.setResultDesc("后台上传文件出错");
            operaResult.setMessageType(OperaResult.Error);
        }catch (ParseException e){
            log.info("生日信息格式错误");
            e.printStackTrace();
            operaResult.setResultCode(OperaResult.OPT_RESULT_CODE_FAIL);
            operaResult.setResultDesc("生日信息格式错误");
            operaResult.setMessageType(OperaResult.Error);
        }
        return operaResult;
    }

工具类

这个工具类主要是对cell中的数据做对应的处理,返回String格式

public class XlsxDataUtil {
    public static String getStringXlsx(Cell cell){
        CellType cellType = cell.getCellTypeEnum();
        //存放值
        String cellValue = "";
        //字符串
        if (cellType == CellType.STRING) {
            cellValue = cell.getStringCellValue().trim();
            cellValue = StringUtil.isNullOrEmpty(cellValue) ? "" : cellValue;
        }
        //数据格式
        if (cellType == CellType.NUMERIC) {
            //判断日期类型
            if (HSSFDateUtil.isCellDateFormatted(cell)) {
                SimpleDateFormat dff = new SimpleDateFormat("yyyy-MM-dd");
                String date1 = dff.format(cell.getDateCellValue());
                cellValue = date1;
            } else {
                cellValue = new DecimalFormat("#").format(cell.getNumericCellValue());
            }
        }
        if (cellType == CellType.BOOLEAN) {
            cellValue = String.valueOf(cell.getBooleanCellValue());
        }
        return cellValue;
    }
}