public Spreadsheet.Sheet AddSheet(string sheetName)
{
uint maxSheetId = 1;
DocumentFormat.OpenXml.Packaging.WorksheetPart wsPart = null;
DocumentFormat.OpenXml.Spreadsheet.Sheets sheets = null;
DocumentFormat.OpenXml.Spreadsheet.Sheet sheet = null;
wsPart = this.workbookPart.AddNewPart <DocumentFormat.OpenXml.Packaging.WorksheetPart>();
wsPart.Worksheet = new DocumentFormat.OpenXml.Spreadsheet.Worksheet(new DocumentFormat.OpenXml.Spreadsheet.SheetData());
sheets = this.workbookPart.Workbook.GetFirstChild <DocumentFormat.OpenXml.Spreadsheet.Sheets>();
if (sheets.Elements <DocumentFormat.OpenXml.Spreadsheet.Sheet>().Count() > 0)
{
maxSheetId = sheets.Elements <DocumentFormat.OpenXml.Spreadsheet.Sheet>().Select(s => s.SheetId.Value).Max();
maxSheetId++;
}
sheet = new DocumentFormat.OpenXml.Spreadsheet.Sheet();
sheet.Id = this.workbookPart.GetIdOfPart(wsPart);
sheet.SheetId = maxSheetId;
sheet.Name = sheetName;
//if (this.workbookPart.Workbook.Sheets == null)
//{
// sheets = this.workbookPart.Workbook.AppendChild(new DocumentFormat.OpenXml.Spreadsheet.Sheets());
//}
sheets.Append(sheet);
Spreadsheet.Sheet newSheet = new Spreadsheet.Sheet(ref wsPart, ref sheet);
this.sheetCollection.Add(newSheet);
return(newSheet);
}
private void InitializeWorksheets()
{
IEnumerable <DocumentFormat.OpenXml.Spreadsheet.Sheet> oXmlSheets = this.workbookPart.Workbook.Sheets.Elements <DocumentFormat.OpenXml.Spreadsheet.Sheet>();
IEnumerable <DocumentFormat.OpenXml.Packaging.WorksheetPart> wsParts = this.workbookPart.WorksheetParts;
if (oXmlSheets.Count() > 0)
{
for (int i = 0; i < oXmlSheets.Count(); i++)
{
DocumentFormat.OpenXml.Spreadsheet.Sheet oXmlSheet = oXmlSheets.ElementAt(i);
DocumentFormat.OpenXml.Packaging.WorksheetPart wsPart = this.workbookPart.GetPartById(oXmlSheet.Id) as DocumentFormat.OpenXml.Packaging.WorksheetPart;
Spreadsheet.Sheet sheet = new Spreadsheet.Sheet(ref wsPart, ref oXmlSheet);
this.sheetCollection.Add(sheet);
}
}
}
public bool RemoveSheet(string sheetName)
{
bool pkgSheetRemoved = false;
Spreadsheet.Sheet sheet = null;
IEnumerable <DocumentFormat.OpenXml.Spreadsheet.Sheet> qSheets = null;
DocumentFormat.OpenXml.Spreadsheet.Sheet oXmlSheet = null;
DocumentFormat.OpenXml.Packaging.WorksheetPart wsPart = null;
sheet = this.sheetCollection.Where <Sheet>(sht => sht.Name.HasValue && string.Equals(sht.Name.Value, sheetName)).First();
qSheets = this.workbookPart.Workbook.Sheets.Elements <DocumentFormat.OpenXml.Spreadsheet.Sheet>().Where(sht => sht.Name.HasValue && string.Equals(sht.Name.Value, sheetName));
if (qSheets.Count() >= 1)
{
oXmlSheet = qSheets.ElementAt(0);
}
wsPart = this.workbookPart.GetPartById(oXmlSheet.Id) as WorksheetPart;
oXmlSheet.Remove();
this.workbookPart.DeletePart(wsPart);
pkgSheetRemoved = true;
return(this.sheetCollection.Remove(sheet) & pkgSheetRemoved);
}
