将访问数据库导出为ex​​cel [英] Export access database to excel

问题描述

大家好，

i希望创建导出数据库表项，以包含项目的列名称。



此代码导出该项目成功但不包括列名称。请帮忙吗？

 '  lib  
  Imports  System.Data 
  Imports  System .Data.OleDb 
 ' 添加名为microsoft.office.interop.excel 11的引用 
  Imports  Excel = Microsoft.Office.Interop.Excel 
 
 ' < span class =code-comment> btn export  
  Dim  cnn  As  OleDbConnection 
  Dim  cnnst  As   String  
  Dim  sql  As   String  
  Dim  i，j 作为 整数 
 
 '  excel  
  Dim  xlapp  As  Excel.Application 
  Dim  xlworkbook  As  Excel.workbook 
  Dim  xlworksheet  As  Excel.worksheet 
  Dim  misvalue 作为 对象 = Reflection.Missing.Value 
 xlapp = 新 Excel.Application 
 xlworkbook = xlapp.workbooks.add（misvalue）
 xlworksheet = xlworkbook.sheets（  sheet1）
 cnnst =   Pr ovider = Microsoft.jet.OLEDB.4.0; Persist Security Info = False; Data Source = .. \..\..\indigeneDB.mdb; 
 cnn = 新 OleDbConnection（cnnst）
 cnn.Open（）
 sql =   select * from lga 
  Dim  cmd  As  < span class =code-keyword>新 OleDbDataAdapter（sql，cnn）
  Dim  ds 正如 新 DataSet 
 cmd.Fill（ds）
 
  For  i =  0    ds.Tables（ 0 ）。Columns.Count  -   1  
 对于 j =  0  至 ds.Tables（ 0 ）。Columns.Count  -   1  
 xlworksheet.Cells（ i +  1 ，j +  1 ）= ds.Tables（ 0 ）。行（i）.Item（j）
 下一步 
 下一步 
 xlworksheet.SaveAs（  Sample_District.xlsx）
 xlworkbook.close（）
 xlapp.Quit（）
 
 Myobject（xlapp）
 Myobject（xlworkbook）
 Myobject（xlworksheet）
 cnn.Close （）
 MessageBox.Show（ 示例数据库导出到'Documents folder' ）

谢谢。

解决方案

您只保存数据。您需要通过调用表单从要导出的表中提取列名：

 ds.Tables（ 0 ）色谱柱（ⅰ）.ColumnName; 

Hello everyone,
i want create export database table items to excel that include the columns name of the items.

this code export that items successfully but excluding the columns name. any help please?

'lib
Imports System.Data
Imports System.Data.OleDb
'add references called microsoft.office.interop.excel 11
Imports Excel = Microsoft.Office.Interop.Excel

'btn export
Dim cnn As OleDbConnection
        Dim cnnst As String
        Dim sql As String
        Dim i, j As Integer

        'excel
        Dim xlapp As Excel.Application
        Dim xlworkbook As Excel.workbook
        Dim xlworksheet As Excel.worksheet
        Dim misvalue As Object = Reflection.Missing.Value
        xlapp = New Excel.Application
        xlworkbook = xlapp.workbooks.add(misvalue)
        xlworksheet = xlworkbook.sheets("sheet1")
        cnnst = "Provider=Microsoft.jet.OLEDB.4.0;Persist Security Info=False;Data Source=..\..\..\indigeneDB.mdb;"
        cnn = New OleDbConnection(cnnst)
        cnn.Open()
        sql = "select * from lga"
        Dim cmd As New OleDbDataAdapter(sql, cnn)
        Dim ds As New DataSet
        cmd.Fill(ds)

        For i = 0 To ds.Tables(0).Columns.Count - 1
            For j = 0 To ds.Tables(0).Columns.Count - 1
                xlworksheet.Cells(i + 1, j + 1) = ds.Tables(0).Rows(i).Item(j)
            Next
        Next
        xlworksheet.SaveAs("Sample_District.xlsx")
        xlworkbook.close()
        xlapp.Quit()

        Myobject(xlapp)
        Myobject(xlworkbook)
        Myobject(xlworksheet)
        cnn.Close()
        MessageBox.Show("The sample database was exported to 'Documents folder'")

thank you.

解决方案

You are only saving the data. You need to extract the column names from the table you are trying to export, by calls of the form:

ds.Tables(0).Columns(i).ColumnName;

这篇关于将访问数据库导出为ex​​cel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！