如何从C＃代码更新数据透视表数据 [英] How do I update pivot table data from C# code

问题描述

我想使用来自C＃库中客户端函数的一些自定义数据，在Excel中创建然后按需更新一些数据透视表报表。为实现这一目标，我实施了以下方法：

I would like to create and then update on demand some pivot table reports in Excel using some custom data coming from client functions in my C# library. To achieve that I have implemented the following methods:

using System;
using System.Linq;
using System.Runtime.InteropServices;
using ADODB;
using ExcelDna.Integration;
using Microsoft.Office.Interop.Excel;

namespace MyNamespace
{
    public static class PivotTableFunctions
    {
        [ExcelFunction(Description = "Creates a pivot table from a RecordSet ",
            Category = Categories.Toolkit, IsMacroType = true, IsHidden = true, IsThreadSafe = false), UsedImplicitly]
        public static object AddPivotTable(
            [ExcelArgument(Description = "The record set of data needed to update the pivot table")] object recordSet,
            [ExcelArgument(Description = "Pivot Table Name")] string pivotTableName)
        {
            try
            {
                var recordset = XlConvert.ToSingle<DataTable>(recordSet);

                var app = (Application)ExcelDnaUtil.Application;
                var workbook = app.ActiveWorkbook;

                var newWorksheet = (Worksheet)workbook.Worksheets.Add(After: workbook.Worksheets[workbook.Worksheets.Count]);
                newWorksheet.Name = pivotTableName;

                var cache = workbook.PivotCaches().Create(XlPivotTableSourceType.xlExternal, Version: XlPivotTableVersionList.xlPivotTableVersion14);
                cache.Recordset = recordset.AsADODBRecordset();
                cache.CreatePivotTable(newWorksheet.Cells[2, 2], pivotTableName, DefaultVersion: XlPivotTableVersionList.xlPivotTableVersion14);
                cache.Refresh();

                workbook.ShowPivotTableFieldList = true;
                return $"{pivotTableName} pivot table has been added";
            }
            catch (Exception e)
            {
                return e.Message;
            }
        }

        [ExcelFunction(Description = "Creates or updates a pivot table from a RecordSet",
            Category = Categories.Database), UsedImplicitly]
        public static object UpdatePivotTable(
            [ExcelArgument(Description = "The recordset of data needed to update the pivot table")] object recordSet,
            [ExcelArgument(Description = "Pivot Table Name")] string pivotTableName,
            [ExcelArgument(Description = "Truncates recordset to only contain a fixed number of rows")] object rows)
        {
            var truncate = XlConvert.ToSingle(rows, int.MaxValue);
            var recordset = GetRecordset(recordSet, truncate);
            var adodbRecordset = recordset.AsADODBRecordset(); // this is an ADO.Recordset
            var pivotTable = GetPivotTable(pivotTableName);
            if (pivotTable == null)
                return $"Failed to update {pivotTableName} : no pivot table with this name was found";

            ExcelAsyncUtil.QueueAsMacro(handle =>
            {
                try
                {
                    var h = (Handle) handle;
                    var pivotCache = h.PivotTable.PivotCache();
                    pivotCache.Recordset = h.RecordSet;
                    h.PivotTable.RefreshTable();
                }
                catch (Exception e)
                {
                    _logger.Error($"Failed to update {pivotTableName} : {e.Message}");
                }
            }, new Handle(pivotTable, adodbRecordset) );

            return $"{pivotTableName} has been updated";
        }

        private static PivotTable GetPivotTable(string pivotTableName)
        {
            var app = (Application)ExcelDnaUtil.Application;
            var workbook = app.ActiveWorkbook;
            var worksheets = workbook.Worksheets;

            foreach (Worksheet worksheet in worksheets)
            {
                var pivotTables = worksheet.PivotTables();
                foreach (PivotTable pivotTable in pivotTables)
                {
                    if (pivotTable.Name.Equals(pivotTableName, StringComparison.CurrentCultureIgnoreCase))
                        return pivotTable;
                }
            }
            return null;
        }

        private static DataTable GetRecordset(object recordSet, int truncate)
        {
            var recordset = XlConvert.ToSingle<DataTable>(recordSet);

            return recordset.RowCount > truncate ? recordset.Copy(Enumerable.Empty<string>(), truncate) : recordset;
        }

