private string CreateSheet(string name, Sheets sheets) { WorksheetPart worksheetPart = _workbookPart.AddNewPart <WorksheetPart>(); //var sheets = _workbookPart.Workbook.Sheets; uint index = (uint)sheets.Elements <Sheet>().Count() + 1; var id = _workbookPart.GetIdOfPart(worksheetPart); Sheet sheet = new Sheet { Id = id, Name = name, SheetId = index }; var refSheet = GetRefSheet(name, sheets); if (refSheet == null) { sheets.Append(sheet); } else { sheets.InsertAfter(sheet, refSheet); } //refSheet.InsertAfterSelf(sheet); return(sheet.Id); }
public void MoveAfter(string sheetName, string referenceSheetName) { WorkbookPart wbpart = this.Document.GetOSpreadsheet().WorkbookPart; Sheets parent = wbpart.Workbook.Sheets; Sheet sheet = parent.Elements <Sheet>().Where(s => s.Name == sheetName).First(); if (sheet == null) { throw new InvalidOperationException("Sheet \"" + sheetName + "\" does not exist."); } Sheet referenceSheet = wbpart.Workbook.Sheets.Elements <Sheet>().Where(s => s.Name == referenceSheetName).First(); if (referenceSheet == null) { throw new InvalidOperationException("Sheet \"" + referenceSheetName + "\" does not exist."); } sheet.Remove(); parent.InsertAfter(sheet, referenceSheet); }
public void MoveAfter(string sheetName, string referenceSheetName) { WorkbookPart workbookPart = this.Document.GetOSpreadsheet().WorkbookPart; Sheets sheets = workbookPart.Workbook.Sheets; Sheet newChild = (from s in sheets.Elements <Sheet>() where s.Name == sheetName select s).First <Sheet>(); if (newChild == null) { throw new InvalidOperationException("Sheet \"" + sheetName + "\" does not exist."); } Sheet refChild = (from s in workbookPart.Workbook.Sheets.Elements <Sheet>() where s.Name == referenceSheetName select s).First <Sheet>(); if (refChild == null) { throw new InvalidOperationException("Sheet \"" + referenceSheetName + "\" does not exist."); } newChild.Remove(); sheets.InsertAfter <Sheet>(newChild, refChild); }
/// <summary> /// Copie une feuille. /// </summary> /// <param name="sheetName">Le nom de la source.</param> /// <param name="clonedSheetName">Le nom de la destination.</param> /// <param name="insertAfter">La feuille après laquelle insérer la nouvelle feuille. Si <c>null</c>, ajoute après la source.</param> /// <returns> /// La nouvelle feuille. /// </returns> public WorksheetPart CopySheet(string sheetName, string clonedSheetName, WorksheetPart insertAfter = null) { WorksheetPart sourceSheetPart = GetWorkSheetPart(sheetName); // AddPart fait du deep cloning SpreadsheetDocument tempSheet = SpreadsheetDocument.Create(new MemoryStream(), _package.DocumentType); WorkbookPart tempWorkbookPart = tempSheet.AddWorkbookPart(); WorksheetPart tempWorksheetPart = tempWorkbookPart.AddPart(sourceSheetPart); // Ajouter le clone WorksheetPart clonedSheet = _package.WorkbookPart.AddPart(tempWorksheetPart); int numTableDefParts = sourceSheetPart.GetPartsOfType <TableDefinitionPart>().Count(); // Corriger les ids if (numTableDefParts != 0) { FixupTableParts(clonedSheet, (uint)numTableDefParts); } // Vérifier qu'une seule vue a le focus CleanView(clonedSheet); Sheets sheets = _package.WorkbookPart.Workbook.GetFirstChild <Sheets>(); Sheet copiedSheet = new Sheet { Name = MakeValidSheetName(clonedSheetName), Id = _package.WorkbookPart.GetIdOfPart(clonedSheet), SheetId = sheets.Elements <Sheet>().Select(s => s.SheetId.Value).Max() + 1 }; sheets.InsertAfter(copiedSheet, GetSheet(insertAfter ?? sourceSheetPart)); return(clonedSheet); }
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(); } }