Exporting data to a PivotTable report with vb.net

Export data

Many times you can use Excel PivotTable reports to export the format of the report you want. So in. NET how to do it? The following code extracts the data from the database and then imports Excel.

Dim Excel as Excel.Application
Dim XBK as Excel._workbook
Dim XSt as Excel._worksheet
Dim Xrange as Excel.Range
Dim Xpivotcache as Excel.pivotcache
Dim xpivottable as Excel.pivottable
Dim Xpivotfield as Excel.pivotfield
Dim CNNSR As String, SQL as String
Dim RowFields () as String = {"", "", ""}
Dim PageFields () as String = {"," "," "," "," "," "}

' Server is the IP address of the name or server
' DB is the database name
' Table is the list name

Try
' Start exporting
CNNSR = "Odbc;driver=sql Server;" Server= "+ SERVER
CNNSR = CNNSR + "; uid=; App=report Tools; Wsid=reportclient;database= "+ DATABASE
CNNSR = CNNSR + "; Trusted_connection=yes "

Excel = New Excel.applicationclass
XBK = Excel. Workbooks.Add (True)
XSt = Xbk.activesheet

xrange = Xst.range ("A4")
Xrange.select ()

' Start
Xpivotcache = XBK.PIVOTCACHES.ADD (sourcetype:=2)
Xpivotcache.connection = CNNSR
Xpivotcache.commandtype = 2

sql = "SELECT * from" + Table

Xpivotcache.commandtext = sql
xpivottable = xpivotcache.createpivottable (tabledestination:= "sheet1! R3c1 ", tablename:= PivotTable 1, defaultversion:=1)

' Prepare row fields
RowFields (0) = "Field 1"
RowFields (1) = "Field 2"
RowFields (2) = "Field 3"
' Prepare page fields
PageFields (0) = "Field 4"
PageFields (1) = "Field 5"
PageFields (2) = "Field 6"
PageFields (3) = "Field 7"
PageFields (4) = "Field 8"
PageFields (5) = "Field 9"
Xpivottable.addfields (Rowfields:=rowfields, Pagefields:=pagefields)

Xpivotfield = Xpivottable.pivotfields ("Quantity")
Xpivotfield.orientation = 4

' Close the tool bar
' Xbk.showpivottablefieldlist = False
' Excel.commandbars ("PivotTable"). Visible = False

Excel. Visible = True

Catch ex as Exception
If CNN. State = ConnectionState.Open Then
Cnn. Close ()
End If
Xbk.close (0)
Excel. Quit ()
MessageBox.Show (ex. Message, "Report Tool", MessageBoxButtons.OK, messageboxicon.warning)
End Try