vbnet数据库导出excel

① 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

② vb中如何将数据导出到excel

介绍
下面通过一步一步的介绍，如何通过VB.NET来读取数据，并且将数据导入到Excel中。

第一步：
打开VS开发工具，并且添加引用。
然后选择。

Microsoft Excel 12.0 object library and。

Microsoft Excel 14.0 object library。

③ vb.net中从DataGridView里面把数据导出到excel中

DataGridView
一般用的DATASET或
DATATABLE
的数据源
网上搜下excel操作类就行..
我有个项目就用的..读写excel的类,你找不到我再贴上来吧
GL~

④ 利用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中实现把SQL数据库里表的数据导入到Excel中谢谢！

这个我是这么实现的.要用到

Excel.Application

Set xlapp = CreateObject("Excel.Application") '激活EXCEL
xlapp.Visible = True '让EXCEL可见
Set xlbook = xlapp.Workbooks.Open(App.Path + "个人简历.xlt")
'打开工作簿，strDestination为一个EXCEL报表文件
Set xlsheet = xlbook.Worksheets(1) '设定工作表

这个有点像是vb6的、vb.net也有相应的方法的.总之是
excel.application 用到这个就可以了.必要的话导入excel的dll文件.
这样你就发现、可以用vb.net来对excel来操作.你可以查下微软的msdn、有具体的属性方法.

如:
Dim ef As New Excel.Application
Dim es As New Excel.Worksheet
Dim er As Excel.Range
ef.Workbooks.Add()
es = ef.Worksheets("sheet1")
es.Activate()
er = es.Range("a1:C1")
er.Select()
er.Merge()
er.Value = "gordon1117"

ef.Visible = True 目前应用了三个对象:application,worksheet和range,三者的关系应该清晰,做到这点就能从基本开始做了:)格式设置都在range对象,只不过在设置对齐属性上是verticalalignment和horizon

range 用来定位、
然后你可以循环把数据输出到指定了格式的excel文件里了吧、其实连格式都是可以程序控制的好好学下吧.

可以参考一下这篇文章
http://blog.csdn.net/hawordcheng/archive/2006/10/26/1351931.aspx

⑥ vb.net 输出 excel

这里有段VB6.0的，你可以参考。
注意添加引用。

Private Sub Command6_Click()
Dim i, j As Integer
Dim xlApplication As Excel.Application, xlWorkbook As Excel.Workbook, xlSheet
Dim xlApp As Excel.Application
On Error Resume Next
Set xlApplication = GetObject(, "Excel.Application")
Set xlApp = CreateObject("Excel.Application")
If MsgBox("确认将文件信息导出到EXCEL中？�", vbExclamation + vbYesNo, "警告") = vbYes Then
If Err.Number <> 0 Then Set xlApplication = CreateObject("Excel.Application")
Set xlWorkbook = xlApplication.Workbooks.Add
Set xlSheet = xlWorkbook.ActiveSheet
xlSheet.Cells(1, 2) = lblcl.Caption
xlSheet.Range("A1:E1").MergeCells = True
xlSheet.Range("A1:E1").HorizontalAlignment = xlCenter
xlSheet.Cells(2, 2).ColumnWidth = 18
For i = 1 To DataGrid1.Columns.Count
xlSheet.Cells(2, 1) = "编号"
xlSheet.Cells(2, i + 1) = DataGrid1.Columns(i).Caption
For j = 0 To DataGrid1.VisibleRows - 1
xlSheet.Cells(j + 3, 1) = j + 1
xlSheet.Cells(j + 3, i + 1) = DataGrid1.Columns(i).CellText(DataGrid1.RowBookmark(j))
Next j
Next i
xlApplication.Visible = True
Set xlSheet = Nothing
Set xlWorkbook = Nothing
Set xlApplication = Nothing

'xlApp.Range("A2:L2").Columns.Interior.ColorIndex = 40
'xlApp.Range("A2:L2").Borders.LineStyle = xlContinuous
'xlApp.Visible = True

'xlApp.Range(xlSheet.Cells(2 + PartsRs.RecordCount + 1, 1), xlSheet.Cells(2 + PartsRs.RecordCount + 1, 8)).Columns.Interior.ColorIndex = 40
'xlApp.Range(xlSheet.Cells(2 + PartsRs.RecordCount + 1, 1), xlSheet.Cells(2 + PartsRs.RecordCount + 1, 8)).Borders.LineStyle = xlContinuous
Else
MsgBox "无信息可供您导出，请确认!", vbExclamation + vbOKOnly, "警告"
End If
End Sub