Contents
- Environment Allocation
- Excel Basic Manipulation with ASP.NET
- Generate Excel DataTable with ASP.NET
- Generate Excel Chart with ASP.NET
- Browse, Download and Delete Excel Files on Server
- Appendix
1. Environment Allocation
The following are the environments which I have tested.
- Windows 9x + Personal Web Server (PWS) + Microsoft Office
- Windows 2000 Professional + PWS + Microsoft Office
- Windows 2000 Server + Internet Information Services (IIS) + Microsoft Office
At present, it tests successfully in the latter two environments. Generally speaking, any Office version is OK. But in consideration of uncertainty and incompatible of customers' allocation, it's better to choose the older version to avoid that it will not be displayed after downloading.
There are two discoveries.
There are errors when creating Excel objects if WPS 2002 is installed.
It is unstable to create object if opening FrontPage. Sometimes it is successful, sometimes not. It is difficult to create an Excel object if Microsoft Office is run on server.
On the server, COM component permissions must be set. Type DCOMCNFG in the command line and then enter COM setting interface. Choose Microsoft Excel and click properties. Select custom and add Everyone to all permissions. Save and restart server.
2. Excel Basic Manipulation with ASP.NET
a) Create Excel Object
set objExcelApp = CreateObject("Excel.Application")
objExcelApp.DisplayAlerts = false
objExcelApp.Application.Visible = false
b) Create a New Excel File
objExcelApp.WorkBooks.add
set objExcelBook = objExcelApp.ActiveWorkBook
set objExcelSheets = objExcelBook.Worksheets
set objExcelSheet = objExcelBook.Sheets(1)
c) Read Existed Excel File
strAddr = Server.MapPath(".")
objExcelApp.WorkBooks.Open(strAddr & "\Templet\Table.xls")
set objExcelBook = objExcelApp.ActiveWorkBook
set objExcelSheets = objExcelBook.Worksheets
set objExcelSheet = objExcelBook.Sheets(1)
d) Save as Excel File
objExcelBook.SaveAs strAddr & "\Temp\Table.xls"
e) Save Excel File
objExcelBook.Save
f) Quit
objExcelApp.Quit
set objExcelApp = Nothing
3. Generate Excel DataTable with ASP.NET
a) Insert Data in A Range
objExcelSheet.Range("B3:k3").Value = Array("67", "87", "5", "9", "7", "45", "45", "54", "54", "10")
b) Insert Data in A Cell
objExcelSheet.Cells(3,1).Value="Internet Explorer"
c) Select A Range
d) Draw Border on Cell. (Right, Left, Top and Bottom)
e) Set Cell Background Color
f) Merge Cell
g) Insert Row and Column
Recommend a tip:
Export Database to Excel, PDF, HTML, RTF, XML, etc. for ASP.NET without Automation
4. Generate Chart with ASP.NET
a) Create Chart
objExcelApp.Charts.Add
b) Set Chart Type
objExcelApp.ActiveChart.ChartType = 97
Note: 4, Line charts; 5, Pie charts; 51, Bar charts.
c) Set Chart Title
objExcelApp.ActiveChart.HasTitle = True
objExcelApp.ActiveChart.ChartTitle.Text = "A test Chart"
d) Set Chart through Source Data
objExcelApp.ActiveChart.SetSourceData objExcelSheet.Range("A1:k5"),1
e) Set Chart Data Directly
objExcelApp.ActiveChart.SeriesCollection.NewSeries
objExcelApp.ActiveChart.SeriesCollection(1).Name = "=""333"""
objExcelApp.ActiveChart.SeriesCollection(1).Values = "={1,4,5,6,2}"
f) Bind Chart
objExcelApp.ActiveChart.Location 1
g) Display DataTable
objExcelApp.ActiveChart.HasDataTable = True
h) Display Legend
objExcelApp.ActiveChart.DataTable.ShowLegendKey = True
5. Browse, Download and Delete Excel Files on Server.
There are several solutions to browse,
Location.href=
,
Navigate
,
Response.Redirect
. But it's better to use server because it has more time to generate Excel with server.
It is a little troublesome to download. It is better to download component by using the server online or customize a component. The other way is to manipulate Excel component on server.
There are three programs to delete.
Name the Excel files which are generated by one users as same. So, the new file can cover the old one automatically.
Set to delete users' temporary files when Session_onEnd method is aroused in Global.asa file.
Set to delete all the files in temporary folder when Application_onStart method is aroused in Global.asa file.
6. Appendix
Add "
On Error Resume Next
" before each file to avoid the progress dies when errors appear. Therefore, we must run “Application.Quit” no matter if there are errors in files to make sure that there are no dead progress left after completing program.
Example
strAddr = Server.MapPath(".")
set objExcelApp = CreateObject("Excel.Application")
objExcelApp.DisplayAlerts = false
objExcelApp.Application.Visible = false
objExcelApp.WorkBooks.Open(strAddr & "\Templet\Null.xls")
set objExcelBook = objExcelApp.ActiveWorkBook
set objExcelSheets = objExcelBook.Worksheets
set objExcelSheet = objExcelBook.Sheets(1)
objExcelSheet.Range("B2:k2").Value = Array("Week1", "Week2", "Week3", "Week4", "Week5", "Week6", "Week7", "Week8", "Week9", "Week10")
objExcelSheet.Range("B3:k3").Value = Array("67", "87", "5", "9", "7", "45", "45", "54", "54", "10")
objExcelSheet.Range("B4:k4").Value = Array("10", "10", "8", "27", "33", "37", "50", "54", "10", "10")
objExcelSheet.Range("B5:k5").Value = Array("23", "3", "86", "64", "60", "18", "5", "1", "36", "80")
objExcelSheet.Cells(3,1).Value="Internet Explorer"
objExcelSheet.Cells(4,1).Value="Netscape"
objExcelSheet.Cells(5,1).Value="Other"
objExcelSheet.Range("b2:k5").Select
objExcelApp.Charts.Add
objExcelApp.ActiveChart.ChartType = 97
objExcelApp.ActiveChart.BarShape =3
objExcelApp.ActiveChart.HasTitle = True
objExcelApp.ActiveChart.ChartTitle.Text = "Visitors log for each week shown in browsers percentage"
objExcelApp.ActiveChart.SetSourceData objExcelSheet.Range("A1:k5"),1
objExcelApp.ActiveChart.Location 1
objExcelBook.SaveAs strAddr & "\Temp\Excel.xls"
objExcelApp.Quit
set objExcelApp = Nothing
sub btnExport_onclick()
dim objExcel
on error resume next
Set objExcel = CreateObject("excel.application")
With objExcel.visible =true
workbooks.add.sheets("sheet1").select
End with
m_row="0"
for a=0 to document.all.mytable.rows.length-1
m_row = cstr(int (m_row)+1)
for b=0 to document.all.mytable.rows(a).cells.length-1
m_col = chr(asc("A")+b)
objexcel.range(m_col&m_row).select
M_value = document.all.mytable.rows(a).cells(b).innerText
objexcel.activecell.value=cstr(m_value)
next
next
objexcel.visible=true
objexcel.range("A1").select
End sub