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(); }