vbnet導出excel文件

① vb中如何將數據導出到excel

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

第一步：
打開VS開發工具，並且添加引用。
然後選擇。

Microsoft Excel 12.0 object library and。

Microsoft Excel 14.0 object library。

② 利用VB.NET實現導出DataTable數據到excel中，各位大俠幫幫忙，最好有源碼，謝謝，江湖救急

剛好寫了個Helper類，你試驗一下DataTable2Exce(這個方法代碼如下：
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中 導出excel表格代碼怎麼寫

private void ExportExcelFromDataGrid
( string filename , System.Web.UI.WebControls.DataGrid ToExcelGrid )
...{
Response.Clear();
Response.Buffer= true;
Response.Charset="utf-8";
Response.AppendHeader ("Content-Disposition","attachment;filename="+Server.UrlEncode ( filename ) );
Response.ContentEncoding=System.Text.Encoding.Default;//設置輸出流為簡體中文
Response.ContentType = "application/ms-excel";//設置輸出文件類型為excel文件。
this.EnableViewState = false;
System.Globalization.CultureInfo myCItrad =
new System.Globalization.CultureInfo("ZH-CN",true);
System.IO.StringWriter oStringWriter = new System.IO.StringWriter(myCItrad);
System.Web.UI.HtmlTextWriter oHtmlTextWriter =
new System.Web.UI.HtmlTextWriter(oStringWriter);
ToExcelGrid.RenderControl(oHtmlTextWriter);
Response.Write(oStringWriter.ToString());
Response.End();
}

private void Button4_Click(object sender, System.EventArgs e)
...{
this.Panel1.Visible = false;
string filename = "內訓師.xls";
this.DataGrid1.Columns[6].Visible = true;
this.DataGrid1.Columns[7].Visible = true;
this.DataGrid1.Columns[this.DataGrid1.Columns.Count-1].Visible = false; // *
this.DataGrid1.Columns[this.DataGrid1.Columns.Count-2].Visible = false; // *
this.DataGrid1.AllowSorting = false; // *
this.DataGrid1.AllowPaging = false;
this.DataGrid1.SelectedIndex = -1; // *
this.BindGrid();
this.ExportExcelFromDataGrid ( filename , this.DataGrid1 );
}
}
原理是利用DataGrid（其實是其父類Control）的RenderControl方法輸出，整個DataGrid的外觀時，將這些HTML代碼寫入到緩沖區，同時設置一下 ContentType ，讓Excel自己的自動糾錯功能將這個輸出存為一個Excel文件。

很多網上的朋友使用了以上這種可粘貼性強的代碼，發現不好用，反映的錯誤類似：
「LinkButton必須放在一個具有runat=server的標簽的Form」之類的話，而更多的網友說：
問題很明顯，因為DataGrid沒有放在runat=server 的Form裡面，加上就可以了。

我認為，這種回答是很不準確的，理由如下：

通常使用這種代碼的人他/她的DataGrid，最起碼已經能用了，所以必定放在那個具有runat=server 的form標簽里了。 報錯是LinkButton，而不是DataGrid，很多細心的朋友很可能會說，我一直在用DataGrid，LinkButton在哪裡來的。 其實真正的問題是，上面的代碼沒有加了 // * 的那幾行代碼引起的。
當然如果你的DataGrid，沒有排序，沒有使用那種按鈕列的話，是不會出問題的。
言歸正傳，LinkButtion其實是你將DataGrid設為可排序時候，的表頭包含的，所以我要將DataGrid的排序設為False。
同樣得到上面的其實，那些按鈕列，什麼"編輯"、「刪除」等等這些，也是用了LinkButton，把他設為不可見就是了。
其實說得再明白一點就是，將有可能產生LinkButton，或其他控制項的東西都不讓它輸出就是了

vb.net dataGridView中的數據導出為excel表2010-03-01 15:03在form1窗體里添加一個dataGridView1（根據自己是使用情況要給他命名，符合命名規范喲。）在添加一個Button1（命名）。記著，dataGridView的屬性里有一個allowUsersToAddRows，要設置成FALSE。否則會發生一個錯誤，當然錯誤是什麼你自己試一下就ok了。

還有一點要注意：因為你要用到excel表，所以要首先在項目里添加引用喲：項目（project）-添加引用（add reference）-Microsoft .Office.Interop.Excel

Button1的代碼：

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
'要先添加引用才能用到 Microsoft.Office.Interop.Excel.Application()
Dim MyExcel As New Microsoft.Office.Interop.Excel.Application()
MyExcel.Application.Workbooks.Add(True)
MyExcel.Visible = True

'去除dataGridView1的編號列（這里也可以不要）
Dim m As Integer
For m = 0 To DataGridView1.ColumnCount - 1
MyExcel.Cells(1, m + 1) = Me.DataGridView1.Columns(m).HeaderText
Next m

'往excel表裡添加數據
Dim i As Integer
For i = 0 To DataGridView1.RowCount - 1
Dim j As Integer
For j = 0 To DataGridView1.ColumnCount - 1
If Me.DataGridView1(j, i).Value Is System.DBNull.Value Then

MyExcel.Cells(i + 2, j + 1) = ""
Else
MyExcel.Cells(i + 2, j + 1) = DataGridView1(j, i).Value.ToString

End If
Next j
Next i

End Sub