我用了如下代码(从网上抄的),但是打不开,在服务器端都打不开,但是每次文件确实有改变,请高手指教。
Sub exportbutton_click(sender As Object, e As System.Web.UI.ImageClickEventArgs)
Dim oExcel As New Excel.Application
Dim oBooks As Excel.Workbooks, oBook As Excel.Workbook
Dim oSheets As Excel.Sheets, oSheet As Excel.Worksheet
Dim oCells As Excel.Range
Dim sFile As String, sTemplate As String '定义一个datatable
Dim dt As DataTable = CType(session("nowds").tables(session("table")), DataTable) sFile=Server.MapPath(Request.ApplicationPath) & "/temp/MyExcel.xls"
sTemplate = Server.MapPath(Request.ApplicationPath) & "/temp/MyTemplate.xls"
oExcel.Visible =true oExcel.UserControl = true
oExcel.DisplayAlerts =false '定义一个新的工作簿
oBooks = oExcel.Workbooks
oBooks.Open(Server.MapPath(Request.ApplicationPath) & "/temp/MyTemplate.xls")
oBook = oBooks.Item(1)
oSheets = oBook.Worksheets
oSheet = CType(oSheets.Item(1), Excel.Worksheet)
'命名该sheet oSheet.Name = "用水指标"
oCells = oSheet.Cells '调用dumpdata过程,将数据导入到Excel中去
DumpData(dt, oCells) '保存
oSheet.SaveAs(sFile)
oBook.Close()
'退出Excel,并且释放调用的COM资源
oExcel.Quit()
ReleaseComObject(oCells) : ReleaseComObject(oSheet) ReleaseComObject(oSheets) : ReleaseComObject(oBook) ReleaseComObject(oBooks) : ReleaseComObject(oExcel)
oExcel = Nothing : oBooks = Nothing : oBook = Nothing oSheets = Nothing : oSheet = Nothing : oCells = Nothing
System.GC.Collect()
Response.Redirect(sFile)
End Sub
'将DATATABLE的内容导出到Excel的单元格中去
Private Function DumpData(ByVal dt As DataTable, ByVal oCells As Excel.Range) As String
Dim dr As DataRow, ary() As Object
Dim iRow As Integer, iCol As Integer '输出列标题
oCells(2, 1) = "用水类型"
oCells(2, 3) = "一般标准"
oCells(2, 4) = "计算采用值"
'将数据导出到相应的单元格
For iRow = 0 To dt.Rows.Count - 1
dr = dt.Rows.Item(iRow)
ary = dr.ItemArray
For iCol = 0 To UBound(ary)
oCells(iRow + 3, iCol + 1) = ary(iCol).ToString
Response.Write(ary(iCol).ToString & vbTab)
Next
Next
End Function