2

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);
    }
}
Mistalis
  • 17,793
  • 13
  • 73
  • 97
B. Clay Shannon-B. Crow Raven
  • 8,547
  • 144
  • 472
  • 862
  • 1
    what happens here: `ReportRunnerConstsAndUtils.PlacePicture(_logo, logoRange);` -- I have experienced an assembly being Dynamically loaded / generated with certain serialization operations. – Glenn Ferrie Nov 07 '16 at 18:03
  • 1
    EPPlus doesn't use Interop or generate any dynamic assemblies, it generates an XLSX file directly. That's why it exists in the first place. Why are you mixing up Excel Interop with EPPlus? From your description it seems that you don't use EPPlus at all, and all your code uses Interop – Panagiotis Kanavos Jan 17 '17 at 09:37
  • There are some things EPPlus doesn't do well, such as PivotTables; so, I've pretty much switched now to Aspose Cells. I refactored from Excel Interop, and now in the process of refactoring to Aspose. – B. Clay Shannon-B. Crow Raven Jan 17 '17 at 15:43

0 Answers0