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