有没有办法在不出现CharconversionException异常的情况下打开xlsx文件?

huangapple go评论91阅读模式
英文:

Is there a way I can open an xlsx file in without facing the CharconversionException exception?

问题

我的应用程序处理Excel文件;但是在打开xlsx格式的Excel文件时,它给我以下错误。我是不是做错了什么,还是有什么我遗漏了吗?以下是代码和错误堆栈;我已经追踪到错误的来源在这里:

myExcelBook = new XSSFWorkbook(new FileInputStream(f));
myExcelSheet = ((XSSFWorkbook) myExcelBook).getSheetAt(0);

异常堆栈:

Caused by: java.io.CharConversionException: 不支持大于4个字节的字符字节0x8f表示超过4个字节的长度
	at org.apache.xmlbeans.impl.piccolo.xml.UTF8XMLDecoder.decode(UTF8XMLDecoder.java:162)
	at org.apache.xmlbeans.impl.piccolo.xml.XMLStreamReader$FastStreamDecoder.read(XMLStreamReader.java:762)
	at org.apache.xmlbeans.impl.piccolo.xml.XMLStreamReader.read(XMLStreamReader.java:162)
	at org.apache.xmlbeans.impl.piccolo.xml.PiccoloLexer.yy_refill(PiccoloLexer.java:3477)
	at org.apache.xmlbeans.impl.piccolo.xml.PiccoloLexer.yylex(PiccoloLexer.java:3962)
	at org.apache.xmlbeans.impl.piccolo.xml.Piccolo.yylex(Piccolo.java:1290)
	at org.apache.xmlbeans.impl.piccolo.xml.Piccolo.yyparse(Piccolo.java:1400)
	at org.apache.xmlbeans.impl.piccolo.xml.Piccolo.parse(Piccolo.java:714)
	at org.apache.xmlbeans.impl.store.Locale$SaxLoader.load(Locale.java:3479)
	at org.apache.xmlbeans.impl.store.Locale.parseToXmlObject(Locale.java:1277)
	at org.apache.xmlbeans.impl.store.Locale.parseToXmlObject(Locale.java:1264)
	at org.apache.xmlbeans.impl.schema.SchemaTypeLoaderBase.parse(SchemaTypeLoaderBase.java:345)
	at org.openxmlformats.schemas.spreadsheetml.x2006.main.SstDocument$Factory.parse(Unknown Source)
	at org.apache.poi.xssf.model.SharedStringsTable.readFrom(SharedStringsTable.java:119)
	at org.apache.poi.xssf.model.SharedStringsTable.<init>(SharedStringsTable.java:106)
	at java.base/jdk.internal.reflect.DirectConstructorHandleAccessor.newInstance(DirectConstructorHandleAccessor.java:67)
	... 61 more

我想从xlsx文件中获取列数和单元格数据,以便在我的应用程序中进行处理,但是当我在JavaFx应用程序中加载文件时,错误出现并且应用程序被冻结。

英文:

My application processes excel files; but when opening xlsx Excel format, it gives me the following error. Is there something I'm doing wrong or anything I've left out?. Below is the code and the error stack trace; I've traced the error's origin to here:

myExcelBook = new XSSFWorkbook(new FileInputStream(f));
myExcelSheet = ((XSSFWorkbook) myExcelBook).getSheetAt(0);

The exception stack trace:

Caused by: java.io.CharConversionException: Characters larger than 4 bytes are not supported: byte 0x8f implies a length of more than 4 bytes
at org.apache.xmlbeans.impl.piccolo.xml.UTF8XMLDecoder.decode(UTF8XMLDecoder.java:162)
at org.apache.xmlbeans.impl.piccolo.xml.XMLStreamReader$FastStreamDecoder.read(XMLStreamReader.java:762)
at org.apache.xmlbeans.impl.piccolo.xml.XMLStreamReader.read(XMLStreamReader.java:162)
at org.apache.xmlbeans.impl.piccolo.xml.PiccoloLexer.yy_refill(PiccoloLexer.java:3477)
at org.apache.xmlbeans.impl.piccolo.xml.PiccoloLexer.yylex(PiccoloLexer.java:3962)
at org.apache.xmlbeans.impl.piccolo.xml.Piccolo.yylex(Piccolo.java:1290)
at org.apache.xmlbeans.impl.piccolo.xml.Piccolo.yyparse(Piccolo.java:1400)
at org.apache.xmlbeans.impl.piccolo.xml.Piccolo.parse(Piccolo.java:714)
at org.apache.xmlbeans.impl.store.Locale$SaxLoader.load(Locale.java:3479)
at org.apache.xmlbeans.impl.store.Locale.parseToXmlObject(Locale.java:1277)
at org.apache.xmlbeans.impl.store.Locale.parseToXmlObject(Locale.java:1264)
at org.apache.xmlbeans.impl.schema.SchemaTypeLoaderBase.parse(SchemaTypeLoaderBase.java:345)
at org.openxmlformats.schemas.spreadsheetml.x2006.main.SstDocument$Factory.parse(Unknown Source)
at org.apache.poi.xssf.model.SharedStringsTable.readFrom(SharedStringsTable.java:119)
at org.apache.poi.xssf.model.SharedStringsTable.&lt;init&gt;(SharedStringsTable.java:106)
at java.base/jdk.internal.reflect.DirectConstructorHandleAccessor.newInstance(DirectConstructorHandleAccessor.java:67)
... 61 more

I wanted to get the number of columns and cell data from the xlsx file for processing in my application, but when I load the file in the JavaFx application, the error shows up and the application freezes.

