使用POI实现excel和数据库的交互

最新推荐文章于 2021-02-04 12:09:16 发布

落日飛車

最新推荐文章于 2021-02-04 12:09:16 发布

阅读量325

点赞数

文章标签： java spring boot

本文链接：https://blog.csdn.net/qq_44365786/article/details/103950753

版权

首先是导入依赖

		<dependency>
            <groupId>commons-fileupload</groupId>
            <artifactId>commons-fileupload</artifactId>
            <version>1.3.1</version>
        </dependency>
        <dependency>
            <groupId>commons-io</groupId>
            <artifactId>commons-io</artifactId>
            <version>2.4</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.9</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.9</version>
        </dependency>
        <dependency>
            <groupId>org.apache.xmlbeans</groupId>
            <artifactId>xmlbeans</artifactId>
            <version>2.6.0</version>
        </dependency>

这里只导入此功能需要的依赖，一些基础的依赖如jdbc，driver并没有在这里显示

接下来是实体类

对应数据库是person表

@Data
public class Person implements Serializable {

    private Integer id;

    private String name;

    private Integer age;

}

对应的controller

@RestController
public class ExcelControl {

    @Autowired
    private ExcelService excelService;

    @PostMapping("/add")
    public Result addPerson(@RequestParam("file")MultipartFile file){
        Result result = new Result();
        String filename = file.getOriginalFilename();
        try {
            result = excelService.excleImport(filename,file);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return  result;
    }

}

对应的service实现类！！（重点）

主要的操作和逻辑都在这里

@Service
public class ExcelServiceImpl implements ExcelService {

    @Autowired
    private ExcelMapper excelMapper;


    @Override
    public Result excleImport(String fileName, MultipartFile file) throws Exception {
        Result result = new Result();
        List<Person> personList = new ArrayList<Person>();
        if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
            result.setMsg("上传文件格式错误");
            return result;
        }
        //03和07之后的文件命名不一样，需分别开来
        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);
        Person person = new Person();
        //r从1开始，因为数据是从第二行开始记录
        for (int r = 1; r <= sheet.getLastRowNum(); r++) {
            Row row = sheet.getRow(r);
            if (row == null){
                continue;
            }

            if( row.getCell(0).getCellType() !=HSSFCell.CELL_TYPE_STRING){
                result.setMsg("导入失败,名字应为文本");
            }else{
                String name = row.getCell(0).getStringCellValue();
                person.setName(name);
            }

            Cell cell = row.getCell(1);
            if(cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC){
                int age = (int) cell.getNumericCellValue();
                person.setAge(age);
            }else{
                result.setMsg("错误，年龄应为数字");
            }
            personList.add(person);
            result.setMsg("导入数据库成功");
        }
        for (Person personResord : personList) {
            //逐条导入数据到数据库
            excelMapper.addPerson(personResord);
        }

        return result;
    }

}

对应mapper

@Mapper
@Repository
public interface ExcelMapper {

    @Insert("insert into person(name,age) values(#{name},#{age})")
    @Results(value = {
            @Result(property = "name", column = "name"),
            @Result(property = "age", column = "age")
    })
    void addPerson(Person person);

}