/// <summary>
/// Clones a row identified by NamedRange, updates the template rows named range using the NamedRangeValues dicitonary and inserts it offset rows down
/// </summary>
/// <param name="NamedRange"></param>
/// <param name="offset"></param>
/// <param name="NamedRangeValues"></param>
/// <remarks>
/// References are maintain in the UpdateReferences method
/// </remarks>
public void CloneRow(string NamedRange, int offset, Dictionary <string, string> NamedRangeValues)
{
//Add name to dictionary
NamedRangeValues[NamedRange] = NamedRangeValues["TemplateRowName"];
//find template row
XmlNode TemplateRow = xmlDoc.SelectSingleNode(".//ss:Row[ss:Cell[ss:NamedCell[@ss:Name='" + NamedRange + "']]]", nsmgr); // gets the cell with the named cell name
XmlNode TemplateRowClone = TemplateRow.Clone();
//Update all named values that match a key in the dictionary
SetNamedCellValue(TemplateRowClone, NamedRangeValues);
//Find reference row
//Example: Our template row is row 4, offset is one, we will insert at row 5, and use row for as the node before
NamedRange oNamedRange = dicNamedRanges[NamedRange];
int NamedRangeRow = oNamedRange.row;
int ReferenceRowBeforeInsert = NamedRangeRow + offset - 1;
int InsertRowNumber = NamedRangeRow + offset; //we are inserting the row just below the reference row
//get reference and before nodes and insert our cloned reference row
XmlNode ReferenceRowNode = xmlDoc.SelectSingleNode(".//ss:Row[position() >= " + ReferenceRowBeforeInsert + "]", nsmgr);
XmlNode parent = TemplateRow.ParentNode;
parent.InsertAfter(TemplateRowClone, ReferenceRowNode);
//Maintain references
UpdateReferences(InsertRowNumber);
}
/// <summary>
/// Sets the formula for the named range
/// </summary>
/// <param name="NamedRange"></param>
/// <param name="formula"></param>
public void SetFormula(string NamedRange, string formula)
{
//check the named range exists
if (!dicNamedRanges.ContainsKey(NamedRange))
{
return;
}
//get row node based on the row number of the named ranged
NamedRange oNamedRange = dicNamedRanges[NamedRange];
XmlNode ReferenceRowNode = xmlDoc.SelectSingleNode(".//ss:Row[position() >= " + oNamedRange.row + "]", nsmgr);
//find named cells and update formula, if cell exists
XmlNodeList NamedCells = ReferenceRowNode.SelectNodes(".//ss:Cell[ss:NamedCell[@ss:Name='" + NamedRange + "']]", nsmgr); // gets the cell with the named cell name
if (NamedCells.Count == 1)
{
NamedCells[0].Attributes["ss:Formula"].Value = formula;
}
}
/// <summary>
/// Formats all cells in a row based on CellStyle parameter
/// </summary>
/// <param name="NamedRange"></param>
/// <param name="offset"></param>
/// <param name="RowFormat"></param>
public void FormatRow(string NamedRange, int offset, CellStyle RowFormat)
{
XmlNode CellNode;
//get row node
NamedRange oNamedRange = dicNamedRanges[NamedRange];
int NamedRangeRow = oNamedRange.row;
int ReferenceRow = NamedRangeRow + offset;
XmlNode ReferenceRowNode = xmlDoc.SelectSingleNode(".//ss:Row[position() >= " + ReferenceRow + "]", nsmgr);
//get cells
XmlNodeList CellNodes = ReferenceRowNode.SelectNodes(".//ss:Cell", nsmgr);
//loop through all cells and format using the FormatCell method
for (int i = 0; i < CellNodes.Count; i++)
{
CellNode = CellNodes[i];
FormatCell(CellNode, RowFormat);
}
}