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 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
– centic, Commented Mar 16, 2016 at 9:42

mfulton26 · Accepted Answer · 2016-03-16 14:22:17Z

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

answered Mar 16, 2016 at 14:22

mfulton26

31.4k7 gold badges71 silver badges93 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

daejon Over a year ago

An additional problem arises: After creating and filling hasmaptable "table" I want to get the value to a key: for(Map.Entry<String, Integer> 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...

mfulton26 Over a year ago

@daejon you are using ==, not equals(). See stackoverflow.com/questions/7520432/… for a refresher (it never hurts to review the fundamentals of a language, we all benefit from some repetition).

Collectives™ on Stack Overflow

create guava hashmaptable from excel with apache poi

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related