c# - 如何使用 C# 更新 Excel 数据透视表数据

问题描述

让我概述一下我的要求。我有一个带有多个数据透视表（链接到图表/切片器等）的 excel 电子表格和 2 个包含这些数据透视表引用的数据的工作表。目前，我必须手动执行 SQL 查询，复制数据，将其粘贴到电子表格中的当前数据上，然后每天刷新数据透视表。

这充其量是次优的。所以我想要实现的是一些我可以按计划执行的 C# 代码。

使用 EPPlus，我设法将 excel 文件加载为模板，创建一个新文件，从 SQL 获取数据，用新数据更新 2 个数据表，然后保存文件。

using (var templateStream = new MemoryStream(File.ReadAllBytes(@"PATH_TO_TEMPLATE_FILE")))
{
    using (var newStream = new MemoryStream())
    {
        //Create e NEW excel doc from the given template
        using (ExcelPackage excelPackage = new ExcelPackage(newStream, templateStream))
        {
            //load the data from SQL
            DataSet data = LoadDatasetFromQuery(configs, QueueItem);
            //loop over the DataTables inside the DataSet
            for (int i = 1; i <= data.Tables.Count; i++)
            {
                //Resolve the worksheet to put the data on
                var worksheetName = configs.FirstOrDefault(c => c.Name.StartsWith($"Worksheet.{i}."));
                ExcelWorksheet worksheet = excelPackage.Workbook.Worksheets[worksheetName.Value];
                //Put the data on the worksheet top/left = B3
                worksheet.Cells["B3"].LoadFromDataTable(data.Tables[i - 1], false);
            }
            //Save the file to the memory stream
            excelPackage.Save();
        }

        //Write the file to the file system
        File.WriteAllBytes(@"PATH_TO_OUTPUT_FILE", newStream.ToArray());
    }
}

问题是，当我尝试打开 excel 文件时，它说它已损坏并尝试通过完全删除数据透视表来修复它。我的模板文件使用了此 SO 帖子中提到的命名范围，但这并没有解决问题。

附上它如何完成“修复”的excel日志

我还涉足了一些互操作库（Microsoft.Office.Interop.Excel）的使用，但在调试/文档等方面这真的就像一个黑洞。我不反对使用它，我只是不不知道怎么做。（好吧，无论如何我尝试过的都没有正常工作）

任何有关上述内容的帮助将不胜感激。如果您需要更多信息，请随时询问。

标签： c#excelpivot-tableepplus

好的，看来我上面的代码是正确的，但是我加载的 excel 模板很狡猾。为了纠正这个问题，我必须确保所有数据透视表都使用命名范围来引用数据（单击数据透视表上的任意位置，然后单击顶部功能区中的“公式”选项卡，然后单击“名称管理器”）源然后按照我上面帖子中的链接中的建议使用偏移计算（启用动态范围）。

=OFFSET(数据源!$A$1,0,0,COUNTA(数据源!$A:$A),COUNTA(数据源!$1:$1))

其中 DataSource = 包含数据的工作表的名称

最后，我设置了数据透视表以在打开时刷新数据（右键单击数据透视表，转到数据选项卡并勾选“打开时刷新”选项）

有点痛苦的是，当我打开生成的文档时，它处于“受保护模式”，因此数据 + calcs 不会刷新，但如果我只是单击“启用编辑”，它会全部更新并恢复正常服务，快乐的日子！

c# - 如何使用 C# 更新 Excel 数据透视表数据

问题描述

解决方案

推荐阅读