c# - 将表而不是范围定义为数据透视表 'cacheSource'

Question

我正在构建一个工具来自动创建包含表格和关联数据透视表的 Excel 工作簿。表结构在一张纸上，稍后将使用另一种工具从数据库中提取数据。数据透视表位于第二张工作表上，使用前一张工作表中的表格作为源。

我正在使用 EPPlus 来帮助构建工具，但在指定 cacheSource 时遇到了问题。我正在使用以下内容创建范围和数据透视表:

 var dataRange = dataWorksheet.Cells[dataWorksheet.Dimension.Address.ToString()];

 var pivotTable = pivotWorksheet.PivotTables.Add(pivotWorksheet.Cells["B3"], dataRange, name);

这会将 cacheSource 设置为:

<x:cacheSource type="worksheet" xmlns:x="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<x:worksheetSource ref="A1:X2" sheet="dataWorksheet" />

或在 Excel 中，数据源设置为:

dataWorksheet!$A$1:$X$2

如果表格大小永远不变，这会很好地工作，但由于行数是动态的，我发现刷新数据时，数据仅从指定的初始范围读取。

我想做的是以编程方式将 cacheSource 设置为:

<x:cacheSource type="worksheet" xmlns:x="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
  <x:worksheetSource name="dataWorksheet" />
</x:cacheSource>

或在 Excel 中，将数据源设置为:

dataWorksheet

我相信可以通过直接访问 XML 来做到这一点(任何关于这方面的指示都是最受欢迎的)但是有没有办法使用 EPPlus 来做到这一点？

Answer 1

这是可以做到的，但这不是世界上最美好的事情。您可以提取缓存 def xml 并从创建的 EPPlus 数据透视表对象中对其进行编辑，但是当您调用 package.save()(或 GetAsByteArray()) 因为它在保存时解析 xml 以生成最终文件。正如您所说，这是 EPPlus 无法将表作为源处理的结果。

因此，您的替代方法是通常使用 EPPlus 保存文件，然后使用 .net ZipArchive 操作 xlsx 的内容，它是重命名的 zip 文件。诀窍是您不能乱序操作 zip 中的文件，否则 Excel 在打开文件时会报错。由于您无法插入条目(只能添加到末尾)，因此您必须重新创建 zip。以下是 ZipArchive 的扩展方法，可让您更新缓存源:

public static bool SetCacheSourceToTable(this ZipArchive xlsxZip, FileInfo destinationFileInfo, string tablename, int cacheSourceNumber = 1)
{
    var cacheFound = false;
    var cacheName = String.Format("pivotCacheDefinition{0}.xml", cacheSourceNumber);

    using (var copiedzip = new ZipArchive(destinationFileInfo.Open(FileMode.Create, FileAccess.ReadWrite), 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
        xlsxZip.Entries.ToList().ForEach(entry =>
        {
            var newentry = copiedzip.CreateEntry(entry.FullName);
            var newstream = newentry.Open();
            var orgstream = entry.Open();

            //Copy all other files except the cache def we are after
            if (entry.Name != cacheName)
            {
                orgstream.CopyTo(newstream);
            }
            else
            {
                cacheFound = true;

                //Load the xml document to manipulate
                var xdoc = new XmlDocument();
                xdoc.Load(orgstream);

                //Get reference to the worksheet xml for proper namespace
                var nsm = new XmlNamespaceManager(xdoc.NameTable);
                nsm.AddNamespace("default", xdoc.DocumentElement.NamespaceURI);

                //get the source
                var worksheetSource = xdoc.SelectSingleNode("/default:pivotCacheDefinition/default:cacheSource/default:worksheetSource", nsm);

                //Clear the attributes
                var att = worksheetSource.Attributes["ref"];
                worksheetSource.Attributes.Remove(att);

                att = worksheetSource.Attributes["sheet"];
                worksheetSource.Attributes.Remove(att);

                //Create the new attribute for table
                att = xdoc.CreateAttribute("name");
                att.Value = tablename;
                worksheetSource.Attributes.Append(att);

                xdoc.Save(newstream);
            }

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

    return cacheFound;

}

下面是如何使用它:

//Throw in some data
var datatable = new DataTable("tblData");
datatable.Columns.AddRange(new[]
{
    new DataColumn("Col1", typeof (int)), new DataColumn("Col2", typeof (int)), new DataColumn("Col3", typeof (object))
});

for (var i = 0; i < 10; i++)
{
    var row = datatable.NewRow();
    row[0] = i; row[1] = i*10; row[2] = Path.GetRandomFileName();
    datatable.Rows.Add(row);
}

const string tablename = "PivotTableSource";
using (var pck = new ExcelPackage())
{
    var workbook = pck.Workbook;

    var source = workbook.Worksheets.Add("source");
    source.Cells.LoadFromDataTable(datatable, true);
    var datacells = source.Cells["A1:C11"];

    source.Tables.Add(datacells, tablename);

    var pivotsheet = workbook.Worksheets.Add("pivot");
    pivotsheet.PivotTables.Add(pivotsheet.Cells["A1"], datacells, "PivotTable1");

    using (var orginalzip = new ZipArchive(new MemoryStream(pck.GetAsByteArray()), ZipArchiveMode.Read))
    {
        var fi = new FileInfo(@"c:\temp\Pivot_From_Table.xlsx");
        if (fi.Exists)
            fi.Delete(); 

        var result = orginalzip.SetCacheSourceToTable(fi, tablename, 1);
        Console.Write("Cache source was updated: ");
        Console.Write(result);
    }
}