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();
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;
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) );
seems to resolve the memory issue but sometimes Excel crashes for no obvious reason.