import excel sheet to Guava Table

Question

Is it possible to import excel as a guava table object where the excel sheet contains more than 3 columns?

Getting confused on this as most code samples talk about only 3 columns in sheet as seen in below link too

https://www.geeksforgeeks.org/table-guava-java/

Answer 1

You misinterpret the Table<R,C,V>. This are not three columns but Rrow, Column and Value.

A Excel table would be a Table<String, String, Object> where row keys are R1, R2, R3, .. and the column keys are C1, C2, C3, ... The objects are the cell values.

When we get each cell content as String, then a Excel table would be:

Table<String, String, String> excelTable = HashBasedTable.create();

and a cell content would be put there like:

excelTable.put("R" + r, "C" + c, value);

Given an Excel sheet like:

The following code gets all it's content to a Guava Table.

import org.apache.poi.ss.usermodel.*;

import java.io.FileInputStream;

import java.util.Map;

import com.google.common.collect.HashBasedTable; 
import com.google.common.collect.Table; 

class ReadExcelToGuavaTable {

 public static void main(String[] args) throws Exception {

  Table<String, String, String> excelTable = HashBasedTable.create();

  Workbook workbook = WorkbookFactory.create(new FileInputStream("Excel.xlsx"));
  DataFormatter dataFormatter = new DataFormatter(java.util.Locale.US);
  FormulaEvaluator formulaEvaluator = workbook.getCreationHelper().createFormulaEvaluator();

  Sheet sheet = workbook.getSheetAt(0);
  int r = 1;
  int c = 1;
  for (Row row : sheet) {
   r = row.getRowNum() + 1;
   for (Cell cell : row) {
    c = cell.getColumnIndex() + 1;
    String value = dataFormatter.formatCellValue(cell, formulaEvaluator);
    //System.out.println("R" + r + "C" + c + " = " + value);
    excelTable.put("R" + r, "C" + c, value);
   }
  }

  // get Map corresponding to row 1 in Excel 
  Map<String, String> rowMap = excelTable.row("R1"); 
  System.out.println("List of row 1 content : "); 
  for (Map.Entry<String, String> row : rowMap.entrySet()) { 
   System.out.println("Column : " + row.getKey() + ", Value : " + row.getValue()); 
  } 

  // get a Map corresponding to column 4 in Excel
  Map<String, String> columnMap = excelTable.column("C4"); 
  System.out.println("List of column 4 content : "); 
  for (Map.Entry<String, String> column : columnMap.entrySet()) { 
   System.out.println("Row : " + column.getKey() + ", Value : " + column.getValue()); 
  } 

  // get single cell content R5C5
  System.out.println("Single cell content R5C5 :"); 
  System.out.println("R5C5 : " + excelTable.get("R5", "C5")); 

  // get all rows and columns
  Map<String,Map<String,String>> allMap = excelTable.rowMap();
  System.out.println("List of whole table : "); 
  for (Map.Entry<String, Map<String, String>> row : allMap.entrySet()) { 
   Map<String, String> colMap = row.getValue();
   for (Map.Entry<String, String> column : colMap.entrySet()) { 
    System.out.println(row.getKey() + column.getKey() + " = " + column.getValue()); 
   } 
  }   

  workbook.close();
 }
}