excel, Apache Poi, unable to write to cell

Question

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.

ohh sorry 1 2 3 4 5 6 is also being written. only the "how are you?" gets deleted — Anish6595
– Anish6595, Commented Feb 3, 2014 at 6:37

sadhu · Accepted Answer · 2014-02-03 06:39:16Z

2

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

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

answered Feb 3, 2014 at 6:39

sadhu

1,4898 silver badges15 bronze badges

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

3 Comments

Anish6595 Over a year ago

but im trying to write "1" in the cell to the left of it. so why does the "how are you get deleted?" and how can i overcome this problem?

sadhu Over a year ago

But it re-creates the whole row including the column where you wrote how are you?

sadhu Over a year ago

change the loop counter to start from 2: for(int i=2; i<7; i++) {

Rahul · Accepted Answer · 2014-02-03 06:39:30Z

1

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

answered Feb 3, 2014 at 6:39

Rahul

45.2k11 gold badges89 silver badges108 bronze badges

Comments

Scary Wombat · Accepted Answer · 2014-02-03 07:16:04Z

1

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.

edited Feb 3, 2014 at 7:16

answered Feb 3, 2014 at 6:40

Scary Wombat

44.9k6 gold badges38 silver badges67 bronze badges

Collectives™ on Stack Overflow

excel, Apache Poi, unable to write to cell

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related