Writing list of data in excel file using JAVA is entering the data only in the last column

Question

I am iterating through a list of data which I am sending from the runner file(FunctionVerifier.java). When I am calling the function writeExcel() in excelHandler.java it is entering only the last data from the list that I am iterating through.

Can someone please let me know the reason and how to fix this

public void writeExcel(String sheetName, int r, int c, String data) throws IOException {
    file = new FileInputStream(new File(inFilePath));
    wb = new XSSFWorkbook(file);
    Sheet sh;
    sh = wb.getSheet(sheetName);
    Row row = sh.createRow(r);
    row.createCell(c).setCellValue(data);
    closeExcelInstance();
    FileOutputStream outputStream = new FileOutputStream(inFilePath);
    wb.write(outputStream);
    wb.close();
    outputStream.close();
}

public void closeExcelInstance() {
    try {
        file.close();
    } catch (Exception e) {
        System.out.println(e);
    }
}

FunctionVerifier.java

    package Sterling.oms;

import Sterling.oms.Process.CouponValidationProcess;
import Sterling.oms.Utilities.ExcelHandler;

import java.util.ArrayList;

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

        String filePath = System.getProperty("user.dir") + "/src/test/resources/TestData/test.xlsx";
        ExcelHandler excelHandler = new ExcelHandler(filePath);
        excelHandler.readExcelData("Validation");
        CouponValidationProcess couponValidationProcess = new CouponValidationProcess("OMS-T781");
        excelHandler.createSheet();
//        couponValidationProcess.enterValidationHeaderRowInExcel(filePath);
        String sheet = "ValidationData";
        if (excelHandler.getRowCountWhenNull(sheet) < 1) {
            ArrayList<String> header = new ArrayList<>();
            header.add("Test Case");
            header.add("Coupon ID");
            header.add("Grand Total");
            header.add("Manual Grand Total");
            for (int i = 0; i < header.size(); i++) {
//                excelHandler = new ExcelHandler(filePath);
                excelHandler.writeExcel(sheet, 0, i, header.get(i));
//                excelHandler.closeExcelInstance();
            }
        }
    }
}

Axel Richter · Accepted Answer · 2022-07-10 04:41:47Z

2

The reason for only storing the last item is that Sheet.createRow as well as Row.createCell are doing exactly what their method names tell. They create a new empty row or cell each time they get called. So every times Row row = sh.createRow(r) gets called, it creates a new empty row at row index r and looses all former created cells in that row.

The correct way to use rows would be first trying to get the row from the sheet. And only if it is not present (null), then create a new row. The same is for cells in rows. First try to get them. And only if not present, then create them.

...
    Sheet sh;
    sh = wb.getSheet(sheetName);
    Row row = sh.getRow(r); if (row == null) row = sh.createRow(r);
    Cell cell = row.getCell(c); if (cell == null) cell = row.createCell(c);
    cell.setCellValue(data);
...

That's the answer to your current question.

But the whole approach, to open the Excel file to create a Workbook, then set data of only one cell in and then write the whole workbook out to the file, and doing this for each cell, is very sub optimal. Instead the workbook should be opened, then all known new cell values should be set into the sheet and then the workbook should be written out.

answered Jul 10, 2022 at 4:41

Axel Richter

62.5k7 gold badges70 silver badges95 bronze badges

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

1 Comment

Priyom saha Over a year ago

Thanks for explaining so well. It helped to take my new approach posted the answer below and it is working now. Let me know if this approach would be correct. As of now it is working for me.

fatih · Accepted Answer · 2022-07-09 23:02:33Z

1

Your approach is wrong, you open your files again for each line you want to write to Excel, then save again. You just have to create one FileInputStream and send it to your Workbook where you do all your Excel work. After you have finished writing all your lines, you can create only one FileOutputStream and export your changes in your Workbook to a file of your choice.

answered Jul 9, 2022 at 23:02

fatih

1,42015 silver badges30 bronze badges

1 Comment

Priyom saha Over a year ago

Hey thanks for replying ! Can you please show one sample with the way suggested. It would be very helpful

Priyom saha · Accepted Answer · 2022-07-10 04:50:46Z

0

writeExcel()

public void writeExcel(String sheetName, int r, int c, ArrayList<String> data) throws IOException {
    file = new FileInputStream(new File(inFilePath));
    wb = new XSSFWorkbook(file);
    Sheet sh;
    sh = wb.getSheet(sheetName);
    Row row = sh.createRow(r);
    //Adding data column wise
    for (String h : data) {
        row.createCell(c++).setCellValue(h);
    }
    closeExcelInstance();
    FileOutputStream outputStream = new FileOutputStream(inFilePath);
    wb.write(outputStream);
    wb.close();
    outputStream.close();
}

answered Jul 10, 2022 at 4:50

Priyom saha

6882 gold badges12 silver badges30 bronze badges

Collectives™ on Stack Overflow

Writing list of data in excel file using JAVA is entering the data only in the last column

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related