从excel文件中替换无效的XML字符并将其写回磁盘原因文件在MS中打开时出现损坏错误

Question

关于问题的一些背景：

我们有一个ASP.NET MVC5应用程序，在该应用程序中，我们使用FlexMonster在网格中显示数据。数据源是一个存储过程，它将所有数据导入UI网格，一旦用户单击“导出”按钮，就会将报表导出到Excel。然而，在某些情况下，导出到excel是失败的。有些数据有一些无效的字符，并且不可能/不可能按建议的here修复源。

到目前为止我的方法：

EPPlus库在初始化工作簿时失败，因为输入excel文件包含一些无效的XML字符。我可以发现该文件被转储，其中包含一些无效字符。我研究了可能的方法。

首先，我在excel文件中识别了问题字符。我首先尝试使用Notepad++手动用空格替换无效字符，EPPlus可以成功地读取文件。

现在，使用在其他SO线程here和here中给出的方法，我替换了所有可能出现的无效字符。我现在正在用

XmlConvert.IsXmlChar

方法查找有问题的XML字符并替换为空白。

我创建了一个示例程序，在这里我试图处理有问题的excel表。

//in main method 
String readFile = File.ReadAllText(filePath);
string content = RemoveInvalidXmlChars(readFile);
File.WriteAllText(filePath, content);

//removal of invalid characters
        static string RemoveInvalidXmlChars(string inputText)  
        {
            StringBuilder withoutInvalidXmlCharsBuilder = new StringBuilder();
            int firstOccurenceOfRealData = inputText.IndexOf("<t>");
            int lastOccurenceOfRealData = inputText.LastIndexOf("</t>");

            if (firstOccurenceOfRealData < 0 ||
                lastOccurenceOfRealData < 0 ||
                firstOccurenceOfRealData > lastOccurenceOfRealData)
                return inputText;

            withoutInvalidXmlCharsBuilder.Append(inputText.Substring(0, firstOccurenceOfRealData)); 
            int remaining = lastOccurenceOfRealData - firstOccurenceOfRealData;
            string textToCheckFor = inputText.Substring(firstOccurenceOfRealData, remaining); 

            foreach (char c in textToCheckFor)
            {
                withoutInvalidXmlCharsBuilder.Append((XmlConvert.IsXmlChar(c)) ? c : ' ');
            }
      withoutInvalidXmlCharsBuilder.Append(inputText.Substring(lastOccurenceOfRealData));

            return withoutInvalidXmlCharsBuilder.ToString();

        }

如果我使用notepad++手动替换有问题的字符，则该文件将在MSExcel中打开精细。上述代码成功地替换了相同的无效字符，并将内容写回文件。但是，当我试图使用MS打开Excel文件时，它会抛出一个错误，说明文件可能已损坏，并且没有显示(快照)。此外，以下代码

var excelPackage = new ExcelPackage(new FileInfo(filePath));

在我通过Notepad++更新的文件中，引发以下异常

"CRC error: the file being extracted appears to be corrupted. Expected 0x7478AABE, Actual 0xE9191E00"}

我的问题：

1. 我用这种方式修改内容的方法正确吗？
2. 如果是，如何将更新的字符串写入Excel文件？
3. 如果我的方法是错误的，那么我如何着手处理无效的XML字符呢？

打开文件时显示的错误(没有无效的XML char):

第一次流行

​

​

当我点击“是”

​

​

提前谢谢！

Answer 1

根据您最后的评论，它听起来确实是一个二进制(可推定的XLSX)文件。要确认，请使用7zip打开FlexMonster创建的文件。如果正确打开并在文件夹中看到一堆XML文件，那么它就是XLSX。

在这种情况下，对二进制文件进行搜索/替换听起来是个非常糟糕的主意。它可以在XML部件上工作，但也可以替换其他部分中的合法字符。我认为更好的方法是像@PanagiotisKanavos建议的那样使用ZipArchive。但是，您必须按照正确的顺序重新构建它，否则Excel会抱怨。类似于这里的https://stackoverflow.com/a/33312038/1324284，您可以这样做：

public static void ReplaceXmlString(this ZipArchive xlsxZip, FileInfo outFile, string oldString, string newstring)
{
    using (var outStream = outFile.Open(FileMode.Create, FileAccess.ReadWrite))
    using (var copiedzip = new ZipArchive(outStream, ZipArchiveMode.Update))
    {
        //Go though each file in the zip one by one and copy over to the new file - entries need to be in order
        foreach (var entry in xlsxZip.Entries)
        {
            var newentry = copiedzip.CreateEntry(entry.FullName);
            var newstream = newentry.Open();
            var orgstream = entry.Open();

            //Copy non-xml files over
            if (!entry.Name.EndsWith(".xml"))
            {
                orgstream.CopyTo(newstream);
            }
            else
            {
                //Load the xml document to manipulate
                var xdoc = new XmlDocument();
                xdoc.Load(orgstream);

                var xml = xdoc.OuterXml.Replace(oldString, newstring);
                xdoc = new XmlDocument();
                xdoc.LoadXml(xml);

                xdoc.Save(newstream);
            }

            orgstream.Close();
            newstream.Flush();
            newstream.Close();
        }
    }
}

当它像这样使用时：

[TestMethod]
public void ReplaceXmlTest()
{
    var datatable = new DataTable("tblData");
    datatable.Columns.AddRange(new[]
    {
        new DataColumn("Col1", typeof (int)),
        new DataColumn("Col2", typeof (int)),
        new DataColumn("Col3", typeof (string))
    });

    for (var i = 0; i < 10; i++)
    {
        var row = datatable.NewRow();
        row[0] = i;
        row[1] = i * 10;
        row[2] = i % 2 == 0 ? "ABCD" : "AXCD";
        datatable.Rows.Add(row);
    }

    using (var pck = new ExcelPackage())
    {
        var workbook = pck.Workbook;
        var worksheet = workbook.Worksheets.Add("source");

        worksheet.Cells.LoadFromDataTable(datatable, true);
        worksheet.Tables.Add(worksheet.Cells["A1:C11"], "Table1");

        //Now similulate the copy/open of the excel file into a zip archive
        using (var orginalzip = new ZipArchive(new MemoryStream(pck.GetAsByteArray()), ZipArchiveMode.Read))
        {
            var fi = new FileInfo(@"c:\temp\ReplaceXmlTest.xlsx");
            if (fi.Exists)
                fi.Delete();

            orginalzip.ReplaceXmlString(fi, "AXCD", "REPLACED!!");
        }
    }
}

给出如下内容：

​

​

记住这完全是野蛮的力量。您可以做的任何事情，使文件过滤器更聪明，而不是简单地做所有xml文件将是一件非常好的事情。如果这是问题所在，或者是工作表文件夹中的xml文件，那么可以将其限制在SharedString.xml文件中。在不了解更多数据的情况下，很难说。