This project has moved. For the latest updates, please go here.

Disable row subtotals on Pivot Table

Jan 3, 2013 at 8:03 PM
Edited Jan 3, 2013 at 8:05 PM

I'm trying to disable row subtotals on a Pivot Table.  I turned them off in Excel and saved the worksheet to see what it was doing.  It added defaultSubtotal="0" to the pivotField element.

<pivotField axis="axisRow" showAll="0" defaultSubtotal="0">

I found this in ExcelPivotTableField so I set the SubTotalFunctions to none.

rowField.SubTotalFunctions = eSubTotalFunctions.None;
pivot.RowFields.Add(rowField);
colField.SubTotalFunctions = eSubTotalFunctions.None;
pivot.ColumnFields.Add(colField);

When I add this, Excel says there's invalid XML and fixes it, but the totals are still there. Any help please?

Jan 4, 2013 at 4:59 AM

Code novice checking in!

Can you post as much code as possible so I could paste it into Visual Studio and have a look at it.

Jan 4, 2013 at 8:34 PM

 

        public static ExcelPackage ExportDataSetAsExcelPivot(this SqlDataReader rs, string sheetName, string title, Portlet portlet)
        {
            List<PortletColumn> columns = portlet.cols;
            List<PortletAxis> axes = portlet.axes;
            List<PortletAggregate> aggregates = portlet.aggregates;

            string curFormat = "#,##0";
            if (portlet.curScale > 0)
                curFormat += "." + new String('0', portlet.curScale);
            if (portlet.curSymbol.Length > 1)
                curFormat += " " + portlet.curSymbol;
            else
                curFormat = portlet.curSymbol + curFormat;


            ExcelPackage pck = new ExcelPackage();
            //Order of Add() determines order in workbook
            ExcelWorksheet wsPivot = pck.Workbook.Worksheets.Add(sheetName); 
            ExcelWorksheet wsData = pck.Workbook.Worksheets.Add("data");

            DataTable dt = new DataTable();
            dt.Load(rs);

            int i = 1;
            int record = 1;
            foreach (PortletColumn column in columns)
            {
                wsData.Column(i).Style.Numberformat.Format = column.GetFormatter(curFormat);
                try
                {
                    dt.Columns[column.name].ColumnName = column.GetHeader();
                }
                catch (DuplicateNameException) { }

                i++;
            }
            record++;

            List<PortletAxis> leftAxes = axes.Where(a => a.axisPosition.ToLower() == PortletAxis.Positions.LEFT).ToList();
            int leftAxesCount = leftAxes.Count();
            List<PortletAxis> topAxes = axes.Where(a => a.axisPosition.ToLower() == PortletAxis.Positions.TOP).ToList();
            int topAxesCount = topAxes.Count();


            wsData.Cells["A1"].LoadFromDataTable(dt, true, TableStyles.None);

            ExcelRange dataRange = wsData.Cells[wsData.Dimension.Address.ToString()];
            dataRange.AutoFitColumns();


            //The title in the first cell.  It's a pivot so we don't know what the total columns will be (and they channge as you use the pivot)
            wsPivot.Cells["A1"].Value = title;
            wsPivot.Cells["A1"].Style.Font.Bold = true;
            wsPivot.Cells["A1"].Style.Font.Size = 14;

            ExcelPivotTable pivot = wsPivot.PivotTables.Add(wsPivot.Cells["A4"], dataRange, title);
            pivot.MultipleFieldFilters = true;
            pivot.RowGrandTotals = true;
            pivot.ColumGrandTotals = true;
            pivot.Compact = true;
            pivot.CompactData = true;
            pivot.GridDropZones = true;
            pivot.Outline = false;
            pivot.OutlineData = false;
            pivot.ShowError = true;
            pivot.ErrorCaption = "[error]";
            pivot.ShowHeaders = true;
            pivot.UseAutoFormatting = true;
            pivot.ApplyWidthHeightFormats = true;
            pivot.ShowDrill = true;
            pivot.FirstDataCol = leftAxesCount + 1;
            pivot.TableStyle = TableStyles.Medium2;
            pivot.RowHeaderCaption = portlet.pivotAxisTitle;


            foreach (PortletAxis axis in leftAxes)
            {
                ExcelPivotTableField rowField = pivot.Fields[axis.dataIndex];
                if (rowField == null)
                {
                    string dataIndex = columns.Single(c => c.name == axis.dataIndex).GetHeader();
                    rowField = pivot.Fields[dataIndex];
                }
                //rowField.SubTotalFunctions = eSubTotalFunctions.None;
                pivot.RowFields.Add(rowField);
            }

            foreach (PortletAxis axis in topAxes)
            {
                ExcelPivotTableField colField = pivot.Fields[axis.dataIndex];
                if (colField == null)
                {
                    string dataIndex = columns.Single(c => c.name == axis.dataIndex).GetHeader();
                    colField = pivot.Fields[dataIndex];
                }
                pivot.ColumnFields.Add(colField);
            }

            foreach (PortletAggregate agg in aggregates)
            {
                ExcelPivotTableField aggField = pivot.Fields[agg.dataIndex];
                PortletColumn col = columns.Single(c => c.name == agg.dataIndex);
                if (aggField == null)
                {
                    aggField = pivot.Fields[col.GetHeader()];
                }
                ExcelPivotTableDataField dataField = pivot.DataFields.Add(aggField);
                dataField.Format = col.GetFormatter(curFormat);
                switch (agg.summaryType.ToLower())
                {
                    case PortletAggregate.SummaryTypes.COUNT:
                        dataField.Function = DataFieldFunctions.Count;
                        break;
                    case PortletAggregate.SummaryTypes.SUM:
                        dataField.Function = DataFieldFunctions.Sum;
                        break;
                    case PortletAggregate.SummaryTypes.MAX:
                        dataField.Function = DataFieldFunctions.Max;
                        break;
                    case PortletAggregate.SummaryTypes.MIN:
                        dataField.Function = DataFieldFunctions.Min;
                        break;
                    case PortletAggregate.SummaryTypes.AVERAGE:
                        dataField.Function = DataFieldFunctions.Average;
                        break;
                    default:
                        dataField.Function = DataFieldFunctions.None;
                        break;
                }
            }

            return pck;
        }

