我正在使用EPPlus创建一个Excel工作簿，我已经创建了一个数据透视表。
var pivotTable1 = sheet.PivotTables.Add(sheet.Cells["M1"], sourcePivotData, "MyPivotTable");
pivotTable1.ColumnFields.Add("col4");
pivotTable1.RowFields.Add(pivotTable1.Fields["Col1"]);
pivotTable1.RowFields.Add(pivotTable1.Fields["Col2"]);
pivotTable1.RowFields.Add(pivotTable1.Fields["Col3"]);
var dataField = pivotTable1.DataFields.Add(pivotTable1.Fields["Amount"]);
我现在想要将数据透视表OUTPUT数据引用为一个Range。我知道左上角的单元格，但是考虑到数据透视表的行和列的大小是基于底层数据源动态的，我无法找到一种方法来动态地计算数据透视表输出范围中的最后一行和最后一列，而且我找不到数据透视表对象的任何属性来给出这个。
此外，它看起来不像透视表输出数据显示在
中。worksheet.Cells[]
属性，所以我不能通过这个机制来计算。
是否有一种方法来找出使用EPPlus数据透视表输出数据的范围?
我确实看到了与此匹配的GitHub问题。所以，在他们解决这个问题/发布一个新功能之前，你不能没有一个hack的解决方案。
在EPPlus中(至少在6.1之前)，您只能创建"how"的模式定义应该生成数据透视表，而不是实际的表本身。至少默认配置/设置是这样的。我是不是一些EPPlus专家。但是我没有看到任何设置/配置指示EPPlus与模式一起生成数据。
这就是您无法看到worksheet.Cells的任何数据的原因，也是底层XML文件没有rowItems和colItems节点的原因。
实际的表只有在使用.xlsx阅读器(如Libre或Excel)打开时才会生成。然后，它根据EPPlus(您的代码)生成的定义计算/生成数据透视表。如果此时保存文件，它也会将数据复制到XML文件中。
一旦你达到这个阶段，你应该能够像往常一样使用worksheet.Cells访问数据，甚至使用pivotTable.Address属性获得范围(或者如果Address属性不起作用，可能使用下面的函数)。
这听起来可能不像一个解决方案，但是，作为一个简单的解决方案，您可以执行以下步骤来生成和读取底层数据。
- 创建数据透视表
- 使用Excel应用程序打开文件(从代码)
- 向进程发送Ctrl+S命令。(这会将生成的数据透视表保存到底层XML文件中。)
- 从你的代码中读取Excel表格。
你可以参考这个hack部分
或者可以使用另一个实际生成数据的库，而不仅仅是定义。
using OfficeOpenXml;
using OfficeOpenXml.Table.PivotTable;
using System.Text;
public static class ExcelExtensions
{
// https://stackoverflow.com/a/182924/15066006
static int ToColumnNumber(this string colAdress)
{
int[] digits = new int[colAdress.Length];
for (int i = 0; i < colAdress.Length; ++i)
{
digits[i] = Convert.ToInt32(colAdress[i]) - 64;
}
int mul = 1; int res = 0;
for (int pos = digits.Length - 1; pos >= 0; --pos)
{
res += digits[pos] * mul;
mul *= 26;
}
return res;
}
// https://stackoverflow.com/a/2652855/15066006
static string ToColumnAdress(this int col)
{
if (col <= 26)
{
return Convert.ToChar(col + 64).ToString();
}
int div = col / 26;
int mod = col % 26;
if (mod == 0) { mod = 26; div--; }
return ToColumnAdress(div) + ToColumnAdress(mod);
}
public static ExcelRange? GetExcelRange(this ExcelPivotTable pivotTable)
{
var locationTag = pivotTable.PivotTableXml.GetElementsByTagName("location")[0];
var baseLoc = locationTag?.Attributes?.GetNamedItem("ref")?.Value ?? string.Empty;
if (baseLoc.Contains(':'))
return pivotTable.WorkSheet.Cells[baseLoc];
var firstHeaderValue = locationTag?.Attributes?.GetNamedItem("firstHeaderRow")?.Value ?? string.Empty;
var firstDataRowValue = locationTag?.Attributes?.GetNamedItem("firstDataRow")?.Value ?? string.Empty;
var firstDataColValue = locationTag?.Attributes?.GetNamedItem("firstDataCol")?.Value ?? string.Empty;
var firstHeaderRow = firstHeaderValue is null ? 1 : Int32.Parse(firstHeaderValue);
var firstDataRow = firstDataRowValue is null ? 1 : Int32.Parse(firstDataRowValue);
var firstDataCol = firstDataColValue is null ? 1 : Int32.Parse(firstDataColValue);
var headers = firstHeaderRow - 1;
var colItems = pivotTable.GetColumnItems();
var rowItems = pivotTable.GetRowItems();
var totalRows = firstDataRow + headers + rowItems;
var totalColumns = firstDataCol + colItems;
string columnLetter; int currentRowNumber; int i = 0;
var columnLetterBuffer = new StringBuilder();
while (Char.IsLetter(baseLoc[i]))
{
columnLetterBuffer.Append(baseLoc[i]);
++i;
}
columnLetter = columnLetterBuffer.ToString();
currentRowNumber = int.Parse(baseLoc[i..]);
var currentColNumber = columnLetter.ToColumnNumber();
var lastColNumber = currentColNumber + totalColumns - 1;
var lastRowNumber = currentRowNumber + totalRows;
var bottomRightCell = $"{lastColNumber.ToColumnAdress()}{lastRowNumber}";
return pivotTable.WorkSheet.Cells[$"{baseLoc}:{bottomRightCell}"];
}
public static int GetRowItems(this ExcelPivotTable pivotTable)
{
var rowItems = pivotTable.PivotTableXml.GetElementsByTagName("rowItems")[0];
if (rowItems is null)
{
Console.WriteLine("Data hasn't been generated yet. Open it in some excel application to actually generate the pivot table first");
return 0;
}
else
{
var attrValue = rowItems?.Attributes?.GetNamedItem("count")?.Value;
return attrValue is null ? 0 : Int32.Parse(attrValue);
}
}
public static int GetColumnItems(this ExcelPivotTable pivotTable)
{
var colItems = pivotTable.PivotTableXml.GetElementsByTagName("colItems")[0];
if (colItems is null)
{
Console.WriteLine("Data hasn't been generated yet. Open it in some excel application to actually generate the pivot table first");
return 0;
}
else
{
var attrValue = colItems?.Attributes?.GetNamedItem("count")?.Value;
return attrValue is null ? 0 : Int32.Parse(attrValue);
}
}
}
using (ExcelPackage excelPackage = new ExcelPackage(new FileInfo("Workbook.xlsx")))
{
// Get the worksheet that contains the pivot table
ExcelWorksheet worksheet = excelPackage.Workbook.Worksheets["Sheet1"];
// Get the pivot table
ExcelPivotTable pivotTable = worksheet.PivotTables["PivotTable1"];
// Get the range of the pivot table
ExcelRange pivotTableRange = pivotTable.CacheDefinition.SourceRange;
// Get the last row of the pivot table range
int lastRow = pivotTableRange.End.Row;
// Get the last column of the pivot table range
int lastColumn = pivotTableRange.End.Column;
}
通过使用ExcelRange对象的End属性和生成的ExcelRange的Row和Column属性，可以动态地计算出数据透视表范围的最后一行和最后一列。
使用如下代码:
var range = pivotTable1.CacheDefinition.SourceRange;