将表而不是范围定义为数据透视表"cacheSource" [英] Defining a table rather than a range as a PivotTable 'cacheSource'
问题描述
我正在构建一个工具来自动创建一个包含表和关联的数据透视表的Excel工作簿.表结构在一张纸上，稍后将使用另一种工具从数据库中提取其数据.数据透视表位于第二张纸上，使用上一张纸上的表格作为源.
I am building a tool to automate the creation of an Excel workbook that contains a table and an associated PivotTable. The table structure is on one sheet, the data for which will be pulled from a database using another tool at a later point. The PivotTable is on a second sheet using the table from the previous sheet as the source.
我正在使用EPPlus来方便地构建工具，但是在指定cacheSource时遇到了问题.我正在使用以下内容来创建范围和数据透视表:
I am using EPPlus to facilitate building the tool but am running into problems specifying the cacheSource. I am using the following to create the range and PivotTable:
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中，数据源设置为:
or within Excel, the data source is set to:
dataWorksheet!$A$1:$X$2
如果表的大小从不改变，这很好用，但是由于行数是动态的，我发现刷新数据时，仅从指定的初始范围读取数据.
This works fine if the table size never changes, but as the number of rows will be dynamic, I am finding when the data is refreshed, data is only read from the initial range specified.
我想要做的是以编程方式将cacheSource设置为:
What I am want to do is to programmatically set the cacheSource to:
<x:cacheSource type="worksheet" xmlns:x="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<x:worksheetSource name="dataWorksheet" />
</x:cacheSource>
或在Excel中，将数据源设置为:
or in Excel, set the data source to:
dataWorksheet
我相信可以通过直接访问XML来做到这一点(对此表示欢迎)，但是有什么方法可以使用EPPlus?
I believe it may be possible to do this by accessing the XML directly (any pointers on this would be most welcome) but is there any way to do this using EPPlus?
推荐答案
可以做到，但这并不是世界上最漂亮的事情.您可以提取缓存def xml并从创建的EPPlus数据透视表对象中对其进行编辑，但是当您调用package.save()(或GetAsByteArray())时，这将对保存逻辑造成严重破坏，因为它会在保存时解析xml以生成最终文件. .如您所说，这是由于EPPlus无法将表作为源处理的结果.
It can be done but it is not the prettiest thing in the world. You can extract the cache def xml and edit it from the created EPPlus pivot table object but that will wreak havoc with the save logic when you call package.save() (or GetAsByteArray()) since it parses the xml on save to generate the final file. This is the result of, as you said, EPPlus not capable of handling a table as the source.
因此，您的替代方法是使用EPPlus正常保存文件，然后使用.net ZipArchive处理xlsx(已重命名的zip文件)的内容.诀窍是您无法在zip中乱序处理文件，否则Excel在打开文件时会抱怨.并且由于您不能插入条目(只能添加到末尾)，因此必须重新创建该zip.这是ZipArchive上的扩展方法，使您可以更新缓存源:
So, your alternative is to save the file with EPPlus normally and then manipulate the content of the xlsx which is a renamed zip file using a .net ZipArchive. The trick is you cannot manipulate the files out of order in the zip otherwise Excel will complain when it opens the file. And since you cannot insert an entry (only add to the end) you have to recreate the zip. Here is an extension method on a ZipArchive that will allow you to update the cache source:
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);
}
}
这篇关于将表而不是范围定义为数据透视表"cacheSource"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
