Update excel using Java

Question

I am trying to update a column in the excel sheet but only the first row is getting update. The second iteration is not working. Could anyone help me on this? Below is my code that I am trying.

    String excelPath = "path";

    String YES = "Updated YES";
    String NO = "Updated NO";
    try {

        FileInputStream fis= new FileInputStream(excelPath);


        HSSFWorkbook  workSheet = new HSSFWorkbook(fis);
        Cell cell = null;
        FileOutputStream output_file =new FileOutputStream(excelPath);

        for (int i = 0; i < TCID.size(); i++) {
            HSSFSheet sheet1 = workSheet.getSheetAt(0);
            String data = sheet1.getRow(i+1).getCell(i).toString();

            if(data.equals(TCID.get(i))){

                cell = sheet1.getRow(i+1).getCell(i+2);
                cell.setCellValue(YES);                 
                workSheet.write(output_file);
            }else {
                cell.setCellValue(NO);
                workSheet.write(output_file);
            }               

        }

        fis.close();
        output_file.close();
        workSheet.close();

    }catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }

}

My latest code is below. Now it is updating the columns but the last row is not getting updated. What am i missing.

FileInputStream fis= new FileInputStream(excelPath);

        HSSFWorkbook  workSheet = new HSSFWorkbook(fis);
        HSSFSheet sheet1 = workSheet.getSheetAt(0);
        //FileOutputStream output_file =new FileOutputStream(excelPath);
        for (int i = 0; i < TCID.size(); i++) {

            String data = sheet1.getRow(i+1).getCell(0).toString();
            Cell cell = sheet1.getRow(i+1).getCell(2);
            if(data.equals(TCID.get(i))){
                cell.setCellValue(YES);                 
            }else {
                cell.setCellValue(NO);
            }               

        }

        fis.close();
        //output_file.close();
        //workSheet.close();
        FileOutputStream output_file =new FileOutputStream(excelPath);
        workSheet.write(output_file);
        output_file.close();

In your else block` you are not populating the cell variable — Scary Wombat
– Scary Wombat, Commented Oct 28, 2019 at 6:27
Also move HSSFSheet sheet1 = workSheet.getSheetAt(0); to before your for loop - add some debugging to make sure that you are looping more than once and if this does not solve your issue then edit your question with your latest code, — Scary Wombat
– Scary Wombat, Commented Oct 28, 2019 at 6:41
I have updated code and it is updating the columns, but not the full columns. My method signature has list. I am passing this list and i m comparing the first column of the excel. The expected result should be, if both the value matches, it should update the third column as Yes and remaining values should be updated as NO. I am not sure, if I am iterating the loop correctly. Right now I am iterating on the list, I think I should iterate on the entire first column. Let me know if it makes sense. Also how to iterate on the entire column ? — Sayeed
– Sayeed, Commented Oct 28, 2019 at 8:04

Rick · Accepted Answer · 2019-10-28 06:47:25Z

1

The row and the column are both keyed off 'i', so you'll be traversing the sheet diagonally.

But certainly do the things the other people are recommending too, they are all good suggestions.

Typically when working with a two dimensional block of information, I find it useful to have one loop for rows and then nested inside that a loop for columns (or vice versa)

e.g.

for (y = startRow; y <= maxRow; y++) {

  ....

  for (x = startCol; x <= maxCol; x++) {


     .... // do something to each column in the current row

     cell = sheet1.getRow(y).getCell(x);

     .....

answered Oct 28, 2019 at 6:47

Rick

5763 silver badges12 bronze badges

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

1 Comment

Sayeed Over a year ago

what you said is correct. It is updating diagonally. I want to use only one for loop.

Ambro-r · Accepted Answer · 2019-10-28 07:37:55Z

Ok, so there are a couple of things.

Declare HSSFSheet sheet1 = workSheet.getSheetAt(0); outside of your loop. You are working with the same sheet each iteration of your for loop, so this only has to be invoked once.
Your logic to get the cell data (String data = sheet1.getRow(i+1).getCell(i).toString();) will not return you the same column. On your first iteration, you'll get (R)ow 1 : (C)olumn 0. The next iteration will return R2 : C1, then R2:C2, then R3:C3, etc. Notice the pattern that you are going diagonally down the columns, not vertically.
Rows start at 0.
You only need to workSheet.write(output_file); once you have done all your processing.
If the Row does not exist you will get a NullPointerException
As you are working with a unique Cell each iteration, you just declare it in the loop (so no need for Cell cell = null; outside your loop).

Here is a an example:

try {
  FileInputStream fis = new FileInputStream(excelPath);
  Workbook workbook = new HSSFWorkbook(fis);
  Sheet sheet = workbook.getSheetAt(0);
  for (int i = 0; i < 5; i++) {
    Row row = sheet.getRow(i);
    if (row != null) {
      Cell cell = row.getCell(0);
      cell.setCellValue("Updated cell.");
    }
  }
  FileOutputStream output_file =  new FileOutputStream(excelPath);
  workbook.write(output_file);
  output_file.close();
  fis.close();
} catch (Exception e) {
  e.printStackTrace();
}

@Sayeed, has your question been answered? If so please mark this as resolved, by checking the tick. Tx.

www.hybriscx.com · Accepted Answer · 2019-10-28 08:30:08Z

1

I think moving Cell reference inside for loop should fix it for you.

Cell cell = null;

Also you might also need to move outside if-block incase you face NullPointerException in else-block

cell = sheet1.getRow(i+1).getCell(i+2)

Something like this...

HSSFSheet sheet1 = workSheet.getSheetAt(0);
for (int i = 0; i < TCID.size(); i++) {
            String data = sheet1.getRow(i+1).getCell(0).toString();
            Cell cell = sheet1.getRow(i+1).getCell(2);
            if(data.equals(TCID.get(i))){
                cell.setCellValue(YES);                 
            }else {
                cell.setCellValue(NO);
            }
            workSheet.write(output_file)
       }

edited Oct 28, 2019 at 8:30

answered Oct 28, 2019 at 6:37

www.hybriscx.com

1,1296 silver badges22 bronze badges

10 Comments

Sayeed Over a year ago

actually i am passing a list of ids and comparing this list with the first column values one by one. If the the value is not present in the first column then updating the third column as No. Below is the method signature. private static void updateExecutionStatus(List<String> TCID, String columnName, String excelSheetName, String sheetName)

www.hybriscx.com Over a year ago

Then simply adding line in else-block in your program should or moving it outside if-else should help you....... cell = sheet1.getRow(i+1).getCell(i+2); Let me know if it works for your need.

Sayeed Over a year ago

Thanks for the suggestion. It is updating diagonally and the last row is not updated.

www.hybriscx.com Over a year ago

It is updating diagonally because you are doing getCell(i+2), and if you see value of i is increasing in for loop. Saying that, you would need to use a fix value instead of using i to get cell reference. Check updated answer

Sayeed Over a year ago

Thanx for ur help. It is updating correctly now. Only the last row is not getting updated. I have posted my latest code in the question.

|

Collectives™ on Stack Overflow

Update excel using Java

3 Answers 3

1 Comment

1 Comment

10 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

10 Comments

Your Answer

Sign up or log in

Post as a guest

Related