以下是我以前百度找的資料 希望對你有用 你讀取DataGridView到DataGrid然后直接調(diào)用函數(shù)即可
創(chuàng)新互聯(lián)專注為客戶提供全方位的互聯(lián)網(wǎng)綜合服務，包含不限于成都做網(wǎng)站、成都網(wǎng)站建設、溫縣網(wǎng)絡推廣、微信小程序開發(fā)、溫縣網(wǎng)絡營銷、溫縣企業(yè)策劃、溫縣品牌公關、搜索引擎seo、人物專訪、企業(yè)宣傳片、企業(yè)代運營等，從售前售中售后，我們都將竭誠為您服務，您的肯定，是我們最大的嘉獎；創(chuàng)新互聯(lián)為所有大學生創(chuàng)業(yè)者提供溫縣建站搭建服務，24小時服務熱線：028-86922220，官方網(wǎng)址：www.cdcxhl.com
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
剛好寫了個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("該文件已經(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
不好意思 現(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； 就可以了
先在: My Project 中引用 Microsoft Excel 11.0 Object Library（這個是Excel 2003，Excel 2007 是12.0）,之后在窗體代碼中加入相關內(nèi)容就行了。以下是一個窗體的簡單實例
假設在C盤根文件夾中有：TEST.xls
Public Class 引用EXCEL窗體
Private A() As String = {"A", "B", "CC", "C", "D"}
Private B() As Integer = {1, 22, 34, 50, 16, 99, 14}
Private excelapp As New Microsoft.Office.Interop.Excel.Application
Private excelworkbook As Microsoft.Office.Interop.Excel.Workbook
Private Sub 引用EXCEL_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim Index As Integer
excelworkbook = excelapp.Workbooks.Open("c:\test.xls")
excelapp.Application.Workbooks.Add(True)
excelapp.Cells(1, 1) = "數(shù)組:A"
For Index = 0 To UBound(A)
excelapp.Cells(Index + 2, 1) = A(Index)
Next
excelapp.Cells(1, 2) = "數(shù)組:B"
For Index = 0 To UBound(B)
excelapp.Cells(Index + 2, 2) = B(Index)
Next
excelapp.Visible = True
End Sub
End Class
運行結果：
數(shù)組:A 數(shù)組:B
A 1
B 22
CC 34
C 50
D 16
99
14
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 = "內(nèi)訓師.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代碼寫入到緩沖區(qū)，同時設置一下 ContentType ，讓Excel自己的自動糾錯功能將這個輸出存為一個Excel文件。
很多網(wǎng)上的朋友使用了以上這種可粘貼性強的代碼，發(fā)現(xiàn)不好用，反映的錯誤類似：
“LinkButton必須放在一個具有runat=server的標簽的Form”之類的話，而更多的網(wǎng)友說：
問題很明顯，因為DataGrid沒有放在runat=server 的Form里面，加上就可以了。
我認為，這種回答是很不準確的，理由如下：
通常使用這種代碼的人他/她的DataGrid，最起碼已經(jīng)能用了，所以必定放在那個具有runat=server 的form標簽里了。 報錯是LinkButton，而不是DataGrid，很多細心的朋友很可能會說，我一直在用DataGrid，LinkButton在哪里來的。 其實真正的問題是，上面的代碼沒有加了 // * 的那幾行代碼引起的。
當然如果你的DataGrid，沒有排序，沒有使用那種按鈕列的話，是不會出問題的。
言歸正傳，LinkButtion其實是你將DataGrid設為可排序時候，的表頭包含的，所以我要將DataGrid的排序設為False。
同樣得到上面的其實，那些按鈕列，什么"編輯"、“刪除”等等這些，也是用了LinkButton，把他設為不可見就是了。
其實說得再明白一點就是，將有可能產(chǎn)生LinkButton，或其他控件的東西都不讓它輸出就是了
vb.net dataGridView中的數(shù)據(jù)導出為excel表2010-03-01 15:03在form1窗體里添加一個dataGridView1（根據(jù)自己是使用情況要給他命名，符合命名規(guī)范喲。）在添加一個Button1（命名）。記著，dataGridView的屬性里有一個allowUsersToAddRows，要設置成FALSE。否則會發(fā)生一個錯誤，當然錯誤是什么你自己試一下就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表里添加數(shù)據(jù)
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
一、DCOM修復
1.運行：mmc comexp.msc /32，找到我的電腦 - DCom配置中的Microsoft Excel Application
2.在Microsoft Excel Application上點擊右鍵，選擇"屬性"
3.點擊"標識"標簽,選擇"交互式用戶"
4.點擊"安全"標簽,在"啟動和激活權限"上點擊"自定義",然后點擊對應的"編輯"按鈕,在彈出的"安全性"對話框中填加一個"NETWORK SERVICE"用戶(注意要選擇本計算機名),并給它賦予"本地啟動"和"本地激活"權限.
5.依然是"安全"標簽,在"訪問權限"上點擊"自定義",然后點擊"編輯",在彈出的"安全性"對話框中也填加一個"NETWORK SERVICE"用戶,然后賦予"本地訪問"權限.
二、如果找不到Microsoft Excel 應用程序
1、進行OFFICE菜單中的組件重注冊
2、如果仍未解決問題，點 開始--運行--輸入EXCEL文件所在的路徑 “c:\program files\exel.exe” /regserver 進行修復注冊
參考：
重慶分公司
重慶分公司
