使用Apache POI更新Excel文件

问题描述：

我想使用Apache POI更新现有的Excel文件。每次运行我的代码时，都会收到如下所示的错误消息。我也试过FileInputStreamNewFile的东西。使用Apache POI更新Excel文件

Exception in thread "main" java.lang.NullPointerException 
    at com.gma.test.WriteExcelTest.writeXLSXFile(WriteExcelTest.java:26) 
    at com.gma.test.WriteExcelTest.main(WriteExcelTest.java:44)

请找到下面的代码。感谢你的帮助。

package com.gma.test; 

import java.io.File; 
import java.io.FileInputStream; 
import java.io.FileNotFoundException; 
import java.io.FileOutputStream; 
import java.io.IOException; 

import org.apache.poi.ss.usermodel.Cell; 
import org.apache.poi.xssf.usermodel.XSSFCell; 
import org.apache.poi.xssf.usermodel.XSSFRow; 
import org.apache.poi.xssf.usermodel.XSSFSheet; 
import org.apache.poi.xssf.usermodel.XSSFWorkbook; 

public class WriteExcelTest { 

    public static void writeXLSXFile(int row, int col) throws IOException { 
     try { 
      FileInputStream file = new FileInputStream("C:\\Anuj\\Data\\Data.xlsx"); 

      XSSFWorkbook workbook = new XSSFWorkbook(file); 
      XSSFSheet sheet = workbook.getSheetAt(0); 
      Cell cell = null; 

      //Update the value of cell 
      cell = sheet.getRow(row).getCell(col); 
      cell.setCellValue("Pass"); 

      file.close(); 

      FileOutputStream outFile =new FileOutputStream(new File("C:\\Anuj\\Data\\Data.xlsx")); 
      workbook.write(outFile); 
      outFile.close(); 

     } catch (FileNotFoundException e) { 
      e.printStackTrace(); 
     } catch (IOException e) { 
      e.printStackTrace(); 
     } 
    } 

    public static void main(String[] args) throws IOException { 
     // TODO Auto-generated method stub 
     writeXLSXFile(3, 3); 
    } 

}

检查sheet.getRow（行）不为空，然后如果你问一个行单元格不设置值 –

前空|没有定义的单元格，你会得到一个空值！ –

答

如果更换

//Update the value of cell 
cell = sheet.getRow(row).getCell(col); 
cell.setCellValue("Pass");

随着

//Retrieve the row and check for null 
HSSFRow sheetrow = sheet.getRow(row); 
if(sheetrow == null){ 
    sheetrow = sheet.createRow(row); 
} 
//Update the value of cell 
cell = sheetrow.getCell(col); 
if(cell == null){ 
    cell = sheetrow.createCell(col); 
} 
cell.setCellValue("Pass");

很有效！

如果sheet.getRow（row）返回Null，该怎么办？ –

在行上做类似检查 –

是的，行和单元格都是从0开始的，所以在这个例子中，如果他没有D4的值，他会得到一个NullPointerException。 –

答

谢谢Jelle Heuzel为您举个好榜样。
我只是想添加生成的工作代码，以便其他人可以更快地将它合并到代码中。

我也不得不使用XSSFRow而不是HSSFRow，但除此之外它对我来说工作正常。

package stackoverflow.appachePOI; 

import java.io.File; 
import java.io.FileInputStream; 
import java.io.FileNotFoundException; 
import java.io.FileOutputStream; 
import java.io.IOException; 

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; 

public class WriteExcelTest { 

    public static void writeXLSXFile(int row, int col) throws IOException { 
     try { 
      FileInputStream file = new FileInputStream("C:\\Users\\Sam\\files\\Masterproef lca\\lca-analysebeheer\\Test-Files\\exceltemplates\\template.xlsx"); 

      XSSFWorkbook workbook = new XSSFWorkbook(file); 
      XSSFSheet sheet = workbook.getSheetAt(0); 
      Cell cell = null; 

      //Retrieve the row and check for null 
      XSSFRow sheetrow = sheet.getRow(row); 
      if(sheetrow == null){ 
       sheetrow = sheet.createRow(row); 
      } 
      //Update the value of cell 
      cell = sheetrow.getCell(col); 
      if(cell == null){ 
       cell = sheetrow.createCell(col); 
      } 
      cell.setCellValue("Pass"); 

      file.close(); 

      FileOutputStream outFile =new FileOutputStream(new File("C:\\Users\\Sam\\files\\Masterproef lca\\lca-analysebeheer\\Test-Files\\exceltemplates\\Output.xlsx")); 
      workbook.write(outFile); 
      outFile.close(); 

     } catch (FileNotFoundException e) { 
      e.printStackTrace(); 
     } catch (IOException e) { 
      e.printStackTrace(); 
     } 
    } 

    public static void main(String[] args) throws IOException { 
     // TODO Auto-generated method stub 
     writeXLSXFile(3, 3); 
    } 

}

这里给出空指针异常XSSFSheet sheet = workbook.getSheetAt（0）;这里有人吗？ –

第一次猜测是在文件流中出错，并且无法创建工作簿。 – turoni

答

我想这个工作对XLSX和XSSF

import java.io.File; 
import java.io.FileInputStream; 
import java.io.FileNotFoundException; 
import java.io.FileOutputStream; 
import java.io.IOException; 

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; 

public class TestStackOver { 

    public static void writeXLSXFile(int row, int col) throws IOException { 
     try { 
      FileInputStream file = new FileInputStream(Constante.ruta); 
      XSSFWorkbook workbook = new XSSFWorkbook(file); 
      XSSFSheet sheet = workbook.getSheetAt(0); 
      Cell cell = null; 
      //Retrieve the row and check for null 
      XSSFRow sheetrow = sheet.getRow(row); 
      if(sheetrow == null){ 
       sheetrow = sheet.createRow(row); 
      } 
      //Update the value of cell 
      cell = sheetrow.getCell(col); 
      if(cell == null){ 
       cell = sheetrow.createCell(col); 
      } 
      cell.setCellValue("Pass"); 

      file.close(); 


      FileOutputStream outFile =new FileOutputStream(new File(Constante.ruta_salida)); 
      workbook.write(outFile); 
      outFile.close(); 

     } catch (FileNotFoundException e) { 
      e.printStackTrace(); 
     } catch (IOException e) { 
      e.printStackTrace(); 
     } 
    } 

    public static void main(String[] args) throws IOException { 
     // TODO Auto-generated method stub 
     System.out.println("inicio"); 
     writeXLSXFile(1, 14); 
     System.out.println("terminado"); 
    } 

}

使用Apache POI更新Excel文件

相关推荐