英文:
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")
英文:
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");
答案1
得分: 2
Cells在设置单元格值之前必须存在。getCell(1)
返回null,因为尚未创建该单元格。您可以尝试使用getCell(1, MissingCellPolicy.CREATE_NULL_AS_BLANK)
,或者使用createCell(1)
,如果您不介意覆盖该单元格。
在使用POI时,强烈建议使用接口而不是XSSFWorkbook
(接口为Workbook
)和XSSFSheet
(接口为Sheet
)的实现。 (对于行和单元格,您已经在使用接口)您可以使用WorkbookFactory.create
来创建工作簿,而不是使用构造函数。这样,您的代码将适用于xls和xlsx。有一些情况下您可能需要实现而不是接口,但99%的情况下不需要。
英文:
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.
答案2
得分: 1
你的代码存在三个问题。首先,你要写入的单元格不存在,所以你必须首先调用createCell()方法创建它:
for (int i = 1; i <= QuantidadeDeLinhas; i++) {
Row row = sheet.getRow(i);
row.createCell(1);
Cell cell = row.getCell(1);
//写入数据
cell.setCellValue("10");
}
其次,在尝试写出文件之前,你关闭了XSSFWorkbook。
最后,你在文档是从文件而不是InputStream创建时写出。因此,你必须要么写出到一个单独的文件,要么从InputStream打开它。这里我将关闭工作簿并写出到一个新文件:
//Planilha.close();
FileOutputStream outFile = new FileOutputStream(new File("tools\\cnpj_new.xlsx"));
Planilha.write(outFile);
outFile.close();
Planilha.close();
System.out.println("Excel文件编辑成功!");
英文:
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!");
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论