java解析导入Excel数据

发表于 分类于 阅读次数： 本文字数： 54 阅读时长 ≈ 1 分钟

java解析导入Excel数据，项目实例。

依赖

1
2
3
4
5
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.14</version>
</dependency>

示例

生成临时模板文件解析数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
// 获取文件名
String fileName = multiFile.getOriginalFilename();
// 获取文件后缀
String prefix = fileName.substring(fileName.lastIndexOf("."));
File file = File.createTempFile(fileName, prefix);
multiFile.transferTo(file);

//set excel机柜名称不唯一性 行数
Set<Integer> rowSet = new HashSet<Integer>();
//set excel机柜名称不唯一性 行数
List<Cabinet> cabinetList = new ArrayList<Cabinet>();

//声明一个工作薄
Workbook workbook = WorkbookFactory.create(new FileInputStream(file));
Sheet sheet = workbook.getSheetAt(0);
System.out.println(fileName + " 表格行数 " + sheet.getLastRowNum());
for (int i = 1; i < sheet.getLastRowNum() - 1; i++) {
    Row row = sheet.getRow(i);
    Cabinet cabinet = new Cabinet();
    if (row != null &&
    !row.getCell(0).toString().equals("") &&
    !row.getCell(1).toString().equals("") &&
    !row.getCell(2).toString().equals("") &&
    !row.getCell(3).toString().equals("")) {

        String cabinetName = row.getCell(1).toString();
        String cabinetType = row.getCell(2).toString();
        String siteName = row.getCell(3).toString();
        cabinet.setCabinetName(cabinetName);
        cabinet.setCabinetType(cabinetType);
        cabinet.setSiteName(siteName);

        //判断机柜名称是否唯一
        if (!cabinetService.checkOnlyCabinetName(cabinetName)) {
            if (cabinetType.equals("ODF架")) {
                if (!row.getCell(4).toString().equals("")) {
                    Double s = Double.parseDouble(row.getCell(4).toString());
                    cabinet.setSiteLine(s.intValue());
                }
                if (!row.getCell(5).toString().equals("")) {
                    Double s = Double.parseDouble(row.getCell(5).toString());
                    cabinet.setSiteList(s.intValue());
                }
            } else {
                String oxcVolume = row.getCell(6).toString();
                cabinet.setSiteLine(1);
                cabinet.setSiteList(1);
                cabinet.setOxcVolume(oxcVolume);
            }
            cabinet.setAOrB(row.getCell(8).toString());
            if (!row.getCell(7).toString().equals("")) {
                cabinet.setTerminal(row.getCell(7).toString());
            }
            if (!row.getCell(9).toString().equals("")) {
                cabinet.setDemo(row.getCell(9).toString());
            }
            cabinetList.add(cabinet);
        } else {
            //不唯一
            rowSet.add(i);
        }
}

直接解析文件流数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
public JSONObject importFibercore(@RequestParam("uploadFile") MultipartFile file) {
        JSONObject result;
        int count = 0;
        try {
            InputStream in = file.getInputStream();
            XSSFWorkbook wb = new XSSFWorkbook(in);
            //获取文件中的表格
            XSSFSheet sheet = wb.getSheetAt(0);
            //获取文件中的长度从0开始
            int rowNum = sheet.getLastRowNum() + 1;
            int cellNum = sheet.getRow(1).getPhysicalNumberOfCells();
            for (int i = 2; i <= rowNum; i++) {
                XSSFRow firstRow = sheet.getRow(0);
                String firstString = firstRow.getCell(0).getStringCellValue();
                String siteName;
                String cabinetType;
                if (firstString.contains("机房")) {
                    String[] name1 = firstString.split("机房");
                    siteName = name1[0] + "机房";
                    String name2 = name1[1];
                    if (name2.contains("光交")) {
                        cabinetType = "光交";
                    } else {
                        cabinetType = "ODF";
                    }
                } else {
                    String[] name1 = firstString.split("光交");
                    siteName = name1[0] + "光交";
                    String name2 = name1[1];
                    if (name2.contains("光交")) {
                        cabinetType = "光交";
                    } else {
                        cabinetType = "ODF";
                    }
                }
                String cabinetName = firstString.split("-")[0];
                String aOrB = firstString.split("-")[1];
                for (int j = 4; j <= cellNum; j++) {
                    XSSFRow row = sheet.getRow(i);
                    XSSFCell cell = row.getCell(j);
                    DataFormatter df = new DataFormatter();
                    if (df.formatCellValue(cell) != null && !df.formatCellValue(cell).equals("")) {
                        String fibercoreName = row.getCell(j).getStringCellValue();
                        Fibercore fibercore = new Fibercore();
                        fibercore.setFibercoreId(setFibercoreId());
                        fibercore.setFibercoreName(fibercoreName);
                        fibercore.setFibercoreLine(String.valueOf(i - 1));
                        fibercore.setFibercoreList(String.valueOf(j - 3));
                        fibercore.setSiteName(siteName);
                        fibercore.setAOrB(aOrB);
                        fibercore.setCabinetType(cabinetType);
                        fibercore.setCabinetName(cabinetName);
                        if (row.getCell(1) != null) {
                            String opticalCable = getMergedRegionValue(sheet, i, 1);
                            fibercore.setOpticalCable(opticalCable);
                            String opticalPort = df.formatCellValue(row.getCell(3));
                            fibercore.setOpticalPort(opticalPort);
                            fibercore.setCreateTime(new Date());
                            fibercore.setUpdateTime(new Date());
                            fibercore.setCreateMan(ShiroUtils.getProfile().getUserName());
                            log.info("导入纤芯信息：{}", fibercore);
                            fibercoreService.addFibercore(fibercore);
                            count++;
                        }
                    }
                }
            }
            result = Result.succ(200, "T", count);
        } catch (Exception e) {
            log.error("导入异常", e);
            result = Result.fail("F", "导入纤芯异常，导入" + count + "个");
        }
        log.info("[fibercore/importFibercore]:结果" + result);
        return result;
    }

判断空列或者空行

1
2
3
4
if (df.formatCellValue(sheet.getRow(i).getCell(j)).isBlank()) {
    System.out.println("第" + i + "行" + "第" + j + "列为空");
    break;
}