Jan 5, 2013 at 5:25 AM
Edited Jan 5, 2013 at 5:33 AM

At first glance without checking all the code you posted I noticed that you specified a TableStyles.None parameter with the .LoadFromDataTable method.  This randomly can cause a corrupted xlsx spreadsheet.  Have a Look at this thread and my last post at the bottom:  http://epplus.codeplex.com/discussions/349770.  So firstly try .LoadFromDataTable with no TableStyles.x parameter at all.

I don't think I can try the code you posted as it does not include stuff like a class definition for portlet and what is loaded to datatable dt from rs.

Jan 7, 2013 at 10:19 PM

I tried your suggestion.  It didn't work: Excel completed file level validation and repair. Some parts of this workbook may have been repaired or discarded.Repaired Records: PivotTable report from /xl/pivotTables/pivotTable1.xml part (PivotTable view)

The data loaded is dynamic.  The definitions for the pivot tables are stored in a database, then loaded into these classes.  Sorry for the delay.  Even though I have the email me replies checked down there, I don't get them.

    public class Portlet
    {
        [DataMember]
        public string url { get; set; }
        [DataMember]
        public string title { get; set; }
        [DataMember]
        public int portletId { get; set; }
        [DataMember]
        public int userPortletId { get; set; }
        [DataMember]
        public string type { get; set; }
        [DataMember]
        public bool fill { get; set; }
        [DataMember]
        public bool alive { get; set; }
        [DataMember]
        public string groupField { get; set; }
        [DataMember]
        public string curSymbol { get; set; }
        [DataMember]
        public int curScale { get; set; }
        [DataMember]
        public List<PortletColumn> cols { get; set; }
        [DataMember]
        public string pivotAxisTitle { get; set; }
        [DataMember]
        public List<PortletAxis> axes { get; set; }
        [DataMember]
        public List<PortletAggregate> aggregates { get; set; }

        public List<PortletParameter> parameters { get; set; }

        public Portlet()
        {
            this.cols = new List<PortletColumn>();
            this.axes = new List<PortletAxis>();
            this.aggregates = new List<PortletAggregate>();
        }

    }
   public class PortletColumn
    {
        [DataMember]
        public string name { get; set; }
        [DataMember]
        public string renderer { get; set; }
        [DataMember]
        public string type { get; set; }
        [DataMember]
        public string summaryType { get; set; }
        [DataMember]
        public string text { get; set; }
        [DataMember]
        public bool hidden { get; set; }

        public class DataTypes
        {
            public const string STRING = "string";
            public const string NUMBER = "number";
            public const string INTEGER = "int";
            public const string CURRENCY = "currency";
        }

        public class SummaryTypes
        {
            public const string COUNT = "count";
            public const string SUM = "sum";
            public const string MAX = "max";
            public const string MIN = "min";
            public const string AVERAGE = "average";
            public const string DISTINCT_COUNT = "distinct";
        }

        public string GetHeader()
        {
            return this.text.IsEmpty() ? this.name : this.text;
        }

        public string GetFormatter(string curFormat)
        {
            switch (this.type.ToLower())
            {
                case PortletColumn.DataTypes.INTEGER:
                    return "#,##0";
                case PortletColumn.DataTypes.NUMBER:
                    return "#,##0.0";
                case PortletColumn.DataTypes.CURRENCY:
                    return curFormat;
            }
            return "";
        }
    }
    public class PortletAxis
    {
        [DataMember]
        public string axisPosition { get; set; }
        [DataMember]
        public string dataIndex { get; set; }
        [DataMember]
        public string title { get; set; }

        public class Positions
        {
            public const string LEFT = "left";
            public const string TOP = "top";
        }
    }
    public class PortletAggregate
    {
        [DataMember]
        public string dataIndex { get; set; }
        [DataMember]
        public string title { get; set; }
        [DataMember]
        public string summaryType { get; set; }
        [DataMember]
        public string renderer { get; set; }

        public class SummaryTypes
        {
            public const string COUNT = "count";
            public const string SUM = "sum";
            public const string MAX = "max";
            public const string MIN = "min";
            public const string AVERAGE = "average";
        }
    }

