/// <summary>
/// Create empty rows and cols to improve performance.
/// </summary>
/// <param name="rowCount"></param>
/// <param name="colCount"></param>
internal void CreateEmptyCells(int rowCount, int colCount)
{
if (Rows.Count != 0)
{
throw new InvalidOperationException("Must be called before rows are filled");
}
var sheetDataNode = WorksheetXml.XPathSelectElement("//d:sheetData", NameSpaceManager);
for (int rowNum = 1; rowNum <= rowCount; rowNum++)
{
// Add element
var rowElement = ExtensonMethods.NewElement("row");
rowElement.SetAttribute("r", rowNum.ToString());
sheetDataNode.Add(rowElement);
ExcelRow row = new ExcelRow(this, rowElement);
Rows.Add(rowNum, row);
for (int colNum = 1; colNum <= colCount; colNum++)
{
var cellElement = ExtensonMethods.NewElement("c");
cellElement.SetAttribute(ExcelWorksheet.tempColumnNumberTag, colNum.ToString());
rowElement.Add(cellElement);
ExcelCell cell = new ExcelCell(this, cellElement, rowNum, colNum);
row.Cells.Add(colNum, cell);
}
}
}
public void Add(String name, String rangeRef)
{
if (!IsValidName(name))
{
throw new ArgumentException("name");
}
if (!IsValidRangeRef(rangeRef))
{
throw new ArgumentException("Invalid rangeRef");
}
if (Contains(name))
{
throw new ArgumentException("Already exists: " + name);
}
// Create list element if needed
if (_definedNames == null)
{
var wbNode = _worsheetsXml.XPathSelectElement("//d:workbook", _worksheets.NsManager);
if (wbNode == null)
{
throw new NullReferenceException("Workbook node missing.");
}
_definedNames = ExtensonMethods.NewElement("definedNames");
wbNode.Add(_definedNames);
}
var dnElement = ExtensonMethods.NewElement("definedName").SetAttribute("name", name);
dnElement.Value = rangeRef;
_definedNames.Add(dnElement);
}
private void SaveAttribute(string name, ExcelHeaderFooterText excelHeaderFooterText)
{
var elm = ExtensonMethods.NewElement(name);
_headerFooterNode.Add(elm);
elm.Value = GetHeaderFooterText(excelHeaderFooterText);
}
void CreateSharedStringsDoc()
{
// create the shared strings xml doc (with no entries in it)
_xmlSharedStrings = new XDocument(
ExtensonMethods.NewElement("sst")
.SetAttribute("count", "0")
.SetAttribute("uniqueCount", "0"));
}
/// <summary>
/// Inserts conditional formatting for the cell range.
/// Currently only supports the dataBar style.
/// </summary>
/// <param name="startCell"></param>
/// <param name="endCell"></param>
/// <param name="color"></param>
public void CreateConditionalFormatting(ExcelCell startCell, ExcelCell endCell, string color)
{
var formatNode = WorksheetXml.XPathSelectElement("//d:conditionalFormatting", NameSpaceManager);
if (formatNode == null)
{
formatNode = ExtensonMethods.NewElement("conditionalFormatting");
var prevNode = WorksheetXml.XPathSelectElement("//d:mergeCells", NameSpaceManager);
if (prevNode == null)
{
prevNode = WorksheetXml.XPathSelectElement("//d:sheetData", NameSpaceManager);
}
prevNode.AddAfterSelf(formatNode);
}
XAttribute attr = formatNode.Attribute("sqref");
if (attr == null)
{
attr = new XAttribute("sqref", "");
formatNode.Add(attr);
}
attr.Value = string.Format("{0}:{1}", startCell.CellAddress, endCell.CellAddress);
var node = formatNode.XPathSelectElement("./d:cfRule", NameSpaceManager);
if (node == null)
{
node = ExtensonMethods.NewElement("cfRule");
formatNode.Add(node);
}
attr = node.Attribute("type");
if (attr == null)
{
attr = new XAttribute("type", "");
node.Add(attr);
}
attr.Value = "dataBar";
attr = node.Attribute("priority");
if (attr == null)
{
attr = new XAttribute("priority", "");
node.Add(attr);
}
attr.Value = "1";
// the following is poor code, but just an example!!!
var databar = ExtensonMethods.NewElement(
"databar",
ExtensonMethods.NewElement("cfvo").SetAttrValue("type", "min").SetAttrValue("val", "0"),
ExtensonMethods.NewElement("cfvo").SetAttrValue("type", "max").SetAttrValue("val", "0"),
ExtensonMethods.NewElement("color").SetAttrValue("rgb", color)
);
node.Add(databar);
}
/// <summary>
/// Inserts a new row into the spreadsheet. Existing rows below the insersion position are
/// shifted down. All formula are updated to take account of the new row.
/// </summary>
/// <param name="position">The position of the new row</param>
public void InsertRow(int position)
{
XElement rowNode = null;
// create the new row element
XElement rowElement = ExtensonMethods.NewElement("row").SetAttrValue("r", position.ToString());
var sheetDataNode = WorksheetXml.XPathSelectElement("//d:sheetData", NameSpaceManager);
if (sheetDataNode != null)
{
int renumberFrom = 1;
var nodes = sheetDataNode.Nodes().Cast <XElement>().ToList();
int nodeCount = nodes.Count;
XElement insertAfterRowNode = null;
int insertAfterRowNodeID = 0;
for (int i = 0; i < nodeCount; i++)
{
int currentRowID = int.Parse(nodes[i].Attribute("r").Value);
if (currentRowID < position)
{
insertAfterRowNode = nodes[i];
insertAfterRowNodeID = i;
}
if (currentRowID >= position)
{
renumberFrom = currentRowID;
break;
}
}
// update the existing row ids
for (int i = insertAfterRowNodeID + 1; i < nodeCount; i++)
{
int currentRowID = int.Parse(nodes[i].Attribute("r").Value);
if (currentRowID >= renumberFrom)
{
nodes[i].Attribute("r").Value = Convert.ToString(currentRowID + 1);
// now update any formula that are in the row
var formulaNodes = nodes[i].XPathSelectElements("./d:c/d:f", NameSpaceManager);
foreach (var formulaNode in formulaNodes)
{
formulaNode.Value = ExcelCell.UpdateFormulaReferences(formulaNode.Value, 1, 0, position, 0);
}
}
}
// now insert the new row
insertAfterRowNode?.AddAfterSelf(rowElement);
}
// Update stored rows
ShiftRows(position, 1);
Rows.Add(position, new ExcelRow(this, rowElement));
}
/// <summary>
/// Creates a new instance of the ExcelWorksheets class.
/// For internal use only!
/// </summary>
/// <param name="xlPackage"></param>
protected internal ExcelWorksheets(ExcelPackage xlPackage)
{
_xlPackage = xlPackage;
// Create a NamespaceManager to handle the default namespace,
// and create a prefix for the default namespace:
NameTable nt = new NameTable();
_nsManager = new XmlNamespaceManager(nt);
_nsManager.AddNamespace("d", ExcelPackage.schemaMain.NamespaceName);
_nsManager.AddNamespace("r", ExcelPackage.schemaRelationships.NamespaceName);
// obtain container node for all worksheets
_worksheetsNode = _xlPackage.Workbook.WorkbookXml.XPathSelectElement("//d:sheets", _nsManager);
if (_worksheetsNode == null)
{
// create new node as it did not exist
_worksheetsNode = ExtensonMethods.NewElement("sheets", ExcelPackage.schemaMain);
_xlPackage.Workbook.WorkbookXml.Document.Add(_worksheetsNode);
}
_worksheets = new List <ExcelWorksheet>();
foreach (var sheetNode in _worksheetsNode.Nodes().Cast <XElement>())
{
string name = sheetNode.Attribute("name").Value;
// Get the relationship id attribute:
string relId = sheetNode.Attribute("r:id").Value;
int sheetID = Convert.ToInt32(sheetNode.Attribute("sheetId").Value);
//if (sheetID != count)
//{
// // renumber the sheets as they are in an odd order
// sheetID = count;
// sheetNode.Attributes["sheetId"].Value = sheetID.ToString();
//}
// get hidden attribute (if present)
bool hidden = false;
var attr = sheetNode.Attribute("hidden");
if (attr != null)
{
hidden = Convert.ToBoolean(attr.Value);
}
//string type = "";
//attr = sheetNode.Attributes["type"];
//if (attr != null)
// type = attr.Value;
PackageRelationship sheetRelation = _xlPackage.Workbook.Part.GetRelationship(relId);
Uri uriWorksheet = PackUriHelper.ResolvePartUri(_xlPackage.Workbook.WorkbookUri, sheetRelation.TargetUri);
// add worksheet to our collection
_worksheets.Add(new ExcelWorksheet(_xlPackage, relId, name, uriWorksheet, sheetID, hidden));
}
_definedNames = new ExcelDefinedNames(this, _xlPackage.Workbook.WorkbookXml);
}
/// <summary>
/// Creates the XML document representing a new empty worksheet
/// </summary>
/// <returns></returns>
protected internal XDocument CreateNewWorksheet()
{
// create the new worksheet
var worksheetXml = new XDocument(
ExtensonMethods.NewElement("worksheet",
ExtensonMethods.NewElement("sheetViews",
ExtensonMethods.NewElement("sheetView").SetAttribute("workbookViewId", "0"))).SetAttribute("xmlns:r", ExcelPackage.schemaRelationships.NamespaceName),
// create the empty sheetData tag (must be present, but can be empty)
ExtensonMethods.NewElement("sheetData"));
return(worksheetXml);
}
private XElement GetOrCreateCellElement(ExcelWorksheet xlWorksheet, int row, int col)
{
XElement cellNode = null;
// this will create the row if it does not already exist
var rowNode = xlWorksheet.Row(row).Node;
if (rowNode != null)
{
cellNode = (XElement)rowNode.XPathSelectElement(string.Format("./d:c[@" + ExcelWorksheet.tempColumnNumberTag + "='{0}']", col), _xlWorksheet.NameSpaceManager);
if (cellNode == null)
{
// Didn't find the cell so create the cell element
cellNode = ExtensonMethods.NewElement("c");
cellNode.SetAttribute(ExcelWorksheet.tempColumnNumberTag, col.ToString());
// You must insert the new cell at the correct location.
// Loop through the children, looking for the first cell that is
// beyond the cell you're trying to insert. Insert before that cell.
XElement biggerNode = null;
var cellNodes = rowNode.XPathSelectElements("./d:c", _xlWorksheet.NameSpaceManager);
if (cellNodes != null)
{
foreach (var node in cellNodes)
{
var colNode = node.Attribute(ExcelWorksheet.tempColumnNumberTag);
if (colNode != null)
{
int colFound = Convert.ToInt32(colNode.Value);
if (colFound > col)
{
biggerNode = node;
break;
}
}
}
}
if (biggerNode == null)
{
rowNode.Add(cellNode);
}
else
{
biggerNode.AddBeforeSelf(cellNode);
}
}
}
return(cellNode);
}
/// <summary>
/// Adds a new formula node to the cell in the correct location
/// </summary>
/// <returns></returns>
protected internal XElement AddFormulaElement()
{
var formulaElement = ExtensonMethods.NewElement("f");
// find the right location for insersion
var valueNode = _cellElement.XPathSelectElement("./d:v", _xlWorksheet.NameSpaceManager);
if (valueNode == null)
{
_cellElement.Add(formulaElement);
}
else
{
valueNode.AddBeforeSelf(formulaElement);
}
return(formulaElement);
}
/// <summary>
/// Creates a new instance of the ExcelRow class.
/// For internal use only!
/// </summary>
/// <param name="worksheet">The parent worksheet</param>
/// <param name="row">The row number</param>
protected internal ExcelRow(ExcelWorksheet worksheet, int row)
{
_xlWorksheet = worksheet;
// Search for the existing row
_rowElement = (XElement)worksheet.WorksheetXml.XPathSelectElement($"//d:sheetData/d:row[@r='{row}']", _xlWorksheet.NameSpaceManager);
if (_rowElement == null)
{
// We didn't find the row, so add a new row element.
// HOWEVER we MUST insert new row in the correct position - otherwise Excel 2007 will complain!!!
_rowElement = ExtensonMethods.NewElement("row");
_rowElement.SetAttribute("r", row.ToString());
// now work out where to insert the new row
var sheetDataNode = worksheet.WorksheetXml.XPathSelectElement("//d:sheetData", _xlWorksheet.NameSpaceManager);
if (sheetDataNode != null)
{
XElement followingRow = null;
foreach (var currentRow in worksheet.WorksheetXml.XPathSelectElements("//d:sheetData/d:row", _xlWorksheet.NameSpaceManager))
{
int rowFound = Convert.ToInt32(currentRow.Attribute("r").Value);
if (rowFound > row)
{
followingRow = currentRow;
break;
}
}
if (followingRow == null)
{
// no data rows exist, so just add row
sheetDataNode.Add(_rowElement);
}
else
{
sheetDataNode.AddBeforeSelf(_rowElement, followingRow);
}
}
}
}
public XElement New(string name, params object[] content)
{
return(ExtensonMethods.NewElement(name, Namespace, content));
}
/// <summary>
/// Creates a new instance of the ExcelColumn class.
/// For internal use only!
/// </summary>
/// <param name="Worksheet"></param>
/// <param name="col"></param>
protected internal ExcelColumn(ExcelWorksheet Worksheet, int col)
{
NameTable nt = new NameTable();
_nsManager = new XmlNamespaceManager(nt);
_nsManager.AddNamespace("d", ExcelPackage.schemaMain.NamespaceName);
_xlWorksheet = Worksheet;
var parent = Worksheet.WorksheetXml.XPathSelectElement("//d:cols", _nsManager);
if (parent == null)
{
parent = ExtensonMethods.NewElement("cols");
var refChild = Worksheet.WorksheetXml.XPathSelectElement("//d:sheetData", _nsManager);
refChild.AddBeforeSelf(parent);
}
XAttribute minAttr;
XAttribute maxAttr;
XElement insertBefore = null;
// the column definitions cover a range of columns, so find the one we want
bool insertBeforeFound = false;
foreach (XElement colNode in parent.Nodes())
{
int min = 1;
int max = 1;
minAttr = colNode.Attribute("min");
if (minAttr != null)
{
min = int.Parse(minAttr.Value);
}
maxAttr = colNode.Attribute("max");
if (maxAttr != null)
{
max = int.Parse(maxAttr.Value);
}
if (!insertBeforeFound && (col <= min || col <= max))
{
insertBeforeFound = true;
insertBefore = colNode;
}
if (col >= min && col <= max)
{
_colElement = colNode;
break;
}
}
if (_colElement == null)
{
// create the new column definition
_colElement = ExtensonMethods.NewElement("col");
_colElement.SetAttribute("min", col.ToString());
_colElement.SetAttribute("max", col.ToString());
if (insertBefore != null)
{
insertBefore.AddBeforeSelf(_colElement);
}
else
{
parent.Add(_colElement);
}
}
}
/// <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 (var sheet in _worksheetsNode.Nodes().Cast <XElement>())
{
var attr = sheet.Attribute("sheetId");
if (attr != null)
{
int curID = int.Parse(attr.Value);
if (curID > sheetID)
{
sheetID = curID;
}
}
attr = sheet.Attribute("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));
var worksheetXml = CreateNewWorksheet();
worksheetXml.Save(streamWorksheet);
streamWorksheet.Dispose();
_xlPackage.Package.Flush();
// create the relationship between the workbook and the new worksheet
PackageRelationship rel = _xlPackage.Workbook.Part.CreateRelationship(uriWorksheet, TargetMode.Internal, ExcelPackage.schemaRelationships.NamespaceName + "/worksheet");
_xlPackage.Package.Flush();
// now create the new worksheet tag and set name/SheetId attributes in the workbook.xml
var worksheetNode = ExtensonMethods.NewElement("sheet");
// 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.Add(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);
}
