public static void CleanView(WorksheetPart worksheetPart)
{
//There can only be one sheet that has focus
SheetViews views = worksheetPart.Worksheet.GetFirstChild <SheetViews>();
if (views != null)
{
views.Remove();
worksheetPart.Worksheet.Save();
}
}
/// <summary>
/// Копирует лист
/// </summary>
/// <param name="ws">Исходный лист</param>
/// <param name="newSheetName">Имя нового листа</param>
/// <param name="docType">Тип исходного листа SpreadsheetDocumentType</param>
public static Worksheet Duplicate(this Worksheet ws, string newSheetName, SpreadsheetDocumentType docType = SpreadsheetDocumentType.Workbook)
{
var sourceSheetPart = ws.WorksheetPart;
SpreadsheetDocument tempSheet = SpreadsheetDocument.Create(new MemoryStream(), docType);
WorkbookPart tempWorkbookPart = tempSheet.AddWorkbookPart();
WorksheetPart tempWorksheetPart = tempWorkbookPart.AddPart(sourceSheetPart);
var WbPart = ws.GetWorkbookPart();
//Add cloned sheet and all associated parts to workbook
WorksheetPart clonedSheet = WbPart.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>();
//Clean up table definition parts (tables need unique ids)
if (numTableDefParts != 0)
{
//Every table needs a unique id and name
foreach (TableDefinitionPart tableDefPart in clonedSheet.TableDefinitionParts)
{
numTableDefParts++;
tableDefPart.Table.Id = (uint)numTableDefParts;
tableDefPart.Table.DisplayName = "CopiedTable" + numTableDefParts;
tableDefPart.Table.Name = "CopiedTable" + numTableDefParts;
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 = WbPart.Workbook.GetFirstChild <Sheets>();
Sheet copiedSheet = new Sheet
{
Name = newSheetName,
Id = WbPart.GetIdOfPart(clonedSheet),
SheetId = (uint)sheets.ChildElements.Count + 1
};
sheets.Append(copiedSheet);
//Save Changes
WbPart.Workbook.Save();
return(clonedSheet.Worksheet);
}
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);
}
//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();
}
}
/// <summary>
/// Deletes the supplied sheet instance from the workbook part
/// </summary>
/// <param name="workbookPart">The workbook part to delete the sheet from</param>
/// <param name="sheet">The sheet to delete</param>
/// <exception cref="ArgumentNullException">
/// workbookPart
/// or
/// sheet
/// </exception>
public static void DeleteSheet(this WorkbookPart workbookPart, DocumentFormat.OpenXml.Spreadsheet.Sheet sheet)
{
if (workbookPart == null)
{
throw new ArgumentNullException("workbookPart");
}
if (sheet == null)
{
throw new ArgumentNullException("sheet");
}
// get the id of the sheet for deletion
Int32Value sheetId = Int32Value.FromInt32((int)sheet.SheetId.Value);
// Remove the sheet reference from the workbook.
WorksheetPart worksheetPart = (WorksheetPart)(workbookPart.GetPartById(sheet.Id));
SheetViews views = worksheetPart.Worksheet.GetFirstChild <SheetViews>();
if (views != null)
{
views.Remove();
worksheetPart.Worksheet.Save();
}
sheet.Remove();
// Delete the worksheet part.
workbookPart.DeletePart(worksheetPart);
// Get the CalculationChainPart
// Note: An instance of this part type contains an ordered set of references to all cells in all worksheets in the
// workbook whose value is calculated from any formula
CalculationChainPart calChainPart = workbookPart.CalculationChainPart;
if (calChainPart != null)
{
List <CalculationCell> forRemoval = new List <CalculationCell>();
var calChainEntries = calChainPart.CalculationChain.Descendants <CalculationCell>().ToList();
foreach (CalculationCell item in calChainEntries)
{
if (item.SheetId == null)
{
item.Remove();
}
else if (item.SheetId.HasValue && item.SheetId.Value.Equals(sheetId))
{
item.Remove();
}
}
if (calChainPart.CalculationChain.Count() == 0)
{
workbookPart.DeletePart(calChainPart);
}
}
workbookPart.Workbook.Save();
}