Jan 11, 2013 at 10:04 AM

subtotal hiding is possible in new version of epplus dll.. i tried it.. and i succeded.. :)

Jan 11, 2013 at 2:00 PM

can you post some code?

Jan 11, 2013 at 2:29 PM
Edited Jan 11, 2013 at 2:31 PM

I pasted all the code you posted into Visual Studio.  It has flagged a missing class definition for PortletParameter.  Also I need to know what raw data you are using which I presume you are passing to the function:

 

public static ExcelPackage ExportDataSetAsExcelPivot(this SqlDataReader rs, string sheetName, string title, Portlet portlet)

 

When I can start flogging the debugger I will try to find a fix that disables row subtotals on a pivot table.

Jan 11, 2013 at 3:17 PM

PortletParameter isn't used in this case, you can delete that property from the Portlet class.

Here's raw from one of my tests (it's in JSON)

{
    "userPortletId": 6,
    "url": "",
    "portletId": 55017,
    "alive": true,
    "fill": false,
    "groupField": "",
    "curSymbol": "$",
    "curScale": 2,
    "pivotAxisTitle": "Table",
    "title": "Price Table Analysis",
    "name": "Price Table Analysis",
    "type": "pivotGrid",
    "cols": [
        {
            "hidden": false,
            "name": "mly",
            "renderer": "",
            "summaryType": "",
            "text": "Layer",
            "type": "string"
        },
        {
            "hidden": false,
            "name": "mrg",
            "renderer": "",
            "summaryType": "",
            "text": "Group",
            "type": "string"
        },
        {
            "hidden": false,
            "name": "mrw",
            "renderer": "",
            "summaryType": "",
            "text": "Discount",
            "type": "string"
        },
        {
            "hidden": false,
            "name": "mcl",
            "renderer": "",
            "summaryType": "",
            "text": "Column",
            "type": "string"
        },
        {
            "hidden": false,
            "name": "amt",
            "renderer": "",
            "summaryType": "",
            "text": "Price",
            "type": "number"
        }
    ],
    "axes": [
        {
            "axisPosition": "left",
            "dataIndex": "mly",
            "title": "Layer"
        },
        {
            "axisPosition": "left",
            "dataIndex": "mrg",
            "title": "Group"
        },
        {
            "axisPosition": "left",
            "dataIndex": "mrw",
            "title": "Discount"
        },
        {
            "axisPosition": "top",
            "dataIndex": "mcl",
            "title": "Column"
        }
    ],
    "aggregates": [
        {
            "dataIndex": "amt",
            "renderer": "PriceTableAmount",
            "summaryType": "",
            "title": "Price"
        }
    ],
    "data": [
        {
            "mly": "Base Prices",
            "mrg": "Early Bird",
            "mrw": "(EMP) Employee",
            "mcl": "A",
            "amt": 25
        },
        {
            "mly": "Base Prices",
            "mrg": "Early Bird",
            "mrw": "(STU) Student",
            "mcl": "A",
            "amt": 20
        },
        {
            "mly": "Base Prices",
            "mrg": "Standard Rates",
            "mrw": "(ADU) Adult",
            "mcl": "A",
            "amt": 30
        },
        {
            "mly": "Base Prices",
            "mrg": "Standard Rates",
            "mrw": "(CHI) Child",
            "mcl": "A",
            "amt": 25
        },
        {
            "mly": "Base Prices",
            "mrg": "Standard Rates",
            "mrw": "(COM) Complimentary",
            "mcl": "A",
            "amt": 0
        },
        {
            "mly": "Base Prices",
            "mrg": "Early Bird",
            "mrw": "(EMP) Employee",
            "mcl": "B",
            "amt": 30
        },
        {
            "mly": "Base Prices",
            "mrg": "Early Bird",
            "mrw": "(STU) Student",
            "mcl": "B",
            "amt": 25
        },
        {
            "mly": "Base Prices",
            "mrg": "Standard Rates",
            "mrw": "(ADU) Adult",
            "mcl": "B",
            "amt": 35
        },
        {
            "mly": "Base Prices",
            "mrg": "Standard Rates",
            "mrw": "(CHI) Child",
            "mcl": "B",
            "amt": 30
        },
        {
            "mly": "Base Prices",
            "mrg": "Standard Rates",
            "mrw": "(COM) Complimentary",
            "mcl": "B",
            "amt": 0
        },
        {
            "mly": "Booking Fee",
            "mrg": "Standard Rates",
            "mrw": "(ADU) Adult",
            "mcl": "A",
            "amt": 5
        },
        {
            "mly": "Booking Fee",
            "mrg": "Standard Rates",
            "mrw": "(CHI) Child",
            "mcl": "A",
            "amt": 2.5
        },
        {
            "mly": "Booking Fee",
            "mrg": "Standard Rates",
            "mrw": "(ADU) Adult",
            "mcl": "B",
            "amt": 5
        },
        {
            "mly": "Booking Fee",
            "mrg": "Standard Rates",
            "mrw": "(CHI) Child",
            "mcl": "B",
            "amt": 2.5
        }
    ]
}
Jan 12, 2013 at 10:44 AM

