不打开EXCEL导出EXCEL报表

不打开EXCEL导出EXCEL报表

- 中国WEB开发者网络 (http://www.webasp.net)
-- 技术教程 (http://www.webasp.net/article/)
--- 不打开EXCEL导出EXCEL报表 (http://www.webasp.net/article/28/27197.htm)

-- 作者：雨浪(jack651)
-- 发布日期： 2006-01-09

网上有太多的使用打开隐藏文档的方式将DATASET或者DATATABLE导出到EXCEL.
这样做有几个问题:
1.EXCEL进程很难杀死,甚至出现无响应
2.响应速度慢
3.必须要安装OFFICE才能使用
当然,也有它的优点,那就是格式化能力强,能够做出格式化的EXCEL报表.但是比起常常出错,反映速度,以及特定环境看来,优点也不是很明显
在此贡献给大家一个不同的导出EXCEL方式
优点是:
1.速度快
2.不容易出错
3.无需安装OFFICE
但是也有确定,就是导出的报表象EXCEL一样,没有格式化.
因为此种方法就是将EXCEL当成数据库来创建的.
以下为详细的程序,需要研究的朋友只需要建立一个WIN窗体,将以下代码覆盖原来的全部代码就OK了.不过请自己在BIN目录下放一个数据库,或者将程序里面的连接代码改一下:
Imports System.Data.OleDb
Imports System.Data
Imports Scripting

Public Class Form1
Inherits System.Windows.Forms.Form

Private dataset As New dataset
Private mytable As DataTable
Private dbpath As String = Application.StartupPath & "\YJ_Manage.mdb" '这里请自己改
Private dbconnstr As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbpath

Private dataadapter As OleDbDataAdapter
Private dataconnection As OleDbConnection
Private dataset As dataset

#Region " Windows 窗体设计器生成的代码 "

Public Sub New()
MyBase.New()

'该调用是 Windows 窗体设计器所必需的。
InitializeComponent()

'在 InitializeComponent() 调用之后添加任何初始化

End Sub

'窗体重写 dispose 以清理组件列表。
Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
If disposing Then
If Not (components Is Nothing) Then
components.Dispose()
End If
End If
MyBase.Dispose(disposing)
End Sub

'Windows 窗体设计器所必需的
Private components As System.ComponentModel.IContainer

'注意: 以下过程是 Windows 窗体设计器所必需的
'可以使用 Windows 窗体设计器修改此过程。
'不要使用代码编辑器修改它。
Friend WithEvents btnExport As System.Windows.Forms.Button
Friend WithEvents SaveDl As System.Windows.Forms.SaveFileDialog
<System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
Me.btnExport = New System.Windows.Forms.Button
Me.SaveDl = New System.Windows.Forms.SaveFileDialog
Me.SuspendLayout()
'
'btnExport
'
Me.btnExport.Location = New System.Drawing.Point(104, 128)
Me.btnExport.Name = "btnExport"
Me.btnExport.Size = New System.Drawing.Size(80, 32)
Me.btnExport.TabIndex = 0
Me.btnExport.Text = "Button1"
'
'Form1
'
Me.AutoScaleBaseSize = New System.Drawing.Size(6, 14)
Me.ClientSize = New System.Drawing.Size(292, 273)
Me.Controls.Add(Me.btnExport)
Me.Name = "Form1"
Me.Text = "Form1"
Me.ResumeLayout(False)

End Sub

#End Region

Private Function getdatafromdb(ByVal sqlstr As String) As dataset
Try
dataconnection = New OleDbConnection
dataconnection.ConnectionString = dbconnstr
dataadapter = New OleDbDataAdapter(sqlstr, dataconnection)
dataset = New DataSet
dataset.Clear()
dataadapter.Fill(dataset)
dataconnection.Close()
Catch ex As Exception
MessageBox.Show(Err.Description, "数据错误", MessageBoxButtons.OK, MessageBoxIcon.Warning)
Exit Function
End Try
If dataset.Tables(0).Rows.Count > 0 Then
Return dataset
Else
Return Nothing
End If
End Function

Private Sub btnExport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExport.Click
Dim MyOleDbCn As New OleDbConnection
Dim MyOleDbCmd As New OleDbCommand
Dim intRowsCnt, intColsCnt As Integer
Dim strSql As String, strFlName As String
Dim Fso As New FileSystemObject

dataset = getdatafromdb("select * from Employee_userinfo_main")
mytable = dataset1.Tables(0)
SaveDl.Title = "保持为"
SaveDl.Filter = "xls工作薄|*.xls"

If SaveDl.ShowDialog = DialogResult.OK Then
If SaveDl.FileName <> "" Then
strFlName = SaveDl.FileName()
Else
Exit Sub
End If
Else
Exit Sub
End If

Try
Me.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor

MyOleDbCn.ConnectionString = "Provider=Microsoft.Jet.OleDb.4.0;" & _
"Data Source=" & strFlName & ";" & _
"Extended ProPerties=""Excel 8.0;HDR=Yes;"""
MyOleDbCn.Open()
MyOleDbCmd.Connection = MyOleDbCn
MyOleDbCmd.CommandType = CommandType.Text

'第一行插入列标题
strSql = "CREATE TABLE sheet1("
For intColsCnt = 0 To MyTable.Columns.Count - 1
If intColsCnt <> MyTable.Columns.Count - 1 Then
strSql = strSql & MyTable.Columns(intColsCnt).Caption & " text,"
Else
strSql = strSql & MyTable.Columns(intColsCnt).Caption & " text)"
End If
Next
MyOleDbCmd.CommandText = strSql
MyOleDbCmd.ExecuteNonQuery()

'插入各行
For intRowsCnt = 0 To MyTable.Rows.Count - 1
strSql = "INSERT INTO sheet1 VALUES('"
For intColsCnt = 0 To MyTable.Columns.Count - 1
If intColsCnt <> MyTable.Columns.Count - 1 Then
strSql = strSql & MyTable.Rows(intRowsCnt).Item(intColsCnt) & "','"
Else
strSql = strSql & MyTable.Rows(intRowsCnt).Item(intColsCnt) & "')"
End If
Next
MyOleDbCmd.CommandText = strSql
MyOleDbCmd.ExecuteNonQuery()
Next
MessageBox.Show("数据已经成功导入EXCEL文件" & strFlName, "数据导出", MessageBoxButtons.OK, MessageBoxIcon.Information)
Catch ErrCode As Exception
MsgBox("错误信息：" & ErrCode.Message & vbCrLf & vbCrLf & _
"引发事件：" & ErrCode.TargetSite.ToString, MsgBoxStyle.OKOnly + MsgBoxStyle.Information, "错误来源：" & ErrCode.Source)
Exit Sub
Finally
MyOleDbCmd.Dispose()
MyOleDbCn.Close()
MyOleDbCn.Dispose()
Me.Cursor.Current = System.Windows.Forms.Cursors.Default
End Try
End Sub

End Class

当然,将EXCEL导入到access,mssql等数据库,datagrid等容器也是用相反类似的方法,将EXCEL作为数据库来使用.有感兴趣的朋友可以加我QQ:270499458
大家一起研究,共同进步~~~

webasp.net