如何使用VB.NET将所选列的数据从数据库导出到Excel？ [英] How to export data of a selected columns from database to excel using VB.NET?

问题描述

我的Windows窗体上只有一个按钮，一个按钮，用于将数据从数据库导出到Excel。

它适用于导出，但数据库中的所有列都已导出。我只想导出那些特定/选定/所需的列。我不想导出所有列。我还想将所选列从数据库导出到excel中的选定列。例如：要导出到列的数据库的column1，column4和column6数据：excel的A，C，D。我希望你能帮助我。谢谢。



我尝试了什么：



我试过了下面的代码，但它导出所有列。

 私有  Sub  Button1_Click（ ByVal  sender  As  System。对象， ByVal  e  As  System.EventArgs）句柄 Button1.Click 
  Dim  dbSource  As  字符串 
  Dim  sql  As  字符串 
  Dim  sqlcon  As  新 OleDb.OleDbConnection 
  Dim  ds  As  新 DataSet 
 < span class =code-keyword> Dim  dbProvider  As   String  
 < span class =code-keyword> Dim  i，j  As  整数 
 
  Dim  xlApp  As  Excel.Application 
  Dim  xlWorkBook  As  Excel.Workbook 
  Dim  xlWorkSheet  As  Excel.Worksheet 
  Dim  misValue  As  对象 = System.Reflection.Missing.Value 
 
 xlApp = 新 Excel。申请
 xlWorkBook = xlApp.Workbooks.Add（misValue）
 xlWorkSheet = xlWorkBook.Sheets（  sheet1）
 
 dbProvider =   Provider = Microsoft.ACE.OLEDB.15.0; 
 dbProvider =   Provider = Microsoft.ACE.OLEDB.12.0; 
 dbSource =  数据源= psipop.accdb; Jet OLEDB：数据库密码= cscfo13poppsi;  
 sqlcon.ConnectionString = dbProvider& dbSource 
 
 sql =   SELECT * FROM publicationinfo 
  Dim  dscmd = 新 OleDb.OleDbDataAdapter （sql，sqlcon）
 dscmd.Fill（ds）
 
 对于 i =  0    ds.Tables（ 0 ）。Rows.Count  -   1  
 对于 j =  0    ds.Tables（ 0 ）。Columns.Count  -   1  
 xlWorkSheet.Cells（i +  18 ，j +  1 ）= ds.Tables（  0 ）。行（i）.Item（j）  + 18这里是rownumber（i）而+1是列号（j） 
 下一步 
 下一步 
 
 xlWorkSheet.SaveAs（  C：\ Users \Admin \Desktop\vbexcel.xlsx）
 xlWorkBook.Close（）
 xlApp.Quit（）
 
 releaseObject（xlApp）
 releaseObject（xlWorkBook）
 releaseObject（xlWorkSheet）
 
 sqlcon.Close（）
 
 MsgBox（ 你可以找到文件C：\ Users \Admin \Desktop \ ubebecel.xlsx）
 结束  Sub  
 
 私有  Sub  releaseObject（ ByVal  obj 作为 对象）
 尝试 
 System.Runtime.InteropServices.Marshal.ReleaseComObject（obj）
 obj =  Nothing  
  Catch  ex  As 例外
 obj = 没什么 
 最后 
 GC.Collect（）
 结束 尝试 
 结束  Sub  

解决方案

引用：

我尝试了下面的代码，但它导出了所有列。

可能是因为这个

 sql =  <跨越ss =code-string> SELECT * FROM publicationinfo 

请求数据库中的所有列。

要么你有要说明在SQL查询中需要哪些列，要么在写入Excel时必须对它们进行过滤。



SQL SELECT语句 [ ^ ]

I have only one button on my windows form, a button that performs exporting of data from database to excel.
It works in exporting but all those columns in database have exported. I want to export only those specific/selected/desired columns. I don't want to export all columns. I want also to export selected columns from database to selected columns in excel. For example: column1, column4, and column 6 data of database to be exported to columns: A,C,D of excel. I hope you can help me. Thanks.

What I have tried:

I have tried the code below but it exporting all columns.

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim dbSource As String
        Dim sql As String
        Dim sqlcon As New OleDb.OleDbConnection
        Dim ds As New DataSet
        Dim dbProvider As String
        Dim i, j As Integer

        Dim xlApp As Excel.Application
        Dim xlWorkBook As Excel.Workbook
        Dim xlWorkSheet As Excel.Worksheet
        Dim misValue As Object = System.Reflection.Missing.Value

        xlApp = New Excel.Application
        xlWorkBook = xlApp.Workbooks.Add(misValue)
        xlWorkSheet = xlWorkBook.Sheets("sheet1")

        dbProvider = "Provider=Microsoft.ACE.OLEDB.15.0;"
        dbProvider = "Provider=Microsoft.ACE.OLEDB.12.0;"
        dbSource = "Data Source =psipop.accdb;Jet OLEDB:Database Password=cscfo13poppsi; "
        sqlcon.ConnectionString = dbProvider & dbSource

        sql = "SELECT * FROM publicationinfo"
        Dim dscmd = New OleDb.OleDbDataAdapter(sql, sqlcon)
        dscmd.Fill(ds)

        For i = 0 To ds.Tables(0).Rows.Count - 1
            For j = 0 To ds.Tables(0).Columns.Count - 1
                xlWorkSheet.Cells(i + 18, j + 1) = ds.Tables(0).Rows(i).Item(j) '+18 here is the rownumber(i) and +1 is the columnnumber(j)
            Next
        Next

        xlWorkSheet.SaveAs("C:\Users\Admin\Desktop\vbexcel.xlsx")
        xlWorkBook.Close()
        xlApp.Quit()

        releaseObject(xlApp)
        releaseObject(xlWorkBook)
        releaseObject(xlWorkSheet)

        sqlcon.Close()

        MsgBox("You can find the file C:\Users\Admin\Desktop\vbexcel.xlsx")
    End Sub

    Private Sub releaseObject(ByVal obj As Object)
        Try
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
            obj = Nothing
        Catch ex As Exception
            obj = Nothing
        Finally
            GC.Collect()
        End Try
    End Sub

解决方案

Quote:

I have tried the code below but it exporting all columns.

Probably because this

sql = "SELECT * FROM publicationinfo"

request all the columns from database.
Either you have to say which columns you want in the SQL query, either you have to filter them when you write to excel.

SQL SELECT Statement[^]

这篇关于如何使用VB.NET将所选列的数据从数据库导出到Excel？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！