CODEWITHSUNDEEP
javaseleniumjunitapache-poi
Himanshu Narula
Himanshu Narula

Reputation: 33

writing a new cell to a sheet apache poi

i am using following code, for reading a excel using apache poi, its a .xlsx file. Please let me know what i can do, to also alter a value of a cell, in each row as my loop keeps going. Thanks

import java.io.FileInputStream;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

String fileName = "C:/createCDN.xlsx";
FileInputStream fis = null;
fis = new FileInputStream(fileName);
XSSFWorkbook workbook = new XSSFWorkbook(fis);
FileOutputStream fos=new FileOutputStream(fileName);
XSSFSheet sheet = workbook.getSheetAt(0);
int totalRows=sheet.getLastRowNum();
for(int i=1;i<=totalRows;i++) {
    XSSFRow row = sheet.getRow(i);
    method.fillTextBox(row.getCell(0),"name", pageProp);
    method.fillTextBox(row.getCell(0),"name", pageProp);
} //Do something here, or inside loop to write to lets say cell(2) of same row.

Thanks.

Upvotes: 3

Views: 30603

Answers (2)

francisco daniel
francisco daniel

Reputation: 11

at first you need to create the space in the excel file, like this:

for(int row = 0; row < 10 ; row++) {
    sheet.createRow(row);
    for(int column = 0; column < 10; column++) { 
        sheet.getRow(row).createCell(column);                
    }                
}

then you can write the excel using this command:

sheet.getRow(row).getCell(column).setCellValue("hello");

Upvotes: 1

Artem Shafranov
Artem Shafranov

Reputation: 2673

It should be like this:

for(int i = 1; i <= totalRows; i++) {
    Row row = sheet.getRow(i);
    Cell cell = row.getCell(2);
    if (cell == null) {
        cell = row.createCell(2);
    }
    cell.setCellType(Cell.CELL_TYPE_STRING);
    cell.setCellValue("some value");
}

To save the workbook you could just write:

FileOutputStream fos = new FileOutputStream(fileName);
workbook.write(fos);
fos.close();

You should take a look at POI's Busy Developers' Guide.

Upvotes: 14

Related Questions