Beispiel #1
0
        /// <summary>
        /// Creates a shared formula based on the formula already in startCell
        /// Essentially this supports the formula attributes such as t="shared" ref="B2:B4" si="0"
        /// as per Brian Jones: Open XML Formats blog. See
        /// http://blogs.msdn.com/brian_jones/archive/2006/11/15/simple-spreadsheetml-file-part-2-of-3.aspx
        /// </summary>
        /// <param name="startCell">The cell containing the formula</param>
        /// <param name="endCell">The end cell (i.e. end of the range)</param>
        public void CreateSharedFormula(ExcelCell startCell, ExcelCell endCell)
        {
            XmlElement formulaElement;
            string     formula = startCell.Formula;

            if (formula == "")
            {
                throw new Exception("CreateSharedFormula Error: startCell does not contain a formula!");
            }

            // find or create a shared formula ID
            int sharedID = -1;

            foreach (XmlNode node in _worksheetXml.SelectNodes("//d:sheetData/d:row/d:c/d:f/@si", NameSpaceManager))
            {
                int curID = int.Parse(node.Value);
                if (curID > sharedID)
                {
                    sharedID = curID;
                }
            }
            sharedID++;  // first value must be zero

            for (int row = startCell.Row; row <= endCell.Row; row++)
            {
                for (int col = startCell.Column; col <= endCell.Column; col++)
                {
                    ExcelCell cell = Cell(row, col);

                    // to force Excel to re-calculate the formula, we must remove the value
                    cell.RemoveValue();

                    formulaElement = (XmlElement)cell.Element.SelectSingleNode("./d:f", NameSpaceManager);
                    if (formulaElement == null)
                    {
                        formulaElement = cell.AddFormulaElement();
                    }
                    formulaElement.SetAttribute("t", "shared");
                    formulaElement.SetAttribute("si", sharedID.ToString());
                }
            }

            // finally add the shared cell range to the startCell
            formulaElement = (XmlElement)startCell.Element.SelectSingleNode("./d:f", NameSpaceManager);
            formulaElement.SetAttribute("ref", string.Format("{0}:{1}", startCell.CellAddress, endCell.CellAddress));
        }