create guava hashmaptable from excel with apache poi

Question

Create guava hashmaptable from excel with apache poi

I have got an Excel-sheet which is built up like that:

SiO2    pyrite    0

SiO2   siderite   57

SiO2   plag50     53

Al2O3  pyrite     0

Al2O3  siderite   32

etc… they are around 400 entries.

I want now to transfer this Excel-sheet (2013) with Apache POI (XSSF) to a guava hashmaptable<String, String, Integer>

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

etc.

How to do that ? And how to transfer a hashmaptable like that back to an excel sheet ? Thanks in advance !

Please look at the documentation first, especially http://poi.apache.org/spreadsheet/quick-guide.html which will provide many pieces that you are trying to do and then ask specific questions instead of such a broad one. — centic, Mar 16 '16 at 09:42

score 1 · Answer 1 · answered Mar 16 '16 at 14:22

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);
    }
}

An additional problem arises: After creating and filling hasmaptable "table" I want to get the value to a key: for(Map.Entry oxSumMap : table.entrySet()) { System.out.println("in for-loop for oxsummap"); "+oxSumMap.getKey()); System.out.println("value: "+oxSumMap.getValue()); if (oxSumMap.getKey() =="SiO2") { System.out.println("in sio2-if");} It does not go into the if-clause..(????)) It seems to be so easy... also "toString" does not work... — daejon, Mar 17 '16 at 08:09
@daejon you are using `==`, not `equals()`. See http://stackoverflow.com/questions/7520432/what-is-the-difference-between-vs-equals-in-java for a refresher (it never hurts to review the fundamentals of a language, we all benefit from some repetition). — mfulton26, Mar 17 '16 at 12:42

create guava hashmaptable from excel with apache poi

1 Answers1