public void Apply(SeriesFormatSequence seriesFormatSequence) { // find BRAI record for categories foreach (var aiSequence in seriesFormatSequence.AiSequences) { if (aiSequence.BRAI.braiId == BRAI.BraiId.SeriesCategory) { var brai = aiSequence.BRAI; // don't create a c:cat node for automatically generated category axis data! if (brai.rt != BRAI.DataSource.Automatic) { // c:cat (or c:xVal for scatter and bubble charts) this._writer.WriteStartElement(Dml.Chart.Prefix, this._parentElement, Dml.Chart.Ns); { switch (brai.rt) { case BRAI.DataSource.Literal: // c:strLit this._writer.WriteStartElement(Dml.Chart.Prefix, Dml.Chart.ElStrLit, Dml.Chart.Ns); { convertStringPoints(seriesFormatSequence); } this._writer.WriteEndElement(); break; case BRAI.DataSource.Reference: // c:strRef this._writer.WriteStartElement(Dml.Chart.Prefix, Dml.Chart.ElStrRef, Dml.Chart.Ns); { // c:f string formula = FormulaInfixMapping.mapFormula(brai.formula.formula, this.WorkbookContext); this._writer.WriteElementString(Dml.Chart.Prefix, Dml.Chart.ElF, Dml.Chart.Ns, formula); // c:strCache this._writer.WriteStartElement(Dml.Chart.Prefix, Dml.Chart.ElStrCache, Dml.Chart.Ns); { convertStringPoints(seriesFormatSequence); } this._writer.WriteEndElement(); } this._writer.WriteEndElement(); break; } } this._writer.WriteEndElement(); // c:cat } break; } } }
/// <summary> /// </summary> public void Apply(SeriesFormatSequence seriesFormatSequence) { // find BRAI record for values foreach (var aiSequence in seriesFormatSequence.AiSequences) { if (aiSequence.BRAI.braiId == BRAI.BraiId.SeriesValues) { // c:val this._writer.WriteStartElement(Dml.Chart.Prefix, this._parentElement, Dml.Chart.Ns); { var brai = aiSequence.BRAI; switch (brai.rt) { case BRAI.DataSource.Literal: // c:numLit this._writer.WriteStartElement(Dml.Chart.Prefix, Dml.Chart.ElNumLit, Dml.Chart.Ns); convertNumData(seriesFormatSequence); this._writer.WriteEndElement(); // c:numLit break; case BRAI.DataSource.Reference: // c:numRef this._writer.WriteStartElement(Dml.Chart.Prefix, Dml.Chart.ElNumRef, Dml.Chart.Ns); // c:f string formula = FormulaInfixMapping.mapFormula(brai.formula.formula, this.WorkbookContext); this._writer.WriteElementString(Dml.Chart.Prefix, Dml.Chart.ElF, Dml.Chart.Ns, formula); // TODO: optional data cache // c:numCache this._writer.WriteStartElement(Dml.Chart.Prefix, Dml.Chart.ElNumCache, Dml.Chart.Ns); convertNumData(seriesFormatSequence); this._writer.WriteEndElement(); // c:numCache this._writer.WriteEndElement(); // c:numRef break; } } this._writer.WriteEndElement(); // c:val break; } } }
/// <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) { var resultStack = new Stack <string>(); try { var opStack = new Stack <AbstractPtg>(stack); if (opStack.Count == 0) { throw new Exception(); } while (opStack.Count != 0) { var ptg = opStack.Pop(); if (ptg is PtgInt || ptg is PtgNum || ptg is PtgBool || ptg is PtgMissArg || ptg is PtgErr) { resultStack.Push(ptg.getData()); } else if (ptg is PtgRef) { var 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)); } else if (ptg is PtgRefN) { var ptgrefn = (PtgRefN)ptg; int realCol = (int)ptgrefn.col; int realRw; if (ptgrefn.colRelative) { realCol += col; } if (realCol >= 0xFF) { realCol -= 0x0100; } if (ptgrefn.rwRelative) { realRw = (short)ptgrefn.rw + 1; realRw += rw; } else { realRw = ptgrefn.rw + 1; } resultStack.Push(ExcelHelperClass.intToABCString(realCol, (realRw).ToString(), ptgrefn.colRelative, ptgrefn.rwRelative)); } else if (ptg is PtgUplus || ptg is PtgUminus) { string buffer = ""; if (ptg.PopSize() > resultStack.Count) { throw new ExtractorException(ExtractorException.PARSEDFORMULAEXCEPTION); } buffer = ptg.getData() + resultStack.Pop(); resultStack.Push(buffer); } else if (ptg is PtgParen) { string buffer = ""; if (ptg.PopSize() > resultStack.Count) { throw new ExtractorException(ExtractorException.PARSEDFORMULAEXCEPTION); } buffer = "(" + resultStack.Pop() + ")"; resultStack.Push(buffer); } else if (ptg is PtgPercent) { string buffer = ""; if (ptg.PopSize() > resultStack.Count) { throw new ExtractorException(ExtractorException.PARSEDFORMULAEXCEPTION); } buffer = resultStack.Pop() + ptg.getData(); resultStack.Push(buffer); } else if (ptg is PtgAdd || ptg is PtgDiv || ptg is PtgMul || ptg is PtgSub || ptg is PtgPower || ptg is PtgGt || ptg is PtgGe || ptg is PtgLt || ptg is PtgLe || ptg is PtgEq || ptg is PtgNe || ptg is PtgConcat || ptg is PtgUnion || ptg is 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); } else if (ptg is PtgStr) { resultStack.Push("\"" + ptg.getData() + "\""); } else if (ptg is PtgArea) { string buffer = ""; var 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); } else if (ptg is PtgAreaN) { string buffer = ""; var 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 = (short)ptgarean.rwFirst + 1; realRwFirst += rw; } else { realRwFirst = ptgarean.rwFirst + 1; } if (ptgarean.rwLastRelative) { realRwLast = (short)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); } else if (ptg is PtgAttrSum) { string buffer = ""; var 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 if (ptg is PtgExp) { var ptgexp = (PtgExp)ptg; var newptgstack = ((WorkSheetData)xlsContext.CurrentSheet).getArrayData(ptgexp.rw, ptgexp.col); resultStack.Push(FormulaInfixMapping.mapFormula(newptgstack, xlsContext)); ((WorkSheetData)xlsContext.CurrentSheet).setFormulaUsesArray(ptgexp.rw, ptgexp.col); } else if (ptg is PtgRef3d) { try { var ptgr3d = (PtgRef3d)ptg; string refstring = ExcelHelperClass.EscapeFormulaString(xlsContext.XlsDoc.WorkBookData.getIXTIString(ptgr3d.ixti)); string cellref = ExcelHelperClass.intToABCString((int)ptgr3d.col, (ptgr3d.rw + 1).ToString(), ptgr3d.colRelative, ptgr3d.rwRelative); resultStack.Push("'" + refstring + "'" + "!" + cellref); } catch (Exception) { resultStack.Push("#REF!"); } } else if (ptg is PtgArea3d) { try { var ptga3d = (PtgArea3d)ptg; string refstring = ExcelHelperClass.EscapeFormulaString(xlsContext.XlsDoc.WorkBookData.getIXTIString(ptga3d.ixti)); 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!"); } } else if (ptg is PtgNameX) { var ptgnx = (PtgNameX)ptg; string opstring = xlsContext.XlsDoc.WorkBookData.getExternNameByRef(ptgnx.ixti, ptgnx.nameindex); resultStack.Push(opstring); } else if (ptg is PtgName) { var ptgn = (PtgName)ptg; string opstring = xlsContext.XlsDoc.WorkBookData.getDefinedNameByRef(ptgn.nameindex); resultStack.Push(opstring); } else if (ptg is PtgRefErr) { var ptgreferr = (PtgRefErr)ptg; resultStack.Push(ptgreferr.getData()); } else if (ptg is PtgRefErr3d) { try { var ptgreferr3d = (PtgRefErr3d)ptg; string refstring = ExcelHelperClass.EscapeFormulaString(xlsContext.XlsDoc.WorkBookData.getIXTIString(ptgreferr3d.ixti)); resultStack.Push("'" + refstring + "'" + "!" + ptgreferr3d.getData()); } catch (Exception) { resultStack.Push("#REF!"); } } else if (ptg is PtgAreaErr3d) { try { var ptgareaerr3d = (PtgAreaErr3d)ptg; string refstring = ExcelHelperClass.EscapeFormulaString(xlsContext.XlsDoc.WorkBookData.getIXTIString(ptgareaerr3d.ixti)); resultStack.Push("'" + refstring + "'" + "!" + ptgareaerr3d.getData()); } catch (Exception) { resultStack.Push("#REF!"); } } else if (ptg is PtgFunc) { var ptgf = (PtgFunc)ptg; var 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); } else if (ptg is PtgFuncVar) { var ptgfv = (PtgFuncVar)ptg; // is Ftab or Cetab if (!ptgfv.fCelFunc) { var 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 += "("; 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); } } } else if (ptg is PtgMemFunc) { string value; value = FormulaInfixMapping.mapFormula(((PtgMemFunc)ptg).ptgStack, xlsContext); resultStack.Push(value); } } } catch (Exception ex) { TraceLogger.DebugInternal(ex.Message.ToString()); resultStack.Push(""); } if (resultStack.Count == 0) { resultStack.Push(""); } return(resultStack.Pop()); }
/// <summary> /// This static method is used to convert the ptgStack to the infixnotation /// Normal parsing without row and column changes /// </summary> /// <param name="stack"></param> /// <param name="xlsContext"></param> /// <returns></returns> public static string mapFormula(Stack <AbstractPtg> stack, ExcelContext xlsContext) { return(FormulaInfixMapping.mapFormula(stack, xlsContext, 0, 0)); }
public void Apply(SeriesFormatSequence seriesFormatSequence) { // EG_SerShared // c:idx // TODO: check the meaning of this element writeValueElement(Dml.Chart.ElIdx, seriesFormatSequence.SerToCrt.id.ToString()); // c:order writeValueElement(Dml.Chart.ElOrder, seriesFormatSequence.order.ToString()); // c:tx // find BRAI record for series name foreach (var aiSequence in seriesFormatSequence.AiSequences) { if (aiSequence.BRAI.braiId == BRAI.BraiId.SeriesNameOrLegendText) { var brai = aiSequence.BRAI; if (aiSequence.SeriesText != null) { switch (brai.rt) { case BRAI.DataSource.Literal: // c:tx this._writer.WriteStartElement(Dml.Chart.Prefix, Dml.Chart.ElTx, Dml.Chart.Ns); // c:v this._writer.WriteElementString(Dml.Chart.Prefix, Dml.Chart.ElV, Dml.Chart.Ns, aiSequence.SeriesText.stText.Value); this._writer.WriteEndElement(); // c:tx break; case BRAI.DataSource.Reference: // c:tx this._writer.WriteStartElement(Dml.Chart.Prefix, Dml.Chart.ElTx, Dml.Chart.Ns); // c:strRef this._writer.WriteStartElement(Dml.Chart.Prefix, Dml.Chart.ElStrRef, Dml.Chart.Ns); { // c:f string formula = FormulaInfixMapping.mapFormula(brai.formula.formula, this.WorkbookContext); this._writer.WriteElementString(Dml.Chart.Prefix, Dml.Chart.ElF, Dml.Chart.Ns, formula); // c:strCache //convertStringCache(seriesFormatSequence); } this._writer.WriteEndElement(); // c:strRef this._writer.WriteEndElement(); // c:tx break; } } break; } } if (seriesFormatSequence.SsSequence.Count > 0) { seriesFormatSequence.SsSequence[0].Convert(new ShapePropertiesMapping(this.WorkbookContext, this.ChartContext)); } }
/// <summary> /// The overload apply method /// Creates the Workbook xml document /// </summary> /// <param name="bsd">WorkSheetData</param> public void Apply(WorkBookData workbookData) { this._writer.WriteStartDocument(); this._writer.WriteStartElement("workbook", OpenXmlNamespaces.SpreadsheetML); this._writer.WriteAttributeString("xmlns", Sml.Ns); this._writer.WriteAttributeString("xmlns", "r", "", OpenXmlNamespaces.Relationships); // fileVersion this._writer.WriteStartElement(Sml.Workbook.ElFileVersion, Sml.Ns); this._writer.WriteAttributeString(Sml.Workbook.AttrAppName, "xl"); this._writer.WriteEndElement(); // workbookPr this._writer.WriteStartElement(Sml.Workbook.ElWorkbookPr, Sml.Ns); if (workbookData.CodeName != null) { this._writer.WriteAttributeString(Sml.Workbook.AttrCodeName, workbookData.CodeName.codeName.Value); } this._writer.WriteEndElement(); this._writer.WriteStartElement("sheets"); // create the sheets foreach (var sheet in workbookData.boundSheetDataList) { OpenXmlPart sheetPart; switch (sheet.boundsheetRecord.dt) { case BoundSheet8.SheetType.Worksheet: { var worksheetPart = this._workbookPart.AddWorksheetPart(); sheet.Convert(new WorksheetMapping(this._xlsContext, worksheetPart)); sheetPart = worksheetPart; } break; case BoundSheet8.SheetType.Chartsheet: { var chartsheetPart = this._workbookPart.AddChartsheetPart(); sheet.Convert(new ChartsheetMapping(this._xlsContext, chartsheetPart)); sheetPart = chartsheetPart; } break; default: { sheet.emtpyWorksheet = true; var worksheetPart = this._workbookPart.AddWorksheetPart(); sheet.Convert(new WorksheetMapping(this._xlsContext, worksheetPart)); sheetPart = worksheetPart; } break; } this._writer.WriteStartElement("sheet"); this._writer.WriteAttributeString("name", sheet.boundsheetRecord.stName.Value); this._writer.WriteAttributeString("sheetId", sheetPart.RelId.ToString()); this._writer.WriteAttributeString("r", "id", OpenXmlNamespaces.Relationships, sheetPart.RelIdToString); this._writer.WriteEndElement(); } this._writer.WriteEndElement(); // close sheetData bool ParentTagWritten = false; if (workbookData.supBookDataList.Count != 0) { /* * <externalReferences> * <externalReference r:id="rId4" /> * <externalReference r:id="rId5" /> * </externalReferences> */ foreach (var var in workbookData.supBookDataList) { if (!var.SelfRef) { if (!ParentTagWritten) { this._writer.WriteStartElement("externalReferences"); ParentTagWritten = true; } this._writer.WriteStartElement("externalReference"); this._writer.WriteAttributeString("r", "id", OpenXmlNamespaces.Relationships, var.ExternalLinkRef); this._writer.WriteEndElement(); } } if (ParentTagWritten) { this._writer.WriteEndElement(); } } // write definedNames if (workbookData.definedNameList.Count > 0) { //<definedNames> //<definedName name="abc" comment="test" localSheetId="1">Sheet1!$B$3</definedName> //</definedNames> this._writer.WriteStartElement("definedNames"); foreach (var item in workbookData.definedNameList) { if (item.rgce.Count > 0) { this._writer.WriteStartElement("definedName"); if (item.Name.Value.Length > 1) { this._writer.WriteAttributeString("name", item.Name.Value); } else { string internName = "_xlnm." + ExcelHelperClass.getNameStringfromBuiltInFunctionID(item.Name.Value); this._writer.WriteAttributeString("name", internName); } if (item.itab > 0) { this._writer.WriteAttributeString("localSheetId", (item.itab - 1).ToString()); } this._writer.WriteValue(FormulaInfixMapping.mapFormula(item.rgce, this._xlsContext)); this._writer.WriteEndElement(); } } this._writer.WriteEndElement(); } // close tags this._writer.WriteEndElement(); // close worksheet this._writer.WriteEndDocument(); // flush writer this._writer.Flush(); }
/// <summary> /// The overload apply method /// Creates the Worksheet xml document /// </summary> /// <param name="bsd">WorkSheetData</param> public void Apply(WorkSheetData bsd) { this._xlsContext.CurrentSheet = bsd; this._writer.WriteStartDocument(); this._writer.WriteStartElement("worksheet", OpenXmlNamespaces.SpreadsheetML); //if (bsd.emtpyWorksheet) //{ // _writer.WriteStartElement("sheetData"); // _writer.WriteEndElement(); //} //else { // default info if (bsd.defaultColWidth >= 0 || bsd.defaultRowHeight >= 0) { this._writer.WriteStartElement("sheetFormatPr"); if (bsd.defaultColWidth >= 0) { double colWidht = (double)bsd.defaultColWidth; this._writer.WriteAttributeString("defaultColWidth", Convert.ToString(colWidht, CultureInfo.GetCultureInfo("en-US"))); } if (bsd.defaultRowHeight >= 0) { var tv = new TwipsValue(bsd.defaultRowHeight); this._writer.WriteAttributeString("defaultRowHeight", Convert.ToString(tv.ToPoints(), CultureInfo.GetCultureInfo("en-US"))); } if (bsd.zeroHeight) { this._writer.WriteAttributeString("zeroHeight", "1"); } if (bsd.customHeight) { this._writer.WriteAttributeString("customHeight", "1"); } if (bsd.thickTop) { this._writer.WriteAttributeString("thickTop", "1"); } if (bsd.thickBottom) { this._writer.WriteAttributeString("thickBottom", "1"); } this._writer.WriteEndElement(); // sheetFormatPr } // Col info if (bsd.colInfoDataTable.Count > 0) { this._writer.WriteStartElement("cols"); foreach (var col in bsd.colInfoDataTable) { this._writer.WriteStartElement("col"); // write min and max // booth values are 0 based in the binary format and 1 based in the oxml format // so you have to add 1 to the value! this._writer.WriteAttributeString("min", (col.min + 1).ToString()); this._writer.WriteAttributeString("max", (col.max + 1).ToString()); if (col.widht != 0) { double colWidht = (double)col.widht / 256; this._writer.WriteAttributeString("width", Convert.ToString(colWidht, CultureInfo.GetCultureInfo("en-US"))); } if (col.hidden) { this._writer.WriteAttributeString("hidden", "1"); } if (col.outlineLevel > 0) { this._writer.WriteAttributeString("outlineLevel", col.outlineLevel.ToString()); } if (col.customWidth) { this._writer.WriteAttributeString("customWidth", "1"); } if (col.bestFit) { this._writer.WriteAttributeString("bestFit", "1"); } if (col.phonetic) { this._writer.WriteAttributeString("phonetic", "1"); } if (col.style > 15) { this._writer.WriteAttributeString("style", Convert.ToString(col.style - this._xlsContext.XlsDoc.WorkBookData.styleData.XFCellStyleDataList.Count, CultureInfo.GetCultureInfo("en-US"))); } this._writer.WriteEndElement(); // col } this._writer.WriteEndElement(); } // End col info this._writer.WriteStartElement("sheetData"); // bsd.rowDataTable.Values foreach (var row in bsd.rowDataTable.Values) { // write row start tag // Row this._writer.WriteStartElement("row"); // the rowindex from the binary format is zero based, the ooxml format is one based this._writer.WriteAttributeString("r", (row.Row + 1).ToString()); if (row.height != null) { this._writer.WriteAttributeString("ht", Convert.ToString(row.height.ToPoints(), CultureInfo.GetCultureInfo("en-US"))); if (row.customHeight) { this._writer.WriteAttributeString("customHeight", "1"); } } if (row.hidden) { this._writer.WriteAttributeString("hidden", "1"); } if (row.outlineLevel > 0) { this._writer.WriteAttributeString("outlineLevel", row.outlineLevel.ToString()); } if (row.collapsed) { this._writer.WriteAttributeString("collapsed", "1"); } if (row.customFormat) { this._writer.WriteAttributeString("customFormat", "1"); if (row.style > 15) { this._writer.WriteAttributeString("s", (row.style - this._xlsContext.XlsDoc.WorkBookData.styleData.XFCellStyleDataList.Count).ToString()); } } if (row.thickBot) { this._writer.WriteAttributeString("thickBot", "1"); } if (row.thickTop) { this._writer.WriteAttributeString("thickTop", "1"); } if (row.minSpan + 1 > 0 && row.maxSpan > 0 && row.minSpan + 1 < row.maxSpan) { this._writer.WriteAttributeString("spans", (row.minSpan + 1).ToString() + ":" + row.maxSpan.ToString()); } row.Cells.Sort(); foreach (var cell in row.Cells) { // Col this._writer.WriteStartElement("c"); this._writer.WriteAttributeString("r", ExcelHelperClass.intToABCString((int)cell.Col, (cell.Row + 1).ToString())); if (cell.TemplateID > 15) { this._writer.WriteAttributeString("s", (cell.TemplateID - this._xlsContext.XlsDoc.WorkBookData.styleData.XFCellStyleDataList.Count).ToString()); } if (cell is StringCell) { this._writer.WriteAttributeString("t", "s"); } if (cell is FormulaCell) { var fcell = (FormulaCell)cell; if (((FormulaCell)cell).calculatedValue is string) { this._writer.WriteAttributeString("t", "str"); } else if (((FormulaCell)cell).calculatedValue is double) { this._writer.WriteAttributeString("t", "n"); } else if (((FormulaCell)cell).calculatedValue is byte) { this._writer.WriteAttributeString("t", "b"); } else if (((FormulaCell)cell).calculatedValue is int) { this._writer.WriteAttributeString("t", "e"); } // <f>1</f> this._writer.WriteStartElement("f"); if (!fcell.isSharedFormula) { string value = FormulaInfixMapping.mapFormula(fcell.PtgStack, this._xlsContext); if (fcell.usesArrayRecord) { this._writer.WriteAttributeString("t", "array"); this._writer.WriteAttributeString("ref", ExcelHelperClass.intToABCString((int)cell.Col, (cell.Row + 1).ToString())); } if (fcell.alwaysCalculated) { this._writer.WriteAttributeString("ca", "1"); } if (value.Equals("")) { TraceLogger.Debug("Formula Parse Error in Row {0}\t Column {1}\t", cell.Row.ToString(), cell.Col.ToString()); } this._writer.WriteString(value); } /// If this cell is part of a shared formula /// else { var sfd = bsd.checkFormulaIsInShared(cell.Row, cell.Col); if (sfd != null) { // t="shared" this._writer.WriteAttributeString("t", "shared"); // <f t="shared" ref="C4:C11" si="0">H4+I4-J4</f> this._writer.WriteAttributeString("si", sfd.ID.ToString()); if (sfd.RefCount == 0) { /// Write value and reference this._writer.WriteAttributeString("ref", sfd.getOXMLFormatedData()); string value = FormulaInfixMapping.mapFormula(sfd.PtgStack, this._xlsContext, sfd.rwFirst, sfd.colFirst); this._writer.WriteString(value); sfd.RefCount++; } } else { TraceLogger.Debug("Formula Parse Error in Row {0}\t Column {1}\t", cell.Row.ToString(), cell.Col.ToString()); } } this._writer.WriteEndElement(); /// write down calculated value from a formula /// this._writer.WriteStartElement("v"); if (((FormulaCell)cell).calculatedValue is int) { this._writer.WriteString(FormulaInfixMapping.getErrorStringfromCode((int)((FormulaCell)cell).calculatedValue)); } else { this._writer.WriteString(Convert.ToString(((FormulaCell)cell).calculatedValue, CultureInfo.GetCultureInfo("en-US"))); } this._writer.WriteEndElement(); } else {// Data !!! this._writer.WriteElementString("v", cell.getValue()); } // add a type to the c element if the formula returns following types this._writer.WriteEndElement(); // close cell (c) } this._writer.WriteEndElement(); // close row } // close tags this._writer.WriteEndElement(); // close sheetData // Add the mergecell part // // - <mergeCells count="2"> // <mergeCell ref="B3:C3" /> // <mergeCell ref="E3:F4" /> // </mergeCells> if (bsd.MERGECELLSData != null) { this._writer.WriteStartElement("mergeCells"); this._writer.WriteAttributeString("count", bsd.MERGECELLSData.cmcs.ToString()); foreach (var mcell in bsd.MERGECELLSData.mergeCellDataList) { this._writer.WriteStartElement("mergeCell"); this._writer.WriteAttributeString("ref", mcell.getOXMLFormatedData()); this._writer.WriteEndElement(); } // close mergeCells Tag this._writer.WriteEndElement(); } // hyperlinks! if (bsd.HyperLinkList.Count != 0) { this._writer.WriteStartElement("hyperlinks"); bool writtenParentElement = false; foreach (var link in bsd.HyperLinkList) { // Uri url; // if (link.absolute) // { // if (link.url.StartsWith("http", true, CultureInfo.GetCultureInfo("en-US")) // || link.url.StartsWith("mailto", true, CultureInfo.GetCultureInfo("en-US"))) // { // url = new Uri(link.url, UriKind.Absolute); // } // else // { // link.url = "file:///" + link.url; // url = new Uri(link.url, UriKind.Absolute); // } // } // else // { // url = new Uri(link.url, UriKind.Relative); // } // try // { // if (System.Uri.IsWellFormedUriString(url.LocalPath.ToString(), System.UriKind.Absolute)) // { //if (!writtenParentElement) //{ // writtenParentElement = true; //} string refstring; if (link.colLast == link.colFirst && link.rwLast == link.rwFirst) { refstring = ExcelHelperClass.intToABCString((int)link.colLast, (link.rwLast + 1).ToString()); } else { refstring = ExcelHelperClass.intToABCString((int)link.colFirst, (link.rwFirst + 1).ToString()) + ":" + ExcelHelperClass.intToABCString((int)link.colLast, (link.rwLast + 1).ToString()); } if (link.url != null) { var er = this._xlsContext.SpreadDoc.WorkbookPart.GetWorksheetPart().AddExternalRelationship(OpenXmlRelationshipTypes.HyperLink, link.url.Replace(" ", "")); this._writer.WriteStartElement("hyperlink"); this._writer.WriteAttributeString("ref", refstring); this._writer.WriteAttributeString("r", "id", OpenXmlNamespaces.Relationships, er.Id.ToString()); this._writer.WriteEndElement(); } else if (link.location != null) { this._writer.WriteStartElement("hyperlink"); this._writer.WriteAttributeString("ref", refstring); this._writer.WriteAttributeString("location", link.location); if (link.display != null) { this._writer.WriteAttributeString("display", link.display); } this._writer.WriteEndElement(); } /* } * } * catch (Exception ex) * { * TraceLogger.DebugInternal(ex.Message.ToString()); * TraceLogger.DebugInternal(ex.StackTrace.ToString()); * } * }*/ } this._writer.WriteEndElement(); // hyperlinks if (writtenParentElement) { } } // worksheet margins !! if (bsd.leftMargin != null && bsd.topMargin != null && bsd.rightMargin != null && bsd.bottomMargin != null && bsd.headerMargin != null && bsd.footerMargin != null) { this._writer.WriteStartElement("pageMargins"); { this._writer.WriteAttributeString("left", Convert.ToString(bsd.leftMargin, CultureInfo.GetCultureInfo("en-US"))); this._writer.WriteAttributeString("right", Convert.ToString(bsd.rightMargin, CultureInfo.GetCultureInfo("en-US"))); this._writer.WriteAttributeString("top", Convert.ToString(bsd.topMargin, CultureInfo.GetCultureInfo("en-US"))); this._writer.WriteAttributeString("bottom", Convert.ToString(bsd.bottomMargin, CultureInfo.GetCultureInfo("en-US"))); this._writer.WriteAttributeString("header", Convert.ToString(bsd.headerMargin, CultureInfo.GetCultureInfo("en-US"))); this._writer.WriteAttributeString("footer", Convert.ToString(bsd.footerMargin, CultureInfo.GetCultureInfo("en-US"))); } this._writer.WriteEndElement(); // pageMargins } // page setup settings if (bsd.PageSetup != null) { this._writer.WriteStartElement("pageSetup"); if (!bsd.PageSetup.fNoPls && bsd.PageSetup.iPaperSize > 0 && bsd.PageSetup.iPaperSize < 255) { this._writer.WriteAttributeString("paperSize", bsd.PageSetup.iPaperSize.ToString()); } if (bsd.PageSetup.iScale >= 10 && bsd.PageSetup.iScale <= 400) { this._writer.WriteAttributeString("scale", bsd.PageSetup.iScale.ToString()); } this._writer.WriteAttributeString("firstPageNumber", bsd.PageSetup.iPageStart.ToString()); this._writer.WriteAttributeString("fitToWidth", bsd.PageSetup.iFitWidth.ToString()); this._writer.WriteAttributeString("fitToHeight", bsd.PageSetup.iFitHeight.ToString()); if (bsd.PageSetup.fLeftToRight) { this._writer.WriteAttributeString("pageOrder", "overThenDown"); } if (!bsd.PageSetup.fNoOrient) { if (bsd.PageSetup.fPortrait) { this._writer.WriteAttributeString("orientation", "portrait"); } else { this._writer.WriteAttributeString("orientation", "landscape"); } } //10 <attribute name="usePrinterDefaults" type="xsd:boolean" use="optional" default="true"/> if (bsd.PageSetup.fNoColor) { this._writer.WriteAttributeString("blackAndWhite", "1"); } if (bsd.PageSetup.fDraft) { this._writer.WriteAttributeString("draft", "1"); } if (bsd.PageSetup.fNotes) { if (bsd.PageSetup.fEndNotes) { this._writer.WriteAttributeString("cellComments", "atEnd"); } else { this._writer.WriteAttributeString("cellComments", "asDisplayed"); } } if (bsd.PageSetup.fUsePage) { this._writer.WriteAttributeString("useFirstPageNumber", "1"); } switch (bsd.PageSetup.iErrors) { case 0x00: this._writer.WriteAttributeString("errors", "displayed"); break; case 0x01: this._writer.WriteAttributeString("errors", "blank"); break; case 0x02: this._writer.WriteAttributeString("errors", "dash"); break; case 0x03: this._writer.WriteAttributeString("errors", "NA"); break; default: this._writer.WriteAttributeString("errors", "displayed"); break; } this._writer.WriteAttributeString("horizontalDpi", bsd.PageSetup.iRes.ToString()); this._writer.WriteAttributeString("verticalDpi", bsd.PageSetup.iVRes.ToString()); if (!bsd.PageSetup.fNoPls) { this._writer.WriteAttributeString("copies", bsd.PageSetup.iCopies.ToString()); } this._writer.WriteEndElement(); } // embedded drawings (charts etc) if (bsd.ObjectsSequence != null) { this._writer.WriteStartElement(Sml.Sheet.ElDrawing, Sml.Ns); { this._writer.WriteAttributeString("r", "id", OpenXmlNamespaces.Relationships, this._worksheetPart.DrawingsPart.RelIdToString); bsd.ObjectsSequence.Convert(new DrawingMapping(this._xlsContext, this._worksheetPart.DrawingsPart, false)); } this._writer.WriteEndElement(); } } this._writer.WriteEndElement(); // close worksheet this._writer.WriteEndDocument(); // close writer this._writer.Flush(); }