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

Could you share the code you have tried?

M B
– M B

2020-11-28 07:36:58 +00:00
Commented Nov 28, 2020 at 7:36 — M B
– M B, Commented Nov 28, 2020 at 7:36

Abra · Accepted Answer · 2020-11-28 11:09:44Z

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

amount and branch are mismatched. When I change the code int i =1 into int i =0, the code repeatedly displayed and replaced the amount something like 10001, BO1, 10001
@AskWarvin my code answers your original question. Obviously if you change the code it may not work as you expect. Maybe you should consider posting a new question?

M B · Accepted Answer · 2020-11-28 08:20:18Z

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

radan · Accepted Answer · 2020-11-28 17:08:49Z

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

Collectives™ on Stack Overflow

Reading Excel File Column in Java

3 Answers 3

2 Comments

Comments

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

1 Comment

Related