Writing Formulas using the Write Excel Operator

    When you create a Schema that describes the structure of an existing Excel worksheet, some cells may contain a formula rather than a value.  If you want to preserve the formula when reading the records, you will select the Preserve formulas option in the New Excel Schema window.  In the Schema, the type assigned to this field will be Formula and the type of its corresponding attribute will be string.

    formulas.png

    When you need to create a Schema that describes the structure of a worksheet that your dataflow will write, you will most likely create the Schema using the structure of the record emitted by the operator upstream of the Write Excel operator or from the description of a composite type.  Within Expressor, formulas extracted from a worksheet, or formulas that your code might create, are assigned to string attributes so that when you create the Schema, the type assigned to the corresponding field will be string.  If you want to emit an executable formula, you must change this type assignment to Formula.  If you do not make this type change, the actual formula, rather than its computed value, will be displayed in the worksheet cell.

    formulas_2.png

    Now when the worksheet is opened, the computed value will be displayed in the cell but the formula will be displayed in the formula bar when you click in the cell.

     

    So the question becomes how do you represent an Excel formula within Expressor?  Since the formula will be assigned to a string attribute, you simply enter the formula as you would in the Excel formula bar, being certain to include it within quotes.  When you write the formula you must, however, use the R1C1 Reference style.  That is, if you want to sum the values in columns A and B into column C, a formula such as =SUM(A2,B2) should be written =SUM(RC[-2]:RC[-1]).  The reason this is important is that it allows your code to be completely ignorant of the row number of the record in the emitted worksheet.  This means that your downstream operators can perform sorts or other record manipulations without a concern that the formula could become invalid.

     

    When the Write Excel operator writes a formula to a worksheet, it enables the R1C1 Reference style so that formulas read from an input worksheet and copied to the output worksheet will be in this style regardless of how they were entered into the source worksheet.