The Portlet class seems to be missing a variable definition for as the JSON you pasted contains: 

 "name": "Price Table Analysis",

I am new to JSON and was not able to 'JavaScriptSerializer'-> 'Deserialize' the JSON you posted from a text file with an additional name
variable in the Portlet class as above. And also I re-ordered the Portlet class variables to follow the JSON you posted.
 If you could mail me a working visual studio solution set of files I could try that. Otherwise I cant assist further.
Jan 15, 2013 at 7:07 PM
Edited Jan 15, 2013 at 7:42 PM

The best I could do is a partial fix which can produce a xlsx spreadsheet that has a pivot table with no subtotals that is constrained to your pre defined data structure but can accept new values:-

1.  Run your original code that does not have rowField.SubTotalFunctions = eSubTotalFunctions.None; which produces a spreadsheet that has subtotals.

2.  Manually edit the spreadsheet in Excel to remove subtotals.  Delete the 'data' worksheet.  As the Pivot table does not automatically refresh it retains its structure  and old data from the 'data' worksheet that is now deleted as a placeholder. Save this spreadsheet as pivot_table_no_subtotals_template.xlsx.

3.  I commented out all of your code that creates the pivot table worksheet and the code that creates and defines ExcelPivotTable pivot = wsPivot.PivotTables.Add(wsPivot.Cells["A4"], dataRange, title); I added code that loads
pivot_table_no_subtotals_template.xlsx as a template file.  So the ExcelPackage pck when created has the pre-defined 'Pivot' worksheet with the pivot table that has no subtotals injected into it.  Your code that defines the structure and values of the data is retained.  I changed the values of the data to demonstrate that the pivot table which references the 'data' worksheet will update correctly when the new final spreadsheet is opened in Excel. 

