使用vb.net从Windows应用程序创建数据透视表 [英] creating a pivot table from windows application using vb.net

问题描述

你好；

我想使用vb代码在excel中创建数据透视表.净额.
Thabks
Dnyaneshwar ...:)

Hello;

I want to create pivote table in excel using code of vb. net.
Thabks
Dnyaneshwar ... :)

推荐答案

我为...解决了问题.

I got solution for the That...

Imports System
Imports System.Runtime.InteropServices
Imports System.Data
Imports System.Data.SqlClient
Imports System.IO
Imports System.Net.Mime.MediaTypeNames
Imports Microsoft.Office.Interop
Imports Microsoft.Office.Interop.Excel
Public Class Form1
    Inherits System.Windows.Forms.Form
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) 'Handles Button1.Click
        '' COMs for excel and office references were added to project
        Dim xlApp As Excel.Application
        Dim xlBook As Excel.Workbook
        Dim xlSheet As Excel.Worksheet
        Dim xlSheets As Excel.Worksheets
        Dim ConnectionString As String = _
        "Server=SERVER;" & _
        "DataBase=DATABASE;" & _
        "user ID=DNYANESHWAR;password=DNYANESHWAR"
        Dim ptSQL As String
        ptSQL = "SELECT * FROM BankVoucher"
        Dim cnSQL As SqlConnection
        Dim cmSQL As SqlCommand
        Dim drSQL As SqlDataReader
        Dim dsSQL As DataSet
        '' 
        ''
        Dim Row As Integer
        xlApp = CType(CreateObject("Excel.Application"), Excel.Application)
        xlBook = CType(xlApp.Workbooks.Add, Excel.Workbook)
        xlSheet = CType(xlBook.Worksheets(1), Excel.Worksheet)
        xlApp.Visible = False
        Try
            cnSQL = New SqlConnection(ConnectionString)
            cnSQL.Open()
            cmSQL = New SqlCommand(ptSQL, cnSQL)
            drSQL = cmSQL.ExecuteReader
            xlSheet.Cells(1, 1).value = "BranchCode"
            xlSheet.Cells(1, 2).value = "DepartmentCode"
            xlSheet.Cells(1, 3).value = "SerialNo"
            xlSheet.Cells(1, 4).value = "VoucherNo"
            xlSheet.Cells(1, 5).value = "VoucherDate"
            xlSheet.Cells(1, 6).value = "GLCode"
            xlSheet.Cells(1, 7).value = "PartyCode"
            xlSheet.Cells(1, 8).value = "Debit"
            xlSheet.Cells(1, 9).value = "Credit"
            xlSheet.Cells(1, 10).value = "BillNo"
            xlSheet.Cells(1, 11).value = "BillDate"
            Row = 2
            While drSQL.Read
                xlSheet.Cells(Row, 1).value = drSQL.Item("BranchCode")
                xlSheet.Cells(Row, 2).value = drSQL.Item("DepartmentCode")
                xlSheet.Cells(Row, 3).value = drSQL.Item("SerialNo")
                xlSheet.Cells(Row, 4).value = drSQL.Item("VoucherNo")
                xlSheet.Cells(Row, 5).value = drSQL.Item("VoucherDate")
                xlSheet.Cells(Row, 6).value = drSQL.Item("GLCode")
                xlSheet.Cells(Row, 7).value = drSQL.Item("PartyCode")
                xlSheet.Cells(Row, 8).value = drSQL.Item("Debit")
                xlSheet.Cells(Row, 9).value = drSQL.Item("Credit")
                xlSheet.Cells(Row, 10).value = drSQL.Item("BillNo")
                xlSheet.Cells(Row, 11).value = drSQL.Item("BillDate")
                Row = Row + 1
            End While


        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            drSQL.Close()
            cnSQL.Close()
            cmSQL.Dispose()
            cnSQL.Dispose()
        End Try
        xlSheet.Cells.EntireColumn.AutoFit()
        Dim xllastcell As String
        xllastcell = xlSheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell).Address
        xlApp.Sheets.Add.name = "CTS Pivot Table"
        xlBook.ActiveSheet.PivotTableWizard(Excel.XlPivotTableSourceType.xlDatabase, xlSheet.Range("A1:" & xllastcell))
        xlBook.ActiveSheet.PivotTables(1).PivotFields("BranchCode").Orientation = Excel.XlPivotFieldOrientation.xlPageField
        xlBook.ActiveSheet.PivotTables(1).PivotFields("DepartmentCode").Orientation = Excel.XlPivotFieldOrientation.xlPageField
        xlBook.ActiveSheet.PivotTables(1).PivotFields("SerialNo").Orientation = Excel.XlPivotFieldOrientation.xlPageField
        xlBook.ActiveSheet.PivotTables(1).PivotFields("VoucherNo").Orientation = Excel.XlPivotFieldOrientation.xlRowField
        xlBook.ActiveSheet.PivotTables(1).PivotFields("VoucherDate").Orientation = Excel.XlPivotFieldOrientation.xlColumnField
        xlBook.ActiveSheet.PivotTables(1).PivotFields("GLCode").Orientation = Excel.XlPivotFieldOrientation.xlPageField
        xlBook.ActiveSheet.PivotTables(1).PivotFields("PartyCode").Orientation = Excel.XlPivotFieldOrientation.xlPageField
        xlBook.ActiveSheet.PivotTables(1).PivotFields("Debit").Orientation = Excel.XlPivotFieldOrientation.xlPageField
        xlBook.ActiveSheet.PivotTables(1).PivotFields("Credit").Orientation = Excel.XlPivotFieldOrientation.xlDataField
        xlBook.ActiveSheet.PivotTables(1).PivotFields("BillNo").Orientation = Excel.XlPivotFieldOrientation.xlPageField
        xlBook.ActiveSheet.PivotTables(1).PivotFields("BillDate").Orientation = Excel.XlPivotFieldOrientation.xlPageField
        ' Get the last cell in the pivot table.
        xllastcell = xlBook.ActiveSheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell).Address
        ' Set the number format for the data cells
        ' xlBook.ActiveSheet.range("B5:" & xllastcell).numberformat = "##,##0.00"
        '' Worth considering ---
        xlApp.CommandBars("PivotTable").Visible = False
        '''' Group the selection... ??
        '''' Here's where I need to the help to group the date column..
        xlBook.ActiveSheet.PivotTables(1).PivotFields("BranchCode").Subtotals(1) = False
        xlBook.ActiveSheet.PivotTables(1).PivotFields("DepartmentCode").Subtotals(1) = False
        xlBook.ActiveSheet.PivotTables(1).PivotFields("SerialNo").Subtotals(1) = False
        xlBook.ActiveSheet.PivotTables(1).PivotFields("VoucherNo").Subtotals(1) = False
        xlBook.ActiveSheet.PivotTables(1).PivotFields("VoucherDate").Subtotals(1) = False
        xlBook.ActiveSheet.PivotTables(1).PivotFields("GLCode").Subtotals(1) = False
        xlBook.ActiveSheet.PivotTables(1).PivotFields("PartyCode").Subtotals(1) = False
        xlBook.ActiveSheet.PivotTables(1).PivotFields("Debit").Subtotals(1) = False
        xlBook.ActiveSheet.PivotTables(1).PivotFields("Credit").Subtotals(1) = False
        xlBook.ActiveSheet.PivotTables(1).PivotFields("BillNo").Subtotals(1) = False
        xlBook.ActiveSheet.PivotTables(1).PivotFields("BillDate").Subtotals(1) = False
        xlBook.ActiveSheet.Cells.EntireColumn.AutoFit()
        xlApp.Visible = True
    End Sub
End Class

这篇关于使用vb.net从Windows应用程序创建数据透视表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！