/// <summary>
/// Appends the sheet to excel stream.
/// </summary>
/// <param name="inputStream">The input stream.</param>
/// <param name="workbookPath">The workbook path.</param>
/// <param name="worksheetName">Name of the worksheet.</param>
private static void InsertAppendSheetToExcelStream(InsertAppend insertAppend, MemoryStream inputStream, string workbookPath, string worksheetName)
{
if (inputStream == null)
{
throw new ArgumentNullException("inputStream");
}
if (string.IsNullOrEmpty(workbookPath))
{
throw new ArgumentNullException("workbookPath");
}
if (string.IsNullOrEmpty(worksheetName))
{
throw new ArgumentNullException("worksheetName");
}
var fi = new FileInfo(workbookPath);
if (!fi.Exists)
{
throw new ExportException(string.Format("Supplied workbook path does not exist <{0}>", workbookPath));
}
// start the append process by opening the inputstream as a autosave spreadsheet document
using (SpreadsheetDocument target = SpreadsheetDocument.Open(inputStream, true, new OpenSettings {
AutoSave = true
}))
{
// next open the workbook containing the sheet to append
using (var source = SpreadsheetDocument.Open(workbookPath, false))
{
// get sheet from the source
var wsp = source.GetWorksheetPart(worksheetName);
if (wsp == null)
{
throw new ExportException(string.Format("Unable to clone worksheet <{0}> from workbook path <{1}>", worksheetName, workbookPath));
}
// and insert a clone into the target
if (insertAppend == InsertAppend.Append)
{
var clone = target.WorkbookPart.InsertClonedWorksheetPart(wsp, SheetStateValues.Visible, worksheetName);
// call the resource and style merger, this merges the index driven styles
// between source and target stream
ExportGenerator.MergeResourcesAndStyles(clone, source, target);
}
else
{
var clone = target.WorkbookPart.InsertClonedWorksheetPartAtFirst(wsp, SheetStateValues.Visible, worksheetName);
// call the resource and style merger, this merges the index driven styles
// between source and target stream
ExportGenerator.MergeResourcesAndStyles(clone, source, target);
}
}
}
}
/// <summary>
/// Inserts the append cloned worksheet part.
/// </summary>
/// <param name="action">The action.</param>
/// <param name="targetWorkbookPart">The target workbook part.</param>
/// <param name="sourceWorksheetPart">The source worksheet part.</param>
/// <param name="sourceState">State of the source.</param>
/// <param name="targetSheetName">Name of the target sheet.</param>
/// <returns></returns>
/// <exception cref="ArgumentNullException">
/// workbookPart
/// or
/// sourceWorksheetPart
/// </exception>
private static WorksheetPart InsertAppendClonedWorksheetPart(this InsertAppend action, WorkbookPart targetWorkbookPart, WorksheetPart sourceWorksheetPart, EnumValue <SheetStateValues> sourceState, string targetSheetName)
{
if (targetWorkbookPart == null)
{
throw new ArgumentNullException("workbookPart");
}
if (sourceWorksheetPart == null)
{
throw new ArgumentNullException("sourceWorksheetPart");
}
// take advantage of AddPart for deep cloning
SpreadsheetDocument tempSheet = SpreadsheetDocument.Create(new MemoryStream(), SpreadsheetDocumentType.Workbook);
WorkbookPart tempWorkbookPart = tempSheet.AddWorkbookPart();
WorksheetPart tempWorksheetPart = tempWorkbookPart.AddPart <WorksheetPart>(sourceWorksheetPart);
// add cloned sheet and all associated parts to workbook
WorksheetPart clonedSheet = targetWorkbookPart.AddPart <WorksheetPart>(tempWorksheetPart);
// add new sheet to main workbook part
Sheets sheets = targetWorkbookPart.Workbook.GetFirstChild <Sheets>();
var nextSheetId = sheets.GetNextSheetId();
targetSheetName = sheets.GetSafeSheetName(targetSheetName ?? "Sheet");
var newSheet = new DocumentFormat.OpenXml.Spreadsheet.Sheet
{
Id = targetWorkbookPart.GetIdOfPart(clonedSheet),
// make sure the name is set to the provided name
Name = targetSheetName,
SheetId = nextSheetId,
State = sourceState
};
if (action == InsertAppend.Insert)
{
sheets.InsertAt(newSheet, 0);
}
else
{
sheets.Append(newSheet);
}
return(clonedSheet);
}
