Click here to Skip to main content
15,867,453 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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:
C#
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
C#
pivotCache.Recordset = h.RecordSet;

but I am struggling to find a better solution.

What I have tried:

Moving pivot cache setting outside the QueueAsMacro:
C#
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) );

seems to resolve the memory issue but sometimes Excel crashes for no obvious reason.
Posted
Comments
Helder Antunes 17-Jun-16 13:43pm    
Adding a GC collection on the QueueAsMacro delegate seems to have fixed my problem

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900