        private class Handle
        {
            public Handle(PivotTable pivotTable, Recordset recordSet)
            {
                PivotTable = pivotTable;
                RecordSet = recordSet;
            }

            public PivotTable PivotTable { get; }
            public Recordset RecordSet { get; }

        }
    }
}

代码似乎工作正常但是每次运行更新功能时，我的内存配置文件都会增加。我将泄漏范围缩小到了线上

The code seems to work just fine however every time I run the update function my memory profile increases. I narrowed down the leak to the line

pivotCache.Recordset = h.RecordSet;

但我是努力寻找更好的解决方案。



我尝试过：



在QueueAsMacro之外移动数据透视缓存设置：

but I am struggling to find a better solution.

What I have tried:

Moving pivot cache setting outside the QueueAsMacro:

var pivotCache = pivotTable.PivotCache();
pivotCache.Recordset = adodbRecordset;

ExcelAsyncUtil.QueueAsMacro(handle =>
{
    try
    {
        var h = (Handle) handle;
        h.PivotTable.RefreshTable();
    }
    catch (Exception e)
    {
        _logger.Error($"Failed to update {pivotTableName} : {e.Message}");
    }
}, new Handle(pivotTable, adodbRecordset) );

似乎解决了内存问题，但有时Excel崩溃没有明显的原因。

seems to resolve the memory issue but sometimes Excel crashes for no obvious reason.

推荐答案

{pivotTableName}数据透视表已添加;
}
catch （例外e）
{
return e.Message;
}
}

[ExcelFunction（Description = 创建或从RecordSet，
Category = Categories.Database）更新数据透视表，UsedImplicitly]
public static object UpdatePivotTable（
[ExcelArgument（Description = 更新数据透视表所需的数据记录集）] object recordSet，
[ ExcelArgument（Description = 数据透视表名称）] string pivotTableName，
[ExcelArgument（Description = ）将记录集截断为仅包含固定数量的行）] object 行）
{
var truncate = XlConvert。 ToSingle（rows， int .MaxValue）;
var recordset = GetRecordset（recordSet，truncate）;
var adodbRecordset = recordset.AsADODBRecordset（）; // 这是一个ADO.Recordset
var pivotTable = GetPivotTable（pivotTableName）;
if （pivotTable == null ）
返回

"{pivotTableName} pivot table has been added"; } catch (Exception e) { return e.Message; } } [ExcelFunction(Description = "Creates or updates a pivot table from a RecordSet", Category = Categories.Database), UsedImplicitly] public static object UpdatePivotTable( [ExcelArgument(Description = "The recordset of data needed to update the pivot table")] object recordSet, [ExcelArgument(Description = "Pivot Table Name")] string pivotTableName, [ExcelArgument(Description = "Truncates recordset to only contain a fixed number of rows")] object rows) { var truncate = XlConvert.ToSingle(rows, int.MaxValue); var recordset = GetRecordset(recordSet, truncate); var adodbRecordset = recordset.AsADODBRecordset(); // this is an ADO.Recordset var pivotTable = GetPivotTable(pivotTableName); if (pivotTable == null) return

无法更新{pivotTableName} ：找不到具有此名称的数据透视表;

ExcelAsyncUtil.QueueAsMacro（handle = >
{
try
{
var h =（句柄）句柄;
var pivotCache = h.PivotTable.PivotCache（）;
pivotCache.Recordset = h.RecordSet;
h.PivotTable.RefreshTable（）;
}
catch （例外e）
{
_logger.Error（

"Failed to update {pivotTableName} : no pivot table with this name was found"; ExcelAsyncUtil.QueueAsMacro(handle => { try { var h = (Handle) handle; var pivotCache = h.PivotTable.PivotCache(); pivotCache.Recordset = h.RecordSet; h.PivotTable.RefreshTable(); } catch (Exception e) { _logger.Error(

无法更新{pivotTableName}：{e.Message}）;
}
}， new 句柄（pivotTable，adodbRecordset））;

return

"Failed to update {pivotTableName} : {e.Message}"); } }, new Handle(pivotTable, adodbRecordset) ); return

这篇关于如何从C＃代码更新数据透视表数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！