CODEWITHSUNDEEP
javaapache-poi
Anish6595
Anish6595

Reputation: 108

excel, Apache Poi, unable to write to cell

I have been given a project which requires me to extract information from an excel file, do some calculations on the data and write the information back to an excel sheet. for some reason a part of my data just does feed into the cells. AN example is given below. 

package ExcelDocs;

import java.io.FileOutputStream;

import org.apache.poi.xssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;

public class Trial{

    public static void main(String[] agrs){

        Workbook wb=new XSSFWorkbook();
        Sheet ws=wb.createSheet("Testing");
        Cell c1=ws.createRow(0).createCell(0);
        c1.setCellValue("hello");
        Cell c2=ws.createRow(1).createCell(1);
        c2.setCellValue("how are you?");
        for(int i=1;i<7;i++){
            Cell c3=ws.createRow(i).createCell(0);
            c3.setCellValue(i);
        }

        try{ 
            FileOutputStream out=new FileOutputStream("ANISH.xlsx");
            wb.write(out);
            out.close();
        } catch(Exception e){
            System.out.println("unable to write to excel");
        }

    }
}

This code should generate the following output:

      COL1     COL2
ROW1  hello
ROW2           how are you?
ROW3
ROW4
ROW5
ROW6
ROW7

instead I get this as the output

      COL1     COL2
ROW1  hello
ROW2           
ROW3
ROW4
ROW5
ROW6
ROW7

can anyone tell me why the "how are you?" is getting deleted? I facing the same problem in my other programs too.

Upvotes: 1

Views: 2239

Answers (3)

Scary Wombat
Scary Wombat

Reputation: 44854

In your for loop the createRow is trashing the row that you created before.

So, before the loop, when you do Cell c2=ws.createRow(1).createCell(1); change this so that the Row object is saved.

Row r = Cell c2=ws.createRow(1);
r.createCell(1);

and use r in your loop too.

Upvotes: 1

Rahul
Rahul

Reputation: 45080

That's because you're overwriting the row created at index 1 in this for loop.

for(int i=1;i<7;i++){ // when i = 1
    Cell c3=ws.createRow(i).createCell(0); // it recreates a row at that index
    c3.setCellValue(i); // and re-writes it here in the loop.
}

You need to change the loop to start creating rows from the index 2.

for(int i=2; i<7; i++) { // now it starts from row index 2 and doesn't overwrite your previous row created at index 1
    Cell c3=ws.createRow(i).createCell(0); 
    c3.setCellValue(i);
}

Upvotes: 1

sadhu
sadhu

Reputation: 1479

This line is getting overwritten with the line in the for loop:

Cell c2=ws.createRow(1).createCell(1);

Upvotes: 2

Related Questions