locked
PIvot tables in excel RRS feed

  • Question

  • Hey all,
        I have exported the data from a dataset to an excel sheet. I have also created a chart of that data through interop. Now I need to put the same data from the same dataset to a pivot table and also need to create the pivot chart for the same in an excel sheet. I am working in .NET 2.0 environment. Can anybody help me!
    akhilesh
    Tuesday, April 28, 2009 5:56 AM

Answers

  • Here's an alternative to generate the PivotTable
    This uses the Excel Extension methods "Power Tools"

            public void generatePivotTable()
            {
    
                
                Excel.Range arng;
                Excel.Worksheet asht;
                object[,] data;
                
                asht = (Excel.Worksheet)(Application.ActiveSheet);
                arng = asht.Range("a1:b4");
                data= (object[,])(arng.Value2);
    
                data[1, 1] = "Name";
                data[1, 2] = "Volume";
                data[2, 1] = "Mark";
                data[2, 2] = 2;
                data[3, 1] = "Luke";
                data[3, 2] = 2;
                data[4, 1] = "Mark";
                data[4, 2] = 3;
    
    
                arng.Value2 = data;
    
                WorksheetPivotTableWizardArgs ptwArgs = new WorksheetPivotTableWizardArgs(){ 
                    SourceData = arng ,
                    SourceType = Excel.XlPivotTableSourceType.xlDatabase,
                    TableName = "PivotTable1",
                    TableDestination = asht.Range("a15"),
                    
                };
    
                Excel.PivotTable table1 = asht.PivotTableWizard(ptwArgs);
                Excel.PivotField afld = (Excel.PivotField)(table1.PivotFields("Name"));
                afld.Orientation = Excel.XlPivotFieldOrientation.xlRowField;
                afld.Position = 1;
                Excel.PivotField dataField = (Excel.PivotField)(table1.PivotFields("Volume"));
                table1.AddDataField(dataField, "Tot Volume", Excel.XlConsolidationFunction.xlSum);
    
                
            }

    Tuesday, April 28, 2009 9:21 AM
  • Hey Jose,
         Thanks a lot for your support. It really worked. What if I have to save this file to a client machine? How do I do it using open save file dialog?
    akhilesh
    Thursday, April 30, 2009 6:21 AM

All replies


  • Hi akhilesh!

    The following sample creates a pivot table and chart for the Northwind database's Customers table,
    given that the database table has been laid out in the first worksheet.
    The pivot table shows the number of customers per country:



    object missing = System.Type.Missing;
    string dataColumnName = "Country";
    Excel.Application application = Globals.ThisAddIn.Application;

    // Get the source and destination ranges
    Excel.Range sourceRange = ((Excel.Worksheet)application.ActiveSheet).UsedRange;
    Excel.Worksheet destinationSheet = (Excel.Worksheet)application.Worksheets[2];
    Excel.Range destinationRange = (Excel.Range)destinationSheet.Cells[1, 1];

    // Create the pivot table
    Excel.PivotCache pivotCache = application.ActiveWorkbook.PivotCaches().Add(
        Excel.XlPivotTableSourceType.xlDatabase,
        sourceRange);
    Excel.PivotTable pivotTable = pivotCache.CreatePivotTable(
        destinationRange,
        "Customers By Country",
        missing,
        missing);

    pivotTable.AddFields(
        dataColumnName,
        missing,
        missing,
        missing);
    Excel.PivotField pivotField = (Excel.PivotField)pivotTable.PivotFields(dataColumnName);
    pivotField.Orientation = Excel.XlPivotFieldOrientation.xlDataField;
    pivotField.Function = Excel.XlConsolidationFunction.xlCount;
    pivotTable.ManualUpdate = true;

    // Create the chart
    destinationSheet = (Excel.Worksheet)application.Worksheets[3];
    Excel.ChartObjects chartObjects = (Excel.ChartObjects)destinationSheet.ChartObjects(missing);
    Excel.ChartObject chart = chartObjects.Add(50, 200, 500, 300);
    chart.Chart.SetSourceData(
        destinationRange,
        Excel.XlRowCol.xlColumns);

    Tuesday, April 28, 2009 7:59 AM
  • Here's an alternative to generate the PivotTable
    This uses the Excel Extension methods "Power Tools"

            public void generatePivotTable()
            {
    
                
                Excel.Range arng;
                Excel.Worksheet asht;
                object[,] data;
                
                asht = (Excel.Worksheet)(Application.ActiveSheet);
                arng = asht.Range("a1:b4");
                data= (object[,])(arng.Value2);
    
                data[1, 1] = "Name";
                data[1, 2] = "Volume";
                data[2, 1] = "Mark";
                data[2, 2] = 2;
                data[3, 1] = "Luke";
                data[3, 2] = 2;
                data[4, 1] = "Mark";
                data[4, 2] = 3;
    
    
                arng.Value2 = data;
    
                WorksheetPivotTableWizardArgs ptwArgs = new WorksheetPivotTableWizardArgs(){ 
                    SourceData = arng ,
                    SourceType = Excel.XlPivotTableSourceType.xlDatabase,
                    TableName = "PivotTable1",
                    TableDestination = asht.Range("a15"),
                    
                };
    
                Excel.PivotTable table1 = asht.PivotTableWizard(ptwArgs);
                Excel.PivotField afld = (Excel.PivotField)(table1.PivotFields("Name"));
                afld.Orientation = Excel.XlPivotFieldOrientation.xlRowField;
                afld.Position = 1;
                Excel.PivotField dataField = (Excel.PivotField)(table1.PivotFields("Volume"));
                table1.AddDataField(dataField, "Tot Volume", Excel.XlConsolidationFunction.xlSum);
    
                
            }

    Tuesday, April 28, 2009 9:21 AM
  • Hey Jose,
         Thanks a lot for your support. It really worked. What if I have to save this file to a client machine? How do I do it using open save file dialog?
    akhilesh
    Thursday, April 30, 2009 6:21 AM
  • Hey incre-d,
           Thanks a lot. It really helped me move further in my project. Actually i was stuck a bit in that. Thanks a lot.
           Can you also tell me how to save this output file using an open save dialog or how to export it to the client machine using opn save dialog?Plz
          
    akhilesh
    Thursday, April 30, 2009 6:25 AM
  • Hi akhilesh!

    If you want to bring up the Save File dialog, you can use the following method.
    Just fill-in the parameters using System.Type.Missing:


    Globals.ThisAddIn.Application.Dialogs[Excel.XlBuiltInDialog.xlDialogSaveAs].Show();

    P.S:
    Don't forget to mark a post as "Answer" or "Helpful" if it helped :-)

    Thursday, April 30, 2009 7:09 AM
  • Hey Jose,
        Thanks for your prompt reply.But this method is not working. Actually I am wrking in ASP.NET 2.0. I was not able to find 'Globals' anywhere. Can you plz send an alternative for the same.
     I'll repeat my problem. I want to save this excel generated report to the server and then make the client inport this file as an attachment. Is it possible to do so? 
     Thank you.
    akhilesh
    Monday, May 4, 2009 10:45 AM