利用VB.NET實現(xiàn)導出DataTable數(shù)據(jù)到excel中，各位大俠幫幫忙，最好有源碼，謝謝，江湖救急

剛好寫了個Helper類，你試驗一下DataTable2Exce(這個方法代碼如下：

創(chuàng)新互聯(lián)專注于企業(yè)成都全網(wǎng)營銷、網(wǎng)站重做改版、旬陽網(wǎng)站定制設計、自適應品牌網(wǎng)站建設、H5開發(fā)、電子商務商城網(wǎng)站建設、集團公司官網(wǎng)建設、成都外貿(mào)網(wǎng)站制作、高端網(wǎng)站制作、響應式網(wǎng)頁設計等建站業(yè)務，價格優(yōu)惠性價比高，為旬陽等各大城市提供網(wǎng)站開發(fā)制作服務。

Imports System.IO

Imports System.Data

Imports System.Data.OleDb

Public MustInherit Class ExcelHelper

Private Shared Function buildConnStr(excelFilePath As String) As String

Dim excelFileInfo As New System.IO.FileInfo(excelFilePath)

Dim constr As String

If excelFileInfo.Extension = ".xlsx" Then

constr = String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1'", excelFilePath)

Else

constr = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'", excelFilePath)

End If

Return constr

End Function

'將datatable導入到excel

Public Shared Function DataTable2Excel(dt As DataTable, excelFilePath As String) As Boolean

If File.Exists(excelFilePath) Then

Throw New Exception("該文件已經(jīng)存在！")

End If

If dt.TableName.Trim.Length = 0 Or dt.TableName.ToLower = "table" Then

dt.TableName = "Sheet1"

End If

Dim colCount As Integer = dt.Columns.Count

Dim pa(colCount - 1) As OleDb.OleDbParameter

Dim tableStructStr As String = "Create Table " dt.TableName "("

Dim connString As String = buildConnStr(excelFilePath)

Dim objconn As New OleDbConnection(connString)

Dim objcmd As New OleDbCommand

objcmd.Connection = objconn

Dim dataTypeList As New ArrayList

dataTypeList.Add("System.Decimal")

dataTypeList.Add("System.Double")

dataTypeList.Add("System.Int16")

dataTypeList.Add("System.Int32")

dataTypeList.Add("System.Int64")

dataTypeList.Add("System.Single")

Dim i As Integer = 0

For Each col As DataColumn In dt.Columns

If dataTypeList.IndexOf(col.GetType.ToString) 0 Then

pa(i) = New OleDbParameter("@" col.ColumnName, OleDbType.Double)

objcmd.Parameters.Add(pa(i))

If i + 1 = colCount Then

tableStructStr += col.ColumnName + " double)"

Else

tableStructStr += col.ColumnName + " double,"

End If

Else

pa(i) = New OleDbParameter("@" col.ColumnName, OleDbType.VarChar)

objcmd.Parameters.Add(pa(i))

If i + 1 = colCount Then

tableStructStr += col.ColumnName + " VarChar)"

Else

tableStructStr += col.ColumnName + " VarChar,"

End If

End If

i += 1

Next

Try

objcmd.CommandText = tableStructStr

If objconn.State = ConnectionState.Closed Then objconn.Open()

objcmd.ExecuteNonQuery()

Catch ex As Exception

Throw ex

End Try

Dim InsertSql_1 As String = "Insert into " + dt.TableName + " ("

Dim InsertSql_2 As String = " Values ("

Dim InsertSql As String = ""

For colID As Integer = 0 To colCount - 1 Step 1

If colID + 1 = colCount Then

InsertSql_1 += dt.Columns(colID).ColumnName ")"

InsertSql_2 += "@" + dt.Columns(colID).ColumnName + ")"

Else

InsertSql_1 += dt.Columns(colID).ColumnName + ","

InsertSql_2 += "@" + dt.Columns(colID).ColumnName + ","

End If

Next

InsertSql = InsertSql_1 + InsertSql_2

For rowID As Integer = 0 To dt.Rows.Count - 1 Step 1

For colID = 0 To dt.Columns.Count - 1

If pa(colID).DbType = DbType.Double And dt.Rows(rowID)(colID).ToString.Trim = "" Then

pa(colID).Value = 0

Else

pa(colID).Value = dt.Rows(rowID)(colID).ToString.Trim

End If

Next

Try

objcmd.CommandText = InsertSql

objcmd.ExecuteNonQuery()

Catch ex As Exception

Throw ex

End Try

Next

Try

If objconn.State = ConnectionState.Open Then objconn.Close()

Catch exp As Exception

Throw exp

End Try

Return True

End Function

' 獲取Excel文件數(shù)據(jù)表列表Sheets

Public Shared Function GetExcelTables(ExcelFileName As String) As ArrayList

'Dim sheets As New List(Of String)

'conn.Open()

'Dim dt As DataTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)

'For Each r In dt.Rows

' sheets.Add(r("TABLE_NAME"))

'Next

'conn.Close()

'Return sheets

Dim dt As DataTable

If Not File.Exists(ExcelFileName) Then

Throw New Exception("指定的Excel文件不存在")

Return Nothing

End If

Dim tableList As New ArrayList

Using conn As OleDbConnection = New OleDbConnection(buildConnStr(ExcelFileName))

Try

conn.Open()

dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, New Object() {Nothing, Nothing, Nothing, "TABLE"})

