将表而不是区域定义为数据透视表'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的内容，xlsx是重命名的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:tempPivot_From_Table.xlsx");
        if (fi.Exists)
            fi.Delete(); 
        var result = orginalzip.SetCacheSourceToTable(fi, tablename, 1);
        Console.Write("Cache source was updated: ");
        Console.Write(result);
    }
}