public List <Cell> GetAutoStartCells() { List <Cell> startCells = new List <Cell>(); List <Lbl> autoOpenLabels = wbs.GetAutoOpenLabels(); foreach (var autoOpenLbl in autoOpenLabels) { AbstractPtg peekPtg = autoOpenLbl.rgce.Peek(); switch (peekPtg) { //References a cell case PtgRef3d ptgRef3d: startCells.Add(new Cell(ptgRef3d.rw, ptgRef3d.col)); break; //References a different label case PtgName ptgName: Lbl referencedLabel = ResolveName(ptgName); while (referencedLabel.rgce.Peek() is PtgName) { referencedLabel = ResolveName(referencedLabel.rgce.Peek() as PtgName); } PtgRef3d ref3d = referencedLabel.rgce.Peek() as PtgRef3d; startCells.Add(new Cell(ref3d.rw, ref3d.col)); break; default: throw new NotImplementedException(); } } return(startCells); }
public static Stack <AbstractPtg> getFormulaStack(IStreamReader reader, ushort cce) { var ptgStack = new Stack <AbstractPtg>(); try { for (uint i = 0; i < cce; i++) { var ptgtype = (PtgNumber)reader.ReadByte(); AbstractPtg.PtgDataType dt = AbstractPtg.PtgDataType.REFERENCE; if ((int)ptgtype > 0x5D) { ptgtype -= 0x40; dt = AbstractPtg.PtgDataType.ARRAY; } else if ((int)ptgtype > 0x3D) { ptgtype -= 0x20; dt = AbstractPtg.PtgDataType.VALUE; } AbstractPtg ptg = null; if (ptgtype == PtgNumber.Ptg0x19Sub) { var ptgtype2 = (Ptg0x19Sub)reader.ReadByte(); switch (ptgtype2) { case Ptg0x19Sub.PtgAttrSum: ptg = new PtgAttrSum(reader, ptgtype2); break; case Ptg0x19Sub.PtgAttrIf: ptg = new PtgAttrIf(reader, ptgtype2); break; case Ptg0x19Sub.PtgAttrGoto: ptg = new PtgAttrGoto(reader, ptgtype2); break; case Ptg0x19Sub.PtgAttrSemi: ptg = new PtgAttrSemi(reader, ptgtype2); break; case Ptg0x19Sub.PtgAttrChoose: ptg = new PtgAttrChoose(reader, ptgtype2); break; case Ptg0x19Sub.PtgAttrSpace: ptg = new PtgAttrSpace(reader, ptgtype2); break; case Ptg0x19Sub.PtgAttrBaxcel1: ptg = new PtgAttrBaxcel(reader, ptgtype2, false); break; case Ptg0x19Sub.PtgAttrBaxcel2: ptg = new PtgAttrBaxcel(reader, ptgtype2, true); break; case Ptg0x19Sub.PtgNotDocumented: ptg = new PtgNotDocumented(reader, ptgtype2); break; default: break; } } else if (ptgtype == PtgNumber.Ptg0x18Sub) { } else { switch (ptgtype) { case PtgNumber.PtgInt: ptg = new PtgInt(reader, ptgtype); break; case PtgNumber.PtgAdd: ptg = new PtgAdd(reader, ptgtype); break; case PtgNumber.PtgSub: ptg = new PtgSub(reader, ptgtype); break; case PtgNumber.PtgMul: ptg = new PtgMul(reader, ptgtype); break; case PtgNumber.PtgDiv: ptg = new PtgDiv(reader, ptgtype); break; case PtgNumber.PtgParen: ptg = new PtgParen(reader, ptgtype); break; case PtgNumber.PtgNum: ptg = new PtgNum(reader, ptgtype); break; case PtgNumber.PtgArray: ptg = new PtgArray(reader, ptgtype); break; case PtgNumber.PtgRef: ptg = new PtgRef(reader, ptgtype); break; case PtgNumber.PtgRefN: ptg = new PtgRefN(reader, ptgtype); break; case PtgNumber.PtgPower: ptg = new PtgPower(reader, ptgtype); break; case PtgNumber.PtgPercent: ptg = new PtgPercent(reader, ptgtype); break; case PtgNumber.PtgBool: ptg = new PtgBool(reader, ptgtype); break; case PtgNumber.PtgGt: ptg = new PtgGt(reader, ptgtype); break; case PtgNumber.PtgGe: ptg = new PtgGe(reader, ptgtype); break; case PtgNumber.PtgLt: ptg = new PtgLt(reader, ptgtype); break; case PtgNumber.PtgLe: ptg = new PtgLe(reader, ptgtype); break; case PtgNumber.PtgEq: ptg = new PtgEq(reader, ptgtype); break; case PtgNumber.PtgNe: ptg = new PtgNe(reader, ptgtype); break; case PtgNumber.PtgUminus: ptg = new PtgUminus(reader, ptgtype); break; case PtgNumber.PtgUplus: ptg = new PtgUplus(reader, ptgtype); break; case PtgNumber.PtgStr: ptg = new PtgStr(reader, ptgtype); break; case PtgNumber.PtgConcat: ptg = new PtgConcat(reader, ptgtype); break; case PtgNumber.PtgUnion: ptg = new PtgUnion(reader, ptgtype); break; case PtgNumber.PtgIsect: ptg = new PtgIsect(reader, ptgtype); break; case PtgNumber.PtgMemErr: ptg = new PtgMemErr(reader, ptgtype); break; case PtgNumber.PtgArea: ptg = new PtgArea(reader, ptgtype); break; case PtgNumber.PtgAreaN: ptg = new PtgAreaN(reader, ptgtype); break; case PtgNumber.PtgFuncVar: ptg = new PtgFuncVar(reader, ptgtype); break; case PtgNumber.PtgFunc: ptg = new PtgFunc(reader, ptgtype); break; case PtgNumber.PtgExp: ptg = new PtgExp(reader, ptgtype); break; case PtgNumber.PtgRef3d: ptg = new PtgRef3d(reader, ptgtype); break; case PtgNumber.PtgArea3d: ptg = new PtgArea3d(reader, ptgtype); break; case PtgNumber.PtgNameX: ptg = new PtgNameX(reader, ptgtype); break; case PtgNumber.PtgName: ptg = new PtgName(reader, ptgtype); break; case PtgNumber.PtgMissArg: ptg = new PtgMissArg(reader, ptgtype); break; case PtgNumber.PtgRefErr: ptg = new PtgRefErr(reader, ptgtype); break; case PtgNumber.PtgRefErr3d: ptg = new PtgRefErr3d(reader, ptgtype); break; case PtgNumber.PtgAreaErr: ptg = new PtgAreaErr(reader, ptgtype); break; case PtgNumber.PtgAreaErr3d: ptg = new PtgAreaErr3d(reader, ptgtype); break; case PtgNumber.PtgMemFunc: ptg = new PtgMemFunc(reader, ptgtype); break; case PtgNumber.PtgErr: ptg = new PtgErr(reader, ptgtype); break; default: break; } } i += ptg.getLength() - 1; ptg.dataType = dt; ptgStack.Push(ptg); } } catch (Exception ex) { throw new ExtractorException(ExtractorException.PARSEDFORMULAEXCEPTION, ex); } return(ptgStack); }
public void TestMultiColumnMacroImport() { List <String> simpleMacros = new List <string>() { "A1;B1;C1", "A2;B2;C2", "A3;B3", "A4" }; List <BiffRecord> records = FormulaHelper.ConvertStringsToRecords(simpleMacros, 0, 0, 0, 1); int formulaOffset = 0; for (int recOffset = 3; recOffset < records.Count; recOffset += 4) { Formula f = records[recOffset].AsRecordType <Formula>(); AbstractPtg formulaFunctionPtg = f.ptgStack.ToList()[0]; AbstractPtg dstPtgRef = f.ptgStack.ToList()[1]; AbstractPtg srcPtgRef = f.ptgStack.ToList()[2]; PtgRef dst = (PtgRef)dstPtgRef; int targetRow = dst.rw; int targetCol = dst.col & 0xFF; switch (formulaOffset) { case 0: Assert.AreEqual(0, targetRow); Assert.AreEqual(1, targetCol); break; case 1: Assert.AreEqual(0, targetRow); Assert.AreEqual(2, targetCol); break; case 2: Assert.AreEqual(0, targetRow); Assert.AreEqual(3, targetCol); break; case 3: Assert.AreEqual(1, targetRow); Assert.AreEqual(1, targetCol); break; case 4: Assert.AreEqual(1, targetRow); Assert.AreEqual(2, targetCol); break; case 5: Assert.AreEqual(1, targetRow); Assert.AreEqual(3, targetCol); break; case 6: Assert.AreEqual(2, targetRow); Assert.AreEqual(1, targetCol); break; case 7: Assert.AreEqual(2, targetRow); Assert.AreEqual(2, targetCol); break; case 8: Assert.AreEqual(3, targetRow); Assert.AreEqual(1, targetCol); break; default: Assert.Fail("Too many records generated"); break; } formulaOffset += 1; } }
/// <summary> /// This static method is used to convert the ptgStack to the infixnotation /// This method changes every Ptg***N** PtgRecord from a shared formula /// </summary> /// <param name="stack"></param> /// <param name="xlsContext"></param> /// <param name="rw">Row from the shared formula</param> /// <param name="col">Column from the shared formula</param> /// <returns></returns> public static string mapFormula(Stack <AbstractPtg> stack, ExcelContext xlsContext, int rw, int col) { Stack <string> resultStack = new Stack <string>(); try { Stack <AbstractPtg> opStack = new Stack <AbstractPtg>(stack); if (opStack.Count == 0) { throw new Exception(); } while (opStack.Count != 0) { AbstractPtg ptg = opStack.Pop(); if (ptg is PtgAttrSum) { string buffer = ""; PtgAttrSum ptgref = (PtgAttrSum)ptg; buffer = ptg.getData() + "(" + resultStack.Pop() + ")"; resultStack.Push(buffer); } else if (ptg is PtgAttrIf || ptg is PtgAttrGoto || ptg is PtgAttrSemi || ptg is PtgAttrChoose || ptg is PtgAttrSpace) { } else { switch (ptg.Id) { case PtgNumber.PtgInt: case PtgNumber.PtgNum: case PtgNumber.PtgBool: case PtgNumber.PtgMissArg: case PtgNumber.PtgErr: { resultStack.Push(ptg.getData()); } break; case PtgNumber.PtgRef: { PtgRef ptgref = (PtgRef)ptg; int realCol = ptgref.col; int realRw = ptgref.rw + 1; if (ptgref.colRelative) { realCol += col; } if (ptgref.rwRelative) { realRw += rw; } resultStack.Push(ExcelHelperClass.intToABCString(realCol, (realRw).ToString(), ptgref.colRelative, ptgref.rwRelative)); } break; case PtgNumber.PtgRefN: { PtgRefN ptgrefn = (PtgRefN)ptg; int realCol = (int)ptgrefn.col; int realRw; if (ptgrefn.colRelative) { realCol += col; } if (realCol >= 0xFF) { realCol -= 0x0100; } if (ptgrefn.rwRelative) { realRw = (Int16)ptgrefn.rw + 1; realRw += rw; } else { realRw = ptgrefn.rw + 1; } resultStack.Push(ExcelHelperClass.intToABCString(realCol, (realRw).ToString(), ptgrefn.colRelative, ptgrefn.rwRelative)); } break; case PtgNumber.PtgUplus: case PtgNumber.PtgUminus: { string buffer = ""; if (ptg.PopSize() > resultStack.Count) { throw new ExtractorException(ExtractorException.PARSEDFORMULAEXCEPTION); } buffer = ptg.getData() + resultStack.Pop(); resultStack.Push(buffer); } break; case PtgNumber.PtgParen: { string buffer = ""; if (ptg.PopSize() > resultStack.Count) { throw new ExtractorException(ExtractorException.PARSEDFORMULAEXCEPTION); } buffer = "(" + resultStack.Pop() + ")"; resultStack.Push(buffer); } break; case PtgNumber.PtgPercent: { string buffer = ""; if (ptg.PopSize() > resultStack.Count) { throw new ExtractorException(ExtractorException.PARSEDFORMULAEXCEPTION); } buffer = resultStack.Pop() + ptg.getData(); resultStack.Push(buffer); } break; case PtgNumber.PtgAdd: case PtgNumber.PtgDiv: case PtgNumber.PtgMul: case PtgNumber.PtgSub: case PtgNumber.PtgPower: case PtgNumber.PtgGt: case PtgNumber.PtgGe: case PtgNumber.PtgLt: case PtgNumber.PtgLe: case PtgNumber.PtgEq: case PtgNumber.PtgNe: case PtgNumber.PtgConcat: case PtgNumber.PtgUnion: case PtgNumber.PtgIsect: { string buffer = ""; if (ptg.PopSize() > resultStack.Count) { throw new ExtractorException(ExtractorException.PARSEDFORMULAEXCEPTION); } buffer = ptg.getData() + resultStack.Pop(); buffer = resultStack.Pop() + buffer; resultStack.Push(buffer); } break; case PtgNumber.PtgStr: { resultStack.Push("\"" + ptg.getData() + "\""); } break; case PtgNumber.PtgArea: { string buffer = ""; PtgArea ptgarea = (PtgArea)ptg; buffer = ExcelHelperClass.intToABCString((int)ptgarea.colFirst, (ptgarea.rwFirst + 1).ToString(), ptgarea.colFirstRelative, ptgarea.rwFirstRelative); buffer = buffer + ":" + ExcelHelperClass.intToABCString((int)ptgarea.colLast, (ptgarea.rwLast + 1).ToString(), ptgarea.colLastRelative, ptgarea.rwLastRelative); resultStack.Push(buffer); } break; case PtgNumber.PtgAreaN: { string buffer = ""; PtgAreaN ptgarean = (PtgAreaN)ptg; int realRwFirst; int realRwLast; int realColFirst = (int)ptgarean.colFirst; int realColLast = (int)ptgarean.colLast; if (ptgarean.colFirstRelative) { realColFirst += col; } if (realColFirst >= 0xFF) { realColFirst -= 0x0100; } if (ptgarean.colLastRelative) { realColLast += col; } if (realColLast >= 0xFF) { realColLast -= 0x0100; } if (ptgarean.rwFirstRelative) { realRwFirst = (Int16)ptgarean.rwFirst + 1; realRwFirst += rw; } else { realRwFirst = ptgarean.rwFirst + 1; } if (ptgarean.rwLastRelative) { realRwLast = (Int16)ptgarean.rwLast + 1; realRwLast += rw; } else { realRwLast = ptgarean.rwLast + 1; } buffer = ExcelHelperClass.intToABCString(realColFirst, (realRwFirst).ToString(), ptgarean.colFirstRelative, ptgarean.rwFirstRelative); buffer = buffer + ":" + ExcelHelperClass.intToABCString(realColLast, (realRwLast).ToString(), ptgarean.colLastRelative, ptgarean.rwLastRelative); resultStack.Push(buffer); } break; case PtgNumber.PtgExp: { PtgExp ptgexp = (PtgExp)ptg; Stack <AbstractPtg> newptgstack = ((WorkSheetData)xlsContext.CurrentSheet).getArrayData(ptgexp.rw, ptgexp.col); resultStack.Push(FormulaInfixMapping.mapFormula(newptgstack, xlsContext)); ((WorkSheetData)xlsContext.CurrentSheet).setFormulaUsesArray(ptgexp.rw, ptgexp.col); } break; case PtgNumber.PtgRef3d: { try { PtgRef3d ptgr3d = (PtgRef3d)ptg; string refstring = ExcelHelperClass.EscapeFormulaString(xlsContext.XlsDoc.WorkBookData.getIXTIString(ptgr3d.ixti)); refstring = string.IsNullOrEmpty(refstring) ? "" : "'" + refstring + "'" + "!"; string cellref = ExcelHelperClass.intToABCString((int)ptgr3d.col, (ptgr3d.rw + 1).ToString(), ptgr3d.colRelative, ptgr3d.rwRelative); resultStack.Push(refstring + cellref); } catch (Exception) { resultStack.Push("#REF!"); } } break; case PtgNumber.PtgArea3d: { try { PtgArea3d ptga3d = (PtgArea3d)ptg; string refstring = ExcelHelperClass.EscapeFormulaString(xlsContext.XlsDoc.WorkBookData.getIXTIString(ptga3d.ixti)); refstring = string.IsNullOrEmpty(refstring) ? "" : "'" + refstring + "'" + "!"; string buffer = ""; buffer = ExcelHelperClass.intToABCString((int)ptga3d.colFirst, (ptga3d.rwFirst + 1).ToString(), ptga3d.colFirstRelative, ptga3d.rwFirstRelative); buffer = buffer + ":" + ExcelHelperClass.intToABCString((int)ptga3d.colLast, (ptga3d.rwLast + 1).ToString(), ptga3d.colLastRelative, ptga3d.rwLastRelative); resultStack.Push(refstring + buffer); } catch (Exception) { resultStack.Push("#REF!"); } } break; case PtgNumber.PtgNameX: { PtgNameX ptgnx = (PtgNameX)ptg; string opstring = xlsContext.XlsDoc.WorkBookData.getExternNameByRef(ptgnx.ixti, ptgnx.nameindex); resultStack.Push(opstring); } break; case PtgNumber.PtgName: { PtgName ptgn = (PtgName)ptg; string opstring = xlsContext.XlsDoc.WorkBookData.getDefinedNameByRef(ptgn.nameindex); resultStack.Push(opstring); } break; case PtgNumber.PtgRefErr: { PtgRefErr ptgreferr = (PtgRefErr)ptg; resultStack.Push(ptgreferr.getData()); } break; case PtgNumber.PtgRefErr3d: { try { PtgRefErr3d ptgreferr3d = (PtgRefErr3d)ptg; string refstring = ExcelHelperClass.EscapeFormulaString(xlsContext.XlsDoc.WorkBookData.getIXTIString(ptgreferr3d.ixti)); refstring = string.IsNullOrEmpty(refstring) ? "" : "'" + refstring + "'" + "!"; resultStack.Push(refstring + ptgreferr3d.getData()); } catch (Exception) { resultStack.Push("#REF!"); } } break; case PtgNumber.PtgAreaErr3d: { try { PtgAreaErr3d ptgareaerr3d = (PtgAreaErr3d)ptg; string refstring = ExcelHelperClass.EscapeFormulaString(xlsContext.XlsDoc.WorkBookData.getIXTIString(ptgareaerr3d.ixti)); refstring = string.IsNullOrEmpty(refstring) ? "" : "'" + refstring + "'" + "!"; resultStack.Push(refstring + ptgareaerr3d.getData()); } catch (Exception) { resultStack.Push("#REF!"); } } break; case PtgNumber.PtgFunc: { PtgFunc ptgf = (PtgFunc)ptg; FtabValues value = (FtabValues)ptgf.tab; string buffer = value.ToString(); buffer.Replace("_", "."); // no param if (value == FtabValues.NA || value == FtabValues.PI || value == FtabValues.TRUE || value == FtabValues.FALSE || value == FtabValues.RAND || value == FtabValues.NOW || value == FtabValues.TODAY ) { buffer += "()"; } // One param else if (value == FtabValues.ISNA || value == FtabValues.ISERROR || value == FtabValues.SIN || value == FtabValues.COS || value == FtabValues.TAN || value == FtabValues.ATAN || value == FtabValues.SQRT || value == FtabValues.EXP || value == FtabValues.LN || value == FtabValues.LOG10 || value == FtabValues.ABS || value == FtabValues.INT || value == FtabValues.SIGN || value == FtabValues.LEN || value == FtabValues.VALUE || value == FtabValues.NOT || value == FtabValues.DAY || value == FtabValues.MONTH || value == FtabValues.YEAR || value == FtabValues.HOUR || value == FtabValues.MINUTE || value == FtabValues.SECOND || value == FtabValues.AREAS || value == FtabValues.ROWS || value == FtabValues.COLUMNS || value == FtabValues.TRANSPOSE || value == FtabValues.TYPE || value == FtabValues.ASIN || value == FtabValues.ACOS || value == FtabValues.ISREF || value == FtabValues.CHAR || value == FtabValues.LOWER || value == FtabValues.UPPER || value == FtabValues.PROPER || value == FtabValues.TRIM || value == FtabValues.CODE || value == FtabValues.ISERR || value == FtabValues.ISTEXT || value == FtabValues.ISNUMBER || value == FtabValues.ISBLANK || value == FtabValues.T || value == FtabValues.N || value == FtabValues.DATEVALUE || value == FtabValues.TIMEVALUE || value == FtabValues.CLEAN || value == FtabValues.MDETERM || value == FtabValues.MINVERSE || value == FtabValues.FACT || value == FtabValues.ISNONTEXT || value == FtabValues.ISLOGICAL || value == FtabValues.LENB || value == FtabValues.DBCS || value == FtabValues.SINH || value == FtabValues.COSH || value == FtabValues.TANH || value == FtabValues.ASINH || value == FtabValues.ACOSH || value == FtabValues.ATANH || value == FtabValues.INFO || value == FtabValues.ERROR_TYPE || value == FtabValues.GAMMALN || value == FtabValues.EVEN || value == FtabValues.FISHER || value == FtabValues.FISHERINV || value == FtabValues.NORMSDIST || value == FtabValues.NORMSINV || value == FtabValues.ODD || value == FtabValues.RADIANS || value == FtabValues.DEGREES || value == FtabValues.COUNTBLANK || value == FtabValues.DATESTRING || value == FtabValues.PHONETIC ) { buffer += "(" + resultStack.Pop() + ")"; } // two params else if (value == FtabValues.ROUND || value == FtabValues.REPT || value == FtabValues.MOD || value == FtabValues.TEXT || value == FtabValues.ATAN2 || value == FtabValues.EXACT || value == FtabValues.MMULT || value == FtabValues.ROUNDUP || value == FtabValues.ROUNDDOWN || value == FtabValues.FREQUENCY || value == FtabValues.CHIDIST || value == FtabValues.CHIINV || value == FtabValues.COMBIN || value == FtabValues.FLOOR || value == FtabValues.CEILING || value == FtabValues.PERMUT || value == FtabValues.SUMXMY2 || value == FtabValues.SUMX2MY2 || value == FtabValues.SUMX2PY2 || value == FtabValues.CHITEST || value == FtabValues.CORREL || value == FtabValues.COVAR || value == FtabValues.FTEST || value == FtabValues.INTERCEPT || value == FtabValues.PEARSON || value == FtabValues.RSQ || value == FtabValues.STEYX || value == FtabValues.SLOPE || value == FtabValues.LARGE || value == FtabValues.SMALL || value == FtabValues.QUARTILE || value == FtabValues.PERCENTILE || value == FtabValues.TRIMMEAN || value == FtabValues.TINV || value == FtabValues.POWER || value == FtabValues.COUNTIF || value == FtabValues.NUMBERSTRING ) { buffer += "("; string buffer2 = resultStack.Pop(); buffer2 = resultStack.Pop() + "," + buffer2; buffer += buffer2 + ")"; } // Three params else if (value == FtabValues.MID || value == FtabValues.DCOUNT || value == FtabValues.DSUM || value == FtabValues.DAVERAGE || value == FtabValues.DMIN || value == FtabValues.DMAX || value == FtabValues.DSTDEV || value == FtabValues.DVAR || value == FtabValues.MIRR || value == FtabValues.DATE || value == FtabValues.TIME || value == FtabValues.SLN || value == FtabValues.DPRODUCT || value == FtabValues.DSTDEVP || value == FtabValues.DVARP || value == FtabValues.DCOUNTA || value == FtabValues.MIDB || value == FtabValues.DGET || value == FtabValues.CONFIDENCE || value == FtabValues.CRITBINOM || value == FtabValues.EXPONDIST || value == FtabValues.FDIST || value == FtabValues.FINV || value == FtabValues.GAMMAINV || value == FtabValues.LOGNORMDIST || value == FtabValues.LOGINV || value == FtabValues.NEGBINOMDIST || value == FtabValues.NORMINV || value == FtabValues.STANDARDIZE || value == FtabValues.POISSON || value == FtabValues.TDIST || value == FtabValues.FORECAST ) { buffer += "("; string buffer2 = resultStack.Pop(); buffer2 = resultStack.Pop() + "," + buffer2; buffer2 = resultStack.Pop() + "," + buffer2; buffer += buffer2 + ")"; } // four params else if (value == FtabValues.REPLACE || value == FtabValues.SYD || value == FtabValues.REPLACEB || value == FtabValues.BINOMDIST || value == FtabValues.GAMMADIST || value == FtabValues.HYPGEOMDIST || value == FtabValues.NORMDIST || value == FtabValues.WEIBULL || value == FtabValues.TTEST || value == FtabValues.ISPMT ) { buffer += "("; string buffer2 = resultStack.Pop(); buffer2 = resultStack.Pop() + "," + buffer2; buffer2 = resultStack.Pop() + "," + buffer2; buffer2 = resultStack.Pop() + "," + buffer2; buffer += buffer2 + ")"; } if ((int)value != 0xff) { } resultStack.Push(buffer); } break; case PtgNumber.PtgFuncVar: { PtgFuncVar ptgfv = (PtgFuncVar)ptg; // is Ftab or Cetab if (!ptgfv.fCelFunc) { FtabValues value = (FtabValues)ptgfv.tab; string buffer = value.ToString(); buffer.Replace("_", "."); // 1 to x parameter if (value == FtabValues.COUNT || value == FtabValues.IF || value == FtabValues.ISNA || value == FtabValues.ISERROR || value == FtabValues.AVERAGE || value == FtabValues.MAX || value == FtabValues.MIN || value == FtabValues.SUM || value == FtabValues.ROW || value == FtabValues.COLUMN || value == FtabValues.NPV || value == FtabValues.STDEV || value == FtabValues.DOLLAR || value == FtabValues.FIXED || value == FtabValues.SIN || value == FtabValues.COS || value == FtabValues.LOOKUP || value == FtabValues.INDEX || value == FtabValues.AND || value == FtabValues.OR || value == FtabValues.VAR || value == FtabValues.LINEST || value == FtabValues.TREND || value == FtabValues.LOGEST || value == FtabValues.GROWTH || value == FtabValues.PV || value == FtabValues.FV || value == FtabValues.NPER || value == FtabValues.PMT || value == FtabValues.RATE || value == FtabValues.IRR || value == FtabValues.MATCH || value == FtabValues.WEEKDAY || value == FtabValues.OFFSET || value == FtabValues.ARGUMENT || value == FtabValues.SEARCH || value == FtabValues.CHOOSE || value == FtabValues.HLOOKUP || value == FtabValues.VLOOKUP || value == FtabValues.LOG || value == FtabValues.LEFT || value == FtabValues.RIGHT || value == FtabValues.SUBSTITUTE || value == FtabValues.FIND || value == FtabValues.CELL || value == FtabValues.DDB || value == FtabValues.INDIRECT || value == FtabValues.IPMT || value == FtabValues.PPMT || value == FtabValues.COUNTA || value == FtabValues.PRODUCT || value == FtabValues.STDEVP || value == FtabValues.VARP || value == FtabValues.TRUNC || value == FtabValues.USDOLLAR || value == FtabValues.FINDB || value == FtabValues.SEARCHB || value == FtabValues.LEFTB || value == FtabValues.RIGHTB || value == FtabValues.RANK || value == FtabValues.ADDRESS || value == FtabValues.DAYS360 || value == FtabValues.VDB || value == FtabValues.MEDIAN || value == FtabValues.PRODUCT || value == FtabValues.DB || value == FtabValues.AVEDEV || value == FtabValues.BETADIST || value == FtabValues.BETAINV || value == FtabValues.PROB || value == FtabValues.DEVSQ || value == FtabValues.GEOMEAN || value == FtabValues.HARMEAN || value == FtabValues.SUMSQ || value == FtabValues.KURT || value == FtabValues.SKEW || value == FtabValues.ZTEST || value == FtabValues.PERCENTRANK || value == FtabValues.MODE || value == FtabValues.CONCATENATE || value == FtabValues.SUBTOTAL || value == FtabValues.SUMIF || value == FtabValues.ROMAN || value == FtabValues.GETPIVOTDATA || value == FtabValues.HYPERLINK || value == FtabValues.AVERAGEA || value == FtabValues.MAXA || value == FtabValues.MINA || value == FtabValues.STDEVPA || value == FtabValues.VARPA || value == FtabValues.STDEVA || value == FtabValues.VARA ) { buffer += "("; if (ptgfv.cparams > 0) { string buffer2 = resultStack.Pop(); for (int i = 1; i < ptgfv.cparams; i++) { buffer2 = resultStack.Pop() + "," + buffer2; } buffer += buffer2 + ")"; } resultStack.Push(buffer); } else if ((int)value == 0xFF) { buffer = "("; string buffer2 = resultStack.Pop(); for (int i = 1; i < ptgfv.cparams - 1; i++) { buffer2 = resultStack.Pop() + "," + buffer2; } buffer += buffer2 + ")"; // take the additional Operator from the Operandstack buffer = resultStack.Pop() + buffer; resultStack.Push(buffer); } } } break; case PtgNumber.PtgMemFunc: { string value = FormulaInfixMapping.mapFormula(((PtgMemFunc)ptg).ptgStack, xlsContext); resultStack.Push(value); } break; case PtgNumber.PtgArray: { PtgExtraArray ptgExtraArray = ((PtgArray)ptg).ptgExtraArray; StringBuilder builder = new StringBuilder(); builder.Append("{"); string last = null; foreach (SerAr item in ptgExtraArray.array) { if (last != null) { builder.Append(last); builder.Append(";"); } last = item.ToString(); } if (last != null) { builder.Append(last); } builder.Append("}"); resultStack.Push(builder.ToString()); } break; } } } } catch (Exception ex) { TraceLogger.DebugInternal(ex.Message.ToString()); resultStack.Push(""); } if (resultStack.Count == 0) { resultStack.Push(""); } return(resultStack.Pop()); }
private static string GetFormulaStringInner(ref Stack <AbstractPtg> ptgStack, bool showAttributes = false) { AbstractPtg nextPtg = ptgStack.Pop(); if (nextPtg.OpType() == PtgType.Operator) { uint popSize = nextPtg.PopSize(); switch (nextPtg) { case PtgFunc ptgFunc: int numParams = PtgFunc.GetParamCountFromFtabValue(ptgFunc.Ftab); string formulaString = ptgFunc.Ftab.ToString().Replace('_', '.') + "("; List <string> args = GetFormulaStringFunctionArgs(ref ptgStack, numParams, showAttributes); formulaString += string.Join(",", args); formulaString += ")"; return(formulaString); case PtgFuncVar ptgFuncVar: string formulaVariableString = ""; if (ptgFuncVar.fCelFunc) { formulaVariableString += ptgFuncVar.Cetab.ToString().Replace('_', '.'); } else { formulaVariableString += ptgFuncVar.Ftab.ToString().Replace('_', '.'); } //Handle special case for user defined function invocation if (formulaVariableString.Equals(FtabValues.USERDEFINEDFUNCTION.ToString())) { //Pop the args off first, then we can retrieve the function name List <string> varArgs = GetFormulaStringFunctionArgs(ref ptgStack, ptgFuncVar.cparams - 1, showAttributes); //This should be a PtgName entry formulaVariableString = GetFormulaStringInner(ref ptgStack, showAttributes); formulaVariableString += "("; formulaVariableString += string.Join(",", varArgs); formulaVariableString += ")"; } else { formulaVariableString += "("; List <string> varArgs = GetFormulaStringFunctionArgs(ref ptgStack, ptgFuncVar.cparams, showAttributes); formulaVariableString += string.Join(",", varArgs); formulaVariableString += ")"; } return(formulaVariableString); case PtgAttrBaxcel attrBaxcel: if (showAttributes) { return(string.Format("[AttrBaxcel:{0}]", attrBaxcel.DataBytes) + GetFormulaStringInner(ref ptgStack, showAttributes)); } else { return(GetFormulaStringInner(ref ptgStack, showAttributes)); } case PtgAttrSpace attrSpace: if (showAttributes) { return(string.Format("[AttrSpace:{0}", attrSpace.PtgAttrSpaceType + GetFormulaStringInner(ref ptgStack, showAttributes))); } else { return(GetFormulaStringInner(ref ptgStack, showAttributes)); } case PtgAttrGoto attrGoto: if (showAttributes) { return(string.Format("[AttrGoto:{0}]", attrGoto.PtgOffset) + GetFormulaStringInner(ref ptgStack, showAttributes)); } else { return(GetFormulaStringInner(ref ptgStack, showAttributes)); } case PtgAttrIf attrIf: if (showAttributes) { return(string.Format("[AttrIf:{0}]", attrIf.PtgOffset) + GetFormulaStringInner(ref ptgStack, showAttributes)); } else { return(GetFormulaStringInner(ref ptgStack, showAttributes)); } case PtgName ptgName: return(ptgName.ToString()); default: //Special case for operators if (nextPtg is PtgParen) { return(string.Format("({0})", GetFormulaStringInner(ref ptgStack, showAttributes))); } //If this is a simple operator like + or - if (nextPtg.getLength() == 1) { //We pop the arguments off the stack in reverse order, hence the 'backwards' ordering string formatString = "{2}{1}{0}"; return(string.Format(formatString, GetFormulaStringInner(ref ptgStack, showAttributes), nextPtg.getData(), GetFormulaStringInner(ref ptgStack, showAttributes))); } throw new NotImplementedException(string.Format("Ptg type {0} not implemented for ToString", nextPtg.Id)); } } else { switch (nextPtg) { case PtgRef ptgRef: return(ptgRef.ToString()); case PtgArea ptgArea: return(ptgArea.ToString()); case PtgStr ptgStr: return(string.Format("\"{0}\"", ptgStr.getData())); default: return(nextPtg.getData()); } } }
private static byte[] GetByteDataFromPtg(AbstractPtg ptg) { MemoryStream ms = new MemoryStream(); BinaryWriter bw = new BinaryWriter(ms); switch (ptg) { case PtgInt ptgInt: bw.Write(Convert.ToUInt16(ptg.getData())); break; case PtgArray ptgArray: bw.Write(ptgArray.DataBytes); break; case PtgAdd ptgAdd: case PtgSub ptgSub: case PtgMul ptgMul: case PtgDiv ptgDiv: case PtgParen ptgParen: case PtgPower ptgPower: case PtgPercent ptgPercent: case PtgGt ptgGt: case PtgGe ptgGe: case PtgLt ptgLt: case PtgLe ptgLe: case PtgEq ptgEq: case PtgNe ptgNe: case PtgUminus ptgUminus: case PtgUplus ptgUplus: case PtgConcat ptgConcat: case PtgUnion ptgUnion: case PtgIsect ptgIsect: case PtgMissArg ptgMissArg: //No Data break; case PtgNum ptgNum: bw.Write(Convert.ToDouble(ptg.getData(), CultureInfo.GetCultureInfo("en-US"))); break; case PtgRef ptgRef: RgceLoc loc = new RgceLoc(ptgRef.rw, ptgRef.col, ptgRef.colRelative, ptgRef.rwRelative); bw.Write(loc.Bytes); break; case PtgRefN ptgRefN: RgceLoc loc2 = new RgceLoc(Convert.ToUInt16(ptgRefN.rw), Convert.ToUInt16(ptgRefN.col), ptgRefN.colRelative, ptgRefN.rwRelative); bw.Write(loc2.Bytes); break; case PtgBool ptgBool: string boolData = ptgBool.getData(); bw.Write((boolData.Equals("TRUE") ? (byte)1 : (byte)0)); break; case PtgStr ptgStr: string unescapedString = ExcelHelperClass.UnescapeFormulaString(ptgStr.getData()); ShortXLUnicodeString ptgString = new ShortXLUnicodeString(unescapedString, ptgStr.isUnicode); bw.Write(ptgString.Bytes); break; case PtgFuncVar ptgFuncVar: bw.Write(ptgFuncVar.cparams); ushort tab = ptgFuncVar.tab; ushort fCeFunc = ptgFuncVar.fCelFunc ? (ushort)0x8000 : (ushort)0; tab = (ushort)(tab | fCeFunc); bw.Write(tab); break; case PtgFunc ptgFunc: bw.Write(Convert.ToUInt16(ptgFunc.tab)); break; case PtgExp ptgExp: RgceLoc loc3 = new RgceLoc(Convert.ToUInt16(ptgExp.rw), Convert.ToUInt16(ptgExp.col), false, false); bw.Write(loc3.Bytes); break; case PtgRef3d ptgRef3d: RgceLoc loc4 = new RgceLoc(Convert.ToUInt16(ptgRef3d.rw), Convert.ToUInt16(ptgRef3d.col), ptgRef3d.colRelative, ptgRef3d.rwRelative); bw.Write(Convert.ToUInt16(ptgRef3d.ixti)); bw.Write(loc4.Bytes); break; case PtgAttrBaxcel ptgAttrBaxcel: bw.Write(ptgAttrBaxcel.DataBytes); break; case PtgAttrIf ptgAttrIf: bw.Write(ptgAttrIf.Offset); break; case PtgAttrGoto ptgAttrGoto: bw.Write(ptgAttrGoto.Offset); break; case PtgAttrSpace ptgAttrSpace: bw.Write(ptgAttrSpace.PtgAttrSpaceType); break; case PtgName ptgName: bw.Write(ptgName.nameindex); break; case PtgArea ptgArea: bw.Write(ptgArea.rwFirst); bw.Write(ptgArea.rwLast); bw.Write(ptgArea.colFirst); bw.Write(ptgArea.colLast); break; case PtgErr ptgErr: bw.Write(ptgErr.Err); break; case PtgAttrSum ptgAttrSum: //Start 0x19 ## Section case PtgAttrSemi ptgAttrSemi: case PtgAttrChoose ptgAttrChoose: case PtgNotDocumented ptgNotDocumented: case PtgMemErr ptgMemErr: case PtgAreaN ptgAreaN: case PtgArea3d ptgArea3d: case PtgNameX ptgNameX: case PtgRefErr ptgRefErr: case PtgRefErr3d ptgRefErr3d: case PtgAreaErr ptgAreaErr: case PtgAreaErr3d ptgAreaErr3d: case PtgMemFunc ptgMemFunc: default: throw new NotImplementedException(string.Format("No byte conversion implemented for {0}", ptg)); } return(bw.GetBytesWritten()); }
public void TestMultiColumnMacroImport() { List <String> simpleMacros = new List <string>() { "A1;B1;C1", "A2;B2;C2", "A3;B3", "A4" }; //The column separator character may change, so replace the old column separator with the newer internal one simpleMacros = simpleMacros.Select(s => s.Replace(";", MacroPatterns.MacroColumnSeparator)).ToList(); List <BiffRecord> records = FormulaHelper.ConvertStringsToRecords(simpleMacros, 0, 0, 0, 1); int formulaOffset = 0; for (int recOffset = 3; recOffset < records.Count; recOffset += 4) { Formula f = records[recOffset].AsRecordType <Formula>(); AbstractPtg dstPtgRef = f.ptgStack.ToList()[1]; PtgRef dst = (PtgRef)dstPtgRef; int targetRow = dst.rw; int targetCol = dst.col & 0xFF; switch (formulaOffset) { case 0: Assert.AreEqual(0, targetRow); Assert.AreEqual(1, targetCol); break; case 1: Assert.AreEqual(0, targetRow); Assert.AreEqual(2, targetCol); break; case 2: Assert.AreEqual(0, targetRow); Assert.AreEqual(3, targetCol); break; case 3: Assert.AreEqual(1, targetRow); Assert.AreEqual(1, targetCol); break; case 4: Assert.AreEqual(1, targetRow); Assert.AreEqual(2, targetCol); break; case 5: Assert.AreEqual(1, targetRow); Assert.AreEqual(3, targetCol); break; case 6: Assert.AreEqual(2, targetRow); Assert.AreEqual(1, targetCol); break; case 7: Assert.AreEqual(2, targetRow); Assert.AreEqual(2, targetCol); break; case 8: Assert.AreEqual(3, targetRow); Assert.AreEqual(1, targetCol); break; default: Assert.Fail("Too many records generated"); break; } formulaOffset += 1; } }