在Excel中读取内容文件EPPlus产生 [英] Unreadable content in Excel file generated with EPPlus

问题描述

我有一个小问题，当我生成一个模板Excel文件，使用EPPlus库。该文件包含其中包含用于填充以下表数据透视表数据的头s preadsheet。

I'm having a little problem when I generate an Excel file from a template, using the EPPlus library. The file has a first spreadsheet that contains data that is used for populating pivot tables in the following sheets.

当我打开生成的文件时，我收到以下错误信息： 创先争优发现sampleFromTemplate.xlsx无法读取内容。是否要恢复此工作簿的内容？我对你信任该工作簿的来源，单击是。

When I open the generated file, I get the following error message : "Excel found unreadable content in 'sampleFromTemplate.xlsx'. Do you want to recover the contents of this workbook ? I you trust the source of this workbook, click Yes."

我明明点击的话，那么让做文件修复的总结，并链接到包含此一个XML格式的日志文件：

I obviously click yes, then get a summary of repairs done to the file, and a link to an xml formatted log file containing this :

<?xml version="1.0" encoding="UTF-8" standalone="true"?>
<recoveryLog xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
    <logFileName>error095080_01.xml</logFileName>
    <summary>Errors were detected in file  'C:\TEMP\sampleFromTemplate.xlsx'</summary>
    <repairedRecords summary="Following is a list of repairs:">
        <repairedRecord>Repaired Records: Table from /xl/tables/table1.xml part (Table)</repairedRecord>
    </repairedRecords>
</recoveryLog>

这显然是由一个命名的范围（表1），我在我的code定义，表示将用于在数据透视表中的数据引起的。目前市场上已经是一个表名叫表1的模板，但我似乎无法通过ExcelPackage.Worksheet.Names集合来访问它。被新EPPlus并不太试验与Excel，我不明白的地方我做错了。下面是code，我生成该文件的位：

This is apparently caused by a named range ("Table1") that I define in my code to indicate the data to be used for the pivot tables. There already is a "Table Name" in the template called "Table1", but I can't seem to access it through the ExcelPackage.Worksheet.Names collection. Being new to EPPlus and not very experimented with Excel, I don't understand where I'm doing wrong. Here's the bit of code where I generate the file :

private string GenerateFromTemplate(string fileName, string templateName, DataTable tab)
{
    FileInfo newFile = new FileInfo(string.Format("C:\\MyPath\\{0}.xlsx", fileName));
    FileInfo templateFile = new FileInfo(string.Format("C:\\MyPath\\{0}.xlsx", templateName));

    try
    {
        using (ExcelPackage pkg = new ExcelPackage(newFile, templateFile))
        {
            ExcelWorksheet sheet = pkg.Workbook.Worksheets["MyDataSheet"];
            ExcelRange range = sheet.Cells[string.Format("A1:U{0}", dt.Rows.Count)];
            pkg.Workbook.Names.Add("Table1", range as ExcelRangeBase);

            int sheetRowIndex = 2;

            foreach (DataRow row in this.dt.Rows)
            {
                sheet.Cells[sheetRowIndex, 1].Value = row["Row1"];
                sheet.Cells[sheetRowIndex, 2].Value = row["Row2"];
                [...]
                sheet.Cells[sheetRowIndex, 21].Value = row["Row21"];

                sheetRowIndex++;
            }

            pkg.Save();
            return newFile.FullName;
        }
    }
    catch (IOException ex) { return ex.Message; }
}

请注意，该数据透视表已正确安装，无论如何，那么为什么会出现这种情况？

Note that the pivot tables are populated correctly anyway, so why is this happening ?

感谢： - ）

推荐答案

这个问题不解决，但现在我知道为什么。这个表1的事情不是一个命名范围，但一个表，我可以通过在工作表的表收集访问。

The problem is not solved but now I know exactly why. This "Table1" thing wasn't a named range but a table, which I can access through the "Tables" collection of the worksheet.

现在的问题是，在EPPlus两个表收集和表对象是只读的，所以我无法从我的code定义表的维度，我也不能删除或添加一个新的适合我的需求。 EPPlus的作者已经mentionned，它可能有一天被执行（这里和的这里）总线作为邮件几乎是3岁，我想有一点希望看到这种情况发生......

Now, the problem is that both the Tables' collection and Table objects in EPPlus are readonly so I can't define the table's dimension from my code, and neither can I remove it or add a new one to fit my needs. EPPlus's author has already mentionned that it might someday be implemented (here and here) bus as the messages are almost 3 years old, I guess there is little hope to see that happen...

无论如何，我希望这将帮助任何人遇到了同样的问题。

Anyway, I hope this will help anyone encountering the same issue.

我终于想出了一个办法来绕过问题：ExcelTable对象有一个名为TableXml可写的属性，它包含了表的XML定义 - 当然 - 它的范围。下面是它在我的案件的内容：

I finally came up with a way to bypass the problem : the ExcelTable object has a writable property called "TableXml" which contains the xml definition of the table with - of course - its range. Here's its content in my case :

<?xml version="1.0" encoding="UTF-8" standalone="true"?>
    <table dataCellStyle="Normal 2" headerRowCellStyle="Normal 2" headerRowDxfId="70" totalsRowShown="0" insertRow="1" ref="A1:U2" displayName="Table1" name="Table1" id="1" xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
        <autoFilter ref="A1:U2"/>
        <tableColumns count="21">
            <tableColumn dataCellStyle="Normal 2" name="Activity" id="1"/>
            <tableColumn dataCellStyle="Normal 2" name="Category" id="21"/>
            [...]
            <tableColumn dataCellStyle="Normal 2" name="Closed Year" id="20" dataDxfId="62"/>
        </tableColumns>
        <tableStyleInfo name="TableStyleMedium9" showColumnStripes="0" showRowStripes="1" showLastColumn="0" showFirstColumn="0"/>
</table>

在这里是什么我们感兴趣的是，在表中的裁判属性和自动筛选节点，改变它们的值可以重新定义我们的表的范围内。

What interests us here are the "ref" attributes in the "table" and "autoFilter" nodes, as changing their values allows to redefine the range of our table.

我继续这样：

XmlDocument tabXml = sheet.Tables(0).TableXml;
XmlNode tableNode = tabXml.ChildNodes[1];
tableNode.Attributes["ref"].Value = string.Format("A1:U{0}", dt.Rows.Count + 1);
XmlNode autoFilterNode = tableNode.ChildNodes[0];
autoFilterNode.Attributes["ref"].Value = string.Format("A1:U{0}", dt.Rows.Count + 1);

现在我Excel文件正确地与表1产生的拟合我的数据的实际范围！

And now my Excel file is properly generated with "Table1" fitting the actual range of my data !

这篇关于在Excel中读取内容文件EPPlus产生的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！