使用POI 技术将Excel表格读取-并写入数据库

一、创建测试工程并添加依赖

<!--xls-->
<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi</artifactId>
	<version>3.9</version>
</dependency>
<!--xlsx-->
<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi-ooxml</artifactId>
	<version>3.9</version>
</dependency>
<!--文件上传-->
<dependency>
	<groupId>commons-fileupload</groupId>
	<artifactId>commons-fileupload</artifactId>
	<version>1.3.1</version>
</dependency>

二、业务处理

(1)EduSubjectController

@RestController
@RequestMapping("/teacher/subject")
public class EduSubjectController {

    @Autowired
    private EduSubjectService subjectService;
    @ApiOperation(value = "Excel批量导入")
    @PostMapping("/import")
    public Result addUser(
            @ApiParam(name = "file",value = "Excel文件")
            @RequestParam("file") MultipartFile file
    ){
        try {
            List<String> msg= subjectService.batchImport(file);
            if(msg.size()==0){
                return Result.ok().message("批量导入成功");
            }else{
                return Result.error().message("部分数据导入丢失").data("messageList",msg);
            }
        } catch (Exception e) {
            throw new TeacherException(ResultCodeEnum.EXCEL_DATA_ERROR);
        }
    }
}

(2)SubjectService
接口

public interface EduSubjectService extends IService<EduSubject> {

    List<String> batchImport(MultipartFile file);
}
}

实现类  本测试使用了两列做测试 如果列数较多可以参考上一篇POI的文章获取列数再遍历列进行读取

@Service
@Slf4j
public class EduSubjectServiceImpl extends ServiceImpl<EduSubjectMapper, EduSubject> implements EduSubjectService {
    /**
     * version 1.0
     * @param file
     * @return
     */
    @Transactional
    @Override
    public List<String> batchImport(MultipartFile file) {
        List<String> msg = new ArrayList<>();
        try {
            //1 传入文件流
            ExcelImportHSSFUtil excelHSSFUtil = new ExcelImportHSSFUtil(file.getInputStream());
            //2 获取sheet
            Sheet sheet = excelHSSFUtil.getSheet();
            //3 获取表中的行数
            int rowCount = sheet.getPhysicalNumberOfRows();
            //3.1 判断行数
            if(rowCount<=1){
                msg.add("请填写数据");
                return msg;
            }
            //3.2 遍历各行
            for (int rowNum = 1; rowNum < rowCount; rowNum++) {
                //3.3 通过获取各行信息
                Row rowData = sheet.getRow(rowNum);
                //3.4 判断行是否为空
                if(rowData!=null){//行不为空
                    //一级分类名称
                    String levelOneValue ="";
                    //4 获取各行第一列信息
                    Cell levelOneCell = rowData.getCell(0);
                    //4.1 判断该列是否为空
                    if(levelOneCell!=null){
                        //4.2 获取行和本列确定单元格的值
                    levelOneValue = excelHSSFUtil.getCellValue(levelOneCell, levelOneCell.getCellType());
                        log.error("一级title-{}",levelOneValue);
                        //4.3 判断获取到的单元格值是否为空
                        if (StringUtils.isEmpty(levelOneValue)){
                            msg.add("第"+rowNum+"行一级分类为空");
                            continue;
                        }
                    }
                    //5 通过获取到的单元格的值查询该值是否字儿数据库中已经存在
                    EduSubject subject = this.getByTitle(levelOneValue);
                    EduSubject subjectLevelOne = null;
                    String parentId = null;
                    //5.1 判断从数据库中查到的课程是否为空
                    if(subject==null){//创建一级分类
                        subjectLevelOne = new EduSubject();
                        subjectLevelOne.setTitle(levelOneValue);
                        subjectLevelOne.setSort(0);
                        subjectLevelOne.setParentId("0");
                       //5.2 当数据库中不存在这个一级分类时,向数据库中存入该值 并获取id作为二级分类的父id
                        baseMapper.insert(subjectLevelOne);
                        parentId = subjectLevelOne.getId();
                    }else {
                        //5.3 当数据库中存在这个一级分类时,直接获取id作为二级分类的父id
                        parentId = subject.getId();
                    }
                    //二级分类名称
                    String levelTwoValue = "";
                    //6 获取第二列的信息
                    Cell levelTwoCell = rowData.getCell(1);
                     //6.1 判断该列是否为空
                    if(levelTwoCell!=null){
                        //6.2 获取该行的和本列确定的单元格的值
                     levelTwoValue = excelHSSFUtil.getCellValue(levelTwoCell,levelTwoCell.getCellType());
                        log.error("二级title-{}",levelOneValue);
                        //6.3 判断该单元格的值是否为空
                        if(StringUtils.isEmpty(levelTwoValue)){
                            msg.add("第"+rowNum+"行二级分类为空");
                            continue;
                        }
                    }
                    //7 通过该单元格的值和父类id 查询数据库中是否存在该值
                    EduSubject subjectSub = this.getSubByTitle(levelTwoValue,parentId);
                    EduSubject subjectLevelTwo = null;
                    //7.1 判断从数据库中查到的课程是否为空
                    if(subjectSub==null){//创建二级分类
                        subjectLevelTwo = new EduSubject();
                        subjectLevelTwo.setTitle(levelTwoValue);
                        subjectLevelTwo.setParentId(parentId);
                        subjectLevelTwo.setSort(0);
                        //7.2 当数据库中不存在这个二级分类时,向数据库中存入该值
                        baseMapper.insert(subjectLevelTwo);//添加二级分类
                    }

                }
            }
        } catch (Exception e) {
            throw new TeacherException(ResultCodeEnum.EXCEL_DATA_ERROR);
        }
        //8 返回异常的信息 如果该信息中值 那么以上所都不会执行
        return msg;
    }

}