Catch ex As Exception

Throw ex

End Try

For i As Integer = 0 To dt.Rows.Count - 1

Dim tableName As String = dt.Rows(i)(2).ToString.Trim.TrimEnd("$")

If tableList.IndexOf(tableName) 0 Then tableList.Add(tableName)

Next

End Using

Return tableList

End Function

'將Excel文件導出至DataTable(第一行作為表頭)

Public Shared Function InputFromExcel(ExcelFileName As String, TableName As String) As DataTable

If Not File.Exists(ExcelFileName) Then

Throw New Exception("指定的Excel文件不存在")

End If

Dim tableList As ArrayList = GetExcelTables(ExcelFileName)

If tableList.IndexOf(TableName) 0 Then

TableName = tableList(0).ToString.Trim

End If

Dim dt As New DataTable

Dim conn As New OleDbConnection(buildConnStr(ExcelFileName))

Dim cmd As New OleDbCommand("select * from [" TableName "$]", conn) '調(diào)試是否需要$

Dim adapter As New OleDbDataAdapter(cmd)

Try

If conn.State = ConnectionState.Closed Then conn.Open()

adapter.Fill(dt)

Catch ex As Exception

Throw ex

Finally

If conn.State = ConnectionState.Open Then conn.Close()

End Try

Return dt

End Function

'查詢excel文件中的一個數(shù)據(jù)

Public Shared Function ReadOneDataFromExcel(ExcelFileName As String, TableName As String, sql As String) As Object

If Not File.Exists(ExcelFileName) Then

Throw New Exception("指定的Excel文件不存在")

End If

Dim tableList As ArrayList = GetExcelTables(ExcelFileName)

If tableList.IndexOf(TableName) 0 Then

TableName = tableList(0).ToString.Trim

End If

Dim dt As New DataTable

Dim conn As New OleDbConnection(buildConnStr(ExcelFileName))

Dim cmd As New OleDbCommand(sql, conn) '調(diào)試是否需要$

Dim ret As Object

Try

If conn.State = ConnectionState.Closed Then conn.Open()

ret = cmd.ExecuteScalar()

Catch ex As Exception

Throw ex

Finally

If conn.State = ConnectionState.Open Then conn.Close()

End Try

Return ret

End Function

'獲取Excel文件指定數(shù)據(jù)表的數(shù)據(jù)列表columnNames

