private void UpdateFormula(XSSFCell cell, IFormulaRenderingWorkbook frwb)
{
CT_CellFormula f = cell.GetCTCell().f;
if (f == null)
{
return;
}
string formula = f.Value;
if (formula == null || formula.Length <= 0)
{
return;
}
int sheetIndex = this._wb.GetSheetIndex(cell.Sheet);
Ptg[] ptgs = FormulaParser.Parse(formula, (IFormulaParsingWorkbook)this._fpwb, FormulaType.CELL, sheetIndex);
string formulaString = FormulaRenderer.ToFormulaString(frwb, ptgs);
if (formula.Equals(formulaString))
{
return;
}
f.Value = formulaString;
}
/// <summary>
/// Creates a non shared formula from the shared formula counterpart
/// </summary>
/// <param name="si">Shared Group Index</param>
/// <param name="fpb"></param>
/// <returns>non shared formula created for the given shared formula and this cell</returns>
private String ConvertSharedFormula(int si, XSSFEvaluationWorkbook fpb)
{
XSSFSheet sheet = (XSSFSheet)Sheet;
CT_CellFormula f = sheet.GetSharedFormula(si);
if (f == null)
{
throw new InvalidOperationException(
"Master cell of a shared formula with sid=" + si + " was not found");
}
String sharedFormula = f.Value;
//Range of cells which the shared formula applies to
String sharedFormulaRange = f.@ref;
CellRangeAddress ref1 = CellRangeAddress.ValueOf(sharedFormulaRange);
int sheetIndex = sheet.Workbook.GetSheetIndex(sheet);
SharedFormula sf = new SharedFormula(SpreadsheetVersion.EXCEL2007);
Ptg[] ptgs = FormulaParser.Parse(sharedFormula, fpb, FormulaType.Cell, sheetIndex, RowIndex);
Ptg[] fmla = sf.ConvertSharedFormulas(ptgs,
RowIndex - ref1.FirstRow, ColumnIndex - ref1.FirstColumn);
return(FormulaRenderer.ToFormulaString(fpb, fmla));
}
private void updateRowFormulas(XSSFRow row, FormulaShifter Shifter)
{
foreach (ICell c in row)
{
XSSFCell cell = (XSSFCell)c;
CT_Cell ctCell = cell.GetCTCell();
if (ctCell.IsSetF())
{
CT_CellFormula f = ctCell.f;
String formula = f.Value;
if (formula.Length > 0)
{
String ShiftedFormula = ShiftFormula(row, formula, Shifter);
if (ShiftedFormula != null)
{
f.Value = (ShiftedFormula);
}
}
if (f.isSetRef())
{ //Range of cells which the formula applies to.
String ref1 = f.@ref;
String ShiftedRef = ShiftFormula(row, ref1, Shifter);
if (ShiftedRef != null)
{
f.@ref = ShiftedRef;
}
}
}
}
}
private void SetFormula(String formula, FormulaType formulaType)
{
IWorkbook wb = _row.Sheet.Workbook;
if (formula == null)
{
((XSSFWorkbook)wb).OnDeleteFormula(this);
if (_cell.IsSetF())
{
_cell.unsetF();
}
return;
}
IFormulaParsingWorkbook fpb = XSSFEvaluationWorkbook.Create(wb);
//validate through the FormulaParser
FormulaParser.Parse(formula, fpb, formulaType, wb.GetSheetIndex(this.Sheet), -1);
CT_CellFormula f = new CT_CellFormula();
f.Value = formula;
_cell.f = (f);
if (_cell.IsSetV())
{
_cell.unsetV();
}
}
private void updateRowFormulas(XSSFRow row, FormulaShifter Shifter)
{
foreach (XSSFCell xssfCell in row)
{
CT_Cell ctCell = xssfCell.GetCTCell();
if (ctCell.IsSetF())
{
CT_CellFormula f = ctCell.f;
string formula1 = f.Value;
if (formula1.Length > 0)
{
string str = XSSFRowShifter.ShiftFormula(row, formula1, Shifter);
if (str != null)
{
f.Value = str;
}
}
if (f.isSetRef())
{
string formula2 = f.@ref;
string str = XSSFRowShifter.ShiftFormula(row, formula2, Shifter);
if (str != null)
{
f.@ref = str;
}
}
}
}
}
private void SetFormula(String formula, FormulaType formulaType)
{
XSSFWorkbook wb = (XSSFWorkbook)_row.Sheet.Workbook;
if (formula == null)
{
RemoveFormula();
return;
}
if (wb.CellFormulaValidation)
{
IFormulaParsingWorkbook fpb = XSSFEvaluationWorkbook.Create(wb);
//validate through the FormulaParser
FormulaParser.Parse(formula, fpb, formulaType, wb.GetSheetIndex(this.Sheet), RowIndex);
}
CT_CellFormula f = new CT_CellFormula();
f.Value = formula;
_cell.f = (f);
if (_cell.IsSetV())
{
_cell.unsetV();
}
}
internal void SetCellArrayFormula(string formula, CellRangeAddress range)
{
this.SetFormula(formula, FormulaType.ARRAY);
CT_CellFormula f = this._cell.f;
f.t = ST_CellFormulaType.array;
f.@ref = range.FormatAsString();
}
internal void SetCellArrayFormula(String formula, CellRangeAddress range)
{
SetFormula(formula, FormulaType.Array);
CT_CellFormula cellFormula = _cell.f;
cellFormula.t = (ST_CellFormulaType.array);
cellFormula.@ref = (range.FormatAsString());
}
//public CT_Cell()
//{
// this.extLstField = new CT_ExtensionList();
// //this.isField = new CT_Rst();
// //this.fField = new CT_CellFormula();
// this.sField = (uint)(0);
// this.tField = ST_CellType.n;
// this.cmField = ((uint)(0));
// this.vmField = ((uint)(0));
// this.phField = false;
//}
public void Set(CT_Cell cell)
{
fField = cell.fField;
vField = cell.vField;
isField = cell.isField;
extLstField = cell.extLstField;
rField = cell.rField;
sField = cell.sField;
tField = cell.tField;
cmField = cell.cmField;
vmField = cell.vmField;
phField = cell.phField;
}
private string ConvertSharedFormula(int si)
{
XSSFSheet sheet = (XSSFSheet)this.Sheet;
CT_CellFormula sharedFormula = sheet.GetSharedFormula(si);
if (sharedFormula == null)
{
throw new InvalidOperationException("Master cell of a shared formula with sid=" + (object)si + " was not found");
}
string formula = sharedFormula.Value;
CellRangeAddress cellRangeAddress = CellRangeAddress.ValueOf(sharedFormula.@ref);
int sheetIndex = sheet.Workbook.GetSheetIndex((ISheet)sheet);
XSSFEvaluationWorkbook evaluationWorkbook = XSSFEvaluationWorkbook.Create(sheet.Workbook);
Ptg[] ptgs = new SharedFormula(SpreadsheetVersion.EXCEL2007).ConvertSharedFormulas(FormulaParser.Parse(formula, (IFormulaParsingWorkbook)evaluationWorkbook, FormulaType.CELL, sheetIndex), this.RowIndex - cellRangeAddress.FirstRow, this.ColumnIndex - cellRangeAddress.FirstColumn);
return(FormulaRenderer.ToFormulaString((IFormulaRenderingWorkbook)evaluationWorkbook, ptgs));
}
/**
* Parse cell formula and re-assemble it back using the specified FormulaRenderingWorkbook.
*
* @param cell the cell to update
* @param frwb the formula rendering workbbok that returns new sheet name
*/
private void UpdateFormula(XSSFCell cell, IFormulaRenderingWorkbook frwb)
{
CT_CellFormula f = cell.GetCTCell().f;
if (f != null)
{
String formula = f.Value;
if (formula != null && formula.Length > 0)
{
int sheetIndex = _wb.GetSheetIndex(cell.Sheet);
Ptg[] ptgs = FormulaParser.Parse(formula, _fpwb, FormulaType.Cell, sheetIndex);
String updatedFormula = FormulaRenderer.ToFormulaString(frwb, ptgs);
if (!formula.Equals(updatedFormula))
{
f.Value = (updatedFormula);
}
}
}
}
/// <summary>
/// Update the formulas in specified row using the formula shifting policy specified by shifter
/// </summary>
/// <param name="row">the row to update the formulas on</param>
/// <param name="Shifter">the formula shifting policy</param>
public override void UpdateRowFormulas(IRow row, FormulaShifter Shifter)
{
XSSFSheet sheet = (XSSFSheet)row.Sheet;
foreach (ICell c in row)
{
XSSFCell cell = (XSSFCell)c;
CT_Cell ctCell = cell.GetCTCell();
if (ctCell.IsSetF())
{
CT_CellFormula f = ctCell.f;
String formula = f.Value;
if (formula.Length > 0)
{
String ShiftedFormula = ShiftFormula(row, formula, Shifter);
if (ShiftedFormula != null)
{
f.Value = (ShiftedFormula);
if (f.t == ST_CellFormulaType.shared)
{
int si = (int)f.si;
CT_CellFormula sf = sheet.GetSharedFormula(si);
sf.Value = (ShiftedFormula);
}
}
}
if (f.isSetRef())
{ //Range of cells which the formula applies to.
String ref1 = f.@ref;
String ShiftedRef = ShiftFormula(row, ref1, Shifter);
if (ShiftedRef != null)
{
f.@ref = ShiftedRef;
}
}
}
}
}
private static void ConfirmArrayFormulaCell(ICell c, String cellRef, String formulaText, String arrayRangeRef)
{
if (c == null)
{
throw new AssertionException("Cell should not be null.");
}
CT_Cell ctCell = ((XSSFCell)c).GetCTCell();
Assert.AreEqual(cellRef, ctCell.r);
if (formulaText == null)
{
Assert.IsFalse(ctCell.IsSetF());
Assert.IsNull(ctCell.f);
}
else
{
CT_CellFormula f = ctCell.f;
Assert.AreEqual(arrayRangeRef, f.@ref);
Assert.AreEqual(formulaText, f.Value);
Assert.AreEqual(ST_CellFormulaType.array, f.t);
}
}
public static CT_Cell Parse(XElement node, XmlNamespaceManager namespaceManager)
{
if (node == null)
{
return(null);
}
CT_Cell ctObj = new CT_Cell();
ctObj.r = XmlHelper.ReadString(node.Attribute("r"));
ctObj.s = XmlHelper.ReadUInt(node.Attribute("s"));
if (node.Attribute("t") != null)
{
ctObj.t = (ST_CellType)Enum.Parse(typeof(ST_CellType), node.Attribute("t").Value);
}
ctObj.cm = XmlHelper.ReadUInt(node.Attribute("cm"));
ctObj.vm = XmlHelper.ReadUInt(node.Attribute("vm"));
ctObj.ph = XmlHelper.ReadBool(node.Attribute("ph"));
foreach (XElement childNode in node.ChildElements())
{
if (childNode.Name.LocalName == "f")
{
ctObj.f = CT_CellFormula.Parse(childNode, namespaceManager);
}
else if (childNode.Name.LocalName == "v")
{
ctObj.v = childNode.Value;
}
else if (childNode.Name.LocalName == "is")
{
ctObj.@is = CT_Rst.Parse(childNode, namespaceManager);
}
else if (childNode.Name.LocalName == "extLst")
{
ctObj.extLst = CT_ExtensionList.Parse(childNode, namespaceManager);
}
}
return(ctObj);
}
public static CT_Cell Parse(XmlNode node, XmlNamespaceManager namespaceManager)
{
if (node == null)
{
return(null);
}
CT_Cell ctObj = new CT_Cell();
ctObj.r = XmlHelper.ReadString(node.Attributes["r"]);
ctObj.s = XmlHelper.ReadUInt(node.Attributes["s"]);
if (node.Attributes["t"] != null)
{
ctObj.t = (ST_CellType)Enum.Parse(typeof(ST_CellType), node.Attributes["t"].Value);
}
ctObj.cm = XmlHelper.ReadUInt(node.Attributes["cm"]);
ctObj.vm = XmlHelper.ReadUInt(node.Attributes["vm"]);
ctObj.ph = XmlHelper.ReadBool(node.Attributes["ph"]);
foreach (XmlNode childNode in node.ChildNodes)
{
if (childNode.LocalName == "f")
{
ctObj.f = CT_CellFormula.Parse(childNode, namespaceManager);
}
else if (childNode.LocalName == "v")
{
ctObj.v = childNode.InnerText;
}
else if (childNode.LocalName == "is")
{
ctObj.@is = CT_Rst.Parse(childNode, namespaceManager);
}
else if (childNode.LocalName == "extLst")
{
ctObj.extLst = CT_ExtensionList.Parse(childNode, namespaceManager);
}
}
return(ctObj);
}
/**
* package/hierarchy use only - reuse an existing evaluation workbook if available for caching
*
* @param fpb evaluation workbook for reuse, if available, or null to create a new one as needed
* @return a formula for the cell
* @throws InvalidOperationException if the cell type returned by {@link #getCellType()} is not CELL_TYPE_FORMULA
*/
protected internal String GetCellFormula(XSSFEvaluationWorkbook fpb)
{
CellType cellType = CellType;
if (cellType != CellType.Formula)
{
throw TypeMismatch(CellType.Formula, cellType, false);
}
CT_CellFormula f = _cell.f;
if (IsPartOfArrayFormulaGroup && f == null)
{
XSSFCell cell = ((XSSFSheet)Sheet).GetFirstCellInArrayFormula(this);
return(cell.GetCellFormula(fpb));
}
if (f.t == ST_CellFormulaType.shared)
{
//return ConvertSharedFormula((int)f.si);
return(ConvertSharedFormula((int)f.si, fpb == null ? XSSFEvaluationWorkbook.Create(Sheet.Workbook) : fpb));
}
return(f.Value);
}
/**
* Parse cell formula and re-assemble it back using the specified FormulaRenderingWorkbook.
*
* @param cell the cell to update
* @param frwb the formula rendering workbbok that returns new sheet name
*/
private void UpdateFormula(XSSFCell cell, String oldName, String newName)
{
CT_CellFormula f = cell.GetCTCell().f;
if (f != null)
{
String formula = f.Value;
if (formula != null && formula.Length > 0)
{
int sheetIndex = _wb.GetSheetIndex(cell.Sheet);
Ptg[] ptgs = FormulaParser.Parse(formula, _fpwb, FormulaType.Cell, sheetIndex, -1);
foreach (Ptg ptg in ptgs)
{
UpdatePtg(ptg, oldName, newName);
}
String updatedFormula = FormulaRenderer.ToFormulaString(_fpwb, ptgs);
if (!formula.Equals(updatedFormula))
{
f.Value = (updatedFormula);
}
}
}
}
/// <summary>
/// Set the cells type (numeric, formula or string)
/// </summary>
/// <param name="cellType"></param>
public void SetCellType(CellType cellType)
{
CellType prevType = CellType;
if (IsPartOfArrayFormulaGroup)
{
NotifyArrayFormulaChanging();
}
if (prevType == CellType.Formula && cellType != CellType.Formula)
{
((XSSFWorkbook)Sheet.Workbook).OnDeleteFormula(this);
}
switch (cellType)
{
case CellType.Blank:
SetBlank();
break;
case CellType.Boolean:
String newVal = ConvertCellValueToBoolean() ? TRUE_AS_STRING : FALSE_AS_STRING;
_cell.t = (ST_CellType.b);
_cell.v = (newVal);
break;
case CellType.Numeric:
_cell.t = (ST_CellType.n);
break;
case CellType.Error:
_cell.t = (ST_CellType.e);
break;
case CellType.String:
if (prevType != CellType.String)
{
String str = ConvertCellValueToString();
XSSFRichTextString rt = new XSSFRichTextString(str);
rt.SetStylesTableReference(_stylesSource);
int sRef = _sharedStringSource.AddEntry(rt.GetCTRst());
_cell.v = sRef.ToString();
}
_cell.t = (ST_CellType.s);
break;
case CellType.Formula:
if (!_cell.IsSetF())
{
CT_CellFormula f = new CT_CellFormula();
f.Value = "0";
_cell.f = (f);
if (_cell.IsSetT())
{
_cell.unsetT();
}
}
break;
default:
throw new ArgumentException("Illegal cell type: " + cellType);
}
if (cellType != CellType.Formula && _cell.IsSetF())
{
_cell.unsetF();
}
}
public void unsetF()
{
this.fField = null;
}