Create a Pivot table report using vb.net

spowari · March 24th, 2005, 02:30 PM

Hi All!!

I need to create a pivot table using VB.NET. I am using a dataset to create a Excel pivot table. Can some one suggest how should I go about it?

Please ASAP.

Thanks and regards
Spowari

gokul_blr · March 28th, 2005, 04:38 AM

http://www.ozgrid.com/Excel/excel-pivot-tables.htm

Gokulan Ethiraj

spowari · March 28th, 2005, 04:09 PM

Hi Gokulan ,

Thanks a lot for the reply. The link that you sent me doesnt say a word about .NET . I need to create a Pivot table report using VB.NET in ASP.NET. I have all the data in a dataset.

I will appriciate ur help in this regard

Thanks
spowari

Hal Levy · March 29th, 2005, 02:19 PM

Are you using v2.0 of the framework or v1.1?

Hal Levy
I am here to help you, not do it for you.
That is, unless you hire me. I am looking for work.

kavitha_ram · November 28th, 2006, 09:54 AM

Hi,

Quote:

quote:Originally posted by spowari
Hi All!!

I need to create a pivot table using VB.NET. I am using a dataset to create a Excel pivot table. Can some one suggest how should I go about it?

Please ASAP.

Thanks and regards
Spowari

kavitha_ram · November 28th, 2006, 09:57 AM

Hi,
I have the same requirement. could you please help on how I can implement pivot table using dot net, if you have done this already?

thanks in advance,
kavitha

Quote:

quote:Originally posted by spowari
Hi Gokulan ,

Thanks a lot for the reply. The link that you sent me doesnt say a word about .NET . I need to create a Pivot table report using VB.NET in ASP.NET. I have all the data in a dataset.

I will appriciate ur help in this regard

Thanks
spowari

joshypala · December 11th, 2009, 04:22 PM

Private Sub CreatePivotTable(ByVal strClient As String)
Dim DS As New DataSet
Dim objPivotTable As Excel.PivotTable
Dim objField As Excel.PivotField
Dim strSQL As String
Dim strTitle As String
Dim strDept As String

'Grab the department name.
If Not strClient = "(All)" Then
strSQL = "SELECT FacilityName FROM Facilities WITH (NOLOCK) where FacilityCode = " & Left(strClient, strClient.Length - 2) + "'"
Dim command As New SqlCommand(strSQL, _Connection)
Dim adapter As New SqlDataAdapter(command)
adapter.Fill(DS)
strTitle = StrConv(DS.Tables(0).Rows(0).Item(0), VbStrConv.ProperCase)
Select Case Replace(Right(strClient, 2), "'", "")
Case "1"
strDept = "Liability"
Case "2"
strDept = "Outpatient"
Case "3"
strDept = "Inpatient"
Case "4"
strDept = "Out of State"
Case "6"
strDept = "Charity"
Case "8"
strDept = "Special Project"
Case "U"
strDept = "Undocumented"
End Select
Else
strTitle = Me.ReportTitle
strDept = "Rolled Up"
End If

objPivotTable = m_xlWS.PivotTableWizard
With objPivotTable
.PivotFields("Client").Orientation = Excel.XlPivotFieldOrientation.xlPageField
.PivotFields("Assignment Date").Orientation = Excel.XlPivotFieldOrientation.xlRowField
.PivotFields("Program").Orientation = Excel.XlPivotFieldOrientation.xlRowField

.PivotFields("Account").Orientation = Excel.XlPivotFieldOrientation.xlDataField
.PivotFields("Age").Orientation = Excel.XlPivotFieldOrientation.xlDataField

.DataFields(1).Function = Excel.XlConsolidationFunction.xlCount
.DataFields(2).Function = Excel.XlConsolidationFunction.xlAverage

.PivotFields("Count of Account").Caption = "Number of Accounts"
.PivotFields("Average of Age").NumberFormat = "0"
.PivotFields("Average of Age").Caption = "Average Turnaround from Placement to Certification"

.Format(Excel.XlPivotFormatType.xlReport1)
.Application.Cells.Font.Size = 8

.PivotFields("Client").CurrentPage = Replace(strClient, "'", "")

.NullString = "0"
End With