Public Shared Function GetExcelTableColumns(ExcelFileName As String, TableName As String) As ArrayList

Dim dt As DataTable

If Not File.Exists(ExcelFileName) Then

Throw New Exception("指定的Excel文件不存在")

Return Nothing

End If

Dim ColList As New ArrayList

Using conn As OleDbConnection = New OleDbConnection(buildConnStr(ExcelFileName))

Try

conn.Open()

dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, New Object() {Nothing, Nothing, TableName, Nothing})

Catch ex As Exception

Throw ex

End Try

For i As Integer = 0 To dt.Rows.Count - 1

Dim ColName = dt.Rows(i)("Column_Name").ToString().Trim()

ColList.Add(ColName)

Next

End Using

Return ColList

End Function

End Class

VB.net 從oracle數(shù)據(jù)庫導出CSV文件

和導出txt文件的操作是一樣的。

sql語句從數(shù)據(jù)庫獲得數(shù)據(jù)集，然后逐條插入到csv

下面代碼可以做參考，

Sub?CSVdropmark()

Dim?mFileName?As?String?=?Application.StartupPath?+?"\EmployeeMaster\ExcelData\BaanID.CSV"

Dim?fs?As?FileStream

Dim?st?As?StreamWriter

Dim?reader?As?System.IO.StreamReader

Dim?i?As?Integer

Dim?str?As?String

reader?=?New?System.IO.StreamReader(mFileName)

Try

fs?=?New?FileStream(Application.StartupPath?+?"\EmployeeMaster\ExcelData\BaanIDAD.CSV",?FileMode.Create,?FileAccess.Write)

str?=?reader.ReadToEnd

