建站
咨詢
售后
建站咨詢
新聞中心
vb.net中從DataGridView里面把數(shù)據(jù)導(dǎo)出到excel中
以下是我以前百度找的資料 希望對(duì)你有用 你讀取DataGridView到DataGrid然后直接調(diào)用函數(shù)即可
創(chuàng)新互聯(lián)專注為客戶提供全方位的互聯(lián)網(wǎng)綜合服務(wù)，包含不限于成都做網(wǎng)站、成都網(wǎng)站建設(shè)、溫縣網(wǎng)絡(luò)推廣、微信小程序開發(fā)、溫縣網(wǎng)絡(luò)營(yíng)銷、溫縣企業(yè)策劃、溫縣品牌公關(guān)、搜索引擎seo、人物專訪、企業(yè)宣傳片、企業(yè)代運(yùn)營(yíng)等，從售前售中售后，我們都將竭誠(chéng)為您服務(wù)，您的肯定，是我們最大的嘉獎(jiǎng)；創(chuàng)新互聯(lián)為所有大學(xué)生創(chuàng)業(yè)者提供溫縣建站搭建服務(wù)，24小時(shí)服務(wù)熱線：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("沒有記錄不能導(dǎo)出數(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 '如果用戶忘記添加擴(kuò)展名，將自動(dòng)家上
.DefaultExt = "xls" '默認(rèn)擴(kuò)展名
.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ù)導(dǎo)出成功！", "PurpleStar", MessageBoxButtons.OK, MessageBoxIcon.Information)
Else
Exit Function
End If
Else
MessageBox.Show("沒有記錄不能導(dǎo)出數(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
'設(shè)置標(biāo)題
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.NET實(shí)現(xiàn)導(dǎo)出DataTable數(shù)據(jù)到excel中，各位大俠幫幫忙，最好有源碼，謝謝，江湖救急
剛好寫了個(gè)Helper類，你試驗(yàn)一下DataTable2Exce(這個(gè)方法代碼如下：
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導(dǎo)入到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文件導(dǎo)出至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文件中的一個(gè)數(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怎么將DataGridview里的內(nèi)容導(dǎo)出到EXCEL去
不好意思 現(xiàn)在才看到；Private Sub 導(dǎo)出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那頁(yè) 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 前提要先引用一個(gè)Microsoft.Office.Interop.Excel.dll然后在最上面先輸入Imports Microsoft.Office.Interop； 就可以了
在VB.net中，如何使幾個(gè)數(shù)組變量按照列表示出來，并輸出到指定的EXCEL表格中！
先在: My Project 中引用 Microsoft Excel 11.0 Object Library（這個(gè)是Excel 2003，Excel 2007 是12.0）,之后在窗體代碼中加入相關(guān)內(nèi)容就行了。以下是一個(gè)窗體的簡(jiǎn)單實(shí)例
假設(shè)在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
運(yùn)行結(jié)果：
數(shù)組:A 數(shù)組:B
A 1
B 22
CC 34
C 50
D 16
99
14
vb.net中 導(dǎo)出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;//設(shè)置輸出流為簡(jiǎn)體中文
Response.ContentType = "application/ms-excel";//設(shè)置輸出文件類型為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)訓(xùn)師.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（其實(shí)是其父類Control）的RenderControl方法輸出，整個(gè)DataGrid的外觀時(shí)，將這些HTML代碼寫入到緩沖區(qū)，同時(shí)設(shè)置一下 ContentType ，讓Excel自己的自動(dòng)糾錯(cuò)功能將這個(gè)輸出存為一個(gè)Excel文件。
很多網(wǎng)上的朋友使用了以上這種可粘貼性強(qiáng)的代碼，發(fā)現(xiàn)不好用，反映的錯(cuò)誤類似：
“LinkButton必須放在一個(gè)具有runat=server的標(biāo)簽的Form”之類的話，而更多的網(wǎng)友說：
問題很明顯，因?yàn)镈ataGrid沒有放在runat=server 的Form里面，加上就可以了。
我認(rèn)為，這種回答是很不準(zhǔn)確的，理由如下：
通常使用這種代碼的人他/她的DataGrid，最起碼已經(jīng)能用了，所以必定放在那個(gè)具有runat=server 的form標(biāo)簽里了。 報(bào)錯(cuò)是LinkButton，而不是DataGrid，很多細(xì)心的朋友很可能會(huì)說，我一直在用DataGrid，LinkButton在哪里來的。 其實(shí)真正的問題是，上面的代碼沒有加了 // * 的那幾行代碼引起的。
當(dāng)然如果你的DataGrid，沒有排序，沒有使用那種按鈕列的話，是不會(huì)出問題的。
言歸正傳，LinkButtion其實(shí)是你將DataGrid設(shè)為可排序時(shí)候，的表頭包含的，所以我要將DataGrid的排序設(shè)為False。
同樣得到上面的其實(shí)，那些按鈕列，什么"編輯"、“刪除”等等這些，也是用了LinkButton，把他設(shè)為不可見就是了。
其實(shí)說得再明白一點(diǎn)就是，將有可能產(chǎn)生LinkButton，或其他控件的東西都不讓它輸出就是了
vb.net dataGridView中的數(shù)據(jù)導(dǎo)出為excel表2010-03-01 15:03在form1窗體里添加一個(gè)dataGridView1（根據(jù)自己是使用情況要給他命名，符合命名規(guī)范喲。）在添加一個(gè)Button1（命名）。記著，dataGridView的屬性里有一個(gè)allowUsersToAddRows，要設(shè)置成FALSE。否則會(huì)發(fā)生一個(gè)錯(cuò)誤，當(dāng)然錯(cuò)誤是什么你自己試一下就ok了。
還有一點(diǎn)要注意：因?yàn)槟阋玫絜xcel表，所以要首先在項(xiàng)目里添加引用喲：項(xiàng)目（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的編號(hào)列（這里也可以不要）
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
VB.NET輸出到Excel的問題
一、DCOM修復(fù)
1.運(yùn)行：mmc comexp.msc /32，找到我的電腦 - DCom配置中的Microsoft Excel Application
2.在Microsoft Excel Application上點(diǎn)擊右鍵，選擇"屬性"
3.點(diǎn)擊"標(biāo)識(shí)"標(biāo)簽,選擇"交互式用戶"
4.點(diǎn)擊"安全"標(biāo)簽,在"啟動(dòng)和激活權(quán)限"上點(diǎn)擊"自定義",然后點(diǎn)擊對(duì)應(yīng)的"編輯"按鈕,在彈出的"安全性"對(duì)話框中填加一個(gè)"NETWORK SERVICE"用戶(注意要選擇本計(jì)算機(jī)名),并給它賦予"本地啟動(dòng)"和"本地激活"權(quán)限.
5.依然是"安全"標(biāo)簽,在"訪問權(quán)限"上點(diǎn)擊"自定義",然后點(diǎn)擊"編輯",在彈出的"安全性"對(duì)話框中也填加一個(gè)"NETWORK SERVICE"用戶,然后賦予"本地訪問"權(quán)限.
二、如果找不到Microsoft Excel 應(yīng)用程序
1、進(jìn)行OFFICE菜單中的組件重注冊(cè)
2、如果仍未解決問題，點(diǎn) 開始--運(yùn)行--輸入EXCEL文件所在的路徑 “c:\program files\exel.exe” /regserver 進(jìn)行修復(fù)注冊(cè)
參考：
文章名稱：vb.net中輸出到表格 vb如何在列表框輸出
文章出自：http://www.dlmjj.cn/article/hpcsjs.html
