Reading Excel File Column in Java

Question

I have a sample of Excel data that needs to be read and store in the database. Example:

Code  | BO1    | BO2    | BO3    | .....
10001 | 200.00 | 400.00 | 0.00   | .....
10002 | 0.00   | 100.00 | 500.00 | .....

I have tried to use Apache POI in reading data by row. But how do I get the Name and the amount from the column at the same time? Something like this

10001,BO1,200.00
10001,BO2,400.00
10001,BO3,0.00
10002,BO1,0.00
10002,BO2,100.00
10002,BO3,500.00

Any help would be appreciated. Thanks

FileInputStream fis = (FileInputStream) files.getInputStream();
        POIFSFileSystem fs = new POIFSFileSystem(fis);
        HSSFWorkbook wb = new HSSFWorkbook(fs);
        HSSFSheet sheet = wb.getSheetAt(0);
        Row row;
        for(int i=4; i<=sheet.getLastRowNum(); i++)
        {
            row = sheet.getRow(i);
            String gl;
            Cell cell1 = row.getCell(0);
            if(cell1.getCellType()==Cell.CELL_TYPE_NUMERIC)
            {
                gl = String.valueOf(cell1.getNumericCellValue()).replace(".0", "").trim();
            }
            else
            {
                gl = cell1.getStringCellValue();
            }
            write.println("<p>"+gl+"</p>");
        } 

Answer 1

My approach is to read column names and store them in the list for later use during the read of rows with actual data. After data from the row being parsed result is stored into map with code as key and list of pairs: cell value and cell header name.

  private static void readExcel(XSSFSheet sheet) {
        Map<Long, List<Column>> data = new HashMap<>();
        List<String> columnNames = new ArrayList<>();
        boolean isTableHeaderFound = false;
        for (int i = 0; i <= sheet.getLastRowNum(); i++) {
            XSSFRow currentRow = sheet.getRow(i);
            if (skipRow(currentRow)) continue;
            if (!isTableHeaderFound) {
                setTableHeader(currentRow, columnNames);
                isTableHeaderFound = true;
                continue;
            }
            readRowValues(data, currentRow, columnNames);
        }
        printData(data);
    }

Read row method:

private static void readRowValues(Map<Long, List<Column>> data, XSSFRow currentRow, List<String> columnNames) {
        Long code = -1L;
        List<Column> rowValues = new ArrayList<>();
        for (short cellIdx = currentRow.getFirstCellNum(); cellIdx < currentRow.getLastCellNum(); cellIdx++) {
            if (cellIdx == currentRow.getFirstCellNum()) {
                code = Long.valueOf((int)currentRow.getCell(cellIdx).getNumericCellValue());
            } else {
                rowValues.add(new Column(columnNames.get(cellIdx), Double.valueOf(currentRow.getCell(cellIdx).getNumericCellValue())));
            }
        }
        data.put(code, rowValues);
    }

Read cell column names:

    private static void setTableHeader(XSSFRow currentRow, List<String> columnNames) {
        for (short cellIdx = currentRow.getFirstCellNum(); cellIdx < currentRow.getLastCellNum(); cellIdx++)
            columnNames.add(currentRow.getCell(cellIdx).getStringCellValue());
    }

Helper method which determines wheter row should be skipped:

    private static boolean skipRow(XSSFRow currentRow) {
        if(currentRow == null) return  true;
        if (currentRow.getLastCellNum() == currentRow.getFirstCellNum()) return true;
        for (short cellIdx = currentRow.getFirstCellNum(); cellIdx < currentRow.getLastCellNum(); cellIdx++)
            if (currentRow.getCell(cellIdx) != null)
                return false;
        return true;
    }

Finally printing result:

  private static void printData(Map<Long, List<Column>> data) {
        for (Map.Entry<Long, List<Column>> entry : data.entrySet()) {
            for (Column column : entry.getValue()) {
                System.out.println(entry.getKey() + "," + column.getColumnName() + "," + column.getValue());
            }
        }
    }

Answer 2

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

public class XlReader {

    public static void main(String[] args) {
        try (InputStream inp = (FileInputStream) files.getInputStream()) {
            Workbook wb = WorkbookFactory.create(inp);
            Sheet sheet = wb.getSheetAt(0);
            Row row = sheet.getRow(0);
            List<String> columnNames = new ArrayList<>();
            for (Iterator<Cell> iterator = row.cellIterator(); iterator.hasNext();) {
                Cell cell = iterator.next();
                String value = cell.getStringCellValue();
                columnNames.add(value);
            }
            Iterator<Row> iterator = sheet.iterator();
            if (iterator.hasNext()) {
                iterator.next();
            }
            List<String> rows = new ArrayList<>();
            while (iterator.hasNext()) {
                row = iterator.next();
                Cell code = row.getCell(0);
                double d = code.getNumericCellValue();
                int k = Double.valueOf(d).intValue();
                StringBuilder sb = new StringBuilder();
                for (int i = 1; i < columnNames.size(); i++) {
                    sb = new StringBuilder();
                    sb.append(k);
                    sb.append(',');
                    sb.append(columnNames.get(i));
                    sb.append(',');
                    Cell cell = row.getCell(i);
                    sb.append(String.format("%.2f", cell.getNumericCellValue()));
                    rows.add(sb.toString());
                }
            }
            rows.forEach(System.out::println);
        }
        catch (IOException xIo) {
            xIo.printStackTrace();
        }
    }
}

Result:

10001,BO1,200.00
10001,BO2,400.00
10001,BO3,0.00
10002,BO1,0.00
10002,BO2,100.00
10002,BO3,500.00

Answer 3

You will also need Apache POI ooxml in order to work with tables. The code below should achieve something similar to the format you described.

DataFormatter formatter = new DataFormatter();
try (XSSFWorkbook workbook = new XSSFWorkbook(Main.class.getResourceAsStream("workbook.xlsx"))){
    int sheetCount = workbook.getNumberOfSheets();
    // iterate through all sheets
    for (int i = 0; i < sheetCount; i++) {
        XSSFSheet sheet = workbook.getSheetAt(i);
        // iterate through all tables
        for (XSSFTable table : sheet.getTables()) {
            int startRow = table.getStartRowIndex();
            int startCol = table.getStartColIndex();
            // iterate through all cells in the table
            for (CellReference cellReference : table.getCellReferences()
                  .getAllReferencedCells()) {
                if (cellReference.getRow() == startRow) {
                    // skip header row
                    continue;
                } else if (cellReference.getCol() == startCol) {
                    // skip 'Code' column to obtain the desired format
                    continue;
                }
                Cell codeCell = sheet.getRow(cellReference.getRow())
                      .getCell(startCol);
                Cell cell = sheet.getRow(cellReference.getRow())
                      .getCell(cellReference.getCol());
                XSSFTableColumn column = table.getColumns()
                      .get(cellReference.getCol() - startCol);
                System.out.printf("%s,%s,%s\n", formatter.formatCellValue(codeCell), column.getName(),
                      formatter.formatCellValue(cell));
            }
        }
    }
} catch (IOException e) {
    e.printStackTrace();
}