导入-竖表头导入

业务场景

最近笔记在工作中遇到了一份数据文件导入的需求，这次是竖版表头的Excel,处理方式如下。

解决方案

1、相关依赖

        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.1.2</version>
        </dependency>

2、方法构建

    @PostMapping(value = "/import", headers = "content-type=multipart/form-data")
    @ApiOperation(value = "导入", notes = "excel导出", response = ShoreSpareEntity.class)
    public ServiceResponse importExcel(MultipartFile file) throws Exception {

        //Excel数据解析
        Workbook hssfWorkbook = WorkbookFactory.create(file.getInputStream());
        Map<String, List<String>> map = readExcel(hssfWorkbook);

        //数据循环入库
        Integer num = portGuideService.addByMap(map);
        return ServiceResponse.ok("成功导入" + num + "条数据");

    }

    private Map<String, List<String>> readExcel(Workbook workbook) throws Exception {
        Map<String, List<String>> hashMap = new HashMap<>();
        // 循环工作表Sheet
        for (int numSheet = 0; numSheet < workbook.getNumberOfSheets(); numSheet++) {
            Sheet sheet = workbook.getSheetAt(numSheet);
            int lastRowNum = sheet.getLastRowNum();
            for (int rowNum = 0; rowNum <= lastRowNum; rowNum++) {
                List<String> list = new ArrayList<>();
                Row row = sheet.getRow(rowNum);
                String key = "";
                if (row == null) {
                    continue;
                }

                int physicalNumberOfCells = row.getPhysicalNumberOfCells();
                for (int cellNum = 0; cellNum < physicalNumberOfCells; cellNum++) {
                    String stringCellValue = sheet.getRow(rowNum).getCell(cellNum).getStringCellValue();
                    if ((cellNum == 0)) {
                        key = stringCellValue;
                    } else {
                        list.add(stringCellValue);
                    }
                }
                //key-字段名称  list-相关数据集合
                hashMap.put(key, list);
            }
        }
        workbook.close();
        return hashMap;

    }

	public Integer addByMap(Map<String, List<String>> map) {
        List<PortGuideEntity> list = new ArrayList<>();
        Integer num = map.get("港口名词").size();
        for (Integer i = 0; i < num; i++) {
            PortGuideEntity entity = new PortGuideEntity();
            entity.setPortName(getValue(map, "港口名词", i));
            entity.setCountry(getValue(map, "国家", i));
            entity.setNum(getValue(map, "编号", i));
            ......
            entity.setHydrology(getValue(map, "水文资料", i));
            list.add(entity);
        }
        this.saveBatch(list);
        return num;

    }

    private String getValue(Map<String, List<String>> map, String key, Integer num) {
        List<String> list = map.get(key);
        return list.get(num);

    }

尾言

以上便是对竖表头Excel文件导入至数据库的处理过程，有需要的同学可以参考下。