Link to home
Start Free TrialLog in
Avatar of 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)
                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);


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

Open in new window

Avatar of Ken Butters
Ken Butters
Flag of United States of America image

Link to home
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


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) 
    With Selection 
        Selection.NumberFormat = "General" 
        .Value = .Value 
    End With 
End Sub 

Open in new window

Perfect. Thank you.