//service层辅助方法
/**
 * 根据分类名称和父id查询这个一级分类是否存在
 * @param title
 * @return EduSubject
 */
private EduSubject getByTitle(String title) {
    QueryWrapper<EduSubject> queryWrapper = new QueryWrapper<>();
    queryWrapper.eq("title",title);
    queryWrapper.eq("parent_id",0);
    return baseMapper.selectOne(queryWrapper);
}

/**
 *  根据分类名称和父id查询这个二级分类中是否存在
 * @param title
 * @param parentId
 * @return EduSubject
 */
private EduSubject getSubByTitle(String title, String parentId) {
    QueryWrapper<EduSubject> queryWrapper = new QueryWrapper<>();
    queryWrapper.eq("title",title).eq("parent_id",parentId);
    return baseMapper.selectOne(queryWrapper);
}

ExcelImportHSSFUtil 工具类

public class ExcelImportHSSFUtil {

    //计算表达式
    private HSSFFormulaEvaluator formulaEvaluator;
    //sheet表
    private HSSFSheet sheet;
    private String pattern;// 日期格式

    public String getPattern() {
        return pattern;
    }

    public void setPattern(String pattern) {
        this.pattern = pattern;
    }

    public HSSFFormulaEvaluator getFormulaEvaluator() {
        return formulaEvaluator;
    }

    public void setFormulaEvaluator(HSSFFormulaEvaluator formulaEvaluator) {
        this.formulaEvaluator = formulaEvaluator;
    }

    public HSSFSheet getSheet() {
        return sheet;
    }

    public void setSheet(HSSFSheet sheet) {
        this.sheet = sheet;
    }

    public ExcelImportHSSFUtil() {
        super();
    }

    public ExcelImportHSSFUtil(InputStream is) throws IOException {
        this(is, 0, true);
    }

    public ExcelImportHSSFUtil(InputStream is, int seetIndex) throws IOException {
        this(is, seetIndex, true);
    }

    public ExcelImportHSSFUtil(InputStream is, int seetIndex, boolean evaluateFormular) throws IOException {
        super();
        HSSFWorkbook workbook = new HSSFWorkbook(is);
        this.sheet = workbook.getSheetAt(seetIndex);
        if (evaluateFormular) {
            this.formulaEvaluator = new HSSFFormulaEvaluator(workbook);
        }
    }

    /**
     * 根据列和列的类型获取行与列确定的单元格的值
     * @param cell
     * @param cellType
     * @return 返回单元格的值
     * @throws Exception
     */
    public String getCellValue(Cell cell, int cellType) throws Exception {

        switch (cellType) {
            case Cell.CELL_TYPE_NUMERIC://0

                if (HSSFDateUtil.isCellDateFormatted(cell)) {//日期
                    Date date = cell.getDateCellValue();
                    if (pattern != null) {
                        SimpleDateFormat sdf = new SimpleDateFormat(pattern);
                        return sdf.format(date);
                    } else {
                        return date.toString();
                    }
                } else {
                    // 不是日期格式，则防止当数字过长时以科学计数法显示
                    cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                    return cell.toString();
                }

            case Cell.CELL_TYPE_STRING://1 字符串类型
                return cell.getStringCellValue();
            case Cell.CELL_TYPE_FORMULA://2 公式

                if (this.formulaEvaluator == null) {//得到公式
                    return cell.getCellFormula();
                } else {//计算公式
                    CellValue evaluate = this.formulaEvaluator.evaluate(cell);
                    cellType = evaluate.getCellType();
                    return String.valueOf(this.getCellValue(cell, cellType));
                }
            case Cell.CELL_TYPE_BLANK://3 空值
                //注意空和没值不一样，从来没有录入过内容的单元格不属于任何数据类型，不会走这个case
                return "";
            case Cell.CELL_TYPE_BOOLEAN://4 布尔值
                return String.valueOf(cell.getBooleanCellValue());
            case Cell.CELL_TYPE_ERROR: // 数据类型错误
                throw new Exception("数据类型错误");
            default:
                throw new Exception("未知数据类型:" + cellType);
        }
    }
}

 

转载于:https://my.oschina.net/u/4152198/blog/3095483