My hybrid Excel Interop/EPPlus app for generating Excel spreadsheets runs progressively slower with each execution.
In trying to ferret out the reason for this, I downloaded ANTS Memory Profiler and ran the app.
ANTS MP tells me in its "Assemblies loaded into CLR" detail that there are 2 Dynamically generated assemblies. One is in the "InitializeExcelObjects()" method, which makes sense, because it creates an Excel.Application and related objects. But the second method that (supposedly?) load assemblies is this:
private void WriteHeader(String shortName)
{
const int LOGO_FIRST_ROW = 1;
const int LOGO_LAST_ROW = 5;
var rowRngUnitName = _xlSheet.Range[_xlSheet.Cells[UNIT_NAME_ROW, 1], _xlSheet.Cells[UNIT_NAME_ROW, 3]];
rowRngUnitName.Merge(Type.Missing);
rowRngUnitName.Font.Bold = true;
rowRngUnitName.Font.Size = UNIT_NAME_FONT_SIZE;
rowRngUnitName.Value2 = shortName;
var rowRngRptTitle = _xlSheet.Range[_xlSheet.Cells[REPORT_TITLE_ROW, 1], _xlSheet.Cells[REPORT_TITLE_ROW, 5]];
rowRngRptTitle.Merge(Type.Missing);
rowRngRptTitle.Font.Size = REPORT_TITLE_ROW_FONT_SIZE;
rowRngRptTitle.Font.Bold = true;
rowRngRptTitle.Value2 = ProduceUsageByMonthLabel;
var rowRngRptAdvisory = _xlSheet.Range[_xlSheet.Cells[REPORT_ADVISORY_ROW, 1], _xlSheet.Cells[REPORT_ADVISORY_ROW, 4]];
rowRngRptAdvisory.Merge(Type.Missing);
rowRngRptAdvisory.Font.Size = REPORT_ADVISORY_ROW_FONT_SIZE;
rowRngRptAdvisory.Value2 = ProductsHiddenAdvisoryLabel;
// col 5A (ANNUAL_CONTRACT_PRODUCTS_ROW)
var rowRngContractProducts = _xlSheet.Range[
_xlSheet.Cells[ANNUAL_CONTRACT_PRODUCTS_ROW, ITEMDESC_COL],
_xlSheet.Cells[ANNUAL_CONTRACT_PRODUCTS_ROW, ITEMDESC_COL]];
rowRngContractProducts.Merge(Type.Missing);
rowRngContractProducts.Font.Size = ANNUAL_CONTRACT_PRODUCTS_LEGEND_FONT_SIZE;
rowRngContractProducts.Font.Bold = true;
rowRngContractProducts.HorizontalAlignment = XlHAlign.xlHAlignCenter;
rowRngContractProducts.VerticalAlignment = XlVAlign.xlVAlignCenter;
rowRngContractProducts.Value2 = AnnualContractProductsLabel;
Borders border = rowRngContractProducts.Borders;
border.Weight = XlBorderWeight.xlThin;
border.LineStyle = XlLineStyle.xlContinuous;
// Logo
var logoRange = _xlSheet.Range[
_xlSheet.Cells[LOGO_FIRST_ROW, _grandTotalsColumn], _xlSheet.Cells[LOGO_LAST_ROW, _grandTotalsColumn + 1]];
ReportRunnerConstsAndUtils.PlacePicture(_logo, logoRange);
}
...and the implicated is this one:
var rowRngUnitName = _xlSheet.Range[_xlSheet.Cells[UNIT_NAME_ROW, 1], _xlSheet.Cells[UNIT_NAME_ROW, 3]];
...which does nothing out of the ordinary (ranges are assigned all over the place in the code, so why is this one considered different than the others and a spawner of an assembly)?
Is it because this is the first time that a range is assigned? Is some assembly dynamically loaded when that is done (once and once only)?
Here is the code that calls the methods in question:
public bool GenerateProduceUsageRpt()
{
const int SLEEPY_TIME = 10000;
const int MONTH_SPLIT_CUTOFF = 7;
try
{
InitializeExcelObjects();
_monthsInReport = ReportRunnerConstsAndUtils.GetMonthsInReport(_monthBegin, _monthEnd, _beginYearStr, _endYearStr);
_grandTotalsColumn = _monthsInReport + 3;
_grandTotalsColumnPivotTable = _grandTotalsColumn - 1;
_lastMonthColumn = _grandTotalsColumn - 1;
InitializeYearAndMonthVals();
try
{
System.Windows.Forms.Application.DoEvents();
if (_xlSheet != null)
{
_xlSheet.Name = ProduceUsageByMonthSheetName;
_xlPivotDataSheet.Name = ProduceUsageByMonthPivotTableSheetName;
_xlPivotTableSheet.Name = "PivotTable";
WriteHeader(_unit);
WriteColumnHeadings();
. . .
Is this to be expected (that as assembly is dynamically loaded at this point), or might this have something to do with the gradually worsening performance that is being experienced?
UPDATE
Based on the comment by Glenn Ferrie, this method might be of interest:
// From Jürgen Tschandl at http://stackoverflow.com/questions/7413107/excel-image-in-a-cell
internal static void PlacePicture(Image picture, Range destination)
{
Worksheet ws = destination.Worksheet;
Clipboard.SetImage(picture);
ws.Paste(destination, false);
Pictures p = ws.Pictures(Missing.Value) as Pictures;
if (p != null)
{
Picture pic = p.Item(p.Count) as Picture;
ScalePicture(pic, (double)destination.Width, (double)destination.Height);
}
}