有没有aspnet导出excel的代码

m0_47219873 2020-04-20 03:52:27

哪位大神给指点一下，谢谢

...全文

374 8 打赏收藏转发到动态举报

写回复

用AI写文章

8 条回复

切换为时间正序

请发表友善的回复…

发表回复

还想懒够 2020-06-11

打赏
举报

引用 7 楼 fangma2001 的回复:

以上代码可正常运行

服务器上不装Office，或者由于权限问题导致OLE组件权限不够的话，完全无法运行。读写Excel，建议使用NPOI，或者是我认为更好用的EPPlus（只适用于xlsx）

fangma2001 2020-06-10

打赏
举报

以上代码可正常运行

fangma2001 2020-06-10

打赏
举报

Imports System.Collections
Imports System.ComponentModel
Imports System.Data
Imports System.Drawing
Imports System.Web
Imports System.Web.SessionState
Imports System.Web.UI
Imports System.Web.UI.WebControls
Imports System.Web.UI.HtmlControls
Imports System.Data.OleDb
Imports System.Data.SqlClient

Imports System.IO
Imports System.Configuration
Imports System.Security.Permissions.FileDialogPermission
Imports System.Diagnostics
Public Class rep_jd
Inherits System.Web.UI.Page

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

'该调用是 Web 窗体设计器所必需的。
<System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()

End Sub
Protected WithEvents Tbyear As System.Web.UI.WebControls.TextBox
Protected WithEvents Ddlpart As System.Web.UI.WebControls.DropDownList
Protected WithEvents Ddl As System.Web.UI.WebControls.DropDownList
Protected WithEvents bt2 As System.Web.UI.WebControls.Button
Protected WithEvents lblstatus As System.Web.UI.WebControls.Label

'注意: 以下占位符声明是 Web 窗体设计器所必需的。
'不要删除或移动它。
Private designerPlaceholderDeclaration As System.Object

Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init
'CODEGEN: 此方法调用是 Web 窗体设计器所必需的
'不要使用代码编辑器修改它。
InitializeComponent()
End Sub

#End Region

Dim cn As New SqlConnection(ConfigurationSettings.AppSettings("conn"))
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'在此处放置初始化页的用户代码
Dim ss, uname, s4 As String
Dim unid, i As Integer

If Session("userid") Is Nothing Then

RegisterClientScriptBlock("提示：", "<script language=javascript>alert('超时，请重新登录！')</script>")
Exit Sub
Else

If Not IsPostBack Then

Tbyear.Text = DateTime.Now.Year()

i = CInt(DateTime.Now.Month)
If i > 0 And i <= 7 Then
Ddlpart.SelectedValue = "上半年"
Else
Ddlpart.SelectedValue = "下半年"
End If

End If
End If

End Sub

Private Sub bt2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles bt2.Click
Dim ss, uname, s4 As String
Dim unid As Integer

Dim ds As New DataSet
Dim sy As String
Dim i, j As Integer
Dim fname As String = "fname"

Dim beforetime, aftertime As DateTime

beforetime = DateTime.Now 'add 2015
Dim xlApp As New Excel.Application
aftertime = DateTime.Now 'add 2015
Dim xlBooks As Excel.Workbooks, xlBook As Excel.Workbook

Dim xlSheet As Excel.Worksheet

Dim spath As String

spath = Server.MapPath("./")
spath = spath & "uploads\pingwei4.xls"

xlApp.Visible = False
' xlBook = xlApp.Workbooks().Add

xlApp.DisplayAlerts = False
xlBooks = xlApp.Workbooks
xlBooks.Open(spath)
xlBook = xlBooks.Item(1)
'xlBook = xlApp.Application.Workbooks.Open("rep1.xlt", missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing)
xlSheet = xlBook.Worksheets("sheet1")

Dim Table As New DataTable
sy = DateTime.Now.Year

i = 0

Try

ss = "select unit,name,gid,pos,zhicheng,thisyear,partyear,huanjie,fenzu from pingwei where thisyear='"
ss = ss & sy & "' and partyear ='" & Ddlpart.SelectedValue.Trim & "' "
ss = ss & " and huanjie='" & Ddl.SelectedValue.Trim & "' order by fenzu,unit"

ds.Tables.Clear()

ds = Funlib.MyQueryUsesqlDataSet(ss, "temp")
If ds.Tables("temp").Rows.Count > 0 Then
xlApp.Cells(1, 5) = "单位评委报表"
xlApp.Cells(2, 5) = CStr(Funlib.getdate())

For i = 0 To ds.Tables("temp").Rows.Count - 1
For j = 0 To ds.Tables("temp").Columns.Count - 1
xlApp.Cells(i + 4, j + 1) = ds.Tables(0).Rows(i)(j)

s4 = ds.Tables(0).Rows(i)(3)

Next j
Next i

' xlSheet.get_Range(excel.cells(3,1),excel.cells(3,6).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;//设置标题格式为居中对齐

i = ds.Tables("temp").Rows.Count
j = ds.Tables("temp").Columns.Count
With xlSheet
.Cells.Select()
.Cells.Columns.AutoFit()
.Range(.Cells(1, 3), .Cells(1, 3)).Font.Name = "黑体"
.Range(.Cells(1, 3), .Cells(1, 3)).Font.Size = 16
'设标题为黑体字

.Range(.Cells(1, 3), .Cells(1, 3)).Font.Bold = True

'标题字体加粗
.Range(.Cells(3, 1), .Cells(3, 10)).Font.Name = "黑体"

'设标题为黑体字

.Range(.Cells(3, 1), .Cells(3, 10)).Font.Bold = True

'标题字体加粗
.Range(.Cells(1, 3), .Cells(1, 3)).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter
.Range(.Cells(2, 3), .Cells(2, 3)).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter
.Range(.Cells(3, 1), .Cells(3, 10)).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter '//居中对齐

.Range(.Cells(3, 1), .Cells(i + 3, j)).Borders.LineStyle = 1

'设表格边框样式
.Range(.Cells(1, 3), .Cells(1, 10)).Style.wraptext = False
.Range(.Cells(3, 3), .Cells(i + 3, 10)).Style.wraptext = True
'自动换行
End With

Else

lblstatus.Text = "没有符合条件的数据!"
End If
ds.Tables.Clear()

Catch ec As Exception

lblstatus.Text = "没有符合条件的数据!"
Finally

End Try

Dim spath2 As String
spath2 = Server.MapPath("./") & "uploads\pingwei5.xls"

xlBook.SaveAs(spath2)

xlBook.Close()

xlApp.Quit()
xlApp = Nothing

Funlib.exec_gc()

Dim starttime As DateTime 'add 2015
Dim myProcesses() As Process
Dim myProcess As Process

myProcesses = Process.GetProcessesByName("excel")
For Each myProcess In myProcesses
starttime = myProcess.StartTime
If starttime > beforetime And starttime < aftertime Then
If myProcess.CloseMainWindow() = False Then

myProcess.Kill()
Exit For
End If

End If
Next

If File.Exists(spath2) = False Then
Exit Sub
Else

Page.Response.Clear()
Response.Expires = 0
Response.Buffer = True
Page.Response.AddHeader("Content-Type", "application/ms-excel")

Page.Response.AddHeader("Content-Disposition", "attachment;filename=" & Server.UrlEncode(spath2))
Page.Response.WriteFile(spath2)

Page.Response.End()
End If

End Sub
End Class