title: Excel interop and DataField orientation
body: <p>I'm writing a C# program that opens and read an Excel document and created a pivot table with some fields. One field is added oriented as Rows. The rest are calculated datafields. Everything is working and the data is added fine. There is only one problem. The calculated datafields are also added as rows, but I would like to have them as columns. In Excel, doing that manually means dragging the Values from Rows to Columns. But, how do I achieve the same programatically?</p>
  
  <p>My code:</p>
  
  <pre><code> Excel.PivotTable pivot1 = xlSheet.PivotTableWizard(
                   Excel.XlPivotTableSourceType.xlDatabase, // source type
                   pivotRange, // source
                   xlSheet.get_Range(Constants.PLACE_PIVOT_1), //destination
                   "Grade", // table name 
                   false,
                   false,
                   true,
                   false,
                   missing,
                   missing,
                   false,
                   false,
                   Excel.XlOrder.xlDownThenOver,
                   missing,
                   missing,
                   missing);
  
              Excel.PivotField betygField =
                  (Excel.PivotField)pivot1.PivotFields("Grade type");
              betygField.Orientation =
                  Excel.XlPivotFieldOrientation.xlRowField;
              betygField.Position = 1;
  
              // int dataIndex = 2;
              for (int f = Constants.START_START_SAMMAN; f &lt; columns; f++) //add some array of columns as DataFields
              {
                  string name = Convert.ToString(xlSheet.Cells[Constants.BEGIN_STAT_SAMMAN_Y, f].Value);
  
                  Excel.PivotField f1 = pivot1.AddDataField((Excel.PivotField)pivot1.PivotFields(name),
                      String.Format("Sume of {0}", name), Excel.XlConsolidationFunction.xlSum);
                  Excel.PivotField f2 = pivot1.AddDataField((Excel.PivotField)pivot1.PivotFields(name),
                      String.Format("Avg. of {0}", name), Excel.XlConsolidationFunction.xlAverage);
  
                  f1.Orientation =
                  Excel.XlPivotFieldOrientation.xlDataField;
                  f1.NumberFormat = "0.00";
  
                  f2.Orientation =
                  Excel.XlPivotFieldOrientation.xlDataField;
                  f2.NumberFormat = "0.00";
              }
  
              pivot1.GrandTotalName = "Totalt";
              pivot1.RowGrand = true;
              pivot1.ColumnGrand = true;
              pivot1.TableStyle2 = "PivotStyleLight16";
  </code></pre>