使用apache poi从excel创建guava hashmaptable

Question

使用apache poi从excel创建guava hashmaptable

我有一个Excel表格，它是这样构建的：

SiO2    pyrite    0

SiO2   siderite   57

SiO2   plag50     53

Al2O3  pyrite     0

Al2O3  siderite   32

etc…它们大约有400个条目。

现在，我想将这个带有Apache POI (XSSF)的Excel-sheet (2013)转换为一个芭乐hashmaptable<String, String, Integer>

Table <String, String, Integer> mins = HashBasedTable.create();
mins.put(“SiO2”, “siderite”, 57) 

等。

如何做到这一点？如何将这样的hashmaptable转换回excel工作表？提前感谢！

Answer 1

Table<String, String, Integer> table;
try (Workbook workbook = WorkbookFactory.create(new File("input.xlsx"))) {
    Sheet sheet = workbook.getSheet("SheetName");
    table = HashBasedTable.create(sheet.getPhysicalNumberOfRows(), 3);
    for (Row row : sheet) {
        String rowKey = row.getCell(0).getStringCellValue();
        String columnKey = row.getCell(1).getStringCellValue();
        int value = (int) row.getCell(2).getNumericCellValue();
        table.put(rowKey, columnKey, value);
    }
}
try (Workbook workbook = new XSSFWorkbook()) {
    Sheet sheet = workbook.createSheet("SheetName");
    int physicalNumberOfRows = 0;
    for (Table.Cell<String, String, Integer> cell : table.cellSet()) {
        Row row = sheet.createRow(physicalNumberOfRows++);
        row.createCell(0).setCellValue(cell.getRowKey());
        row.createCell(1).setCellValue(cell.getColumnKey());
        row.createCell(2).setCellValue(Objects.requireNonNull(cell.getValue()));
    }
    try (OutputStream outputStream = new FileOutputStream("output.xlsx")) {
        workbook.write(outputStream);
    }
}