public static void NoCalcAndUpdating(this Action action) { xlCalculation calcMode = Calcuation; bool updating = ScreenUpdating; try { if (updating) { ScreenUpdating = false; } if (calcMode != xlCalculation.Manual) { Calcuation = xlCalculation.Manual; } action.Invoke(); } finally { if (updating) { XLApp.ScreenUpdating = true; } if (calcMode != xlCalculation.Manual) { XLApp.Calcuation = calcMode; } } }
/// <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; } } }
public static void NoCalcAndUpdating(this Action action) { xlCalculation calcMode = Calcuation; bool updating = ScreenUpdating; try { if (updating) ScreenUpdating = false; if (calcMode != xlCalculation.Manual) Calcuation = xlCalculation.Manual; action.Invoke(); } finally { if (updating) XLApp.ScreenUpdating = true; if (calcMode != xlCalculation.Manual) XLApp.Calcuation = calcMode; } }