i try copy worksheets 1 workbook workbook. thing is, works if read workbooks via fileinputstreams, not work file objects.
consider following method:
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); // works // oldwb = workbookfactory.create(fisold); // newwb = workbookfactory.create(fisnew); // not work oldwb = workbookfactory.create(new file(oldworkbook)); newwb = workbookfactory.create(new file(newworkbook)); if (newwb == null) { system.out.println("newwb null"); } // cellstyle newstyle = newwb.createcellstyle(); row row; cell cell; copiedsheets = new arraylist<string>(); (int = 0; < oldwb.getnumberofsheets(); i++) { xssfsheet sheetfromoldwb = (xssfsheet) oldwb.getsheetat(i); string sheetnamefromoldwb = sheetfromoldwb.getsheetname(); xssfsheet sheetfornewwb = (xssfsheet) newwb.getsheet(sheetnamefromoldwb); if (sheetfornewwb != null) { int sheetindex = newwb.getsheetindex(sheetnamefromoldwb); newwb.removesheetat(sheetindex); } logger.info("copying new workbook: " + sheetnamefromoldwb); sheetfornewwb = (xssfsheet) newwb.createsheet(sheetfromoldwb.getsheetname()); (int rowindex = 0; rowindex < sheetfromoldwb.getphysicalnumberofrows(); rowindex++) { row = sheetfornewwb.createrow(rowindex); (int colindex = 0; colindex < sheetfromoldwb.getrow(rowindex).getphysicalnumberofcells(); colindex++) { cell = row.createcell(colindex); // cell old wb's sheet , when cell null, return blank cells. cell c = sheetfromoldwb.getrow(rowindex).getcell(colindex, row.missingcellpolicy.create_null_as_blank); // below copying happening. // cellstyle origstyle = c.getcellstyle(); // newstyle.clonestylefrom(origstyle); // cell.setcellstyle(newstyle); switch (c.getcelltypeenum()) { case string: cell.setcellvalue(c.getrichstringcellvalue().getstring()); break; case numeric: if (dateutil.iscelldateformatted(cell)) { cell.setcellvalue(c.getdatecellvalue()); } else { cell.setcellvalue(c.getnumericcellvalue()); } break; case boolean: cell.setcellvalue(c.getbooleancellvalue()); break; case formula: cell.setcellformula(c.getcellformula()); break; default: break; } } } copiedsheets.add(oldwb.getsheetname(i)); } fileout = new fileoutputstream(newworkbook); newwb.write(fileout); // <------ here nullpointerexception fisold.close(); fisnew.close(); oldwb.close(); fileout.close(); newwb.close();
i following exception @ newwb.write(fileout);
:
exception in thread "main" org.apache.poi.poixmlexception: java.lang.nullpointerexception @ org.apache.poi.poixmldocument.getproperties(poixmldocument.java:168) @ org.apache.poi.poixmldocument.write(poixmldocument.java:246) @ com.capgemini.toolkit.app.copyallsheetsacrossworkbook(app.java:263) @ com.capgemini.toolkit.app.main(app.java:58) caused by: java.lang.nullpointerexception @ org.apache.poi.openxml4j.util.zipsecurefile$thresholdinputstream.read(zipsecurefile.java:210) @ com.sun.org.apache.xerces.internal.impl.xmlentitymanager$rewindableinputstream.read(unknown source) @ com.sun.org.apache.xerces.internal.impl.xmlentitymanager.setupcurrententity(unknown source) @ com.sun.org.apache.xerces.internal.impl.xmlversiondetector.determinedocversion(unknown source) @ com.sun.org.apache.xerces.internal.parsers.xml11configuration.parse(unknown source) @ com.sun.org.apache.xerces.internal.parsers.xml11configuration.parse(unknown source) @ com.sun.org.apache.xerces.internal.parsers.xmlparser.parse(unknown source) @ com.sun.org.apache.xerces.internal.parsers.domparser.parse(unknown source) @ com.sun.org.apache.xerces.internal.jaxp.documentbuilderimpl.parse(unknown source) @ javax.xml.parsers.documentbuilder.parse(unknown source) @ org.apache.poi.util.documenthelper.readdocument(documenthelper.java:140) @ org.apache.poi.poixmltypeloader.parse(poixmltypeloader.java:143) @ org.openxmlformats.schemas.officedocument.x2006.extendedproperties.propertiesdocument$factory.parse(unknown source) @ org.apache.poi.poixmlproperties.<init>(poixmlproperties.java:78) @ org.apache.poi.poixmldocument.getproperties(poixmldocument.java:166) ... 3 more
in poi documentation, mentioned better use file
object due lower memory consumption. that's why i'm wondering why not work file
object .
for testing, method running in main method , used 2 fresh excel files (.xlsx) dummy data.
does see why not work file
object ? doing wrong?
fyi: i'm using poi 3.16.
using file
instead of fileinputstream
opening workbook
leads lower memory footprint because then, in case of xssf
(*.xlsx
), zippackage opened *.xlsx
file directly instead reading whole zip
content memory.
but means, zippackage
gets file opened until workbook
closed. until workbook
closed, nothing can write file same time. so, since there not possibility write workbook
content same file workbook
opened from, using file
instead fileinputstream
opening workbook
fine if wants reading workbook
then. not work if wants reading , writing same file. fileinputstream
, fileoutputstream
needed.
so in case tries reading workbook newwb
file
, writing workbook
same file using
fileout = new fileoutputstream(newworkbook); newwb.write(fileout);
while file opened already. fails.
but:
fisnew = new fileinputstream(newworkbook); oldwb = workbookfactory.create(new file(oldworkbook)); newwb = workbookfactory.create(fisnew); ... fileout = new fileoutputstream(newworkbook); newwb.write(fileout); oldwb.close(); newwb.close();
should work.
btw.: if using file
, should not using fileinputstream
same file. don't use fisold
.
another disadvantage of using file
instead of fileinputstream
opening workbook
while closing workbook
, implicitly closing underlaying file system (poifsfilesystem
in case of hssf
, zippackage
in case of xssf
) file gets updated last modified date. there no changings made file file had been opened , new written file system. that's why last modified date updated.
edit sep 21 2017: disadvantage of using file
seems greater thought first. opcpackage.close saves changings underlaying opcpackage
. if opening xssfworkbook
file , wants writing changings file using write(java.io.outputstream stream)
, source file changed while closing opcpackage
. problem occurs if write(java.io.outputstream stream)
used xssfworkbook
since poixmldocument.write called calls poixmldocumentpart.onsave "saves changes in underlying ooxml package.". opcpackage
updated changings before closing.
short example:
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"); workbook.write(new fileoutputstream("filenew.xlsx")); workbook.close(); } }
after code both files filenew.xlsx
as file.xlsx
changed.
Comments
Post a Comment