[FIXED] Reading Excel File Column in Java

Issue

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

Solution

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

Answered By - Abra
Answer Checked By - Pedro (JavaFixing Volunteer)