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