// Given a WorkbookPart, inserts a new worksheet. private static WorksheetPart InsertWorksheet(WorkbookPart workbookPart) { // Add a new worksheet part to the workbook. WorksheetPart newWorksheetPart = workbookPart.AddNewPart <WorksheetPart>(); newWorksheetPart.Worksheet = new Worksheet(new SheetData()); newWorksheetPart.Worksheet.Save(); Sheets sheets = workbookPart.Workbook.GetFirstChild <Sheets>(); string relationshipId = workbookPart.GetIdOfPart(newWorksheetPart); // Get a unique ID for the new sheet. uint sheetId = 1; if (sheets.Elements <Sheet>().Count() > 0) { sheetId = sheets.Elements <Sheet>().Select(s => s.SheetId.Value).Max() + 1; } string sheetName = "Sheet" + sheetId; // Append the new worksheet and associate it with the workbook. Sheet sheet = new Sheet() { Id = relationshipId, SheetId = sheetId, Name = sheetName }; sheets.Append(sheet); workbookPart.Workbook.Save(); return(newWorksheetPart); }
/// <summary> /// Crée une feuille. /// </summary> /// <param name="name">Le nom de la feuille.</param> /// <returns>La feuille créée.</returns> public WorksheetPart CreateSheet(string name) { var sheetPart = _package.WorkbookPart.AddNewPart <WorksheetPart>(); // Taille limite name = new string(name.Take(30).ToArray()); Worksheet workSheet = new Worksheet(); workSheet.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships"); workSheet.Append(new SheetData()); sheetPart.Worksheet = workSheet; // Get a unique ID for the new worksheet. Sheets sheets = _package.WorkbookPart.Workbook.Sheets; uint sheetId = 1; if (sheets.Elements <Sheet>().Any()) { sheetId = sheets.Elements <Sheet>().Select(s => s.SheetId.Value).Max() + 1; } var sheet = new Sheet() { Name = MakeValidSheetName(name), Id = _package.WorkbookPart.GetIdOfPart(sheetPart), SheetId = sheetId }; _package.WorkbookPart.Workbook.Sheets.Append(sheet); return(sheetPart); }
public static WorksheetPart addWorksheetPart(SpreadsheetDocument document, string name) { // Open the document for editing. // Add a blank WorksheetPart. WorksheetPart newWorksheetPart = document.WorkbookPart.AddNewPart <WorksheetPart>(); newWorksheetPart.Worksheet = new Worksheet(new SheetData()); Sheets sheets = document.WorkbookPart.Workbook.GetFirstChild <Sheets>(); string relationshipId = document.WorkbookPart.GetIdOfPart(newWorksheetPart); // 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; } // Give the new worksheet a name. string sheetName = name; // Append the new worksheet and associate it with the workbook. Sheet sheet = new Sheet() { Id = relationshipId, SheetId = sheetId, Name = sheetName }; sheets.Append(sheet); return(newWorksheetPart); }
private Sheet CreateNewWorkSheet(SpreadsheetDocument spreadSheet, string NewSheetName) { // Add a blank WorksheetPart. WorksheetPart newWorksheetPart = spreadSheet.WorkbookPart.AddNewPart <WorksheetPart>(); newWorksheetPart.Worksheet = new Worksheet(new SheetData()); Sheets sheets = spreadSheet.WorkbookPart.Workbook.GetFirstChild <Sheets>(); string relationshipId = spreadSheet.WorkbookPart.GetIdOfPart(newWorksheetPart); // 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; } // Give the new worksheet a name. string sheetName = NewSheetName + sheetId; // Append the new worksheet and associate it with the workbook. Sheet sheet = new Sheet() { Id = relationshipId, SheetId = sheetId, Name = sheetName }; sheets.Append(sheet); newWorksheetPart.Worksheet.Save(); spreadSheet.WorkbookPart.Workbook.Save(); return(sheet); }
public WorksheetPart InsertWorksheet() { if (this.workbook == null) { Console.WriteLine("Error: this spreadsheet has no workbookPart!"); return(null); } WorksheetPart newWorksheetPart = this.workbook.AddNewPart <WorksheetPart>(); newWorksheetPart.Worksheet = new Worksheet(new SheetData()); newWorksheetPart.Worksheet.Save(); Sheets sheets = this.workbook.Workbook.GetFirstChild <Sheets>(); string relationshipID = this.workbook.GetIdOfPart(newWorksheetPart); uint sheetID = 1; if (sheets.Elements <Sheet>().Count() > 0) { sheetID = sheets.Elements <Sheet>().Select(s => s.SheetId.Value).Max() + 1; } string sheetName = "Sheet" + sheetID; Sheet sheet = new Sheet() { Id = relationshipID, SheetId = sheetID, Name = sheetName }; sheets.Append(sheet); this.workbook.Workbook.Save(); return(newWorksheetPart); }
// Given a document name, inserts a new worksheet. public void InsertNewBlankWorksheet(string NewSheetName) { // Open the document for editing. using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Open(filePath, true)) { // Add a blank WorksheetPart. WorksheetPart newWorksheetPart = spreadSheet.WorkbookPart.AddNewPart <WorksheetPart>(); newWorksheetPart.Worksheet = new Worksheet(new SheetData()); Sheets sheets = spreadSheet.WorkbookPart.Workbook.GetFirstChild <Sheets>(); string relationshipId = spreadSheet.WorkbookPart.GetIdOfPart(newWorksheetPart); // 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; } // Give the new worksheet a name. string sheetName = NewSheetName; // Append the new worksheet and associate it with the workbook. Sheet sheet = new Sheet() { Id = relationshipId, SheetId = sheetId, Name = sheetName }; sheets.Append(sheet); } }
/// <summary> /// Inserts a new worksheet /// </summary> /// <param name="sheetName">Sheet name.</param> private void InsertNewSheet(string sheetName) { // Add a blank WorksheetPart. using (SpreadsheetDocument doc = SpreadsheetDocument.Open(_filename, true)) { WorksheetPart newWorksheetPart = doc.WorkbookPart.AddNewPart <WorksheetPart>(); newWorksheetPart.Worksheet = new Worksheet(new SheetData()); Sheets sheets = doc.WorkbookPart.Workbook.GetFirstChild <Sheets>(); string relationshipId = doc.WorkbookPart.GetIdOfPart(newWorksheetPart); // 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); //return this._sheetId; } }
// Inserting public static WorksheetPart InsertBlankWorkSheet(SpreadsheetDocument ss, String newSheetName) { // Add a new worksheet part to the workbook. WorkbookPart wbp = ss.WorkbookPart; WorksheetPart new_wsp = wbp.AddNewPart <WorksheetPart>(); SheetData sheet_data = new SheetData(); new_wsp.Worksheet = new Worksheet(sheet_data); new_wsp.Worksheet.Save(); Sheets sheets = wbp.Workbook.GetFirstChild <Sheets>(); String relationshipId = wbp.GetIdOfPart(new_wsp); // Get a unique ID for the new sheet. uint sheetId = 1; if (sheets.Elements <Sheet>().Count() > 0) { sheetId = sheets.Elements <Sheet>().Select(s => s.SheetId.Value).Max() + 1; } String sheetName = newSheetName; // Append the new worksheet and associate it with the workbook. Sheet sheet = new Sheet() { Id = relationshipId, SheetId = sheetId, Name = sheetName }; sheets.Append(sheet); wbp.Workbook.Save(); return(new_wsp); }
internal static WorksheetPart AppendNewSheet(SpreadsheetDocument doc, WorksheetPart worksheetPart, string sheetName = null) { Sheets sheets = doc.WorkbookPart.Workbook.GetFirstChild <Sheets>(); if (sheets == null) { sheets = doc.WorkbookPart.Workbook.AppendChild(new Sheets()); } uint sheetId = 1; if (sheets.Elements <Sheet>().Count() > 0) { sheetId = sheets.Elements <Sheet>().Select(s => s.SheetId.Value).Max() + 1; } if (string.IsNullOrEmpty(sheetName)) { sheetName = $"Sheet{sheetId}"; } sheets.AppendChild(new Sheet() { Id = doc.WorkbookPart.GetIdOfPart(worksheetPart), SheetId = sheetId, Name = sheetName }); return(worksheetPart); }
private Worksheet CreateWorksheet(Workbook workbook, string name) { SheetData newSheetData = new SheetData(); WorksheetPart newWorksheetPart = workbook.WorkbookPart.AddNewPart <WorksheetPart>(); newWorksheetPart.Worksheet = new Worksheet(newSheetData); Sheets sheets = workbook.WorkbookPart.Workbook.GetFirstChild <Sheets>(); string relationshipId = workbook.WorkbookPart.GetIdOfPart(newWorksheetPart); // Get a unique ID for the new sheet. uint sheetId = 1; if (sheets.Elements <Sheet>().Count() > 0) { sheetId = sheets.Elements <Sheet>().Select(s => s.SheetId.Value).Max() + 1; } Sheet newSheet = new Sheet() { Id = relationshipId, Name = name, SheetId = sheetId }; sheets.Append(newSheet); return(newWorksheetPart.Worksheet); }
private static WorksheetPart InsertWorksheet(WorkbookPart workbookPart) { WorksheetPart newWorksheetPart = workbookPart.AddNewPart <WorksheetPart>(); newWorksheetPart.Worksheet = new Worksheet(new SheetData()); newWorksheetPart.Worksheet.Save(); Sheets sheets = workbookPart.Workbook.GetFirstChild <Sheets>(); string relationshipId = workbookPart.GetIdOfPart(newWorksheetPart); uint sheetId = 1; if (sheets.Elements <Sheet>().Count() > 0) { sheetId = sheets.Elements <Sheet>().Select(s => s.SheetId.Value).Max() + 1; } string sheetName = "Sheet" + sheetId; Sheet sheet = new Sheet() { Id = relationshipId, SheetId = sheetId, Name = sheetName }; sheets.Append(sheet); workbookPart.Workbook.Save(); return(newWorksheetPart); }
public void AddNewWorksheet() { WorkbookPart workbookPart = spreadSheet.WorkbookPart; WorksheetPart newWorksheetPart = workbookPart.AddNewPart <WorksheetPart>(); newWorksheetPart.Worksheet = new Worksheet(new SheetData()); newWorksheetPart.Worksheet.Save(); CurrentWorksheetPart = newWorksheetPart; //workbookPart.SharedStringTablePart.SharedStringTable.Count = workbookPart.SharedStringTablePart.SharedStringTable.Count + 1; //workbookPart.SharedStringTablePart.SharedStringTable.UniqueCount = workbookPart.SharedStringTablePart.SharedStringTable.UniqueCount + 1; string relationshipId = workbookPart.GetIdOfPart(newWorksheetPart); Sheets sheets = workbookPart.Workbook.GetFirstChild <Sheets>(); uint sheetId = 1; if (sheets.Elements <Sheet>().Count() > 0) { sheetId = sheets.Elements <Sheet>().Select(s => s.SheetId.Value).Max() + 1; } string sheetName = "Sheet" + sheetId; // Append the new worksheet and associate it with the workbook. Sheet sheet = new Sheet() { Id = relationshipId, SheetId = sheetId, Name = sheetName }; sheets.Append(sheet); workbookPart.Workbook.Save(); }
/// <summary> /// 엑셀 파일에 Sheet를 생성함. /// </summary> /// <param name="spreadsheet">SpreadSheet</param> /// <param name="sheetName">생성할 sheet이름</param> private void AddSheet(SpreadsheetDocument spreadsheet, string sheetName) { WorksheetPart newWorksheetPart = spreadsheet.WorkbookPart.AddNewPart <WorksheetPart>(); newWorksheetPart.Worksheet = new Worksheet(new SheetData()); Sheets sheets = spreadsheet.WorkbookPart.Workbook.AppendChild <Sheets>(new Sheets()); string relationshipId = spreadsheet.WorkbookPart.GetIdOfPart(newWorksheetPart); // 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> /// Duplicates the worksheet. /// </summary> /// <param name="workbookPart">The workbook part.</param> /// <param name="srcWorksheetPart">The worksheet part.</param> /// <param name="sheetName">Name of the sheet.</param> /// <returns>Dupplicated sheet</returns> internal static Sheet DuplicateWorksheet(WorkbookPart workbookPart, WorksheetPart srcWorksheetPart, string sheetName) { // Add a blank WorksheetPart. WorksheetPart newWorksheetPart = workbookPart.AddNewPart <WorksheetPart>(); newWorksheetPart.Worksheet = (Worksheet)srcWorksheetPart.Worksheet.CloneNode(true); Sheets sheets = workbookPart.Workbook.GetFirstChild <Sheets>(); string relationshipId = workbookPart.GetIdOfPart(newWorksheetPart); // Get a unique ID for the new worksheet. uint sheetId = 1; if (sheets.Elements <Sheet>().Any()) { 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); return(sheet); }
private Worksheet creaHoja(SpreadsheetDocument doc, string nombre) { //Crea el archivo de la hoja WorksheetPart worksheetPart = doc.WorkbookPart.AddNewPart <WorksheetPart>(); worksheetPart.Worksheet = new Worksheet(new SheetData()); //Crea el apuntador al archivo Sheets sheets = doc.WorkbookPart.Workbook.GetFirstChild <Sheets>(); uint sheetId = 1; if (sheets.Elements <Sheet>().Count() > 0) { sheetId = sheets.Elements <Sheet>().Select(s => s.SheetId.Value).Max() + 1; } Sheet sheet = new Sheet() { Id = doc.WorkbookPart.GetIdOfPart(worksheetPart), SheetId = sheetId, Name = nombre }; sheets.Append(sheet); // The SheetData object will contain all the data. SheetData sheetData = worksheetPart.Worksheet.GetFirstChild <SheetData>(); return(worksheetPart.Worksheet); }
public static void OpenAndAddToSpreadsheetStream(Stream stream) { using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Open(stream, true)) { // Add a WorksheetPart. WorksheetPart newWorksheetPart = spreadSheet.WorkbookPart.AddNewPart <WorksheetPart>(); newWorksheetPart.Worksheet = new Worksheet(new SheetData()); // Create Sheets object. Sheets sheets = spreadSheet.WorkbookPart.Workbook.GetFirstChild <Sheets>(); string relationshipId = spreadSheet.WorkbookPart.GetIdOfPart(newWorksheetPart); // Create 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; } // Give the new worksheet a name. string sheetName = "mySheet" + sheetId; // Append the new worksheet and associate it with the workbook. Sheet sheet = new Sheet() { Id = relationshipId, SheetId = sheetId, Name = sheetName }; sheets.Append(sheet); } // Caller must close the stream. }
// Inserts a new worksheet for error. private static WorksheetPart InsertErrorWorksheet(WorkbookPart workbookPart) { // Add a new worksheet part to the workbook. WorksheetPart errorWorksheetPart = workbookPart.AddNewPart <WorksheetPart>(); errorWorksheetPart.Worksheet = new Worksheet(new SheetData()); errorWorksheetPart.Worksheet.Save(); Sheets sheets = workbookPart.Workbook.GetFirstChild <Sheets>(); string relationshipId = workbookPart.GetIdOfPart(errorWorksheetPart); // Get a unique ID for the new sheet. uint sheetId = 1; if (sheets.Elements <Sheet>().Count() > 0) { sheetId = sheets.Elements <Sheet>().Select(s => s.SheetId.Value).Max() + 1; } string sheetName = $"StudentImportErrors{DateTime.Now.Date.Day}{DateTime.Now.Date.Month}{DateTime.Now.Date.Year}{DateTime.Now.Hour}{DateTime.Now.Millisecond}"; // Append the new worksheet and associate it with the workbook. Sheet sheet = new Sheet() { Id = relationshipId, SheetId = sheetId, Name = sheetName }; sheets.Append(sheet); workbookPart.Workbook.Save(); return(errorWorksheetPart); }
public void CreateSheet(string name) { Sheets sheets = this.document.WorkbookPart.Workbook.GetFirstChild <Sheets>(); WorksheetPart newWorksheetPart = this.document.WorkbookPart.AddNewPart <WorksheetPart>(); string relationshipId = this.document.WorkbookPart.GetIdOfPart(newWorksheetPart); // 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; } // Give the new worksheet a name. string sheetName = name; Sheet sheet = new Sheet() { Id = relationshipId, SheetId = sheetId, Name = sheetName }; sheets.Append(sheet); this.part = newWorksheetPart; }
private void ProcessWorkSheet(string filePath, DataTable tbl) { // get worksheetpart using (Stream stream = File.Open(filePath, FileMode.Open)) { // Open a SpreadsheetDocument based on a stream. SpreadsheetDocument spreadsheet = SpreadsheetDocument.Open(stream, true); WorkbookPart wbPart = spreadsheet.WorkbookPart; // Assuming last id is , is id for worksheet . Sheets sheets = spreadsheet.WorkbookPart.Workbook.GetFirstChild <Sheets>(); uint sheetId = 0; if (sheets.Elements <Sheet>().Count() > 0) { sheetId = sheets.Elements <Sheet>().Select(s => s.SheetId.Value).Max() - 1; } WorksheetPart worksheetPart = wbPart.WorksheetParts.ToList()[Convert.ToInt32(sheetId)]; WriteDataTableToExcelWorksheet(tbl, worksheetPart, CultureInfo.InvariantCulture); spreadsheet.WorkbookPart.Workbook.Save(); spreadsheet.Close(); } }
private static void CreatePartsDT(DataTable dt, SpreadsheetDocument spreadsheet) { // Loop through each of the DataTables in our DataSet, and create a new Excel Worksheet for each. uint sheetId = 1; var newWorksheetPart = spreadsheet.WorkbookPart.AddNewPart <WorksheetPart>(); newWorksheetPart.Worksheet = new DocumentFormat.OpenXml.Spreadsheet.Worksheet(); // create sheet data newWorksheetPart.Worksheet.AppendChild(new DocumentFormat.OpenXml.Spreadsheet.SheetData()); // save worksheet WriteDataTableToExcelWorksheet(dt, newWorksheetPart); newWorksheetPart.Worksheet.Save(); Sheets sheets = new Sheets(); spreadsheet.WorkbookPart.Workbook.AppendChild(sheets); if (sheets.Elements <Sheet>().Any()) { sheetId = sheets.Elements <Sheet>().Select(s => s.SheetId.Value).Max() + 1; } spreadsheet.WorkbookPart.Workbook.GetFirstChild <DocumentFormat.OpenXml.Spreadsheet.Sheets>().AppendChild(new Sheet { Id = spreadsheet.WorkbookPart.GetIdOfPart(newWorksheetPart), SheetId = sheetId, Name = dt.TableName }); spreadsheet.WorkbookPart.Workbook.Save(); }
/// <summary> /// Добавляет лист в документ Excel /// </summary> /// <param name="sheetName">Имя добавляемого листа</param> public void AddSpreadSheet(string sheetName) { _worksheetPart = _workbookPart.AddNewPart <WorksheetPart>(); _worksheetPart.Worksheet = new Worksheet(new SheetData()); _workSheet = _worksheetPart.Worksheet; _workSheet.Save(); Sheets sheets = _workbookPart.Workbook.GetFirstChild <Sheets>(); string relationshipId = _workbookPart.GetIdOfPart(_worksheetPart); uint sheetId = 1; if (sheets.Elements <Sheet>().Any()) { sheetId = sheets.Elements <Sheet>().Select(s => s.SheetId.Value).Max() + 1; } Sheet sheet = new Sheet() { Id = relationshipId, SheetId = sheetId, Name = sheetName }; sheets.Append(sheet); //if (_worksheetPart.Worksheet.Elements<Sheet>().All(s => s.SheetId != sheetId)) // _worksheetPart.AddPart<Worksheet>(sheet); _workbookPart.Workbook.Save(); }
public T GetWorksheetPartByName <T>(string sheetName) { // Check the type first; if (typeof(T) != typeof(WorksheetPart) && typeof(T) != typeof(Sheet)) { Console.WriteLine("Error: Invalid return type!"); return(default(T)); } if (this.workbook == null || this.workbook.Workbook == null) { Console.WriteLine("Error: This spreadsheet has no workbook!"); return(default(T)); } if (this.workbook.Workbook.Descendants <Sheets>().Count() == 0 || this.workbook.Workbook.Descendants <Sheet>().Count() == 0) { Console.WriteLine("Error: This spreadsheet has no sheets!"); return(default(T)); } Sheets sheets = this.workbook.Workbook.GetFirstChild <Sheets>(); if (sheets == null) { Console.WriteLine("Error: this workbook has no sheets elements"); return(default(T)); } //Sheet selectedSheet = sheets.Elements<Sheet>().First(); //Console.WriteLine("Name: " + selectedSheet.Name); Sheet selectedSheet = null; if (sheets.Elements <Sheet>().Where(s => s.Name.ToString().Equals(sheetName)).Count() > 0) { selectedSheet = sheets.Elements <Sheet>().Where(s => s.Name.ToString().Equals(sheetName)).FirstOrDefault(); } if (selectedSheet == null) { Console.WriteLine("Error: this workbook has no '{0}'", sheetName); return(default(T)); } string selectedSheetID = selectedSheet.Id; if (typeof(T) == typeof(WorksheetPart)) { return((T)Convert.ChangeType(this.workbook.GetPartById(selectedSheetID), typeof(T))); } else if (typeof(T) == typeof(Sheet)) { return((T)Convert.ChangeType(selectedSheet, typeof(T))); } else { Console.WriteLine("Error!"); return(default(T)); } }
public byte[] Save() { using (var ms = new MemoryStream()) using (var doc = SpreadsheetDocument.Create(ms, SpreadsheetDocumentType.Workbook)) { WorkbookPart workbookPart = doc.AddWorkbookPart(); workbookPart.Workbook = new Workbook(); Sheets sheets = doc.WorkbookPart.Workbook.AppendChild(new Sheets()); var stylesPart = doc.WorkbookPart.AddNewPart <WorkbookStylesPart>(); stylesPart.Stylesheet = new ExcelWorkbookStylesheet().CreateStylesheet(this.sheets); foreach (var item in this.sheets) { var worksheetPart = workbookPart.AddNewPart <WorksheetPart>(); var workSheet = new Worksheet(); var sheetData = new SheetData(); foreach (var rowGroup in item.Cells.GroupBy(g => g.Value.Row)) { var row = new Row() { RowIndex = Convert.ToUInt32(rowGroup.Key) }; foreach (var cellItem in rowGroup) { row.Append(this.CreateCell(cellItem.Value)); } sheetData.Append(row); } workSheet.AppendChild(sheetData); worksheetPart.Worksheet = workSheet; uint sheetId = 1; if (sheets.Elements <Sheet>().Count() > 0) { sheetId = sheets.Elements <Sheet>().Select(s => s.SheetId.Value).Max() + 1; } Sheet sheet1 = new Sheet() { Id = doc.WorkbookPart.GetIdOfPart(worksheetPart), SheetId = sheetId, Name = item.SheetName }; sheets.Append(sheet1); } doc.Close(); return(ms.ToArray()); } }
public WorksheetPart AddNewSheet(string sheetName) { if (this.spreadsheet == null) { return(null); } //Only need get first because a document shall have ony 1 workbook WorkbookPart workbookPart = this.spreadsheet.GetPartsOfType <WorkbookPart>().First(); if (workbookPart == null) { workbookPart = spreadsheet.AddWorkbookPart(); workbookPart.Workbook = new Workbook(); } //workbookPart.WorksheetParts.First(); // Add to workbook: Sheets sheets = spreadsheet.WorkbookPart.Workbook.GetFirstChild <Sheets>(); uint newSheetID; if (sheets == null) { sheets = spreadsheet.WorkbookPart.Workbook.AppendChild <Sheets>(new Sheets()); newSheetID = 1; } else { newSheetID = sheets.Elements <Sheet>().Select(s => s.SheetId.Value).Max() + 1; //uint sID = uint.Parse(sheet.GetAttribute("sheetID", sheet.NamespaceUri).ToString()); } Sheet newSheet; int isExist = sheets.Elements <Sheet>().Where(s => s.Name.ToString().Equals(sheetName)).Count(); // If not exist any worksheet with the name provided, create a new one; if (isExist == 0) { // New sheet.xml file: which contains data of a sheet WorksheetPart worksheetPart = workbookPart.AddNewPart <WorksheetPart>(); worksheetPart.Worksheet = new Worksheet(new SheetData()); newSheet = new Sheet() { Id = this.spreadsheet.WorkbookPart.GetIdOfPart(worksheetPart), SheetId = newSheetID, Name = sheetName }; sheets.Append(newSheet); return(worksheetPart); } else { Sheet selectedSheet = sheets.Elements <Sheet>().Where(s => s.Name.ToString().Equals(sheetName)).FirstOrDefault(); string selectedSheetID = selectedSheet.Id; return((WorksheetPart)this.workbook.GetPartById(selectedSheetID)); } }
private uint GetUniqueSheetId(Sheets sheets) { uint sheetId = 1; if (sheets.Elements <Sheet>().Count() > 0) { sheetId = sheets.Elements <Sheet>().Select(s => s.SheetId.Value).Max() + 1; } return(sheetId); }
private uint NewSheetId() { if (InnerSheets.Elements <Sheet>().Count() == 0) { return(1); } else { return(InnerSheets.Elements <Sheet>().Select(s => s.SheetId.Value).Max() + 1); } }
private Sheet GetRefSheet(string name, Sheets sheets) { var tmplate = sheets.Elements <Sheet>().FirstOrDefault(x => name.Contains(x.Name)); if (tmplate != null) { return(sheets.Elements <Sheet>().LastOrDefault(x => x.Name.ToString().Contains(tmplate.Name))); } return (null); }
public static WorksheetPart InsertSheet(SpreadsheetDocument document, string sheetName = "") { using (SAEONLogs.MethodCall(typeof(WorksheetPart))) { try { if (document is null) { throw new ArgumentNullException(nameof(document)); } // Add a blank WorksheetPart. WorksheetPart worksheetPart = document.WorkbookPart.AddNewPart <WorksheetPart>(); worksheetPart.Worksheet = new Worksheet(new SheetData()); Sheets sheets = document.WorkbookPart.Workbook.GetFirstChild <Sheets>(); if (sheets is null) { sheets = document.WorkbookPart.Workbook.AppendChild <Sheets>(new Sheets()); } string relationshipId = document.WorkbookPart.GetIdOfPart(worksheetPart); SAEONLogs.Verbose("relationshipId: {relationshipId}", relationshipId); // Get a unique ID for the new worksheet. uint sheetId = 1; if (sheets.Elements <Sheet>().Any()) { sheetId = sheets.Elements <Sheet>().Select(s => s.SheetId.Value).Max() + 1; } SAEONLogs.Verbose("sheetId: {sheetId}", sheetId); // Give the new worksheet a name. if (string.IsNullOrEmpty(sheetName)) { sheetName = "Sheet" + sheetId; } SAEONLogs.Verbose("sheetName: {sheetName}", sheetName); // Append the new worksheet and associate it with the workbook. Sheet sheet = new Sheet() { Id = relationshipId, SheetId = sheetId, Name = sheetName }; sheets.Append(sheet); return(worksheetPart); } catch (Exception ex) { SAEONLogs.Exception(ex); throw; } } }
public string InitBookCreation(string TemplateFileName, string ResultFileName, string TemplateSheetsFileName) { //Assume Error string result = "Cannot Create Result File"; result = CopyFile(templatefilepath + TemplateFileName, resultfilepath + ResultFileName); if (result == "Copied file") { try { using (SpreadsheetDocument document = SpreadsheetDocument.Open(resultfilepath + ResultFileName, true)) { IEnumerable <Sheet> sheets = document.WorkbookPart.Workbook.Descendants <Sheet>().Where(s => s.Name == TemplateSheetsFileName); if (sheets.Count() == 0) { // The specified worksheet does not exist. // Create a new worksheet with name as TemplateSheetsFileName // Add a blank WorksheetPart. WorksheetPart newWorksheetPart = document.WorkbookPart.AddNewPart <WorksheetPart>(); newWorksheetPart.Worksheet = new Worksheet(new SheetData()); Sheets wbsheets = document.WorkbookPart.Workbook.GetFirstChild <Sheets>(); string relationshipId = document.WorkbookPart.GetIdOfPart(newWorksheetPart); // Get a unique ID for the new worksheet. uint sheetId = 1; if (wbsheets.Elements <Sheet>().Count() > 0) { sheetId = wbsheets.Elements <Sheet>().Select(s => s.SheetId.Value).Max() + 1; } // Give the new worksheet a name. string sheetName = TemplateSheetsFileName; // Append the new worksheet and associate it with the workbook. Sheet sheet = new Sheet() { Id = relationshipId, SheetId = sheetId, Name = sheetName }; wbsheets.Append(sheet); } } result = "Result file ready"; } catch (Exception ex) { result = ex.Message; } } return(result); }
public static void AppendSpreadSheet(string filePath, string sheetName) { if (!System.IO.File.Exists(filePath)) { throw new Exception("File Not Exists"); } using (Stream stream = File.Open(filePath, FileMode.Open)) { // Open a SpreadsheetDocument based on a stream. SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(stream, true); Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.GetFirstChild <Sheets>(); if (isSheetsAlreadyExists(sheets, sheetName)) { spreadsheetDocument.Close(); stream.Close(); return; } // Add a new worksheet. WorksheetPart newWorksheetPart = spreadsheetDocument.WorkbookPart.AddNewPart <WorksheetPart>(); newWorksheetPart.Worksheet = new Worksheet(new SheetData()); newWorksheetPart.Worksheet.Save(); string relationshipId = spreadsheetDocument.WorkbookPart.GetIdOfPart(newWorksheetPart); // 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); //ready to write column header spreadsheetDocument.WorkbookPart.Workbook.Save(); // Close the document handle. spreadsheetDocument.Close(); stream.Close(); } }