ApachePOI-FileInputStream工作,File对象失败(NullPointerException)

我尝试将所有工作表从一个工作簿复制到另一个工作簿.问题是,如果我通过FileInputStreams读取工作簿,它会正常工作,但它不适用于文件对象.

请考虑以下方法:

import java.io.BufferedReader;
import java.io.File;
import java.io.FileFilter;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.net.URL;
import java.util.ArrayList;
import java.util.Iterator;

import org.apache.commons.io.IOUtils;
import org.apache.commons.io.filefilter.WildcardFileFilter;
import org.apache.poi.EncryptedDocumentException;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.SpreadsheetVersion;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.DataConsolidateFunction;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.ss.util.AreaReference;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.xssf.usermodel.XSSFPivotTable;
import org.apache.poi.xssf.usermodel.XSSFSheet;


public void copyAllSheetsAcrossWorkbook(String oldWorkbook, String newWorkbook)
        throws EncryptedDocumentException, InvalidFormatException, IOException {
    FileInputStream fisOld = null;
    FileInputStream fisNew = null;
    Workbook oldWB = null;
    Workbook newWB = null;
    FileOutputStream fileOut = null;

    System.out.println("oldWorkbook: " + oldWorkbook);
    System.out.println("newWorkbook: " + newWorkbook);
    fisOld = new FileInputStream(oldWorkbook);
    fisNew = new FileInputStream(newWorkbook);

    // THIS WORKS
    // oldWB = WorkbookFactory.create(fisOld);
    // newWB = WorkbookFactory.create(fisNew);

    // THIS DOES NOT WORK
    oldWB = WorkbookFactory.create(new File(oldWorkbook));
    newWB = WorkbookFactory.create(new File(newWorkbook));

    if (newWB == null) {
        System.out.println("newWB is null");
    }
    // CellStyle newStyle = newWB.createCellStyle();
    Row row;
    Cell cell;
    copiedSheets = new ArrayList();
    for (int i = 0; i 

我在以下情况得到以下异常newWB.write(fileOut);:

Exception in thread "main" org.apache.poi.POIXMLException: java.lang.NullPointerException
at org.apache.poi.POIXMLDocument.getProperties(POIXMLDocument.java:168)
at org.apache.poi.POIXMLDocument.write(POIXMLDocument.java:246)
at com.capgemini.toolkit.App.copyAllSheetsAcrossWorkbook(App.java:263)
at com.capgemini.toolkit.App.main(App.java:58)

Caused by: java.lang.NullPointerException
at org.apache.poi.openxml4j.util.ZipSecureFile$ThresholdInputStream.read(ZipSecureFile.java:210)
at com.sun.org.apache.xerces.internal.impl.XMLEntityManager$RewindableInputStream.read(Unknown Source)
at com.sun.org.apache.xerces.internal.impl.XMLEntityManager.setupCurrentEntity(Unknown Source)
at com.sun.org.apache.xerces.internal.impl.XMLVersionDetector.determineDocVersion(Unknown Source)
at com.sun.org.apache.xerces.internal.parsers.XML11Configuration.parse(Unknown Source)
at com.sun.org.apache.xerces.internal.parsers.XML11Configuration.parse(Unknown Source)
at com.sun.org.apache.xerces.internal.parsers.XMLParser.parse(Unknown Source)
at com.sun.org.apache.xerces.internal.parsers.DOMParser.parse(Unknown Source)
at com.sun.org.apache.xerces.internal.jaxp.DocumentBuilderImpl.parse(Unknown Source)
at javax.xml.parsers.DocumentBuilder.parse(Unknown Source)
at org.apache.poi.util.DocumentHelper.readDocument(DocumentHelper.java:140)
at org.apache.poi.POIXMLTypeLoader.parse(POIXMLTypeLoader.java:143)
at org.openxmlformats.schemas.officeDocument.x2006.extendedProperties.PropertiesDocument$Factory.parse(Unknown Source)
at org.apache.poi.POIXMLProperties.(POIXMLProperties.java:78)
at org.apache.poi.POIXMLDocument.getProperties(POIXMLDocument.java:166)
... 3 more


在POI文档中,总是提到File由于较低的内存消耗而更好地使用对象.这就是为什么我想知道为什么它不能用于File对象.

对于测试,这是在main方法中运行的唯一方法,我使用了2个新的Excel文件(.xlsx)和一些虚拟数据.

有谁知道为什么它不能与File对象一起使用？难道我做错了什么？

仅供参考:我正在使用POI 3.16.


1> Axel Richter..：
使用a File而不是a FileInputStream来打开a Workbook导致较低的内存占用量,因为在XSSF(*.xlsx)的情况下,ZipPackage将*.xlsx直接从文件打开而不是将整个ZIP内容读入内存.

但这也意味着,ZipPackage文件Workbook将被打开,直到将被关闭.因此,在Workbook关闭之前,没有任何东西可以同时写入该文件.因此,由于无法将Workbook内容写回到Workbook打开的相同文件中,因此如果您只想从中读取,则使用a File而不是FileInputStream打开a Workbook就可以了Workbook.但是如果您想要读取和写入同一个文件,它就不起作用.然后FileInputStream和FileOutputStream需要.

因此,在您的情况下,您尝试Workbook newWB从a 读取File然后使用将其Workbook写入同一文件

fileOut = new FileOutputStream(newWorkbook);
newWB.write(fileOut);


而文件已经打开.这失败了.

但: 

   fisNew = new FileInputStream(newWorkbook);
   oldWB = WorkbookFactory.create(new File(oldWorkbook));
   newWB = WorkbookFactory.create(fisNew);
...
   fileOut = new FileOutputStream(newWorkbook);
   newWB.write(fileOut);
   fileOut.close();

   oldWB.close();
   newWB.close();


应该管用.

顺便说一句:如果您使用的是a File,那么您不应该使用FileInputStream同一个文件.所以不要使用fisOld.

使用a File而不是a FileInputStream来打开a的另一个缺点Workbook是,当关闭Workbook并因此隐式关闭底层文件系统时(POIFSFileSystem在情况下HSSF和ZipPackage在情况下XSSF),文件获得更新的最后修改日期.文件中没有进行任何更改,但文件已打开,新文件已写入文件系统.这就是更新上次修改日期的原因.



编辑2017年9月21日:使用a的缺点File似乎比首先想象的要大.OPCPackage.close还将所有更改保存到底层OPCPackage.因此,如果您XSSFWorkbook要从文件中打开一个文件,然后想要将变换写入另一个文件write(java.io.OutputStream stream),那么在关闭文件时也会更改源文件OPCPackage.只有从那时起write(java.io.OutputStream stream)使用POIXMLDocument.write调用POIXMLDocumentPart.onSave "保存底层OOXML包中的更改" 时才会出现此问题   .所以在关闭前更新了所有的变化.XSSFWorkbookOPCPackage

简短示例:

import org.apache.poi.ss.usermodel.*;

import java.io.File;
import java.io.FileOutputStream;

class ReadAndWriteExcelWorkbook {

 public static void main(String[] args) throws Exception {

  Workbook workbook  = WorkbookFactory.create(new File("file.xlsx"));

  Sheet sheet = workbook.getSheetAt(0);
  Row row = sheet.getRow(0);
  if (row == null) row = sheet.createRow(0);
  Cell cell = row.getCell(0);
  if (cell == null) cell = row.createCell(0);
  cell.setCellValue("changed");

  FileOutputStream out = new FileOutputStream("fileNew.xlsx");
  workbook.write(out);
  out.close();
  workbook.close();

 }
}


此代码后两个文件fileNew.xlsx以及file.xlsx被改变.