Using the Microsoft Office Open XML SDK 2.5 Productivity Tool I compared the spreadsheet that has pivot table subtotals to another spreadsheet that I manually removed the subtotals in Excel.  The no subtotals xlsx xml package had changes in almost every xml file and was 26kb in total size (unzipped) compared to 12kb total size (unziped) of the xlsx xml package that has subtotals.

And this Microsoft tool did not detect errors from a spreadsheet generated using your original code with rowField.SubTotalFunctions = eSubTotalFunctions.None; included \ un-commented when using the 'Validate' button. 

OpenOffice 3.0 Calc and LibreOffice 3.6 Calc actually can open the xlsx spreadsheet that has
rowField.SubTotalFunctions = eSubTotalFunctions.None; included in your code.  But almost all the custom formatting\styles are not translated.  Even though the Pivot table with no sub totals is.  Excel 2007 and online Excel through SkyDrive report it as corrupted \ cant open the spreadsheet.

The modified code that uses a temaplate xlsx file generated from your old code:-

 

using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Runtime.Serialization;
using OfficeOpenXml;
using System.ComponentModel;
using System.Data;
using OfficeOpenXml.Table;
using OfficeOpenXml.Table.PivotTable;

namespace EPPlusSubtotalTest
{
    class Program
    {
        static void Main(string[] args)
        {
            FileInfo newFile = new FileInfo(Path.Combine(Path.GetTempPath(), @"d:\sample1.xlsx")); // CHANGED
            if (newFile.Exists)
            {
                newFile.Delete();
                newFile = new FileInfo(Path.Combine(Path.GetTempPath(), @"d:\sample1.xlsx")); // CHANGED
            }


            Portlet portlet = new Portlet()
                {
                    userPortletId = 6,
                    url = "",
                    portletId = 55017,
                    alive = true,
                    fill = false,
                    groupField = "",
                    curSymbol = "$",
                    curScale = 2,
                    pivotAxisTitle = "Table",
                    title = "Price Table Analysis",
                    type = "pivotGrid"
                };
            portlet.cols.Add(new PortletColumn{hidden = false,name = "mly",renderer = "",summaryType = "",text = "Layer",type = "string"});
            portlet.cols.Add(new PortletColumn{hidden = false,name = "mrg",renderer = "",summaryType = "",text = "Group",type = "string"});
            portlet.cols.Add(new PortletColumn{hidden = false,name = "mrw",renderer = "",summaryType = "",text = "Discount",type = "string"});
            portlet.cols.Add(new PortletColumn{hidden = false,name = "mcl",renderer = "",summaryType = "",text = "Column",type = "string"});
            portlet.cols.Add(new PortletColumn{hidden = false,name = "amt",renderer = "",summaryType = "",text = "Price",type = "number"});

            portlet.axes.Add(new PortletAxis{axisPosition = "left",dataIndex = "mly",title = "Layer"});
            portlet.axes.Add(new PortletAxis{axisPosition = "left",dataIndex = "mrg",title = "Group"});
            portlet.axes.Add(new PortletAxis{axisPosition = "left",dataIndex = "mrw",title = "Discount"});
            portlet.axes.Add(new PortletAxis{axisPosition = "top",dataIndex = "mcl",title = "Column"});

            portlet.aggregates.Add(new PortletAggregate{dataIndex = "amt", renderer = "PriceTableAmount", summaryType = "", title = "Price"});
            // CHANGED VALUES
            portlet.data.Add(new DataObject { mly = "Base Prices", mrg = "Early Bird", mrw = "(EMP) Employee", mcl = "A", amt = 50 });
            portlet.data.Add(new DataObject { mly = "Base Prices", mrg = "Early Bird", mrw = "(STU) Student", mcl = "A", amt = 40 });
            portlet.data.Add(new DataObject { mly = "Base Prices", mrg = "Standard Rates", mrw = "(ADU) Adult", mcl = "A", amt = 60 });
            portlet.data.Add(new DataObject { mly = "Base Prices", mrg = "Standard Rates", mrw = "(CHI) Child", mcl = "A", amt = 50 });
            portlet.data.Add(new DataObject { mly = "Base Prices", mrg = "Standard Rates", mrw = "(COM) Complimentary", mcl = "A", amt = 0 });
            portlet.data.Add(new DataObject { mly = "Base Prices", mrg = "Early Bird", mrw = "(EMP) Employee", mcl = "B", amt = 60 });
            portlet.data.Add(new DataObject { mly = "Base Prices", mrg = "Early Bird", mrw = "(STU) Student", mcl = "B", amt = 50 });
            portlet.data.Add(new DataObject { mly = "Base Prices", mrg = "Standard Rates", mrw = "(ADU) Adult", mcl = "B", amt = 70 });
            portlet.data.Add(new DataObject { mly = "Base Prices", mrg = "Standard Rates", mrw = "(CHI) Child", mcl = "B", amt = 60 });
            portlet.data.Add(new DataObject { mly = "Base Prices", mrg = "Standard Rates", mrw = "(COM) Complimentary", mcl = "B", amt = 0 });
            portlet.data.Add(new DataObject { mly = "Booking Fee", mrg = "Standard Rates", mrw = "(ADU) Adult", mcl = "A", amt = 10 });
            portlet.data.Add(new DataObject { mly = "Booking Fee", mrg = "Standard Rates", mrw = "(CHI) Child", mcl = "A", amt = 5.5 });
            portlet.data.Add(new DataObject { mly = "Booking Fee", mrg = "Standard Rates", mrw = "(ADU) Adult", mcl = "B", amt = 10.5 });
            portlet.data.Add(new DataObject { mly = "Booking Fee", mrg = "Standard Rates", mrw = "(CHI) Child", mcl = "B", amt = 5.5 });

            string curFormat = "$#,##0.00";
            //string sheetName = "Pivot";
            //string title = "Test Pivot Table";

            List<PortletColumn> columns = portlet.cols;
            List<PortletAxis> axes = portlet.axes;
            List<PortletAggregate> aggregates = portlet.aggregates;

            FileInfo pivot_table_template_spreadsheet = new FileInfo(@"d:\pivot_table_no_subtotals_template.xlsx"); // NEW

            ExcelPackage pck = new ExcelPackage(newFile, pivot_table_template_spreadsheet);
            //ExcelWorksheet wsPivot = pck.Workbook.Worksheets.Add(sheetName);
            ExcelWorksheet wsData = pck.Workbook.Worksheets.Add("data");

            DataTable dt = ToDataTable(portlet.data);
            int i = 1;
            int record = 1;
            foreach (PortletColumn column in columns)
            {
                wsData.Column(i).Style.Numberformat.Format = column.GetFormatter(curFormat);
                try
                {
                    dt.Columns[column.name].ColumnName = column.GetHeader();
                }
                catch (DuplicateNameException) { }

                i++;
            }
            record++;

            List<PortletAxis> leftAxes = axes.Where(a => a.axisPosition.ToLower() == PortletAxis.Positions.LEFT).ToList();
            int leftAxesCount = leftAxes.Count();
            List<PortletAxis> topAxes = axes.Where(a => a.axisPosition.ToLower() == PortletAxis.Positions.TOP).ToList();
            int topAxesCount = topAxes.Count();


            wsData.Cells["A1"].LoadFromDataTable(dt, true);

            ExcelRange dataRange = wsData.Cells[wsData.Dimension.Address.ToString()];
            dataRange.AutoFitColumns();


            //The title in the first cell.  It's a pivot so we don't know what the total columns will be (and they channge as you use the pivot)
            /*wsPivot.Cells["A1"].Value = title;
            wsPivot.Cells["A1"].Style.Font.Bold = true;
            wsPivot.Cells["A1"].Style.Font.Size = 14;

            ExcelPivotTable pivot = wsPivot.PivotTables.Add(wsPivot.Cells["A4"], dataRange, title);
            pivot.MultipleFieldFilters = true;
            pivot.RowGrandTotals = true;
            pivot.ColumGrandTotals = true;
            pivot.Compact = true;
            pivot.CompactData = true;
            pivot.GridDropZones = true;
            pivot.Outline = false;
            pivot.OutlineData = false;
            pivot.ShowError = true;
            pivot.ErrorCaption = "[error]";
            pivot.ShowHeaders = true;
            pivot.UseAutoFormatting = true;
            pivot.ApplyWidthHeightFormats = true;
            pivot.ShowDrill = true;
            pivot.FirstDataCol = leftAxesCount + 1;
            pivot.TableStyle = TableStyles.Medium2;
            pivot.RowHeaderCaption = portlet.pivotAxisTitle;*/


            /*foreach (PortletAxis axis in leftAxes)
            {
                ExcelPivotTableField rowField = pivot.Fields[axis.dataIndex];
                if (rowField == null)
                {
                    string dataIndex = columns.Single(c => c.name == axis.dataIndex).GetHeader();
                    rowField = pivot.Fields[dataIndex];
                }
                //THIS LINE BELOW IS CAUSING PROBLEMS, uncomment and Excel is happy
                rowField.SubTotalFunctions = eSubTotalFunctions.None;
                pivot.RowFields.Add(rowField);
            }

            foreach (PortletAxis axis in topAxes)
            {
                ExcelPivotTableField colField = pivot.Fields[axis.dataIndex];
                if (colField == null)
                {
                    string dataIndex = columns.Single(c => c.name == axis.dataIndex).GetHeader();
                    colField = pivot.Fields[dataIndex];
                }
                pivot.ColumnFields.Add(colField);
            }

            foreach (PortletAggregate agg in aggregates)
            {
                ExcelPivotTableField aggField = pivot.Fields[agg.dataIndex];
                PortletColumn col = columns.Single(c => c.name == agg.dataIndex);
                if (aggField == null)
                {
                    aggField = pivot.Fields[col.GetHeader()];
                }
                //aggField.SubTotalFunctions = eSubTotalFunctions.None;
                ExcelPivotTableDataField dataField = pivot.DataFields.Add(aggField);
                dataField.Format = col.GetFormatter(portlet.curSymbol);
                switch (agg.summaryType.ToLower())
                {
                    case PortletAggregate.SummaryTypes.COUNT:
                        dataField.Function = DataFieldFunctions.Count;
                        break;
                    case PortletAggregate.SummaryTypes.SUM:
                        dataField.Function = DataFieldFunctions.Sum;
                        break;
                    case PortletAggregate.SummaryTypes.MAX:
                        dataField.Function = DataFieldFunctions.Max;
                        break;
                    case PortletAggregate.SummaryTypes.MIN:
                        dataField.Function = DataFieldFunctions.Min;
                        break;
                    case PortletAggregate.SummaryTypes.AVERAGE:
                        dataField.Function = DataFieldFunctions.Average;
                        break;
                    default:
                        dataField.Function = DataFieldFunctions.None;
                        break;
                }
            }*/

            pck.Save();
            Console.WriteLine();
            Console.WriteLine("Press the return key to exit...");
            Console.Read();
        }

