Writing in spreadsheet with Poi and java

Question

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

The Busy Developers' Guide to POI answers a lot of questions like this. For example, see Creating Cells. — andrewJames
– andrewJames, Commented Aug 7, 2020 at 21:43

magicmn · Accepted Answer · 2020-08-07 21:37:15Z

2

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.

answered Aug 7, 2020 at 21:37

magicmn

1,9149 silver badges15 bronze badges

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

Comments

jnorman · Accepted Answer · 2020-08-07 21:20:27Z

1

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!");

answered Aug 7, 2020 at 21:20

jnorman

6369 silver badges17 bronze badges

1 Comment

Paulo Roberto Over a year ago

Perfect, thank you so much for your help and especially for detailing what was wrong with my code, so I learn more.

Collectives™ on Stack Overflow

Writing in spreadsheet with Poi and java

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related