PIVOTCHART- Based on pivot table using VB.net

[stizalke]

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.

Continue reading...