        private static DataTable ToDataTable<T>(IList<T> list)
        {
            PropertyDescriptorCollection props =
                TypeDescriptor.GetProperties(typeof(T));
            DataTable table = new DataTable();
            for (int i = 0; i < props.Count; i++)
            {
                PropertyDescriptor prop = props[i];
                table.Columns.Add(prop.Name, prop.PropertyType);
            }
            object[] values = new object[props.Count];
            foreach (T item in list)
            {
                for (int i = 0; i < values.Length; i++)
                {
                    values[i] = props[i].GetValue(item);
                }
                table.Rows.Add(values);
            }
            return table;
        }
    }

    public class Portlet
    {
        public string url { get; set; }
        public string title { get; set; }
        public int portletId { get; set; }
        public int userPortletId { get; set; }
        public string type { get; set; }
        public bool fill { get; set; }
        public bool alive { get; set; }
        public string groupField { get; set; }
        public string curSymbol { get; set; }
        public int curScale { get; set; }
        public List<PortletColumn> cols { get; set; }
        public string pivotAxisTitle { get; set; }
        public List<PortletAxis> axes { get; set; }
        public List<PortletAggregate> aggregates { get; set; }
        public List<DataObject> data { get; set; }

        public Portlet()
        {
            this.cols = new List<PortletColumn>();
            this.axes = new List<PortletAxis>();
            this.aggregates = new List<PortletAggregate>();
            this.data = new List<DataObject>();
        }

    }

