How can I place my data fields beneath my row fiel

问题:

I have a PivotTable created with Excel Interop that places the data field values beneath the row field values, like so:

When I create the PivotTable with Aspose Cells, the data fields are 1:08 PM 11/18/2016in a column to the right, rather than in the same column and beneath the row values:

Here is the code I'm using to generate the PivotTable in Aspose Cells:

private void PopulatePivotTableSheet()
{
    int DESCRIPTION_COLUMN = 1;
    int MONTHYR_COLUMN = 3;
    int TOTALQTY_COLUMN = 4;
    int TOTALPRICE_COLUMN = 5;
    int PERCENTOFTOTAL_COLUMN = 7;
    int AVGPRICE_COLUMN = 10;
    int COLUMNS_IN_DATA_SHEET = 11;

    AddSheetHeadingSectionToPivotTableSheet();

    Aspose.Cells.Pivot.PivotTableCollection pivotTables = pivotTableSheet.PivotTables;
    int colcount = COLUMNS_IN_DATA_SHEET;
    string lastColAsStr = ReportRunnerConstsAndUtils.GetExcelColumnName(colcount);
    int rowcount = sourceDataSheet.Cells.Rows.Count;
    string sourceDataArg = string.Format("sourceDataSheet!A1:{0}{1}", lastColAsStr, rowcount);
    int index = pivotTableSheet.PivotTables.Add(sourceDataArg, "A6", "PivotTableSheet");
    Aspose.Cells.Pivot.PivotTable pivotTable = pivotTables[index];

    pivotTable.DisplayNullString = true;
    pivotTable.NullString = "0";

    // Dragging the first field to the row area.
    pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Row, DESCRIPTION_COLUMN);
    pivotTable.RowHeaderCaption = "Description";

    // Dragging the second field to the column area.
    pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Column, MONTHYR_COLUMN);
    pivotTable.ColumnHeaderCaption = "Months";

    // Dragging the third field to the data area.
    pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Data, TOTALQTY_COLUMN);
    pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Data, TOTALPRICE_COLUMN);
    pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Data, AVGPRICE_COLUMN);
    pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Data, PERCENTOFTOTAL_COLUMN);
}

Here is the code I'm using to generate the PivotTable in Excel Interop:

private void PopulatePivotTableSheet()
{
    var pch = _xlBook.PivotCaches();
    int pivotDataRowsUsed = _xlBook.Worksheets["PivotData"].UsedRange.Rows.Count;
    int pivotDataColsUsed = _xlBook.Worksheets["PivotData"].UsedRange.Columns.Count;
    string lastColWrittenAsAlpha = ReportRunnerConstsAndUtils.GetExcelColumnName(pivotDataColsUsed);
    string endRange = string.Format("{0}{1}", lastColWrittenAsAlpha, pivotDataRowsUsed);

    Range sourceData = _xlBook.Worksheets["PivotData"].Range[string.Format("A1:{0}", endRange)];

    PivotCache pc = pch.Create(XlPivotTableSourceType.xlDatabase, sourceData);
    PivotTable pvt = pc.CreatePivotTable(_xlPivotTableSheet.Range["A6"], "PivotTable");

    pvt.MergeLabels = true; // The only thing I noticed this doing was centering the heading labels
    // Although somewhat confusing, these "error" settings actually prevent the "#DIV/0!" from displaying
    pvt.ErrorString = "";
    pvt.DisplayErrorString = true;
    // This one converts what would otherwise be blank into "0" for ints and "$0" for decimal vals
    pvt.NullString = "-";

    var descField = pvt.PivotFields("Description");
    descField.Orientation = XlPivotFieldOrientation.xlRowField;

    var monthField = pvt.PivotFields("MonthYr");
    monthField.Orientation = XlPivotFieldOrientation.xlColumnField;
    monthField.NumberFormat = "MMM yy";

    // This changes the label from "Column Labels"
    pvt.CompactLayoutColumnHeader = "Months";
    // This changes the label from "Row Labels"
    pvt.CompactLayoutRowHeader = "Description";

    pvt.AddDataField(pvt.PivotFields("TotalQty"), "Total Packages", XlConsolidationFunction.xlSum).NumberFormat = "###,##0";
    pvt.AddDataField(pvt.PivotFields("TotalPrice"), "Total Purchases", XlConsolidationFunction.xlSum).NumberFormat = "$#,##0.00";
    PivotField avg = pvt.CalculatedFields().Add("Average Price", "=TotalPrice/TotalQty", true);
    avg.Orientation = XlPivotFieldOrientation.xlDataField;
    avg.NumberFormat = "$###0.00";

    // This looks wrong, but the value is calculated below 
    pvt.CalculatedFields()._Add("PercentOfTotal", "=TotalPrice");
    pvt.AddDataField(pvt.PivotFields("PercentOfTotal"), "Percentage of Total", Type.Missing).NumberFormat = "###.##";

    // These two lines don't seem that they would do so, but they do result in the items 
    // being sorted by (grand) total purchases descending
    var fld = ((PivotField)pvt.PivotFields("Description"));
    fld.AutoSort(2, "Total Purchases");

    // This gets the Pivot Table to what it should be, appearance-wise...
    pvt.DataPivotField.Orientation = XlPivotFieldOrientation.xlRowField;

    // Add calculations to Percentage cells
    int pivotDataSheetRowsUsed = _xlBook.Worksheets["PivotTable"].UsedRange.Rows.Count;
    int pivotDataSheetColsUsed = _grandTotalsColumnPivotTable;
    int FIRST_PERCENTAGE_ROW = 12;
    int FIRST_PERCENTAGE_COL = 2;
    int ROWS_BETWEEN_PERCENTAGES = 5;
    int currentPercentageRow = FIRST_PERCENTAGE_ROW;

    while (currentPercentageRow < pivotDataSheetRowsUsed)
    {
        for (int columnLoop = FIRST_PERCENTAGE_COL; columnLoop <= pivotDataSheetColsUsed; columnLoop++)
        {
            var prcntgCell = (Range)_xlPivotTableSheet.Cells[currentPercentageRow, columnLoop];
            prcntgCell.NumberFormat = "##.#0%";
            if (null == prcntgCell.Value2)
            {
                prcntgCell.Value2 = 0.0;
            }
            else
            {
                prcntgCell.PivotField.Calculation = XlPivotFieldCalculation.xlPercentOfColumn;
            }
        }
        currentPercentageRow = currentPercentageRow + ROWS_BETWEEN_PERCENTAGES;
    }
    FormatPivotTable();
}

What do I need to change in my Aspose Cells code to shift the data fields (sans "Data" label) left and down? If this is the code in Excel Interop that does that:

pvt.DataPivotField.Orientation = XlPivotFieldOrientation.xlRowField;

...what is the Aspose Cells equivalent?

回答1:

Please try one of these three things and see which one of it works for you.

//Your pivot table
PivotTable pt = .....

First

//To show pivot table in compact form
pt.ShowInCompactForm();
pt.RefreshData();
pt.CalculateData();

Second

//To show pivot table in outline form
pt.ShowInOutlineForm();
pt.RefreshData();
pt.CalculateData();

Third

//To show pivot table in tabular form
pt.ShowInTabularForm();
pt.RefreshData();
pt.CalculateData();

Note: I am working as Developer Evangelist at Aspose