ArrayResizer.dna

<DnaLibrary RuntimeVersion="v4.0" Language="CS">
<![CDATA[
using System;
using System.Collections.Generic;
using ExcelDna.Integration;

namespace AsyncFunctions
{
    // This class defines a few test functions that can be used to explore the automatic array resizing.
	public static class ResizeTestFunctions
	{
        // Just returns an array of the given size
        public static object[,] MakeArray(int rows, int columns)
        {
            object[,] result = new object [rows, columns];
            for (int i = 0; i < rows; i++)
            {
                for (int j = 0; j < columns; j++)
                {
                    result[i,j] = i + j;
                }
            }
            
            return result;
        }

        public static double[,] MakeArrayDoubles(int rows, int columns)
        {
            double[,] result = new double[rows, columns];
            for (int i = 0; i < rows; i++)
            {
                for (int j = 0; j < columns; j++)
                {
                    result[i,j] = i + (j/1000.0);
                }
            }
            
            return result;
        }
        
        public static object MakeMixedArrayAndResize(int rows, int columns)
        {
            object[,] result = new object [rows, columns];
            for (int j = 0; j < columns; j++)
                {
                    result[0,j] = "Col " + j;
                }
            for (int i = 1; i < rows; i++)
            {
                for (int j = 0; j < columns; j++)
                {
                    result[i,j] = i + (j * 0.1);
                }
            }
            
            return ArrayResizer.Resize(result);
        }

        // Makes an array, but automatically resizes the result
        public static object MakeArrayAndResize(int rows, int columns, string unused, string unusedtoo)
        {
            object[,] result = MakeArray(rows, columns);            
            return ArrayResizer.Resize(result);
            
            // Can also call Resize via Excel - so if the Resize add-in is not part of this code, it should still work
            // (though calling direct is better for large arrays - it prevents extra marshaling).
            // return XlCall.Excel(XlCall.xlUDF, "Resize", result);
        }

        public static double[,] MakeArrayAndResizeDoubles(int rows, int columns)
        {
            double[,] result = MakeArrayDoubles(rows, columns);            
            return ArrayResizer.ResizeDoubles(result);
        }
    }

    public class ArrayResizer : XlCall
    {
        // This function will run in the UDF context.
        // Needs extra protection to allow multithreaded use.
        public static object Resize(object[,] array)
        {
            var caller = Excel(xlfCaller) as ExcelReference;
            if (caller == null) 
                return array;

            int rows = array.GetLength(0);
            int columns = array.GetLength(1);
            
            if (rows == 0 || columns == 0) 
                return array;

            if ((caller.RowLast - caller.RowFirst + 1 == rows) &&
                (caller.ColumnLast - caller.ColumnFirst + 1 == columns))
            {
                // Size is already OK - just return result
                return array;
            }            

            var rowLast = caller.RowFirst + rows - 1;
            var columnLast = caller.ColumnFirst + columns - 1;

            // Check for the sheet limits
            if (rowLast > ExcelDnaUtil.ExcelLimits.MaxRows - 1 ||
                columnLast > ExcelDnaUtil.ExcelLimits.MaxColumns - 1)
            {
                // Can't resize - goes beyond the end of the sheet - just return #VALUE
                // (Can't give message here, or change cells)
                return ExcelError.ExcelErrorValue;
            }

            // TODO: Add some kind of guard for ever-changing result?
            ExcelAsyncUtil.QueueAsMacro(() =>
            {
                // Create a reference of the right size
                var target = new ExcelReference(caller.RowFirst, rowLast, caller.ColumnFirst, columnLast, caller.SheetId);
                DoResize(target); // Will trigger a recalc by writing formula
            });
            // Return what we have - to prevent flashing #N/A
            return array;
        }

        public static double[,] ResizeDoubles(double[,] array)
        {
            var caller = Excel(xlfCaller) as ExcelReference;
            if (caller == null) 
                return array;

            int rows = array.GetLength(0);
            int columns = array.GetLength(1);

            if (rows == 0 || columns == 0) 
                return array;
            
            if ((caller.RowLast - caller.RowFirst + 1 == rows) &&
                (caller.ColumnLast - caller.ColumnFirst + 1 == columns))
            {
                // Size is already OK - just return result
                return array;
            }
            
            var rowLast = caller.RowFirst + rows - 1;
            var columnLast = caller.ColumnFirst + columns - 1;

            if (rowLast > ExcelDnaUtil.ExcelLimits.MaxRows - 1 ||
                columnLast > ExcelDnaUtil.ExcelLimits.MaxColumns - 1)
            {
                // Can't resize - goes beyond the end of the sheet - just return null (for #NUM!)
                // (Can't give message here, or change cells)
                return null;
            }

            // TODO: Add guard for ever-changing result?
            ExcelAsyncUtil.QueueAsMacro(() =>
            {
                // Create a reference of the right size
                var target = new ExcelReference(caller.RowFirst, rowLast, caller.ColumnFirst, columnLast, caller.SheetId);
                DoResize(target); // Will trigger a recalc by writing formula
            });
            // Return what we have - to prevent flashing #N/A
            return array;
        }
        
        static void DoResize(ExcelReference target)
        {
            // Get the current state for reset later
            using (new ExcelEchoOffHelper())
            using (new ExcelCalculationManualHelper())
            using (new ExcelSelectionHelper(target))
            {
                var app = (Microsoft.Office.Interop.Excel.Application)ExcelDnaUtil.Application;
                var firstCell = app.ActiveCell;

                var formula = firstCell.Formula;

                // Clear existing contents
                if (firstCell.HasArray)
                {
                    firstCell.CurrentArray.ClearContents();
                }
                else
                {
                    firstCell.ClearContents();
                }

                // Set formula array in modified range
                app.Selection.FormulaArray = formula;
                app.Selection.Calculate();
            }
        }
    }
   
    // RIIA-style helpers to deal with Excel selections    
    // Don't use if you agree with Eric Lippert here: http://stackoverflow.com/a/1757344/44264
    public class ExcelEchoOffHelper : XlCall, IDisposable
    {
        object oldEcho;

        public ExcelEchoOffHelper()
        {
            oldEcho = Excel(xlfGetWorkspace, 40);
            Excel(xlcEcho, false);
        }
        
        public void Dispose()
        {
            Excel(xlcEcho, oldEcho);
        }
    }
    
    public class ExcelCalculationManualHelper : XlCall, IDisposable
    {
        object oldCalculationMode;

        public ExcelCalculationManualHelper()
        {
            oldCalculationMode = Excel(xlfGetDocument, 14);
            Excel(xlcOptionsCalculation, 3);
        }
        
        public void Dispose()
        {
            Excel(xlcOptionsCalculation, oldCalculationMode);
        }
    }

    // Select an ExcelReference (perhaps on another sheet) allowing changes to be made there.
    // On clean-up, resets all the selections and the active sheet.
    // Should not be used if the work you are going to do will switch sheets, amke new sheets etc.
    public class ExcelSelectionHelper : XlCall, IDisposable
    {
        object oldSelectionOnActiveSheet;
        object oldActiveCellOnActiveSheet;

        object oldSelectionOnRefSheet;
        object oldActiveCellOnRefSheet;

        public ExcelSelectionHelper(ExcelReference refToSelect)
        {
            // Remember old selection state on the active sheet
            oldSelectionOnActiveSheet = Excel(xlfSelection);
            oldActiveCellOnActiveSheet = Excel(xlfActiveCell);

            // Switch to the sheet we want to select
            string refSheet = (string)Excel(xlSheetNm, refToSelect);
            Excel(xlcWorkbookSelect, new object[] { refSheet });

            // record selection and active cell on the sheet we want to select
            oldSelectionOnRefSheet = Excel(xlfSelection);
            oldActiveCellOnRefSheet = Excel(xlfActiveCell);

            // make the selection
            Excel(xlcFormulaGoto, refToSelect);
        }

        public void Dispose()
        {
            // Reset the selection on the target sheet
            Excel(xlcSelect, oldSelectionOnRefSheet, oldActiveCellOnRefSheet);

            // Reset the sheet originally selected
            string oldActiveSheet = (string)Excel(xlSheetNm, oldSelectionOnActiveSheet);
            Excel(xlcWorkbookSelect, new object[] { oldActiveSheet });

            // Reset the selection in the active sheet (some bugs make this change sometimes too)
            Excel(xlcSelect, oldSelectionOnActiveSheet, oldActiveCellOnActiveSheet);
        }
    }
    
}
]]>
</DnaLibrary>