/// <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;
}
}
}