随笔- 54 文章- 0 评论- 200

asp.net操作excel

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

On Error GoTo ERR_FLAG

Dim strPath As String

Dim strSave As String

Dim strOpenFile As String

Dim excelWorkBook As Excel.Workbook

Dim excelWorkSheet As Excel.Worksheet

Dim excelRange As Excel.Range

Dim excelApp As New Excel.Application

strOpenFile = Server.MapPath("ExcelTemplates\TestExcel.xls")

strSave = Server.MapPath("ReportOutput\TestExcel.xls")

excelApp.Application.DisplayAlerts = False

excelApp.Visible = False

excelWorkBook = excelApp.Workbooks.Open(strOpenFile)

excelWorkSheet = excelWorkBook.Worksheets(1)

If Not setWorkSheet(excelWorkSheet) Then

GoTo ERR_FLAG

End If

excelWorkBook.SaveAs(strSave)

excelWorkBook.Close()

excelApp.Quit()

System.Runtime.InteropServices.Marshal.ReleaseComObject(excelWorkSheet)

System.Runtime.InteropServices.Marshal.ReleaseComObject(excelWorkBook)

System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp)

Call showFile(strSave)

Exit Sub

ERR_FLAG:

If Not excelWorkSheet Is Nothing Then

System.Runtime.InteropServices.Marshal.ReleaseComObject(excelWorkSheet)

End If

If Not excelWorkBook Is Nothing Then

excelWorkBook.Close()

System.Runtime.InteropServices.Marshal.ReleaseComObject(excelWorkBook)

End If

If Not excelApp Is Nothing Then

excelApp.Quit()

System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp)

End If

excelWorkSheet = Nothing

excelWorkBook = Nothing

excelApp = Nothing

System.GC.Collect(0)

Response.Write(Err.Description)

End Sub

Function getData() As DataSet

Dim con As SqlConnection

con = New SqlConnection("server=wxhy-lee;database=JustTest;uid=sa;pwd=sa;")

Dim da As SqlDataAdapter

da = New SqlDataAdapter("Select ClassID,ClassName,UpClassID,ClassLevel From ClassName ", con)

Dim ds As New DataSet

da.Fill(ds)

Return ds

End Function

Function setWorkSheet(ByRef excelWorkSheet As Excel.Worksheet) As Boolean

On Error GoTo ERR_FLAG

setWorkSheet = False

Dim dSet As DataSet

Dim intI As Integer

Dim intTotal As Integer

dSet = getData()

intTotal = dSet.Tables(0).Rows.Count

If intTotal = 0 Then

GoTo ERR_FLAG

End If

For intI = 2 To intTotal + 1

excelWorkSheet.Range("A" & intI).Value = dSet.Tables(0).Rows(intI - 2).Item("ClassID")

excelWorkSheet.Range("B" & intI).Value = dSet.Tables(0).Rows(intI - 2).Item("ClassName")

excelWorkSheet.Range("C" & intI).Value = IIf(IsDBNull(dSet.Tables(0).Rows(intI - 2).Item("UpClassID")), "NULL", dSet.Tables(0).Rows(intI - 2).Item("UpClassID"))

excelWorkSheet.Range("D" & intI).Value = dSet.Tables(0).Rows(intI - 2).Item("ClassLevel")

dSet.Clear()

dSet.Dispose()

setWorkSheet = True

Exit Function

ERR_FLAG:

Return False

End Function

Sub showFile(ByVal strSave As String)

Response.ContentType = "application/ms-excel"

Response.AddHeader("content-disposition", "attachment; filename=" & "TestExcel.xls")

Response.WriteFile(strSave)

Response.End()

End Sub

posted @ 2007-04-04 14:14 BoyLee 阅读(375) 评论(0) 编辑收藏所属分类: Asp.Net

新用户注册刷新评论列表

标题

姓名

主页

(博主才能看到)


验证码	* 看不清,换一张 [登录][注册]

内容(请不要发表任何与政治相关的内容)

网站首页

新闻频道

社区

小组

博问

网摘

闪存

找找看

Remember Me?

登录使用高级评论新用户注册返回页首恢复上次提交

[使用Ctrl+Enter键可以直接提交]

该文被作者在 2007-07-24 10:13 编辑过

相关链接:

所属分类的其他文章:
[转]petshop4.0设计说明
推荐几个asp.net测试空间
收藏: C#设计模式之简单工厂篇
收藏:简单工厂模式（SimpleFactory-C#)
vb.net版中文验证码
验证码收藏
asp.net操作csv
UltraWebGrid多条数据scoll bar定位解决
UltraWebGrid使用指南(原创)

最新IT新闻:
百度网络交易平台定名“有啊”
全屏触摸力反馈黑莓Storm发布
微软推出Touchless：无需触屏的触屏技术
AMD剥离芯片制造业务获60亿美元投资
Google向Wine贡献更多代码

2007年4月

日

一

二

三

四

五

六

与我联系

发短消息

搜索

我参加的小组

随笔分类(49)

随笔档案(54)

友情链接

积分与排名

积分 - 45887
排名 - 959

asp.net操作excel

与我联系

搜索

我参加的小组

随笔分类(49)

随笔档案(54)

友情链接

积分与排名

最新随笔

最新评论

阅读排行榜