none
PIVOTCHART- Based on pivot table using VB.net RRS feed

  • Question

  • Hi. First of all, I am really sorry if my question seems like silly and simple question. I am new and beginner for programming. 

    I've created a pivot table using vb.net. And I need to do a pivot chart based on pivot table. I've searched to make a pivot chart using same method as pivot table but  I cannot the answer  and the steps to do pivot chart.

    What I've found is, do the chart using VSTO, but I really don't understand how it works and I don't really familiar with that. I want to try avoid using third party. So, I am using Interop.Excel.

    This is the code  that I do for pivot table. 

    Dim excelApp As Excel.Application = New Excel.Application
            Dim excelWorkBook As Excel.Workbook = excelApp.Workbooks.Open("~\result.xls")
            Dim excelworksheet As Excel.Worksheet = excelWorkBook.ActiveSheet
            Dim sheet2 As Excel.Worksheet = excelWorkBook.Sheets.Add
            ' Added new sheet to create Pivot Table
            sheet2.Name = "Pivot Table"
            ' Assigned sheet Name
            excelworksheet.Activate()
            Dim oRange As Excel.Range = excelworksheet.UsedRange
            Dim oPivotCache As Excel.PivotCache = CType(excelWorkBook.PivotCaches.Add(Excel.XlPivotTableSourceType.xlDatabase, oRange), Excel.PivotCache)
            Dim oRange2 As Excel.Range = sheet2.Cells(1, 1)
            Dim pch As Excel.PivotCaches = excelWorkBook.PivotCaches
            pch.Add(Microsoft.Office.Interop.Excel.XlPivotTableSourceType.xlDatabase, oRange).CreatePivotTable(sheet2.Cells(1, 1), "PivTbl_1", Type.Missing, Type.Missing)
            Dim pvt As Excel.PivotTable = CType(sheet2.PivotTables("PivTbl_1"), Excel.PivotTable)
            pvt.ShowDrillIndicators = False
            Dim fld As Excel.PivotField = CType(pvt.PivotFields("Customer"), Excel.PivotField)
            ' Create a Pivot Field in Pivot table
            fld.Orientation = Excel.XlPivotFieldOrientation.xlRowField
            ' Add the pivot field as Row Field
            fld.Subtotals(1) = False
            'Remove Subtotals for each row and column 
            fld = CType(pvt.PivotFields("Aging Date"), Excel.PivotField)
            fld.Orientation = Excel.XlPivotFieldOrientation.xlRowField
            fld.Subtotals(1) = False
            fld = CType(pvt.PivotFields("Material Number"), Excel.PivotField)
            fld.Orientation = Excel.XlPivotFieldOrientation.xlDataField
            fld = CType(pvt.PivotFields("Shipment Quantity"), Excel.PivotField)
            fld.Orientation = Excel.XlPivotFieldOrientation.xlDataField
            fld.Function = Excel.XlConsolidationFunction.xlSum
            fld = CType(pvt.PivotFields("Extended"), Excel.PivotField)
            fld.Orientation = Excel.XlPivotFieldOrientation.xlDataField
            fld.Function = Excel.XlConsolidationFunction.xlSum
            fld.NumberFormat = "$###,####"
            fld = pvt.PivotFields("Data")
            fld.Orientation = Excel.XlPivotFieldOrientation.xlColumnField
            ' Sort column set as datafield to show the Pivot table as per requirement- It will show the total count of data and not needed so later on we will hide this Column
            sheet2.UsedRange.Columns.AutoFit()
            pvt.ColumnGrand = True
            pvt.RowGrand = True
            ' Used to hide Grand total for Rows
            excelApp.DisplayAlerts = False
            ' Used to hide unappropriate message prompt from Excel
            excelworksheet.Delete()
            ' Delete the Sheet with Raw data because not needed and we created new sheet which represent data in pivot table format
            sheet2.Activate()
            ' Set focus on Sheet Containing data in Pivot table format
            'sheet2.get_Range("A1", "A1").Select()
            Dim OutputPath As String = "~\result1.xls"
            ' Set focus of column J to hide Pivot Table Field List (Left pane) when we open the file
            excelWorkBook.SaveAs(OutputPath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing)
            ' Used to Save as and overwrite the Excel file if already exist
            excelApp.DisplayAlerts = True
            ' Reset the property of Excel
            excelWorkBook.Close()
            ' Close the workbook
            excelApp.Quit()
            ' Quit the Excel application

    Please do anyone give me any suggestion on how should I do pivot chart. Thanks and I am really sorry for being this noob. 

    Thursday, May 23, 2019 8:15 AM

Answers

All replies