Beispiel #1
0
        /// <summary>
        /// copy a variant matrix into an excel named range and adjust the size of the named range
        /// 1.) will copy the formatting of the first row when adding new rows
        /// 2.) will copy the formatting of the first row after the named range when removing rows
        /// </summary>
        /// <param name="vt">should be object[,] or simple data type</param>
        /// <param name="outRange"></param>
        /// <param name="localName">This is the local name of the output range (always ex header)</param>
        /// <param name="header">if there is a header the named range will start one cell below</param>
        /// <param name="ignoreFirstCell">will not fill the first cell; header will be inside the range if true</param>
        public static void Fill(this ExcelReference outRange, object vt, string localName = null, bool header = false, bool ignoreFirstCell = false)
        {
            var _vt = vt as object[, ];

            if (_vt == null)
            {
                _vt = new object[, ] {
                    { vt }
                }
            }
            ;

            int name_offset = (header && ignoreFirstCell) ? 1 : 0;
            int origin_offset = ((header && !ignoreFirstCell) ? -1 : 0);
            int header_offset = (header) ? -1 : 0;
            int n = _vt.GetLength(0), k = _vt.GetLength(1);
            int m = outRange.RowLast - outRange.RowFirst + 1;

            //formatting
            bool localRange = !string.IsNullOrEmpty(localName);
            bool format     = true;

            ExcelReference formatRange = null, newRange = null;

            if (m == 1 && localRange)
            {
                formatRange = Name.GetRange(string.Format("'{0}'!{1}", outRange.SheetRef(), localName));
                if (formatRange == null)
                {
                    format = false;
                }
                else
                {
                    m = formatRange.RowLast - formatRange.RowFirst + 1;
                }
            }
            else if (m == 1)
            {
                format = false;
            }


            bool addRows = n + header_offset > m && format;
            bool removeRows = n + header_offset < m && format;


            int x0 = outRange.RowFirst + origin_offset, y0 = outRange.ColumnFirst; //output origin
            int x1 = outRange.RowFirst + name_offset, y1 = outRange.ColumnFirst;   //name origin

            bool          updating = XLApp.ScreenUpdating;
            xlCalculation calcMode = XLApp.Calcuation;

            if (updating)
            {
                XLApp.ScreenUpdating = false;
            }

            try
            {
                var fillRange = new ExcelReference(x0, x0 + n - 1, y0, y0 + k - 1, outRange.SheetId);

                if (addRows)
                {
                    formatRange = new ExcelReference(x1, x1, y1, y1 + k - 1, outRange.SheetId); //first row
                    newRange    = new ExcelReference(x1, x1 + n + header_offset - 1, y1, y1 + k - 1, outRange.SheetId);
                }
                if (removeRows)
                {
                    formatRange = new ExcelReference(x1 + m, x1 + m, y1, y1 + k - 1, outRange.SheetId); //last row + 1
                    newRange    = new ExcelReference(x1 + n + header_offset, x1 + m - 1, y1, y1 + k - 1, outRange.SheetId);
                    newRange.ClearContents();
                }

                //set the range except the first cell
                if (ignoreFirstCell && n > 1)
                {
                    //first row
                    if (k > 1)
                    {
                        object[,] first = new object[1, k - 1];
                        for (int i = 0; i < k - 1; i++)
                        {
                            first[0, i] = _vt[0, i + 1];
                        }
                        fillRange.Offset(0, 1).Resize(1, k - 1).SetValue(first);
                    }
                    //all other rows
                    object[,] rest = new object[n - 1, k];
                    for (int i = 1; i < n; i++)
                    {
                        for (int j = 0; j < k; j++)
                        {
                            rest[i - 1, j] = _vt[i, j];
                        }
                    }
                    fillRange.Offset(1, 0).Resize(n - 1, k).SetValue(rest);
                }
                else if (!ignoreFirstCell)
                {
                    fillRange.SetValue(_vt);
                }


                //set name
                if (localRange)
                {
                    Action action = () =>
                    {
                        string    sheetref = (string)XlCall.Excel(XlCall.xlSheetNm, outRange);
                        Worksheet sheet    = new Worksheet(sheetref);

                        //re-color
                        if (addRows || removeRows)
                        {
                            formatRange.Select();
                            XLApp.Copy();
                            newRange.Select();
                            XLApp.PasteSpecial(xlPasteType.PasteFormats);
                        }

                        string reference = string.Format("='{4}'!R{0}C{2}:R{1}C{3}", x1 + 1, x1 + n + header_offset, y1 + 1, y1 + k, sheetref);

                        //DEFINE.NAME(name_text, refers_to, macro_type, shortcut_text, hidden, category, local)
                        XlCall.Excel(XlCall.xlcDefineName, localName, reference, Type.Missing, Type.Missing, false, Type.Missing, true);
                    };
                    XLApp.ActionOnSelectedRange(fillRange, action);
                }
            }
            finally
            {
                if (updating)
                {
                    XLApp.ScreenUpdating = true;
                }
            }
        }