Link to home
Start Free TrialLog in
Avatar of jssong2000
jssong2000

asked on

create excel pivot in c#

I need to create pivot in excel. But both Grand Total and Total give me 0.
This is the first time I try pivot. I don't know what's wrong.
Any help and comments will be very appreciated!!!

Please see the excel file attached.

This is the function CreatePivotSheet:
        private void CreatePivotSheet(int sheet, string sheetName)
        {
            try
            {
                if (worksheet.UsedRange.Rows.Count > 2)
                {
                    Microsoft.Office.Interop.Excel.PivotCaches pivotCaches = workbook.PivotCaches();
                    string sourceData = "LCLD Report!R1C1:R" + worksheet.UsedRange.Rows.Count + "C" + worksheet.UsedRange.Columns.Count;
                    Microsoft.Office.Interop.Excel.PivotCache pivotCache = pivotCaches.Add(Microsoft.Office.Interop.Excel.XlPivotTableSourceType.xlDatabase, sourceData);

                    worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[sheet];
                    worksheet.Name = sheetName;

                    Microsoft.Office.Interop.Excel.Range oRng = worksheet.get_Range("A3", "A3").Cells;
                    Microsoft.Office.Interop.Excel.PivotTable pivotTable = pivotCache.CreatePivotTable(oRng, "PT1", Type.Missing, Microsoft.Office.Interop.Excel.XlPivotTableVersionList.xlPivotTableVersionCurrent);
                    Microsoft.Office.Interop.Excel.PivotField pivotField;

                    //object[] noSub = { false, false, false, false, false, false, false, false, false, false, false, false };
                    object missing = System.Reflection.Missing.Value;


                    pivotField = (Microsoft.Office.Interop.Excel.PivotField)pivotTable.PivotFields("Letter Count");
                    pivotField.Orientation = Microsoft.Office.Interop.Excel.XlPivotFieldOrientation.xlDataField;
                    pivotField.Function = Microsoft.Office.Interop.Excel.XlConsolidationFunction.xlSum;
                    pivotField.Position = 1;
                    pivotField.Name = "Sum of Letter Count";

                    pivotField = (Microsoft.Office.Interop.Excel.PivotField)pivotTable.PivotFields("Letter Description");
                    pivotField.Orientation = Microsoft.Office.Interop.Excel.XlPivotFieldOrientation.xlRowField;
                    pivotField.Position = 1;
                    //pivotField.set_Subtotals(missing, noSub);

                    //pivotTable.AddDataField(pivotTable.PivotFields("Letter Count"), "Sum of Letter Count", Microsoft.Office.Interop.Excel.XlConsolidationFunction.xlSum);



                    //pivotTable.Format(Microsoft.Office.Interop.Excel.XlPivotFormatType.xlReport9);

                    workbook.ShowPivotTableFieldList = false;
                    worksheet.Cells.Font.Name = "Arial Narrow";
                    worksheet.Cells.Font.Size = 8;
                }
            }

Open in new window

LCLD--2012-11-07-08-57-36.xls
ASKER CERTIFIED SOLUTION
Avatar of Ken Butters
Ken Butters
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of jssong2000
jssong2000

ASKER

I am sorry. How to convert the Letter Count column to numeric in program? Thank you!
This code should do it.

Sub convertToNumber(textRange as range) 
    textRange.Select 
    With Selection 
        Selection.NumberFormat = "General" 
        .Value = .Value 
    End With 
End Sub 

Open in new window

Perfect. Thank you.