/// <inheritdoc/>
public Excel.IWorksheet Copy(Excel.IWorksheet sourceWorksheet, Excel.IWorksheet beforeWorksheet)
{
var source = (Worksheet)sourceWorksheet;
var before = (Worksheet)beforeWorksheet;
var index = before.InteropWorksheet.Index;
source.InteropWorksheet.Copy(before.InteropWorksheet);
var copied = (InteropWorksheet)this.InteropWorkbook.Sheets[index];
var copiedWorksheet = this.New(copied);
copied.Visible = InteropXlSheetVisibility.xlSheetVisible;
return(copiedWorksheet);
}
/// <summary>
/// When index = 0 => add new worksheet before the active worksheet
/// When index <= #sheets >= add new worksheet before the index worksheet
/// When index > #sheets => add new worksheet after the last worksheet
/// When before != null => add new worksheet before that worksheet
/// When after != null => add new worksheet after that worksheet
/// When all params are null => add new worksheet after the last worksheet
/// </summary>
/// <param name="index"></param>
/// <param name="before"></param>
/// <param name="after"></param>
/// <returns></returns>
public Excel.IWorksheet AddWorksheet(int?index, Excel.IWorksheet before = null, Excel.IWorksheet after = null)
{
InteropWorksheet interopWorksheet;
try
{
this.AddIn.Application.WorkbookNewSheet -= this.ApplicationOnWorkbookNewSheet;
if (index.HasValue && index.Value == 0)
{
interopWorksheet = (InteropWorksheet)this.InteropWorkbook.Sheets.Add();
}
else
{
if (before != null)
{
interopWorksheet = (InteropWorksheet)this.InteropWorkbook.Sheets.Add(((Worksheet)before).InteropWorksheet, Missing.Value);
}
else if (after != null)
{
interopWorksheet = (InteropWorksheet)this.InteropWorkbook.Sheets.Add(Missing.Value, ((Worksheet)after).InteropWorksheet);
}
else
{
if (!index.HasValue || index > this.InteropWorkbook.Sheets.Count - 1)
{
index = this.InteropWorkbook.Sheets.Count;
var insertAfter = this.worksheetByInteropWorksheet.Keys.FirstOrDefault(v => v.Index == index);
interopWorksheet = (InteropWorksheet)this.InteropWorkbook.Sheets.Add(Missing.Value, insertAfter);
}
else
{
var insertBefore = this.worksheetByInteropWorksheet.Keys.FirstOrDefault(v => v.Index == index);
interopWorksheet = (InteropWorksheet)this.InteropWorkbook.Sheets.Add(insertBefore);
}
}
}
return(this.TryAdd(interopWorksheet));
}
finally
{
this.AddIn.Application.WorkbookNewSheet += this.ApplicationOnWorkbookNewSheet;
}
}
