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