m_xlPivotWS = m_xlWB.ActiveSheet
With m_xlPivotWS
If strClient = "(All)" Then
.Name = "Rollup Pivot"
Else
.Name = Replace(strClient, "'", "") & " - Pivot Table"
End If
If Not _totals.ContainsKey(strDept) Then
_totals.Add(strDept, New Integer(1) {0, 0})
End If
.Rows("1:11").Insert(Excel.XlInsertShiftDirection. xlShiftDown)
'set the pivot page setup.
Dim pagesetup As Excel.PageSetup = .PageSetup
With pagesetup
.PrintTitleRows = "$1:$12"
.PrintTitleColumns = ""
.RightHeader = _
"&""Arial""&14Certification Turnaround Time Report by Placement Month" & Chr(10) & "&12" & strTitle & Chr(10) & strDept
.LeftHeader = "&G"
.LeftHeaderPicture.Filename = "C:\Inetpub\wwwroot\CHNWebPortal\Images\chn.pn g"
.LeftHeaderPicture.Height = 43
.LeftHeaderPicture.Width = 190
.LeftFooter = Today.Date()
.CenterFooter = "Confidential"
.RightFooter = "Â©2009 Cardon Healthcare Network, Inc."
.TopMargin = m_xlApp.InchesToPoints(0.5)
.BottomMargin = m_xlApp.InchesToPoints(0.8)
.HeaderMargin = m_xlApp.InchesToPoints(0.25)
.FooterMargin = m_xlApp.InchesToPoints(0.25)
.LeftMargin = m_xlApp.InchesToPoints(0.03)
.RightMargin = m_xlApp.InchesToPoints(0.03)
.CenterHorizontally = True
.Orientation = XlPageOrientation.xlLandscape
End With
End With

Dim temp() As Integer = _totals(strDept)
temp(0) += objPivotTable.GetData("'Number of Accounts' 'Grand Total'")
temp(1) += objPivotTable.GetData("'Average Turnaround from Placement to Certification' 'Grand Total'")
_totals(strDept) = temp

If Not objField Is Nothing Then
System.Runtime.InteropServices.Marshal.ReleaseComO bject(objField)
objField = Nothing
End If

If Not objPivotTable Is Nothing Then
System.Runtime.InteropServices.Marshal.ReleaseComO bject(objPivotTable)
objPivotTable = Nothing
End If
End Sub

abhinav864 · September 29th, 2010, 08:07 PM

Hi i have the same problem but i need both the codes ...that is i am displaying the pivot table on a web page using Visual studio.I need the asp.net code that is the .aspx file and the code behind it in vb or c#.

Thank you.
Abhinav Singh Bhadoria

ice123456 · December 7th, 2010, 11:57 PM

C#/.NET Component Recommend:
Spire.XLS for .NET
Spire.Doc for .NET

DanFromHamburg · June 25th, 2015, 04:26 PM

This code allows you to create a pivot table (sorted by either the column values, row values or both). First the code that creates the datatable and calls the function:

Code:

            Dim TaskTable As New DataTable
            Dim dr As DataRow

            Dim TaskPivotTable As New DataTable

            TaskTable = MailboxDs.Tables.Add("TaskPivotTable")
            With MailboxDs.Tables("TaskPivotTable")
                .Columns.Add(New DataColumn("BowlType", GetType(String)))
                .Columns.Add(New DataColumn("ColorCode", GetType(String)))
                .Columns.Add(New DataColumn("Remaining", GetType(Integer)))
            End With

            dr = MailboxDs.Tables("TaskPivotTable").Rows.Add()
            dr.Item("BowlType") = "810"
            dr.Item("ColorCode") = "CW"
            dr.Item("Remaining") = 1

            dr = MailboxDs.Tables("TaskPivotTable").Rows.Add()
            dr.Item("BowlType") = "810"
            dr.Item("ColorCode") = "GW"
            dr.Item("Remaining") = 2

            dr = MailboxDs.Tables("TaskPivotTable").Rows.Add()
            dr.Item("BowlType") = "810"
            dr.Item("ColorCode") = "BI"
            dr.Item("Remaining") = 3

            dr = MailboxDs.Tables("TaskPivotTable").Rows.Add()
            dr.Item("BowlType") = "810"
            dr.Item("ColorCode") = "BI"
            dr.Item("Remaining") = 3

            dr = MailboxDs.Tables("TaskPivotTable").Rows.Add()
            dr.Item("BowlType") = "815"
            dr.Item("ColorCode") = "CW"
            dr.Item("Remaining") = 1

            dr = MailboxDs.Tables("TaskPivotTable").Rows.Add()
            dr.Item("BowlType") = "815"
            dr.Item("ColorCode") = "WN"
            dr.Item("Remaining") = 2

            dr = MailboxDs.Tables("TaskPivotTable").Rows.Add()
            dr.Item("BowlType") = "809"
            dr.Item("ColorCode") = "WN"
            dr.Item("Remaining") = 2

            dr = MailboxDs.Tables("TaskPivotTable").Rows.Add()
            dr.Item("BowlType") = "810"
            dr.Item("ColorCode") = "GW"
            dr.Item("Remaining") = 10


            TaskPivotTable = CreatePivotTable(TaskTable, 1, 0, 2, True, True)