str?=?str.Replace("""",?"")

st?=?New?StreamWriter(fs)

st.WriteLine(str)

st.Flush()

Catch?ex?As?Exception

Finally

If?Not?reader?Is?Nothing?Then?reader.Close()

If?st?IsNot?Nothing?Then?st.Close()

If?fs?IsNot?Nothing?Then?fs.Close()

End?Try

End?Sub

vb.net中從DataGridView里面把數(shù)據(jù)導出到excel中

以下是我以前百度找的資料 希望對你有用 你讀取DataGridView到DataGrid然后直接調(diào)用函數(shù)即可

Public Function ExportXLsD(ByVal datagrid As DataGrid) ', ByVal Title As String)

'Dim Mytable As New DataTable

'Mytable = CType(datagrid.DataSource, DataTable)

If mytable Is Nothing Then

MessageBox.Show("沒有記錄不能導出數(shù)據(jù)", "PurpleStar", MessageBoxButtons.OK, MessageBoxIcon.Information)

Exit Function

End If

If mytable.Rows.Count 0 Then

Dim MyFileName As String

Dim FileName As String

With SaveFileDialog1

.AddExtension = True '如果用戶忘記添加擴展名，將自動家上

.DefaultExt = "xls" '默認擴展名

.Filter = "Excel文件(*.xls)|*.xls"

.Title = "文件保存到"

If .ShowDialog = DialogResult.OK Then

FileName = .FileName

End If

End With

MyFileName = Microsoft.VisualBasic.Right(FileName, 4)

If MyFileName = "" Then

Exit Function

End If

If MyFileName = ".xls" Or MyFileName = ".XLS" Then

Dim FS As FileStream = New FileStream(FileName, FileMode.Create)

Dim sw As StreamWriter = New StreamWriter(FS, System.Text.Encoding.Default)

sw.WriteLine(vbTab FileName vbTab Date.Now)

Dim i, j As Integer

Dim str As String = ""

For i = 0 To mytable.Columns.Count - 1

str = mytable.Columns(i).Caption

sw.Write(str vbTab)

Next

sw.Write(vbCrLf)

For j = 0 To mytable.Rows.Count - 1

For i = 0 To mytable.Columns.Count - 1

Dim strColName, strRow As String

strRow = IIf(mytable.Rows(j).Item(i) Is DBNull.Value, "", mytable.Rows(j).Item(i))

sw.Write(strRow vbTab)

Next

sw.Write(vbLf)

Next

sw.Close()

FS.Close()

MessageBox.Show("數(shù)據(jù)導出成功！", "PurpleStar", MessageBoxButtons.OK, MessageBoxIcon.Information)

Else

Exit Function

End If

Else

MessageBox.Show("沒有記錄不能導出數(shù)據(jù)", "PurpleStar", MessageBoxButtons.OK, MessageBoxIcon.Information)

End If

End Function

Private Sub OK_Button_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles OK_Button.Click

Dim saveExcel As SaveFileDialog

saveExcel = New SaveFileDialog

saveExcel.Filter = "Excel文件(.xls)|*.xls"

Dim filename As String

If saveExcel.ShowDialog = Windows.Forms.DialogResult.Cancel Then Exit Sub

filename = saveExcel.FileName

Dim excel As Excel.Application

excel = New Excel.Application

excel.DisplayAlerts = False

excel.Workbooks.Add(True)

excel.Visible = False

Dim i As Integer

For i = 0 To DataGridView1.Columns.Count - 1

excel.Cells(1, i + 1) = DataGridView1.Columns(i).HeaderText

Next

'設置標題

Dim j As Integer

For i = 0 To DataGridView1.Rows.Count - 1 '填充數(shù)據(jù)

For j = 0 To DataGridView1.Columns.Count - 1

excel.Cells(i + 2, j + 1) = DataGridView1(j, i).Value

Next

Next

excel.Workbooks(1).SaveCopyAs(filename) '保存

Me.Close()

End Sub

vb中如何將數(shù)據(jù)導出到excel

介紹

下面通過一步一步的介紹，如何通過VB.NET來讀取數(shù)據(jù)，并且將數(shù)據(jù)導入到Excel中。

第一步：

打開VS開發(fā)工具，并且添加引用。

然后選擇。

Microsoft Excel 12.0 object library and。

Microsoft Excel 14.0 object library。

第二步：

創(chuàng)建一個Excle在你的電腦中。

第三步：

在VS中寫入如下代碼：

Imports System.Data

Imports System.Data.SqlClient

Imports Excel = Microsoft.Office.Interop.Excel。

Public Class excel

‘添加按鈕

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _

? Handles Button1.Click

Try

?? ?'創(chuàng)建連接

?? ?Dim cnn As DataAccess = New DataAccess(CONNECTION_STRING)

?? ?

?? ?Dim i, j As Integer

?? ?'創(chuàng)建Excel對象

?? ?Dim xlApp As Microsoft.Office.Interop.Excel.Application

?? ?Dim xlWorkBook As Microsoft.Office.Interop.Excel.Workbook

?? ?Dim xlWorkSheet As Microsoft.Office.Interop.Excel.Worksheet

?? ?Dim misValue As Object = System.Reflection.Missing.Value

?? ?xlApp = New Microsoft.Office.Interop.Excel.ApplicationClass

?? ?xlWorkBook = xlApp.Workbooks.Add(misValue)

?? ?' 打開某一個表單

?? ?xlWorkSheet = xlWorkBook.Sheets("sheet1")

?? ?' sql查詢

?? ?'??xlWorkBook.Sheets.Select("A1:A2")

?? ?Dim sql As String = "SELECT * FROM EMP"

?? ?' SqlAdapter

?? ?Dim dscmd As New SqlDataAdapter(sql, cnn.ConnectionString)

?? ?' 定義數(shù)據(jù)集

?? ?Dim ds As New DataSet

?? ?dscmd.Fill(ds)

?? ? ‘添加字段信息到Excel表的第一行

?? ?xlWorkSheet.Cells(1, 1).Value = "First Name"

?? ?xlWorkSheet.Cells(1, 2).Value = "Last Name"

?? ?xlWorkSheet.Cells(1, 3).Value = "Full Name"

?? ?xlWorkSheet.Cells(1, 4).Value = "Salary"

?? ?' 將數(shù)據(jù)導入到excel

?? ???For i = 0 To ds.Tables(0).Rows.Count - 1

?? ?? ? 'Column

?? ?? ? For j = 0 To ds.Tables(0).Columns.Count - 1

?? ?? ?? ???' this i change to header line cells

?? ?? ?? ???xlWorkSheet.Cells(i + 3, j + 1) = _

?? ?? ?? ???ds.Tables(0).Rows(i).Item(j)

?? ?? ? Next

?? ?Next

?? ?'HardCode in Excel sheet

?? ?' this i change to footer line cells??

???xlWorkSheet.Cells(i + 3, 7) = "Total"

?? ?xlWorkSheet.Cells.Item(i + 3, 8) = "=SUM(H2:H18)"

?? ?' 保存到Excel

?? ?xlWorkSheet.SaveAs("D:\vbexcel.xlsx")

?? ?xlWorkBook.Close()

?? ?xlApp.Quit()

?? ?releaseObject(xlApp)

?? ?releaseObject(xlWorkBook)

?? ?releaseObject(xlWorkSheet)

?? ?'彈出對話框顯示保存后的路徑

?? ?MsgBox("You can find the file D:\vbexcel.xlsx")

Catch ex As Exception

End Try

End Sub

' Function of Realease Object in Excel Sheet

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

End Class

復制代碼。

第四步：

看到如下導出結(jié)果。

vb.net datagridview顯示的數(shù)據(jù)導出到其他數(shù)據(jù)庫

一行一行的insert into到另一個數(shù)據(jù)庫就行了。一般不使用批量寫入，字符串太長

VB.NET怎么將DataGridview里的內(nèi)容導出到EXCEL去

不好意思 現(xiàn)在才看到；Private Sub 導出EXCEL_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click

Dim xlApp, xlBook, xlSheet As Object

xlapp = CreateObject("Excel.Application")

xlbook = xlapp.Workbooks.Add

xlsheet = xlbook.Worksheets(1)

'Dim xlapp As New Excel.Application

'Dim xlbook As Excel.Workbook

'Dim xlsheet As Excel.Worksheet

Dim rowindex, colindex As Integer rowindex = 1 '行

colindex = 0 '列

'xlbook = xlapp.Workbooks.Open("c:\EXCEL.xls") '打開EXCEL文件

xlsheet = xlapp.Worksheets("sheet1") '打開sheet1那頁 Dim a As New DataSet

Dim table As New System.Data.DataTable table = DataSet11.Tables("INVMATLISTA") '填充表 Dim row As DataRow '定義row為表格的行

Dim col As DataColumn '定義col為表格的列 '把表格的每一列寫到EXCEL去

For Each col In table.Columns

colindex = colindex + 1

xlapp.Cells(1, colindex) = col.ColumnName Next

'把表格的每一行寫到EXCEL去

For Each row In table.Rows

rowindex = rowindex + 1

colindex = 0

For Each col In table.Columns

colindex = colindex + 1

xlapp.Cells(rowindex, colindex) = row(col.ColumnName)

Next

Next

xlapp.Visible = True

End Sub 前提要先引用一個Microsoft.Office.Interop.Excel.dll然后在最上面先輸入Imports Microsoft.Office.Interop； 就可以了

網(wǎng)頁題目：vb.net數(shù)據(jù)導出,vb如何導入數(shù)據(jù)
分享URL：http://m.newbst.com/article10/hsejdo.html

成都網(wǎng)站建設公司_創(chuàng)新互聯(lián)，為您提供網(wǎng)站維護、域名注冊、商城網(wǎng)站、ChatGPT、手機網(wǎng)站建設、App設計

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源： 創(chuàng)新互聯(lián)