gpt4 book ai didi

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

转载 作者:行者123 更新时间:2023-11-30 17:41:15 25 4
gpt4 key购买 nike

我正在构建一个工具来自动创建包含表格和关联数据透视表的 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 来做到这一点?

最佳答案

这是可以做到的,但这不是世界上最美好的事情。您可以提取缓存 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);
}
}

关于c# - 将表而不是范围定义为数据透视表 'cacheSource',我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/33282955/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com