Now the function that creates the pivot table:

Code:


    Private Function CreatePivotTable(ByVal OrigTable As DataTable, Optional ByVal pivotColumnOrdinal As Integer = 0, Optional ByVal pivotRowOrdinal As Integer = 1, _
            Optional ByVal pivotDataOrdinal As Integer = 3, Optional ByVal SortColumn As Boolean = True, Optional ByVal SortRow As Boolean = True) As DataTable

        Dim PivotTable As New DataTable
        Dim OrigArray() As DataRow
        Dim dr As DataRow
        Dim SortString As String
        Dim origRowInd As Integer
        Dim PivotRowInd As Integer
        Dim PivotcolInd As Integer

        Dim CurRowInd As Integer
        Dim CurColInd As Integer
        Dim teststr As String


        Try
            ' add pivot column name 
            PivotTable.Columns.Add(OrigTable.Columns(pivotRowOrdinal).ColumnName)
            ' add pivot column values in each row as column headers to new Table 

            If (SortColumn = True) Then
                SortString = OrigTable.Columns(pivotColumnOrdinal).ColumnName + " ASC"
            Else
                SortString = " "
            End If


            OrigArray = OrigTable.Select("", SortString, DataViewRowState.CurrentRows)

            For origRowInd = 0 To OrigArray.GetUpperBound(0)
                Try
                    PivotTable.Columns.Add(OrigArray(origRowInd).Item(pivotColumnOrdinal))

                Catch ex As Exception

                End Try
            Next

            For PivotcolInd = 0 To PivotTable.Columns.Count - 1
                teststr = PivotTable.Columns(PivotcolInd).ColumnName
            Next

            If (SortRow = True) Then
                SortString = OrigTable.Columns(pivotRowOrdinal).ColumnName + " ASC"
            Else
                SortString = " "
            End If

            OrigArray = OrigTable.Select("", SortString, DataViewRowState.CurrentRows)

            ' loop through rows 
            For origRowInd = 0 To OrigArray.GetUpperBound(0)
                teststr = OrigArray(origRowInd).Item(pivotRowOrdinal)
                For PivotRowInd = 0 To PivotTable.Rows.Count - 1
                    teststr = PivotTable.Rows(PivotRowInd).Item(0)
                    If (OrigArray(origRowInd).Item(pivotRowOrdinal) = PivotTable.Rows(PivotRowInd).Item(0)) Then
                        CurRowInd = PivotRowInd
                        GoTo RowFound
                    End If
                Next

                'add the DataRow to the new table 
                CurRowInd = PivotTable.Rows.Count
                dr = PivotTable.NewRow()
                dr.Item(0) = OrigArray(origRowInd).Item(pivotRowOrdinal)
                teststr = dr.Item(0)
                PivotTable.Rows.Add(dr)

                For PivotcolInd = 1 To PivotTable.Columns.Count - 1
                    PivotTable.Rows(CurRowInd).Item(PivotcolInd) = 0
                Next


RowFound:
                ' loop through columns 
                For PivotcolInd = 0 To PivotTable.Columns.Count - 1
                    teststr = OrigArray(origRowInd).Item(pivotColumnOrdinal)
                    If (OrigArray(origRowInd).Item(pivotColumnOrdinal) = PivotTable.Columns(PivotcolInd).ColumnName) Then
                        CurColInd = PivotcolInd
                        GoTo ColumnFound
                    End If
                Next

ColumnFound:
                PivotTable.Rows(CurRowInd).Item(CurColInd) = PivotTable.Rows(CurRowInd)(CurColInd) + OrigArray(origRowInd)(pivotDataOrdinal)
                teststr = PivotTable.Rows(CurRowInd).Item(0) + " - " + PivotTable.Columns(CurColInd).ColumnName + " - " + PivotTable.Rows(CurRowInd).Item(CurColInd)
            Next

            For CurRowInd = 0 To PivotTable.Rows.Count - 1
                For CurColInd = 0 To PivotTable.Columns.Count - 1
                    teststr = PivotTable.Rows(CurRowInd).Item(0) + " - " + PivotTable.Columns(CurColInd).ColumnName + " - " + PivotTable.Rows(CurRowInd).Item(CurColInd)
                Next
            Next

        Catch ex As Exception

        End Try

        Return PivotTable

    End Function

I hope that this is helpful.