    public class PortletColumn
    {
        public string name { get; set; }
        public string renderer { get; set; }
        public string type { get; set; }
        public string summaryType { get; set; }
        public string text { get; set; }
        public bool hidden { get; set; }

        public class DataTypes
        {
            public const string STRING = "string";
            public const string NUMBER = "number";
            public const string INTEGER = "int";
            public const string CURRENCY = "currency";
        }

        public class SummaryTypes
        {
            public const string COUNT = "count";
            public const string SUM = "sum";
            public const string MAX = "max";
            public const string MIN = "min";
            public const string AVERAGE = "average";
            public const string DISTINCT_COUNT = "distinct";
        }

        public string GetHeader()
        {
            return this.text == "" ? this.name : this.text;
        }

        public string GetFormatter(string curFormat)
        {
            switch (this.type.ToLower())
            {
                case PortletColumn.DataTypes.INTEGER:
                    return "#,##0";
                case PortletColumn.DataTypes.NUMBER:
                    return "#,##0.0";
                case PortletColumn.DataTypes.CURRENCY:
                    return curFormat;
            }
            return "";
        }
    }
    public class PortletAxis
    {
        public string axisPosition { get; set; }
        public string dataIndex { get; set; }
        public string title { get; set; }

        public class Positions
        {
            public const string LEFT = "left";
            public const string TOP = "top";
        }
    }
    public class PortletAggregate
    {
        public string dataIndex { get; set; }
        public string title { get; set; }
        public string summaryType { get; set; }
        public string renderer { get; set; }

        public class SummaryTypes
        {
            public const string COUNT = "count";
            public const string SUM = "sum";
            public const string MAX = "max";
            public const string MIN = "min";
            public const string AVERAGE = "average";
        }
    }

    public class DataObject
    {
        public string mly { get; set; }
        public string mrg { get; set; }
        public string mrw { get; set; }
        public string mcl { get; set; }
        public double amt { get; set; }
    }

}

Jan 16, 2013 at 3:50 AM

Thanks for all the help.  I really appreciate it.  Unfortunately, it doesn't fix my problem.  The pivots are user defined and the definition is saved in the database.  I couldn't create the template file with the subtotals removed as the data, columns, axes, and aggregates are all dynamic.  I'll just stick with the "You can disable them yourself in Excel" approach for users until, hopefully, this is fixed at some point.