vb.net导出 vb数据库导出excel

利用VB.NET实现导出DataTable数据到excel中，各位大侠帮帮忙，最好有源码，谢谢，江湖救急

刚好写了个Helper类，你试验一下DataTable2Exce(这个方法代码如下：

成都创新互联于2013年开始，是专业互联网技术服务公司，拥有项目成都网站设计、成都做网站网站策划，项目实施与项目整合能力。我们以让每一个梦想脱颖而出为使命，1280元龙山做网站,已为上家服务,为龙山各地企业和个人服务,联系电话:18980820575

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("该文件已经存在！")

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文件数据表列表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) '调试是否需要$

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文件中的一个数据

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) '调试是否需要$

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文件指定数据表的数据列表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怎么将DataGridview里的内容导出到EXCEL去

不好意思 现在才看到；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； 就可以了

VB.net 从oracle数据库导出CSV文件

和导出txt文件的操作是一样的。

sql语句从数据库获得数据集，然后逐条插入到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导出 vb数据库导出excel
当前链接：http://6mz.cn/article/doceeed.html