private void ReplaceWorkSheetparts(SpreadsheetDocument target, ExcelMergeData mergeData)
{
//check make sure relationship id won`t repeat in differnet documents after adding sheets
var worksheetPartList = mergeData.WorksheetPartList.ToList();
foreach (KeyValuePair <string, WorksheetPart> element in worksheetPartList)
{
var addedWorksheetPart = target.WorkbookPart.AddPart(element.Value);
mergeData.WorksheetPartList[element.Key] = addedWorksheetPart;
}
}
private void FixSheetsIds(SpreadsheetDocument target, ExcelMergeData mergeData)
{
uint sheetId = 1;
foreach (var item in mergeData.Sheets)
{
item.Id = target.WorkbookPart.GetIdOfPart(mergeData.WorksheetPartList[item.Id]);
//item.SheetId.Value = sheetId;
sheetId++;
}
}
public SpreadsheetDocument MergeWorkSheets(SpreadsheetDocument target, SpreadsheetDocument source)
{
ExcelMergeData mergeData = new ExcelMergeData();
mergeData.SetDocumentData(source);
var mergedSharedStringItemsResult = GetMergedSharedStringItems(target, source);
target.WorkbookPart.SharedStringTablePart.SharedStringTable.RemoveAllChildren();
target.WorkbookPart.SharedStringTablePart.SharedStringTable.Append(mergedSharedStringItemsResult.SharedStringItems);
FixSharedStringReference(mergeData.WorksheetPartList, mergedSharedStringItemsResult.SharedStringIndexes);
DeleteSheetsAndReferencedWorksheetParts(target, mergeData);
ReplaceWorkSheetparts(target, mergeData);
FixSheetsIds(target, mergeData);
GetMergedCalculationChainPart();
target.WorkbookPart.Workbook.Sheets.Append(mergeData.Sheets);
target.Save();
return(target);
}
private SpreadsheetDocument DeleteSheetsAndReferencedWorksheetParts(SpreadsheetDocument target, ExcelMergeData mergeData)
{
//delete all already existing sheets and related worksheetparts
foreach (Sheet element in mergeData.Sheets)
{
var sheetId = element.Id;
var sheet = target.WorkbookPart.Workbook.Descendants <Sheet>()
.FirstOrDefault(s => s.Id == sheetId);
if (sheet != null)
{
sheet.Remove();
}
try
{
var worksheetPart = (WorksheetPart)(target.WorkbookPart.GetPartById(sheetId));
target.WorkbookPart.DeletePart(worksheetPart);
}
catch (ArgumentOutOfRangeException ex)
{
continue;
}
}
return(target);
}
