本文解决了一个项目中遇到的XLS文件兼容性问题，该XLS文件可能是从更高版本的XLSX转换而来，导致无法正常读取。文章详细介绍了使用Apache POI库处理此问题的方法。
org.apache.poi.hssf.OldExcelFormatException: The supplied spreadsheet seems to be Excel 5.0/7.0 (BIFF5) format.
POI only supports BIFF8 format (from Excel versions 97/2000/XP/2003)
at org.apache.poi.hssf.usermodel.HSSFWorkbook.getWorkbookDirEntryName(HSSFWorkbook.java:237)
at org.apache.poi.hssf.usermodel.HSSFWorkbook.<init>(HSSFWorkbook.java:284)
at org.apache.poi.hssf.usermodel.HSSFWorkbook.<init>(HSSFWorkbook.java:264)
at org.apache.poi.hssf.usermodel.HSSFWorkbook.<init>(HSSFWorkbook.java:199)
at org.apache.poi.hssf.usermodel.HSSFWorkbook.<init>(HSSFWorkbook.java:342)
at org.apache.poi.hssf.usermodel.HSSFWorkbook.<init>(HSSFWorkbook.java:323)
at p04_01.CreateXml01.main(CreateXml01.java:759)
/*** Eclipse Class Decompiler plugin, copyright (c) 2012 Chao Chen (cnfree2000@hotmail.com) ***/
package org.apache.poi.hssf.usermodel;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.PrintStream;
import java.io.PrintWriter;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.Hashtable;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.regex.Pattern;
import org.apache.commons.codec.digest.DigestUtils;
import org.apache.poi.EncryptedDocumentException;
import org.apache.poi.POIDocument;
import org.apache.poi.ddf.EscherBSERecord;
import org.apache.poi.ddf.EscherBitmapBlip;
import org.apache.poi.ddf.EscherBlipRecord;
import org.apache.poi.ddf.EscherMetafileBlip;
import org.apache.poi.ddf.EscherRecord;
import org.apache.poi.hpsf.ClassID;
import org.apache.poi.hssf.OldExcelFormatException;
import org.apache.poi.hssf.model.DrawingManager2;
import org.apache.poi.hssf.model.HSSFFormulaParser;
import org.apache.poi.hssf.model.InternalSheet;
import org.apache.poi.hssf.model.InternalWorkbook;
import org.apache.poi.hssf.model.RecordStream;
import org.apache.poi.hssf.record.AbstractEscherHolderRecord;
import org.apache.poi.hssf.record.BackupRecord;
import org.apache.poi.hssf.record.DrawingGroupRecord;
import org.apache.poi.hssf.record.ExtendedFormatRecord;
import org.apache.poi.hssf.record.FontRecord;
import org.apache.poi.hssf.record.LabelRecord;
import org.apache.poi.hssf.record.LabelSSTRecord;
import org.apache.poi.hssf.record.NameRecord;
import org.apache.poi.hssf.record.RecalcIdRecord;
import org.apache.poi.hssf.record.Record;
import org.apache.poi.hssf.record.RecordFactory;
import org.apache.poi.hssf.record.UnknownRecord;
import org.apache.poi.hssf.record.WindowOneRecord;
import org.apache.poi.hssf.record.aggregates.RecordAggregate.RecordVisitor;
import org.apache.poi.hssf.record.common.UnicodeString;
import org.apache.poi.hssf.util.CellReference;
import org.apache.poi.poifs.filesystem.DirectoryEntry;
import org.apache.poi.poifs.filesystem.DirectoryNode;
import org.apache.poi.poifs.filesystem.EntryUtils;
import org.apache.poi.poifs.filesystem.FilteringDirectoryNode;
import org.apache.poi.poifs.filesystem.Ole10Native;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.formula.FormulaShifter;
import org.apache.poi.ss.formula.SheetNameFormatter;
import org.apache.poi.ss.formula.udf.AggregatingUDFFinder;
import org.apache.poi.ss.formula.udf.IndexedUDFFinder;
import org.apache.poi.ss.formula.udf.UDFFinder;
import org.apache.poi.ss.usermodel.Row.MissingCellPolicy;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.WorkbookUtil;
import org.apache.poi.util.Configurator;
import org.apache.poi.util.HexDump;
import org.apache.poi.util.LittleEndian;
import org.apache.poi.util.POILogFactory;
import org.apache.poi.util.POILogger;
public final class HSSFWorkbook extends POIDocument implements Workbook
{
private static final Pattern COMMA_PATTERN = Pattern.compile(",");
private static final int MAX_STYLES = 4030;
private static final int DEBUG = 1;
public static final int INITIAL_CAPACITY = Configurator.getIntValue("HSSFWorkbook.SheetInitialCapacity", 3);
private InternalWorkbook workbook;
protected List<HSSFSheet> _sheets;
private ArrayList<HSSFName> names;
private Hashtable<Short, HSSFFont> fonts;
private boolean preserveNodes;
private HSSFDataFormat formatter;
private Row.MissingCellPolicy missingCellPolicy;
private static POILogger log = POILogFactory.getLogger(HSSFWorkbook.class);
private UDFFinder _udfFinder;
private static final String[] WORKBOOK_DIR_ENTRY_NAMES = {"Workbook", "WORKBOOK", "BOOK"};
public static HSSFWorkbook create(InternalWorkbook book)
{
return new HSSFWorkbook(book);
}
public HSSFWorkbook()
{
this(InternalWorkbook.createWorkbook());
}
private HSSFWorkbook(InternalWorkbook book)
{
super((DirectoryNode) null);
this.missingCellPolicy = HSSFRow.RETURN_NULL_AND_BLANK;
this._udfFinder = new IndexedUDFFinder(new UDFFinder[]{UDFFinder.DEFAULT});
this.workbook = book;
this._sheets = new ArrayList(INITIAL_CAPACITY);
this.names = new ArrayList(INITIAL_CAPACITY);
}
public HSSFWorkbook(POIFSFileSystem fs) throws IOException
{
this(fs, true);
}
public HSSFWorkbook(POIFSFileSystem fs, boolean preserveNodes) throws IOException
{
this(fs.getRoot(), fs, preserveNodes);
}
public static String getWorkbookDirEntryName(DirectoryNode directory)
{
for (int i = 0; i < WORKBOOK_DIR_ENTRY_NAMES.length; ++i)
{
String wbName = WORKBOOK_DIR_ENTRY_NAMES[i];
try
{
directory.getEntry(wbName);
return wbName;
}
catch (FileNotFoundException e)
{
}
}
try
{
directory.getEntry("EncryptedPackage");
throw new EncryptedDocumentException("The supplied spreadsheet seems to be an Encrypted .xlsx file. It must be decrypted before use by XSSF, it cannot be used by HSSF");
}
catch (FileNotFoundException e)
{
try
{
directory.getEntry("Book");
throw new OldExcelFormatException("The supplied spreadsheet seems to be Excel 5.0/7.0 (BIFF5) format. POI only supports BIFF8 format (from Excel versions 97/2000/XP/2003)");
}
catch (FileNotFoundException e)
{
throw new IllegalArgumentException("The supplied POIFSFileSystem does not contain a BIFF8 'Workbook' entry. Is it really an excel file?");
}
}
}
public HSSFWorkbook(DirectoryNode directory, POIFSFileSystem fs, boolean preserveNodes) throws IOException
{
this(directory, preserveNodes);
}
public HSSFWorkbook(DirectoryNode directory, boolean preserveNodes) throws IOException
{
super(directory);
this.missingCellPolicy = HSSFRow.RETURN_NULL_AND_BLANK;
this._udfFinder = new IndexedUDFFinder(new UDFFinder[]{UDFFinder.DEFAULT});
String workbookName = getWorkbookDirEntryName(directory);
this.preserveNodes = preserveNodes;
if (!(preserveNodes))
{
this.directory = null;
}
this._sheets = new ArrayList(INITIAL_CAPACITY);
this.names = new ArrayList(INITIAL_CAPACITY);
InputStream stream = directory.createDocumentInputStream(workbookName);
List records = RecordFactory.createRecords(stream);
this.workbook = InternalWorkbook.createWorkbook(records);
setPropertiesFromWorkbook(this.workbook);
int recOffset = this.workbook.getNumRecords();
convertLabelRecords(records, recOffset);
RecordStream rs = new RecordStream(records, recOffset);
while (rs.hasNext())
{
InternalSheet sheet = InternalSheet.createSheet(rs);
this._sheets.add(new HSSFSheet(this, sheet));
}
for (int i = 0; i < this.workbook.getNumNames(); ++i)
{
NameRecord nameRecord = this.workbook.getNameRecord(i);
HSSFName name = new HSSFName(this, nameRecord, this.workbook.getNameCommentRecord(nameRecord));
this.names.add(name);
}
}
public HSSFWorkbook(InputStream s) throws IOException
{
this(s, true);
}
public HSSFWorkbook(InputStream s, boolean preserveNodes) throws IOException
{
this(new POIFSFileSystem(s), preserveNodes);
}
private void setPropertiesFromWorkbook(InternalWorkbook book)
{
this.workbook = book;
}
private void convertLabelRecords(List<Record> records, int offset)
{
if (log.check(1))
log.log(1, "convertLabelRecords called");
for (int k = offset; k < records.size(); ++k)
{
Record rec = (Record) records.get(k);
if (rec.getSid() != 516)
continue;
LabelRecord oldrec = (LabelRecord) rec;
records.remove(k);
LabelSSTRecord newrec = new LabelSSTRecord();
int stringid = this.workbook.addSSTString(new UnicodeString(oldrec.getValue()));
newrec.setRow(oldrec.getRow());
newrec.setColumn(oldrec.getColumn());
newrec.setXFIndex(oldrec.getXFIndex());
newrec.setSSTIndex(stringid);
records.add(k, newrec);
}
if (log.check(1))
log.log(1, "convertLabelRecords exit");
}
public Row.MissingCellPolicy getMissingCellPolicy()
{
return this.missingCellPolicy;
}
public void setMissingCellPolicy(Row.MissingCellPolicy missingCellPolicy)
{
this.missingCellPolicy = missingCellPolicy;
}
public void setSheetOrder(String sheetname, int pos)
{
int oldSheetIndex = getSheetIndex(sheetname);
this._sheets.add(pos, this._sheets.remove(oldSheetIndex));
this.workbook.setSheetOrder(sheetname, pos);
FormulaShifter shifter = FormulaShifter.createForSheetShift(oldSheetIndex, pos);
for (HSSFSheet sheet : this._sheets)
{
sheet.getSheet().updateFormulasAfterCellShift(shifter, -1);
}
this.workbook.updateNamesAfterCellShift(shifter);
}
private void validateSheetIndex(int index)
{
int lastSheetIx = this._sheets.size() - 1;
if ((index < 0) || (index > lastSheetIx))
{
String range = "(0.." + lastSheetIx + ")";
if (lastSheetIx == -1)
{
range = "(no sheets)";
}
throw new IllegalArgumentException("Sheet index (" + index + ") is out of range " + range);
}
}
public void setSelectedTab(int index)
{
validateSheetIndex(index);
int nSheets = this._sheets.size();
for (int i = 0; i < nSheets; ++i)
{
getSheetAt(i).setSelected(i == index);
}
this.workbook.getWindowOne().setNumSelectedTabs(1);
}
@Deprecated
public void setSelectedTab(short index)
{
setSelectedTab(index);
}
public void setSelectedTabs(int[] indexes)
{
for (int i = 0; i < indexes.length; ++i)
{
validateSheetIndex(indexes[i]);
}
int nSheets = this._sheets.size();
for (int i = 0; i < nSheets; ++i)
{
boolean bSelect = false;
for (int j = 0; j < indexes.length; ++j)
{
if (indexes[j] == i)
{
bSelect = true;
break;
}
}
getSheetAt(i).setSelected(bSelect);
}
this.workbook.getWindowOne().setNumSelectedTabs((short) indexes.length);
}
public void setActiveSheet(int index)
{
validateSheetIndex(index);
int nSheets = this._sheets.size();
for (int i = 0; i < nSheets; ++i)
{
getSheetAt(i).setActive(i == index);
}
this.workbook.getWindowOne().setActiveSheetIndex(index);
}
public int getActiveSheetIndex()
{
return this.workbook.getWindowOne().getActiveSheetIndex();
}
@Deprecated
public short getSelectedTab()
{
return (short) getActiveSheetIndex();
}
public void setFirstVisibleTab(int index)
{
this.workbook.getWindowOne().setFirstVisibleTab(index);
}
@Deprecated
public void setDisplayedTab(short index)
{
setFirstVisibleTab(index);
}
public int getFirstVisibleTab()
{
return this.workbook.getWindowOne().getFirstVisibleTab();
}
@Deprecated
public short getDisplayedTab()
{
return (short) getFirstVisibleTab();
}
public void setSheetName(int sheetIx, String name)
{
if (name == null)
{
throw new IllegalArgumentException("sheetName must not be null");
}
if (this.workbook.doesContainsSheetName(name, sheetIx))
{
throw new IllegalArgumentException("The workbook already contains a sheet with this name");
}
validateSheetIndex(sheetIx);
this.workbook.setSheetName(sheetIx, name);
}
public String getSheetName(int sheetIndex)
{
validateSheetIndex(sheetIndex);
return this.workbook.getSheetName(sheetIndex);
}
public boolean isHidden()
{
return this.workbook.getWindowOne().getHidden();
}
public void setHidden(boolean hiddenFlag)
{
this.workbook.getWindowOne().setHidden(hiddenFlag);
}
public boolean isSheetHidden(int sheetIx)
{
validateSheetIndex(sheetIx);
return this.workbook.isSheetHidden(sheetIx);
}
public boolean isSheetVeryHidden(int sheetIx)
{
validateSheetIndex(sheetIx);
return this.workbook.isSheetVeryHidden(sheetIx);
}
public void setSheetHidden(int sheetIx, boolean hidden)
{
validateSheetIndex(sheetIx);
this.workbook.setSheetHidden(sheetIx, hidden);
}
public void setSheetHidden(int sheetIx, int hidden)
{
validateSheetIndex(sheetIx);
WorkbookUtil.validateSheetState(hidden);
this.workbook.setSheetHidden(sheetIx, hidden);
}
public int getSheetIndex(String name)
{
return this.workbook.getSheetIndex(name);
}
public int getSheetIndex(Sheet sheet)
{
for (int i = 0; i < this._sheets.size(); ++i)
{
if (this._sheets.get(i) == sheet)
{
return i;
}
}
return -1;
}
@Deprecated
public int getExternalSheetIndex(int internalSheetIndex)
{
return this.workbook.checkExternSheet(internalSheetIndex);
}
@Deprecated
public String findSheetNameFromExternSheet(int externSheetIndex)
{
return this.workbook.findSheetNameFromExternSheet(externSheetIndex);
}
@Deprecated
public String resolveNameXText(int refIndex, int definedNameIndex)
{
return this.workbook.resolveNameXText(refIndex, definedNameIndex);
}
public HSSFSheet createSheet()
{
HSSFSheet sheet = new HSSFSheet(this);
this._sheets.add(sheet);
this.workbook.setSheetName(this._sheets.size() - 1, "Sheet" + (this._sheets.size() - 1));
boolean isOnlySheet = this._sheets.size() == 1;
sheet.setSelected(isOnlySheet);
sheet.setActive(isOnlySheet);
return sheet;
}
public HSSFSheet cloneSheet(int sheetIndex)
{
validateSheetIndex(sheetIndex);
HSSFSheet srcSheet = (HSSFSheet) this._sheets.get(sheetIndex);
String srcName = this.workbook.getSheetName(sheetIndex);
HSSFSheet clonedSheet = srcSheet.cloneSheet(this);
clonedSheet.setSelected(false);
clonedSheet.setActive(false);
String name = getUniqueSheetName(srcName);
int newSheetIndex = this._sheets.size();
this._sheets.add(clonedSheet);
this.workbook.setSheetName(newSheetIndex, name);
int filterDbNameIndex = findExistingBuiltinNameRecordIdx(sheetIndex, 13);
if (filterDbNameIndex != -1)
{
NameRecord newNameRecord = this.workbook.cloneFilter(filterDbNameIndex, newSheetIndex);
HSSFName newName = new HSSFName(this, newNameRecord);
this.names.add(newName);
}
return clonedSheet;
}
private String getUniqueSheetName(String srcName)
{
int uniqueIndex = 2;
String baseName = srcName;
int bracketPos = srcName.lastIndexOf(40);
if ((bracketPos > 0) && (srcName.endsWith(")")))
{
String suffix = srcName.substring(bracketPos + 1, srcName.length() - ")".length());
try
{
uniqueIndex = Integer.parseInt(suffix.trim());
++uniqueIndex;
baseName = srcName.substring(0, bracketPos).trim();
}
catch (NumberFormatException e)
{
}
}
while (true)
{
String index = Integer.toString(uniqueIndex++);
String name;
String name;
if (baseName.length() + index.length() + 2 < 31)
name = baseName + " (" + index + ")";
else
{
name = baseName.substring(0, 31 - index.length() - 2) + "(" + index + ")";
}
if (this.workbook.getSheetIndex(name) == -1)
return name;
}
}
public HSSFSheet createSheet(String sheetname)
{
if (sheetname == null)
{
throw new IllegalArgumentException("sheetName must not be null");
}
if (this.workbook.doesContainsSheetName(sheetname, this._sheets.size()))
{
throw new IllegalArgumentException("The workbook already contains a sheet of this name");
}
HSSFSheet sheet = new HSSFSheet(this);
this.workbook.setSheetName(this._sheets.size(), sheetname);
this._sheets.add(sheet);
boolean isOnlySheet = this._sheets.size() == 1;
sheet.setSelected(isOnlySheet);
sheet.setActive(isOnlySheet);
return sheet;
}
public int getNumberOfSheets()
{
return this._sheets.size();
}
public int getSheetIndexFromExternSheetIndex(int externSheetNumber)
{
return this.workbook.getSheetIndexFromExternSheetIndex(externSheetNumber);
}
private HSSFSheet[] getSheets()
{
HSSFSheet[] result = new HSSFSheet[this._sheets.size()];
this._sheets.toArray(result);
return result;
}
public HSSFSheet getSheetAt(int index)
{
validateSheetIndex(index);
return ((HSSFSheet) this._sheets.get(index));
}
public HSSFSheet getSheet(String name)
{
HSSFSheet retval = null;
for (int k = 0; k < this._sheets.size(); ++k)
{
String sheetname = this.workbook.getSheetName(k);
if (!(sheetname.equalsIgnoreCase(name)))
continue;
retval = (HSSFSheet) this._sheets.get(k);
}
return retval;
}
public void removeSheetAt(int index)
{
validateSheetIndex(index);
boolean wasActive = getSheetAt(index).isActive();
boolean wasSelected = getSheetAt(index).isSelected();
this._sheets.remove(index);
this.workbook.removeSheet(index);
int nSheets = this._sheets.size();
if (nSheets < 1)
{
return;
}
int newSheetIndex = index;
if (newSheetIndex >= nSheets)
{
newSheetIndex = nSheets - 1;
}
if (wasActive)
{
setActiveSheet(newSheetIndex);
}
if (wasSelected)
{
boolean someOtherSheetIsStillSelected = false;
for (int i = 0; i < nSheets; ++i)
{
if (getSheetAt(i).isSelected())
{
someOtherSheetIsStillSelected = true;
break;
}
}
if (!(someOtherSheetIsStillSelected))
setSelectedTab(newSheetIndex);
}
}
public void setBackupFlag(boolean backupValue)
{
BackupRecord backupRecord = this.workbook.getBackupRecord();
backupRecord.setBackup((backupValue) ? 1 : 0);
}
public boolean getBackupFlag()
{
BackupRecord backupRecord = this.workbook.getBackupRecord();
return (backupRecord.getBackup() != 0);
}
@Deprecated
public void setRepeatingRowsAndColumns(int sheetIndex, int startColumn, int endColumn, int startRow, int endRow)
{
HSSFSheet sheet = getSheetAt(sheetIndex);
CellRangeAddress rows = null;
CellRangeAddress cols = null;
if (startRow != -1)
{
rows = new CellRangeAddress(startRow, endRow, -1, -1);
}
if (startColumn != -1)
{
cols = new CellRangeAddress(-1, -1, startColumn, endColumn);
}
sheet.setRepeatingRows(rows);
sheet.setRepeatingColumns(cols);
}
int findExistingBuiltinNameRecordIdx(int sheetIndex, byte builtinCode)
{
for (int defNameIndex = 0; defNameIndex < this.names.size(); ++defNameIndex)
{
NameRecord r = this.workbook.getNameRecord(defNameIndex);
if (r == null)
{
throw new RuntimeException("Unable to find all defined names to iterate over");
}
if (!(r.isBuiltInName()))
continue;
if (r.getBuiltInName() != builtinCode)
{
continue;
}
if (r.getSheetNumber() - 1 == sheetIndex)
{
return defNameIndex;
}
}
return -1;
}
HSSFName createBuiltInName(byte builtinCode, int sheetIndex)
{
NameRecord nameRecord = this.workbook.createBuiltInName(builtinCode, sheetIndex + 1);
HSSFName newName = new HSSFName(this, nameRecord, null);
this.names.add(newName);
return newName;
}
HSSFName getBuiltInName(byte builtinCode, int sheetIndex)
{
int index = findExistingBuiltinNameRecordIdx(sheetIndex, builtinCode);
if (index < 0)
{
return null;
}
return ((HSSFName) this.names.get(index));
}
public HSSFFont createFont()
{
this.workbook.createNewFont();
short fontindex = (short) (getNumberOfFonts() - 1);
if (fontindex > 3)
{
fontindex = (short) (fontindex + 1);
}
if (fontindex == 32767)
{
throw new IllegalArgumentException("Maximum number of fonts was exceeded");
}
return getFontAt(fontindex);
}
public HSSFFont findFont(short boldWeight, short color, short fontHeight, String name, boolean italic, boolean strikeout, short typeOffset, byte underline)
{
for (short i = 0; i <= getNumberOfFonts(); i = (short) (i + 1))
{
if (i == 4)
continue;
HSSFFont hssfFont = getFontAt(i);
if ((hssfFont.getBoldweight() == boldWeight) && (hssfFont.getColor() == color) && (hssfFont.getFontHeight() == fontHeight) && (hssfFont.getFontName().equals(name)) && (hssfFont.getItalic() == italic) && (hssfFont.getStrikeout() == strikeout) && (hssfFont.getTypeOffset() == typeOffset) && (hssfFont.getUnderline() == underline))
{
return hssfFont;
}
}
return null;
}
public short getNumberOfFonts()
{
return (short) this.workbook.getNumberOfFontRecords();
}
public HSSFFont getFontAt(short idx)
{
if (this.fonts == null)
this.fonts = new Hashtable();
Short sIdx = Short.valueOf(idx);
if (this.fonts.containsKey(sIdx))
{
return ((HSSFFont) this.fonts.get(sIdx));
}
FontRecord font = this.workbook.getFontRecordAt(idx);
HSSFFont retval = new HSSFFont(idx, font);
this.fonts.put(sIdx, retval);
return retval;
}
protected void resetFontCache()
{
this.fonts = new Hashtable();
}
public HSSFCellStyle createCellStyle()
{
if (this.workbook.getNumExFormats() == 4030)
{
throw new IllegalStateException("The maximum number of cell styles was exceeded. You can define up to 4000 styles in a .xls workbook");
}
ExtendedFormatRecord xfr = this.workbook.createCellXF();
short index = (short) (getNumCellStyles() - 1);
HSSFCellStyle style = new HSSFCellStyle(index, xfr, this);
return style;
}
public short getNumCellStyles()
{
return (short) this.workbook.getNumExFormats();
}
public HSSFCellStyle getCellStyleAt(short idx)
{
ExtendedFormatRecord xfr = this.workbook.getExFormatAt(idx);
HSSFCellStyle style = new HSSFCellStyle(idx, xfr, this);
return style;
}
public void write(OutputStream stream) throws IOException
{
byte[] bytes = getBytes();
POIFSFileSystem fs = new POIFSFileSystem();
List excepts = new ArrayList(1);
fs.createDocument(new ByteArrayInputStream(bytes), "Workbook");
writeProperties(fs, excepts);
if (this.preserveNodes)
{
excepts.addAll(Arrays.asList(WORKBOOK_DIR_ENTRY_NAMES));
EntryUtils.copyNodes(new FilteringDirectoryNode(this.directory, excepts), new FilteringDirectoryNode(fs.getRoot(), excepts));
fs.getRoot().setStorageClsid(this.directory.getStorageClsid());
}
fs.writeFilesystem(stream);
}
public byte[] getBytes()
{
if (log.check(1))
{
log.log(1, "HSSFWorkbook.getBytes()");
}
HSSFSheet[] sheets = getSheets();
int nSheets = sheets.length;
this.workbook.preSerialize();
for (int i = 0; i < nSheets; ++i)
{
sheets[i].getSheet().preSerialize();
sheets[i].preSerialize();
}
int totalsize = this.workbook.getSize();
SheetRecordCollector[] srCollectors = new SheetRecordCollector[nSheets];
for (int k = 0; k < nSheets; ++k)
{
this.workbook.setSheetBof(k, totalsize);
SheetRecordCollector src = new SheetRecordCollector();
sheets[k].getSheet().visitContainedRecords(src, totalsize);
totalsize += src.getTotalSize();
srCollectors[k] = src;
}
byte[] retval = new byte[totalsize];
int pos = this.workbook.serialize(0, retval);
for (int k = 0; k < nSheets; ++k)
{
SheetRecordCollector src = srCollectors[k];
int serializedSize = src.serialize(pos, retval);
if (serializedSize != src.getTotalSize())
{
throw new IllegalStateException("Actual serialized sheet size (" + serializedSize + ") differs from pre-calculated size (" + src.getTotalSize() + ") for sheet (" + k + ")");
}
pos += serializedSize;
}
return retval;
}
@Deprecated
public int addSSTString(String string)
{
return this.workbook.addSSTString(new UnicodeString(string));
}
@Deprecated
public String getSSTString(int index)
{
return this.workbook.getSSTString(index).getString();
}
InternalWorkbook getWorkbook()
{
return this.workbook;
}
public int getNumberOfNames()
{
int result = this.names.size();
return result;
}
public HSSFName getName(String name)
{
int nameIndex = getNameIndex(name);
if (nameIndex < 0)
{
return null;
}
return ((HSSFName) this.names.get(nameIndex));
}
public HSSFName getNameAt(int nameIndex)
{
int nNames = this.names.size();
if (nNames < 1)
{
throw new IllegalStateException("There are no defined names in this workbook");
}
if ((nameIndex < 0) || (nameIndex > nNames))
{
throw new IllegalArgumentException("Specified name index " + nameIndex + " is outside the allowable range (0.." + (nNames - 1) + ").");
}
return ((HSSFName) this.names.get(nameIndex));
}
public NameRecord getNameRecord(int nameIndex)
{
return getWorkbook().getNameRecord(nameIndex);
}
public String getNameName(int index)
{
String result = getNameAt(index).getNameName();
return result;
}
public void setPrintArea(int sheetIndex, String reference)
{
NameRecord name = this.workbook.getSpecificBuiltinRecord(6, sheetIndex + 1);
if (name == null)
{
name = this.workbook.createBuiltInName(6, sheetIndex + 1);
}
String[] parts = COMMA_PATTERN.split(reference);
StringBuffer sb = new StringBuffer(32);
for (int i = 0; i < parts.length; ++i)
{
if (i > 0)
{
sb.append(",");
}
SheetNameFormatter.appendFormat(sb, getSheetName(sheetIndex));
sb.append("!");
sb.append(parts[i]);
}
name.setNameDefinition(HSSFFormulaParser.parse(sb.toString(), this, 4, sheetIndex));
}
public void setPrintArea(int sheetIndex, int startColumn, int endColumn, int startRow, int endRow)
{
CellReference cell = new CellReference(startRow, startColumn, true, true);
String reference = cell.formatAsString();
cell = new CellReference(endRow, endColumn, true, true);
reference = reference + ":" + cell.formatAsString();
setPrintArea(sheetIndex, reference);
}
public String getPrintArea(int sheetIndex)
{
NameRecord name = this.workbook.getSpecificBuiltinRecord(6, sheetIndex + 1);
if (name == null)
{
return null;
}
return HSSFFormulaParser.toFormulaString(this, name.getNameDefinition());
}
public void removePrintArea(int sheetIndex)
{
getWorkbook().removeBuiltinRecord(6, sheetIndex + 1);
}
public HSSFName createName()
{
NameRecord nameRecord = this.workbook.createName();
HSSFName newName = new HSSFName(this, nameRecord);
this.names.add(newName);
return newName;
}
public int getNameIndex(String name)
{
for (int k = 0; k < this.names.size(); ++k)
{
String nameName = getNameName(k);
if (nameName.equalsIgnoreCase(name))
{
return k;
}
}
return -1;
}
int getNameIndex(HSSFName name)
{
for (int k = 0; k < this.names.size(); ++k)
{
if (name == this.names.get(k))
{
return k;
}
}
return -1;
}
public void removeName(int index)
{
this.names.remove(index);
this.workbook.removeName(index);
}
public HSSFDataFormat createDataFormat()
{
if (this.formatter == null)
this.formatter = new HSSFDataFormat(this.workbook);
return this.formatter;
}
public void removeName(String name)
{
int index = getNameIndex(name);
removeName(index);
}
void removeName(HSSFName name)
{
int index = getNameIndex(name);
removeName(index);
}
public HSSFPalette getCustomPalette()
{
return new HSSFPalette(this.workbook.getCustomPalette());
}
public void insertChartRecord()
{
int loc = this.workbook.findFirstRecordLocBySid(252);
byte[] data = {15, 0, 0, -16, 82, 0, 0, 0, 0, 0, 6, -16, 24, 0, 0, 0, 1, 8, 0, 0, 2, 0, 0, 0, 2, 0, 0, 0, 1, 0, 0, 0, 1, 0, 0, 0, 3, 0, 0, 0, 51, 0, 11, -16, 18, 0, 0, 0, -65, 0, 8, 0, 8, 0, -127, 1, 9, 0, 0, 8, -64, 1, 64, 0, 0, 8, 64, 0, 30, -15, 16, 0, 0, 0, 13, 0, 0, 8, 12, 0, 0, 8, 23, 0, 0, 8, -9, 0, 0, 16};
UnknownRecord r = new UnknownRecord(235, data);
this.workbook.getRecords().add(loc, r);
}
public void dumpDrawingGroupRecords(boolean fat)
{
DrawingGroupRecord r = (DrawingGroupRecord) this.workbook.findFirstRecordBySid(235);
r.decode();
List escherRecords = r.getEscherRecords();
PrintWriter w = new PrintWriter(System.out);
for (Iterator iterator = escherRecords.iterator(); iterator.hasNext();)
{
EscherRecord escherRecord = (EscherRecord) iterator.next();
if (fat)
System.out.println(escherRecord.toString());
else
escherRecord.display(w, 0);
}
w.flush();
}
void initDrawings()
{
DrawingManager2 mgr = this.workbook.findDrawingGroup();
if (mgr != null)
{
for (int i = 0; i < getNumberOfSheets(); ++i)
getSheetAt(i).getDrawingPatriarch();
}
else
this.workbook.createDrawingGroup();
}
public int addPicture(byte[] pictureData, int format)
{
initDrawings();
byte[] uid = DigestUtils.md5(pictureData);
EscherBlipRecord blipRecord;
int blipSize;
short escherTag;
switch (format)
{
case 3:
if (LittleEndian.getInt(pictureData) == -1698247209)
{
byte[] picDataNoHeader = new byte[pictureData.length - 22];
System.arraycopy(pictureData, 22, picDataNoHeader, 0, pictureData.length - 22);
pictureData = picDataNoHeader;
}
case 2:
EscherMetafileBlip blipRecordMeta = new EscherMetafileBlip();
blipRecord = blipRecordMeta;
blipRecordMeta.setUID(uid);
blipRecordMeta.setPictureData(pictureData);
blipRecordMeta.setFilter(-2);
blipSize = blipRecordMeta.getCompressedSize() + 58;
escherTag = 0;
break;
default:
EscherBitmapBlip blipRecordBitmap = new EscherBitmapBlip();
blipRecord = blipRecordBitmap;
blipRecordBitmap.setUID(uid);
blipRecordBitmap.setMarker(-1);
blipRecordBitmap.setPictureData(pictureData);
blipSize = pictureData.length + 25;
escherTag = 255;
}
blipRecord.setRecordId((short) (-4072 + format));
switch (format)
{
case 2:
blipRecord.setOptions(15680);
break;
case 3:
blipRecord.setOptions(8544);
break;
case 4:
blipRecord.setOptions(21536);
break;
case 6:
blipRecord.setOptions(28160);
break;
case 5:
blipRecord.setOptions(18080);
break;
case 7:
blipRecord.setOptions(31360);
}
EscherBSERecord r = new EscherBSERecord();
r.setRecordId(-4089);
r.setOptions((short) (0x2 | format << 4));
r.setBlipTypeMacOS((byte) format);
r.setBlipTypeWin32((byte) format);
r.setUid(uid);
r.setTag(escherTag);
r.setSize(blipSize);
r.setRef(0);
r.setOffset(0);
r.setBlipRecord(blipRecord);
return this.workbook.addBSERecord(r);
}
public List<HSSFPictureData> getAllPictures()
{
List pictures = new ArrayList();
Iterator recordIter = this.workbook.getRecords().iterator();
while (recordIter.hasNext())
{
Record r = (Record) recordIter.next();
if (r instanceof AbstractEscherHolderRecord)
{
((AbstractEscherHolderRecord) r).decode();
List escherRecords = ((AbstractEscherHolderRecord) r).getEscherRecords();
searchForPictures(escherRecords, pictures);
}
}
return pictures;
}
private void searchForPictures(List<EscherRecord> escherRecords, List<HSSFPictureData> pictures)
{
for (EscherRecord escherRecord : escherRecords)
{
if (escherRecord instanceof EscherBSERecord)
{
EscherBlipRecord blip = ((EscherBSERecord) escherRecord).getBlipRecord();
if (blip != null)
{
HSSFPictureData picture = new HSSFPictureData(blip);
pictures.add(picture);
}
}
searchForPictures(escherRecord.getChildRecords(), pictures);
}
}
protected static Map<String, ClassID> getOleMap()
{
Map olemap = new HashMap();
olemap.put("PowerPoint Document", ClassID.PPT_SHOW);
for (String str : WORKBOOK_DIR_ENTRY_NAMES)
{
olemap.put(str, ClassID.XLS_WORKBOOK);
}
return olemap;
}
public int addOlePackage(POIFSFileSystem poiData, String label, String fileName, String command) throws IOException
{
DirectoryNode root = poiData.getRoot();
Map olemap = getOleMap();
for (Map.Entry entry : olemap.entrySet())
{
if (root.hasEntry((String) entry.getKey()))
{
root.setStorageClsid((ClassID) entry.getValue());
break;
}
}
ByteArrayOutputStream bos = new ByteArrayOutputStream();
poiData.writeFilesystem(bos);
return addOlePackage(bos.toByteArray(), label, fileName, command);
}
public int addOlePackage(byte[] oleData, String label, String fileName, String command) throws IOException
{
if (this.directory == null)
{
this.directory = new POIFSFileSystem().getRoot();
this.preserveNodes = true;
}
int storageId = 0;
DirectoryEntry oleDir = null;
do
{
String storageStr = "MBD" + HexDump.toHex(++storageId);
if (!(this.directory.hasEntry(storageStr)))
{
oleDir = this.directory.createDirectory(storageStr);
oleDir.setStorageClsid(ClassID.OLE10_PACKAGE);
}
}
while (oleDir == null);
byte[] oleBytes = {1, 0, 0, 2, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0};
oleDir.createDocument("\1Ole", new ByteArrayInputStream(oleBytes));
Ole10Native oleNative = new Ole10Native(label, fileName, command, oleData);
ByteArrayOutputStream bos = new ByteArrayOutputStream();
oleNative.writeOut(bos);
oleDir.createDocument("\1Ole10Native", new ByteArrayInputStream(bos.toByteArray()));
return storageId;
}
public boolean isWriteProtected()
{
return this.workbook.isWriteProtected();
}
public void writeProtectWorkbook(String password, String username)
{
this.workbook.writeProtectWorkbook(password, username);
}
public void unwriteProtectWorkbook()
{
this.workbook.unwriteProtectWorkbook();
}
public List<HSSFObjectData> getAllEmbeddedObjects()
{
List objects = new ArrayList();
for (int i = 0; i < getNumberOfSheets(); ++i)
{
getAllEmbeddedObjects(getSheetAt(i), objects);
}
return objects;
}
private void getAllEmbeddedObjects(HSSFSheet sheet, List<HSSFObjectData> objects)
{
HSSFPatriarch patriarch = sheet.getDrawingPatriarch();
if (null == patriarch)
{
return;
}
getAllEmbeddedObjects(patriarch, objects);
}
private void getAllEmbeddedObjects(HSSFShapeContainer parent, List<HSSFObjectData> objects)
{
for (HSSFShape shape : parent.getChildren())
if (shape instanceof HSSFObjectData)
objects.add((HSSFObjectData) shape);
else if (shape instanceof HSSFShapeContainer)
getAllEmbeddedObjects((HSSFShapeContainer) shape, objects);
}
public HSSFCreationHelper getCreationHelper()
{
return new HSSFCreationHelper(this);
}
UDFFinder getUDFFinder()
{
return this._udfFinder;
}
public void addToolPack(UDFFinder toopack)
{
AggregatingUDFFinder udfs = (AggregatingUDFFinder) this._udfFinder;
udfs.add(toopack);
}
public void setForceFormulaRecalculation(boolean value)
{
InternalWorkbook iwb = getWorkbook();
RecalcIdRecord recalc = iwb.getRecalcId();
recalc.setEngineId(0);
}
public boolean getForceFormulaRecalculation()
{
InternalWorkbook iwb = getWorkbook();
RecalcIdRecord recalc = (RecalcIdRecord) iwb.findFirstRecordBySid(449);
return ((recalc != null) && (recalc.getEngineId() != 0));
}
public boolean changeExternalReference(String oldUrl, String newUrl)
{
return this.workbook.changeExternalReference(oldUrl, newUrl);
}
public DirectoryNode getRootDirectory()
{
return this.directory;
}
private static final class SheetRecordCollector implements RecordAggregate.RecordVisitor
{
private List<Record> _list;
private int _totalSize;
public SheetRecordCollector()
{
this._totalSize = 0;
this._list = new ArrayList(128);
}
public int getTotalSize()
{
return this._totalSize;
}
public void visitRecord(Record r)
{
this._list.add(r);
this._totalSize += r.getRecordSize();
}
public int serialize(int offset, byte[] data)
{
int result = 0;
int nRecs = this._list.size();
for (int i = 0; i < nRecs; ++i)
{
Record rec = (Record) this._list.get(i);
result += rec.serialize(offset + result, data);
}
return result;
}
}
}
做一个项目里面遇到的一个目录.XLS，我估计是因为本来是2017以上.XLSX版本转换过来不行。
扫一扫
1023
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
