示例#1
0
        /// <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);
        }
示例#2
0
        /// <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;
            }
        }
示例#3
0
        /// <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);
            }
        }