/// <summary> /// Adds a blank worksheet with the desired name /// </summary> /// <param name="Name"></param> /// <param name="rowCount">Number of rows to to create initially (for performance)</param> /// <param name="colCount">Number of columns to to create</param> /// <returns></returns> public ExcelWorksheet Add(string Name, int rowCount, int colCount) { // first find maximum existing sheetID // also check the name is unique - if not throw an error int sheetID = 0; foreach (XmlNode sheet in _worksheetsNode.ChildNodes) { XmlAttribute attr = (XmlAttribute)sheet.Attributes.GetNamedItem("sheetId"); if (attr != null) { int curID = int.Parse(attr.Value); if (curID > sheetID) sheetID = curID; } attr = (XmlAttribute)sheet.Attributes.GetNamedItem("name"); if (attr != null) { if (attr.Value == Name) throw new Exception("Add worksheet Error: attempting to create worksheet with duplicate name"); } } // we now have the max existing values, so add one sheetID++; // add the new worksheet to the package Uri uriWorksheet = new Uri("/xl/worksheets/sheet" + sheetID.ToString() + ".xml", UriKind.Relative); PackagePart worksheetPart = _xlPackage.Package.CreatePart(uriWorksheet, @"application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml", CompressionOption.Normal); // create the new, empty worksheet and save it to the package StreamWriter streamWorksheet = new StreamWriter(worksheetPart.GetStream(FileMode.Create, FileAccess.Write)); XmlDocument worksheetXml = CreateNewWorksheet(); worksheetXml.Save(streamWorksheet); streamWorksheet.Close(); _xlPackage.Package.Flush(); // create the relationship between the workbook and the new worksheet PackageRelationship rel = _xlPackage.Workbook.Part.CreateRelationship(uriWorksheet, TargetMode.Internal, ExcelPackage.schemaRelationships + "/worksheet"); _xlPackage.Package.Flush(); // now create the new worksheet tag and set name/SheetId attributes in the workbook.xml XmlElement worksheetNode = _xlPackage.Workbook.WorkbookXml.CreateElement("sheet", ExcelPackage.schemaMain); // create the new sheet node worksheetNode.SetAttribute("name", Name); worksheetNode.SetAttribute("sheetId", sheetID.ToString()); // set the r:id attribute worksheetNode.SetAttribute("id", ExcelPackage.schemaRelationships, rel.Id); // insert the sheet tag with all attributes set as above _worksheetsNode.AppendChild(worksheetNode); // create a reference to the new worksheet in our collection ExcelWorksheet worksheet = new ExcelWorksheet(_xlPackage, rel.Id, Name, uriWorksheet, sheetID, false); _worksheets.Add(worksheet); worksheet.CreateEmptyCells(rowCount, colCount); return worksheet; }
/// <summary> /// Adds a blank worksheet with the desired name /// </summary> /// <param name="Name"></param> /// <param name="rowCount">Number of rows to to create initially (for performance)</param> /// <param name="colCount">Number of columns to to create</param> /// <returns></returns> public ExcelWorksheet Add(string Name, int rowCount, int colCount) { // first find maximum existing sheetID // also check the name is unique - if not throw an error int sheetID = 0; foreach (XmlNode sheet in _worksheetsNode.ChildNodes) { XmlAttribute attr = (XmlAttribute)sheet.Attributes.GetNamedItem("sheetId"); if (attr != null) { int curID = int.Parse(attr.Value); if (curID > sheetID) { sheetID = curID; } } attr = (XmlAttribute)sheet.Attributes.GetNamedItem("name"); if (attr != null) { if (attr.Value == Name) { throw new Exception("Add worksheet Error: attempting to create worksheet with duplicate name"); } } } // we now have the max existing values, so add one sheetID++; // add the new worksheet to the package Uri uriWorksheet = new Uri("/xl/worksheets/sheet" + sheetID.ToString() + ".xml", UriKind.Relative); PackagePart worksheetPart = _xlPackage.Package.CreatePart(uriWorksheet, @"application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml", CompressionOption.Normal); // create the new, empty worksheet and save it to the package StreamWriter streamWorksheet = new StreamWriter(worksheetPart.GetStream(FileMode.Create, FileAccess.Write)); XmlDocument worksheetXml = CreateNewWorksheet(); worksheetXml.Save(streamWorksheet); streamWorksheet.Close(); _xlPackage.Package.Flush(); // create the relationship between the workbook and the new worksheet PackageRelationship rel = _xlPackage.Workbook.Part.CreateRelationship(uriWorksheet, TargetMode.Internal, ExcelPackage.schemaRelationships + "/worksheet"); _xlPackage.Package.Flush(); // now create the new worksheet tag and set name/SheetId attributes in the workbook.xml XmlElement worksheetNode = _xlPackage.Workbook.WorkbookXml.CreateElement("sheet", ExcelPackage.schemaMain); // create the new sheet node worksheetNode.SetAttribute("name", Name); worksheetNode.SetAttribute("sheetId", sheetID.ToString()); // set the r:id attribute worksheetNode.SetAttribute("id", ExcelPackage.schemaRelationships, rel.Id); // insert the sheet tag with all attributes set as above _worksheetsNode.AppendChild(worksheetNode); // create a reference to the new worksheet in our collection ExcelWorksheet worksheet = new ExcelWorksheet(_xlPackage, rel.Id, Name, uriWorksheet, sheetID, false); _worksheets.Add(worksheet); worksheet.CreateEmptyCells(rowCount, colCount); return(worksheet); }