CODEWITHSUNDEEP
javaapache-poi
Paulo Roberto
Paulo Roberto

Reputation: 1548

Writing in spreadsheet with Poi and java

I am studying POI and I want to find a simple way to write in column B and in each row, certain values.

Right now I have the code below, which reads the spreadsheet, but does not write.

public static void main(String[] args) throws IOException, InvalidFormatException {

        File CaminhoArquivo = new File("tools\\cnpj.xlsx");

        XSSFWorkbook Planilha = new XSSFWorkbook(CaminhoArquivo);

        XSSFSheet sheet = Planilha.getSheetAt(0);

        int QuantidadeDeLinhas = sheet.getLastRowNum();
        System.out.println("Quant. de LInhas: " + QuantidadeDeLinhas);
        
        
        for (int i = 1; i <= QuantidadeDeLinhas; i++) {
            
            Row coluna = sheet.getRow(i);
            Cell resultado = coluna.getCell(1);
        
            //Write Line
            resultado.setCellValue("10");
            
        }

        Planilha.close();       
        
        FileOutputStream outFile = new FileOutputStream(new File("tools\\cnpj.xlsx"));
        Planilha.write(outFile);
        outFile.close();
        System.out.println("Arquivo Excel editado com sucesso!");
        
    }

ERROR

Exception in thread "main" java.lang.NullPointerException at lendoXlsx.xlsx.ReadExcel.main(ReadExcel.java:35)

//Write Line
    resultado.setCellValue("10");

sHEET

Upvotes: 0

Views: 74

Answers (2)

magicmn
magicmn

Reputation: 1914

Cells have to exist before you can set a cell value for them. getCell(1) returns null because it hasn't been created yet. You can try getCell(1, MissingCellPolicy.CREATE_NULL_AS_BLANK) or use createCell(1) if you don't care that you override the cell.

When using POI i highly recommend to use the interfaces and not the implementations of XSSFWorkbook (Interface is Workbook) and XSSFSheet (Interface is Sheet). (For row and cell you are already using the interfaces) Instead of creating your workbook with a constructor you can use WorkbookFactory.create. This way you're code will work for xls and xlsx. There are some cases where you need the implementation instead of the interface, but 99% of the time you don't.

Upvotes: 2

jnorman
jnorman

Reputation: 626

You have three issues in your code. First, the cell you are writing to does not exist, so you have to create it first by calling the createCell() method:

for (int i = 1; i <= QuantidadeDeLinhas; i++) {
    Row coluna = sheet.getRow(i);
    
    coluna.createCell(1);
    Cell resultado = coluna.getCell(1);

    //Write Line
    resultado.setCellValue("10");
    
}

Next, you are closing the XSSFWorkbook before trying to write it out.

Finally, you are writing out when the document was created from a File instead of an InputStream. So, you must either write out to a separate file, or open it from an InputStream.

Here I have moved the workbook close and am writing out to a new file.

//Planilha.close();       

FileOutputStream outFile = new FileOutputStream(new File("tools\\cnpj_new.xlsx"));
Planilha.write(outFile);
outFile.close();
Planilha.close();
System.out.println("Arquivo Excel editado com sucesso!");

Upvotes: 1

Related Questions