public void Add(ExWs ws, string name)
{
try
{
SpreadsheetDocument tempDoc = SpreadsheetDocument.Create(new MemoryStream(), SDoc.DocumentType);
WorkbookPart tempWorkbookPart = tempDoc.AddWorkbookPart();
//SharedStringTablePart sharedStringTable = ws.Wb.SDoc.WorkbookPart.SharedStringTablePart;
//SharedStringTablePart tempSharedStringTable = tempWorkbookPart.AddPart(sharedStringTable);
WorksheetPart tempWorksheetPart = tempWorkbookPart.AddPart(ws.WsPart);
WorksheetPart clonedSheet = SDoc.WorkbookPart.AddPart(tempWorksheetPart);
var sData = clonedSheet.Worksheet.Elements <SheetData>().First();
var sourceNumberingFormats = ws.Wb.SDoc.WorkbookPart.WorkbookStylesPart.Stylesheet.NumberingFormats;
foreach (var row in sData.Elements <Row>())
{
foreach (var cell in row.Elements <Cell>())
{
if (cell.DataType != null && cell.DataType == CellValues.SharedString)
{
string str = ws.Wb.GetSharedStr(cell.CellValue.Text);
cell.DataType = CellValues.String;
cell.CellValue = new CellValue(str);
}
}
}
//clonedSheet.AddPart(tempSharedStringTable);
//Table definition parts are somewhat special and need unique ids…so let’s make an id based on count
int numTableDefParts = ws.WsPart.GetPartsCountOfType <TableDefinitionPart>();
int tableId = numTableDefParts;
//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)
{
tableId++;
tableDefPart.Table.Id = (uint)tableId;
tableDefPart.Table.DisplayName = "CopiedTable" + tableId;
tableDefPart.Table.Name = "CopiedTable" + tableId;
tableDefPart.Table.Save();
}
}
//There should only be one sheet that has focus
CleanView(clonedSheet);
var wbPart = SDoc.WorkbookPart;
Sheets sheets = wbPart.Workbook.GetFirstChild <Sheets>();
Sheet copiedSheet = new Sheet();
copiedSheet.Name = name;
copiedSheet.Id = wbPart.GetIdOfPart(clonedSheet);
copiedSheet.SheetId = (uint)sheets.ChildElements.Count + 1;
sheets.Append(copiedSheet);
wbPart.Workbook.Save();
}
catch (Exception e)
{
Console.WriteLine(e);
//throw;
}
}
/// <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);
}
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();
}
/// <summary>
/// Inserts the append cloned worksheet part.
/// </summary>
/// <param name="action">The action.</param>
/// <param name="targetWorkbookPart">The target workbook part.</param>
/// <param name="sourceWorksheetPart">The source worksheet part.</param>
/// <param name="sourceState">State of the source.</param>
/// <param name="targetSheetName">Name of the target sheet.</param>
/// <returns></returns>
/// <exception cref="ArgumentNullException">
/// workbookPart
/// or
/// sourceWorksheetPart
/// </exception>
private static WorksheetPart InsertAppendClonedWorksheetPart(this InsertAppend action, WorkbookPart targetWorkbookPart, WorksheetPart sourceWorksheetPart, EnumValue <SheetStateValues> sourceState, string targetSheetName)
{
if (targetWorkbookPart == null)
{
throw new ArgumentNullException("workbookPart");
}
if (sourceWorksheetPart == null)
{
throw new ArgumentNullException("sourceWorksheetPart");
}
// take advantage of AddPart for deep cloning
SpreadsheetDocument tempSheet = SpreadsheetDocument.Create(new MemoryStream(), SpreadsheetDocumentType.Workbook);
WorkbookPart tempWorkbookPart = tempSheet.AddWorkbookPart();
WorksheetPart tempWorksheetPart = tempWorkbookPart.AddPart <WorksheetPart>(sourceWorksheetPart);
// add cloned sheet and all associated parts to workbook
WorksheetPart clonedSheet = targetWorkbookPart.AddPart <WorksheetPart>(tempWorksheetPart);
// add new sheet to main workbook part
Sheets sheets = targetWorkbookPart.Workbook.GetFirstChild <Sheets>();
var nextSheetId = sheets.GetNextSheetId();
targetSheetName = sheets.GetSafeSheetName(targetSheetName ?? "Sheet");
var newSheet = new DocumentFormat.OpenXml.Spreadsheet.Sheet
{
Id = targetWorkbookPart.GetIdOfPart(clonedSheet),
// make sure the name is set to the provided name
Name = targetSheetName,
SheetId = nextSheetId,
State = sourceState
};
if (action == InsertAppend.Insert)
{
sheets.InsertAt(newSheet, 0);
}
else
{
sheets.Append(newSheet);
}
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();
}
private void InsertWorksheet(string sheetName, string filePath, int idx)
{
// Open the document for editing.
using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Open(filePath, true))
{
//get template
WorksheetPart clonedSheet = null;
var tempSheets = spreadSheet.WorkbookPart.Workbook.GetFirstChild <Sheets>().Elements <Sheet>().Where(s => s.Name == string.Format("Order Template", idx));
var tempPart = (WorksheetPart)spreadSheet.WorkbookPart.GetPartById(tempSheets.First().Id.Value);
// Add a blank WorksheetPart.
//WorksheetPart newWorksheetPart = spreadSheet.WorkbookPart.AddNewPart<WorksheetPart>();
//newWorksheetPart.Worksheet = new Worksheet(new SheetData());
//copy template to workbook
var copySheet = SpreadsheetDocument.Create(new MemoryStream(), SpreadsheetDocumentType.Workbook);
WorkbookPart copyWorkbookPart = copySheet.AddWorkbookPart();
WorksheetPart copyWorksheetPart = copyWorkbookPart.AddPart <WorksheetPart>(tempPart);
clonedSheet = spreadSheet.WorkbookPart.AddPart <WorksheetPart>(copyWorksheetPart);
Sheets sheets = spreadSheet.WorkbookPart.Workbook.GetFirstChild <Sheets>();
string relationshipId = spreadSheet.WorkbookPart.GetIdOfPart(clonedSheet);
// Get a unique ID for the new worksheet.
uint sheetId = 1;
if (sheets.Elements <Sheet>().Count() > 0)
{
sheetId = sheets.Elements <Sheet>().Select(s => s.SheetId.Value).Max() + 1;
}
// Append the new worksheet and associate it with the workbook.
Sheet sheet = new Sheet()
{
Id = relationshipId, SheetId = sheetId, Name = sheetName
};
sheets.Append(sheet);
}
}
/// <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();
}
}
public static byte[] WriteExcel(this Dictionary <DateTime, string[, ]> lst, string excelFile)
{
var template = new FileInfo(excelFile);
bool hasData = true;
using (var templateStream = new MemoryStream())
{
using (SpreadsheetDocument spreadDocument = SpreadsheetDocument.Open(excelFile, true))
{
WorkbookPart workBookPart = spreadDocument.WorkbookPart;
Workbook workbook = workBookPart.Workbook;
var fileVersion = new FileVersion {
ApplicationName = "Microsoft Office Excel"
};
Workbook wb = new Workbook();
wb.Append(fileVersion);
Sheets sheets = null;
WorksheetPart sourceSheetPart = null;
// add sheet
foreach (KeyValuePair <DateTime, string[, ]> item in lst)
{
sheets = sheets ?? new Sheets();
var sheetId = sheets != null ? (uint)sheets.ChildElements.Count + 1 : 1;
using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(HttpContext.Current.Request.MapPath("~") + "TemplateFile\\Template.xlsx", true))
{
WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
string rId = workbookPart.Workbook.Descendants <Sheet>().Where(s => s.Name.Value.Equals("sheet_name")).First().Id;
WorksheetPart wsPart = (WorksheetPart)workbookPart.GetPartById(rId);
try
{
workbookPart.ChangeIdOfPart(wsPart, "wsPart_" + sheetId);
}
catch
{
workbookPart.ChangeIdOfPart(wsPart, "rId1");
workbookPart.Workbook.Descendants <Sheet>().Where(s => s.Name.Value.Equals("sheet_name")).First().Id = "rId1";
workbookPart.ChangeIdOfPart(wsPart, "wsPart_" + sheetId);
}
workbookPart.Workbook.Descendants <Sheet>().Where(s => s.Name.Value.Equals("sheet_name")).First().Id = "wsPart_" + sheetId;
sourceSheetPart = wsPart;
WorksheetPart worksheetPart = workBookPart.AddPart <WorksheetPart>(sourceSheetPart);
Sheet copiedSheet = new Sheet
{
Name = item.Key.ToString("dd-MM-yyyy"),
Id = workBookPart.GetIdOfPart(worksheetPart)
};
copiedSheet.SheetId = sheetId;
sheets.Append(copiedSheet);
worksheetPart.Worksheet.GetFirstChild <SheetData>().UpdateSheetData(item.Key, item.Value);
workbookPart.ChangeIdOfPart(wsPart, "rId1");
workbookPart.Workbook.Descendants <Sheet>().Where(s => s.Name.Value.Equals("sheet_name")).First().Id = "rId1";
}
}
hasData = sheets != null;
if (sheets != null)
{
wb.Append(sheets);
//Save Changes
workBookPart.Workbook = wb;
wb.Save();
workBookPart.Workbook.Save();
}
spreadDocument.Close();
}
if (hasData)
{
byte[] templateBytes = File.ReadAllBytes(template.FullName);
templateStream.Write(templateBytes, 0, templateBytes.Length);
var result = templateStream.ToArray();
templateStream.Position = 0;
templateStream.Flush();
return(result);
}
else
{
return(null);
}
}
}
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();
}
}
}
