Manipulate Excel with ASP.NET

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

'On Error Resume Next
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 
'objExcelApp.ActiveChart.HasDataTable = True 
'objExcelApp.ActiveChart.DataTable.ShowLegendKey = True
objExcelBook.SaveAs strAddr & "\Temp\Excel.xls" 
objExcelApp.Quit 

set objExcelApp = Nothing 

'Method to Save table in Web
'For example: ID=MYTABLE in Table
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