APACHE POI EXCEL XmlException：是无效的XML字符，是否有任何方法预处理EXCEL文件？

Question

我使用java和Apache POI进行阅读。xlsx文件。（6万行），但我得到了错误。

我使用poi和xmlbeans的最新版本maven插件。

根据我在StackOverflow中发现的相关问题，最新的poi应该能够成功地处理具有特殊字符的文件。

如果是xml文件，我可以自己替换程序中的特殊字符。但它是一个excel文件。

困难在于我不知道如何使用poi成功读取“excel”文件。

或者有什么方法可以处理文件？

我使用openjdk，版本："1.8.0_171-1-redhat"。

像这样的错误消息

Caused by: java.io.IOException: unable to parse shared strings table
    at org.apache.poi.xssf.model.SharedStringsTable.readFrom(SharedStringsTable.java:134)
    at org.apache.poi.xssf.model.SharedStringsTable.<init>(SharedStringsTable.java:111)
    ... 11 more
Caused by: org.apache.xmlbeans.XmlException: error: Character reference "&#55357" is an invalid XML character.
    at org.apache.xmlbeans.impl.store.Locale$SaxLoader.load(Locale.java:3440)
    at org.apache.xmlbeans.impl.store.Locale.parseToXmlObject(Locale.java:1272)
    at org.apache.xmlbeans.impl.store.Locale.parseToXmlObject(Locale.java:1259)
    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:123)

代码

import java.io.ByteArrayInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.nio.charset.StandardCharsets;

import org.apache.commons.codec.binary.Base64;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class test2 {

  public static void main(String[] args) throws Exception {
    File file = new File("D:\\Users\\3389\\Desktop\\Review\\drive-download-20181112T012605Z-001\\ticket.xlsx");
    Workbook workbook = null;
    XSSFWorkbook xssfWorkbook = new XSSFWorkbook(file); //error occured
    workbook = new SXSSFWorkbook(xssfWorkbook);
    Sheet sheet = xssfWorkbook.getSheetAt(0);  
    System.out.println("the first row:"+sheet.getFirstRowNum());
  }
}

pom.xml

        <dependency>
          <groupId>org.apache.poi</groupId>
          <artifactId>poi</artifactId>
          <version>4.0.0</version>
        </dependency>
        <dependency>
          <groupId>org.apache.poi</groupId>
          <artifactId>poi-ooxml</artifactId>
          <version>4.0.0</version>
        </dependency> 

共享字符串中包含UTF16SURROKEPTAIRS。xml（几个示例）

������
��
��������������
etc....

Answer 1

由于您的问题标题包含问题“是否有任何方法可以预处理excel文件？”，我将尝试回答这个问题：

假设：*. xlsx文件中的/xl/share dStrings.xml包含UTF-16-surrogate对XML数字字符引用，如

因此，如果共享字符串。xml*。xlsx文件包含UTF-16代理项对XML数字字符引用，则该文件已损坏，无论如何都不应使用。这个问题应该由创建该代码的人来解决。xlsx文件。

但是，如果仍然需要修复该文件，那么这只能在字符串级别上完成。由于UTF-16代理项对XML数字字符引用，无法解析XML。然后需要获得共享字符串。xml超出了*。xlsx文件。然后获取该共享字符串的字符串内容。xml文件。然后将找到的每个UTF-16代理项对XML数字字符引用替换为其Unicode替换。

我的代码展示了如何使用java.util.regex.Matcher执行此操作。它搜索与模式匹配的实体

import java.io.*;

import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.openxml4j.opc.PackagePart;
import java.util.regex.Pattern; 
import java.util.regex.Matcher;

public class XSSFWrongXMLinSharedStrings {

 static String replaceUTF16SurrogatePairs(String string) {
  Pattern pattern = Pattern.compile("&#(\\d{5});&#(\\d{5});");
  Matcher matcher = pattern.matcher(string);
  while (matcher.find()) {
   String found = matcher.group();
   int h = Integer.valueOf(matcher.group(1));
   int l = Integer.valueOf(matcher.group(2));
   if (0xD800 <= h && h < 0xDC00 && 0xDC00 <= l && l < 0xDFFF) {
    int n = (h - 0xD800) * 0x400 + (l - 0xDC00) + 0x10000;
System.out.print(found + " will be replaced with ");
System.out.println("&#" + n + ";");
    string = string.replace(found, "&#" + n + ";");
   }
  }
  pattern = Pattern.compile("&#(\\d{5});");
  matcher = pattern.matcher(string);
  while (matcher.find()) {
   String found = matcher.group();
   int n = Integer.valueOf(matcher.group(1));
   if (0xD800 <= n && n < 0xDFFF) {
System.out.println(found + " is single part of supplement pair. It will be removed.");
    string = string.replace(found, "");
   }
  }  
  return string;
 }

 public static void main(String[] args) throws Exception {
  File file = new File("ticket.xlsx");

  //Repairing the /xl/sharedStrings.xml on string level. Parsing XML is not possible because of the UTF-16-surrogate-pair XML numeric character references.
  OPCPackage opcPackage = OPCPackage.open(file);
  PackagePart packagePart = opcPackage.getPartsByName(Pattern.compile("/xl/sharedStrings.xml")).get(0);
  ByteArrayOutputStream sharedStringsBytes = new ByteArrayOutputStream();
  byte[] buffer = new byte[1024];
  int length;
  InputStream inputStream = packagePart.getInputStream();
  while ((length = inputStream.read(buffer)) != -1) {
   sharedStringsBytes.write(buffer, 0, length);
  }
  inputStream.close();
  String sharedStrings = sharedStringsBytes.toString("UTF-8");

  //Replace UTF-16-surrogate-pair XML numeric character reference with it's unicode replacement:
  //sharedStrings = sharedStrings.replace("&#55357;&#56833;", "&#x1F601;");
  //ToDo: Create method for replacing all possible UTF-16-surrogate-pair XML numeric character references with their unicode replacements.
  sharedStrings = replaceUTF16SurrogatePairs(sharedStrings);

  OutputStream outputStream = packagePart.getOutputStream();
  outputStream.write(sharedStrings.getBytes("UTF-8"));
  outputStream.flush();
  outputStream.close();
  opcPackage.close();
  //Now the /xl/sharedStrings.xml in the file does not contain UTF-16-surrogate-pair XML numeric character references any more.

  Workbook workbook = new XSSFWorkbook(file); 
  Sheet sheet = workbook.getSheetAt(0);  
  System.out.println("Success.");
 }
}