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