答案1

得分: 0

问题在于我使用了过时的apache poi和XML beans库版本。通过升级到apache poi版本5.2.3org.apache.xmlbeans版本5.1.1poi-ooxml版本5.2.3,我能够打开xlsx文件。此外,在打开我其中一个较大的xlsx文件时,我遇到了下面的错误。

错误:

Exception in thread "main" org.apache.poi.util.RecordFormatException: Tried to read data but the maximum length for this record type is 100,000,000.
If the file is not corrupt and not large, please open an issue on bugzilla to request 
increasing the maximum allowable size for this record type.
You can set a higher override value with IOUtils.setByteArrayMaxOverride()
at org.apache.poi.poi/org.apache.poi.util.IOUtils.throwRecordTruncationException(IOUtils.java:607)
at org.apache.poi.poi/org.apache.poi.util.IOUtils.toByteArray(IOUtils.java:249)
at org.apache.poi.poi/org.apache.poi.util.IOUtils.toByteArrayWithMaxLength(IOUtils.java:220)
at org.apache.poi.ooxml/org.apache.poi.openxml4j.util.ZipArchiveFakeEntry.<init>(ZipArchiveFakeEntry.java:81)
at org.apache.poi.ooxml/org.apache.poi.openxml4j.util.ZipInputStreamZipEntrySource.<init>(ZipInputStreamZipEntrySource.java:98)
at org.apache.poi.ooxml/org.apache.poi.openxml4j.opc.ZipPackage.<init>(ZipPackage.java:132)
at org.apache.poi.ooxml/org.apache.poi.openxml4j.opc.OPCPackage.open(OPCPackage.java:319)
at org.apache.poi.ooxml/org.apache.poi.ooxml.util.PackageHelper.open(PackageHelper.java:59)
at org.apache.poi.ooxml/org.apache.poi.xssf.usermodel.XSSFWorkbook.<init>(XSSFWorkbook.java:290)
at org.apache.poi.ooxml/org.apache.poi.xssf.usermodel.XSSFWorkbook.<init>(XSSFWorkbook.java:286)
at com.example.scrapper/com.example.scrapper.ChangeToCSV.main(ChangeToCSV.java:15)
Process finished with exit code 1

我通过在声明Workbook和其他变量以打开xlsx文件之前使用以下代码行来修复了错误:

IOUtils.setByteArrayMaxOverride(1000000000);

示例:

IOUtils.setByteArrayMaxOverride(1000000000);
try {
     FileInputStream inputStream = new FileInputStream(xlsxFilePath);
     Workbook workbook = new XSSFWorkbook(inputStream);
     Sheet sheet = workbook.getSheetAt(0);
     //剩下的代码
英文:

The issue here is that I was using an outdated version of the apache poi and XML beans libraries.By upgrading to the apache poi version 5.2.3 , org.apache.xmlbeans version 5.1.1 and poi-ooxml version 5.2.3 , I was able to open the xlsx file.
Also, I faced the error below during opening one of my larger xlsx file.
Error:

  Exception in thread &quot;main&quot; org.apache.poi.util.RecordFormatException: Tried to read data but the maximum length for this record type is 100,000,000.
If the file is not corrupt and not large, please open an issue on bugzilla to request 
increasing the maximum allowable size for this record type.
You can set a higher override value with IOUtils.setByteArrayMaxOverride()
at org.apache.poi.poi/org.apache.poi.util.IOUtils.throwRecordTruncationException(IOUtils.java:607)
at org.apache.poi.poi/org.apache.poi.util.IOUtils.toByteArray(IOUtils.java:249)
at org.apache.poi.poi/org.apache.poi.util.IOUtils.toByteArrayWithMaxLength(IOUtils.java:220)
at org.apache.poi.ooxml/org.apache.poi.openxml4j.util.ZipArchiveFakeEntry.&lt;init&gt;(ZipArchiveFakeEntry.java:81)
at org.apache.poi.ooxml/org.apache.poi.openxml4j.util.ZipInputStreamZipEntrySource.&lt;init&gt;(ZipInputStreamZipEntrySource.java:98)
at org.apache.poi.ooxml/org.apache.poi.openxml4j.opc.ZipPackage.&lt;init&gt;(ZipPackage.java:132)
at org.apache.poi.ooxml/org.apache.poi.openxml4j.opc.OPCPackage.open(OPCPackage.java:319)
at org.apache.poi.ooxml/org.apache.poi.ooxml.util.PackageHelper.open(PackageHelper.java:59)
at org.apache.poi.ooxml/org.apache.poi.xssf.usermodel.XSSFWorkbook.&lt;init&gt;(XSSFWorkbook.java:290)
at org.apache.poi.ooxml/org.apache.poi.xssf.usermodel.XSSFWorkbook.&lt;init&gt;(XSSFWorkbook.java:286)
at com.example.scrapper/com.example.scrapper.ChangeToCSV.main(ChangeToCSV.java:15)
Process finished with exit code 1

I fixed the error by using the line below, just before declaring the Workbook and other variables to open the xlsx file:
IOUtils.setByteArrayMaxOverride(1000000000);
Illustration here:

IOUtils.setByteArrayMaxOverride(1000000000);
try {
FileInputStream inputStream = new FileInputStream(xlsxFilePath);
Workbook workbook = new XSSFWorkbook(inputStream);
Sheet sheet = workbook.getSheetAt(0);
//The rest of the code

huangapple
  • 本文由 发表于 2023年3月9日 16:02:39
  • 转载请务必保留本文链接:https://go.coder-hub.com/75681849.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定