public WorksheetPart CopySheet(WorkbookPart workbookPart, WorksheetPart sourceSheetPart, string clonedSheetName)
{
//Il faut uiliser AddPart() qui est bien plus puissante que AddNewPart pour le clonage de WorksheetPart
SpreadsheetDocument tempSheet = SpreadsheetDocument.Create(new MemoryStream(), SpreadsheetDocumentType.Workbook);
WorkbookPart tempWorkbookPart = tempSheet.AddWorkbookPart();
WorksheetPart tempWorksheetPart = tempWorkbookPart.AddPart <WorksheetPart>(sourceSheetPart);
//Ajoute le clone et ses fils au workbook
WorksheetPart clonedSheet = workbookPart.AddPart <WorksheetPart>(tempWorksheetPart);
//Table definition parts are somewhat special and need unique ids...so let's make an id based on count
int numTableDefParts = sourceSheetPart.GetPartsCountOfType <TableDefinitionPart>();
int tableId = numTableDefParts;
//Clean up table definition parts (tables need unique ids)
if (numTableDefParts != 0)
{
foreach (TableDefinitionPart tableDefPart in clonedSheet.TableDefinitionParts)
{
tableId++;
tableDefPart.Table.Id = (uint)tableId;
tableDefPart.Table.DisplayName = "CopiedTable" + tableId;
tableDefPart.Table.Name = "CopiedTable" + tableId;
tableDefPart.Table.Save();
}
}
//There can only be one sheet that has focus
SheetViews views = clonedSheet.Worksheet.GetFirstChild <SheetViews>();
if (views != null)
{
views.Remove();
clonedSheet.Worksheet.Save();
}
//Add new sheet to main workbook part
Sheets sheets = workbookPart.Workbook.GetFirstChild <Sheets>();
Sheet copiedSheet = new Sheet();
copiedSheet.Name = clonedSheetName;
copiedSheet.Id = workbookPart.GetIdOfPart(clonedSheet);
copiedSheet.SheetId = (uint)sheets.ChildElements.Count() + 5;//+5 car le template possede deja 2 feuilles avec pour id 3 et 4
sheets.Append(copiedSheet);
//Save Changes
workbookPart.Workbook.Save();
return(clonedSheet);
}
public static void CloneWorkSheet(SpreadsheetDocument ss, String sheetNameToClone, String newSheetName)
{
WorkbookPart wbp = ss.WorkbookPart; // get reference to Workbook in SpreadSheet doc
// Get the source sheet to be copied
WorksheetPart source_wsp = GetWorkSheetPartByName(ss, sheetNameToClone);
//Take advantage of AddPart for deep cloning
SpreadsheetDocument temp_ss = SpreadsheetDocument.Create(new MemoryStream(), ss.DocumentType);
WorkbookPart temp_wbp = temp_ss.AddWorkbookPart();
WorksheetPart temp_wsp = temp_wbp.AddPart <WorksheetPart>(source_wsp);
//Add cloned sheet and all associated parts to workbook
WorksheetPart cloned_wsp = wbp.AddPart <WorksheetPart>(temp_wsp);
cloned_wsp.Worksheet.Save();
//Table definition parts are somewhat special and need unique ids...so let's make an id based on count
int numTableDefParts = source_wsp.GetPartsCountOfType <TableDefinitionPart>();
//Clean up table definition parts (tables need unique ids)
if (numTableDefParts != 0)
{
FixupTableParts(cloned_wsp, numTableDefParts);
}
//There should only be one sheet that has focus
CleanView(cloned_wsp);
Sheets sheets = wbp.Workbook.GetFirstChild <Sheets>();
// Get a unique ID for the new sheet (important)
uint sheetId = 1;
if (sheets.Elements <Sheet>().Count() > 0)
{
sheetId = sheets.Elements <Sheet>().Select(s => s.SheetId.Value).Max() + 1;
}
// Add new sheet to wbp's sheet collection
Sheet cloned_sheet = new Sheet();
cloned_sheet.Name = newSheetName;
cloned_sheet.Id = wbp.GetIdOfPart(cloned_wsp);
cloned_sheet.SheetId = sheetId;
sheets.Append(cloned_sheet);
// Save Changes
wbp.Workbook.Save();
}
//Create copy of template sheet
private static void CloneSheet(SpreadsheetDocument document, string templateWSheetName, string clonedWSheetName)
{
WorkbookPart workbookPart = document.WorkbookPart;
Sheet sheet = workbookPart.Workbook.Descendants <Sheet>().Where((s) => s.Name == templateWSheetName).FirstOrDefault();
WorksheetPart sourceSheetPart = (WorksheetPart)workbookPart.GetPartById(sheet.Id);
//Take advantage of AddPart for deep cloning
SpreadsheetDocument tempSheet = SpreadsheetDocument.Create(new MemoryStream(), document.DocumentType);
WorkbookPart tempWorkbookPart = tempSheet.AddWorkbookPart();
WorksheetPart tempWorksheetPart = tempWorkbookPart.AddPart <WorksheetPart>(sourceSheetPart);
//Add cloned sheet and all associated parts to workbook
WorksheetPart clonedSheet = workbookPart.AddPart <WorksheetPart>(tempWorksheetPart);
int numTableDefParts = sourceSheetPart.GetPartsCountOfType <TableDefinitionPart>();
int tableId = numTableDefParts;
//Clean up table definition parts (tables need unique ids)
if (numTableDefParts != 0)
{
FixupTableParts(clonedSheet, numTableDefParts, tableId);
}
//There can only be one sheet that has focus
SheetViews views = clonedSheet.Worksheet.GetFirstChild <SheetViews>();
if (views != null)
{
views.Remove();
clonedSheet.Worksheet.Save();
}
//Add new sheet to main workbook part
Sheets sheets = workbookPart.Workbook.GetFirstChild <Sheets>();
Sheet copiedSheet = new Sheet();
copiedSheet.Name = clonedWSheetName;
copiedSheet.Id = workbookPart.GetIdOfPart(clonedSheet);
copiedSheet.SheetId = (uint)sheets.ChildElements.Count + 1;
sheets.Append(copiedSheet);
//Save Changes
workbookPart.Workbook.Save();
}
public void InsertSheet(string toBeCopiedSheetName, string newSheetName)
{
WorkbookPart workbookPart = document.WorkbookPart;
Sheet sourceSheet = FindSheetByName(toBeCopiedSheetName);
WorksheetPart sourceSheetPart = (WorksheetPart)document.WorkbookPart.GetPartById(sourceSheet.Id);
//Take advantage of AddPart for deep cloning
MemoryStream tempStream = new MemoryStream();
try
{
SpreadsheetDocument tempDocument = SpreadsheetDocument.Create(tempStream, document.DocumentType);
try
{
WorkbookPart tempWorkbookPart = tempDocument.AddWorkbookPart();
WorksheetPart tempWorksheetPart = tempWorkbookPart.AddPart <WorksheetPart>(sourceSheetPart);
//Add cloned sheet and all associated parts to workbook
WorksheetPart clonedSheet = workbookPart.AddPart <WorksheetPart>(tempWorksheetPart);
//Table definition parts are somewhat special and need unique ids...so let's make an id based on count
int numTableDefParts = sourceSheetPart.GetPartsCountOfType <TableDefinitionPart>();
if (numTableDefParts != 0)
{
//Every table needs a unique id and name
int tableId = numTableDefParts;
foreach (TableDefinitionPart tableDefPart in clonedSheet.TableDefinitionParts)
{
tableId++;
tableDefPart.Table.Id = (uint)tableId;
tableDefPart.Table.DisplayName = newSheetName + tableDefPart.Table.DisplayName;
tableDefPart.Table.Name = newSheetName + tableDefPart.Table.Name;
tableDefPart.Table.Save();
}
}
//There can only be one sheet that has focus
SheetViews views = clonedSheet.Worksheet.GetFirstChild <SheetViews>();
if (views != null)
{
views.Remove();
clonedSheet.Worksheet.Save();
}
//Add new sheet to main workbook part
Sheets sheets = workbookPart.Workbook.GetFirstChild <Sheets>();
Sheet copiedSheet = new Sheet();
copiedSheet.Name = newSheetName;
copiedSheet.Id = workbookPart.GetIdOfPart(clonedSheet);
copiedSheet.SheetId = (uint)sheets.ChildElements.Count + 1;
sheets.InsertAfter(copiedSheet, currentSheet);
//Save the sheet
workbookPart.Workbook.Save();
//making the copied sheet the current sheet
currentSheetName = newSheetName;
currentSheet = copiedSheet;
currentWorksheet = clonedSheet.Worksheet;
currentSheetData = currentWorksheet.GetFirstChild <SheetData>();
}
finally
{
tempDocument.Dispose();
}
}
finally
{
tempStream.Dispose();
}
}
static public void CopySheet(string filename, string sheetName, string clonedSheetName)
{
//Open workbook
using (SpreadsheetDocument mySpreadsheet = SpreadsheetDocument.Open(filename, true))
{
WorkbookPart workbookPart = mySpreadsheet.WorkbookPart;
//Get the source sheet to be copied
WorksheetPart sourceSheetPart = GetWorkSheetPart(workbookPart, sheetName);
//Take advantage of AddPart for deep cloning
SpreadsheetDocument tempSheet = SpreadsheetDocument.Create(new MemoryStream(), mySpreadsheet.DocumentType);
WorkbookPart tempWorkbookPart = tempSheet.AddWorkbookPart();
WorksheetPart tempWorksheetPart = tempWorkbookPart.AddPart <WorksheetPart>(sourceSheetPart);
//Add cloned sheet and all associated parts to workbook
WorksheetPart clonedSheet = workbookPart.AddPart <WorksheetPart>(tempWorksheetPart);
//Table definition parts are somewhat special and need unique ids...so let's make an id based on count
int numTableDefParts = sourceSheetPart.GetPartsCountOfType <TableDefinitionPart>();
tableId = numTableDefParts;
//Clean up table definition parts (tables need unique ids)
if (numTableDefParts != 0)
{
FixupTableParts(clonedSheet, numTableDefParts);
}
//There should only be one sheet that has focus
CleanView(clonedSheet);
//Add new sheet to main workbook part
Sheets sheets = workbookPart.Workbook.GetFirstChild <Sheets>();
Sheet copiedSheet = new Sheet();
copiedSheet.Name = clonedSheetName;
copiedSheet.Id = workbookPart.GetIdOfPart(clonedSheet);
copiedSheet.SheetId = (uint)sheets.ChildElements.Count + 1;
sheets.Append(copiedSheet);
//Save Changes
workbookPart.Workbook.Save();
}
}
public static void CopySheet(string filename, string sheetName, string clonedSheetName, string destFileName)
{
//Open workbook
using (SpreadsheetDocument mySpreadsheet = SpreadsheetDocument.Open(filename, true))
{
WorkbookPart workbookPart = mySpreadsheet.WorkbookPart;
//Get the source sheet to be copied
WorksheetPart sourceSheetPart = GetWorkSheetPart(workbookPart, sheetName);
SharedStringTablePart sharedStringTable = workbookPart.SharedStringTablePart;
//Take advantage of AddPart for deep cloning
using (SpreadsheetDocument newXLFile = SpreadsheetDocument.Create(destFileName, SpreadsheetDocumentType.Workbook))
{
WorkbookPart newWorkbookPart = newXLFile.AddWorkbookPart();
SharedStringTablePart newSharedStringTable = newWorkbookPart.AddPart <SharedStringTablePart>(sharedStringTable);
WorksheetPart newWorksheetPart = newWorkbookPart.AddPart <WorksheetPart>(sourceSheetPart);
//Table definition parts are somewhat special and need unique ids...so let's make an id based on count
int numTableDefParts = sourceSheetPart.GetPartsCountOfType <TableDefinitionPart>();
tableId = numTableDefParts;
//Clean up table definition parts (tables need unique ids)
if (numTableDefParts != 0)
{
FixupTableParts(newWorksheetPart, numTableDefParts);
}
//There should only be one sheet that has focus
CleanView(newWorksheetPart);
var fileVersion = new FileVersion {
ApplicationName = "Microsoft Office Excel"
};
//Worksheet ws = newWorksheetPart.Worksheet;
Workbook wb = new Workbook();
wb.Append(fileVersion);
//Add new sheet to main workbook part
Sheets sheets = null;
//int sheetCount = wb.Sheets.Count();
if (wb.Sheets != null)
{
sheets = wb.GetFirstChild <Sheets>();
}
else
{
sheets = new Sheets();
}
Sheet copiedSheet = new Sheet
{
Name = clonedSheetName,
Id = newWorkbookPart.GetIdOfPart(newWorksheetPart)
};
if (wb.Sheets != null)
{
copiedSheet.SheetId = (uint)sheets.ChildElements.Count + 1;
}
else
{
copiedSheet.SheetId = 1;
}
sheets.Append(copiedSheet);
newWorksheetPart.Worksheet.Save();
wb.Append(sheets);
//Save Changes
newWorkbookPart.Workbook = wb;
wb.Save();
newXLFile.Close();
}
}
}