Click here to Skip to main content
15,445,081 members
Articles / Web Development / ASP.NET
Tip/Trick
Posted 22 May 2011

Tagged as

Stats

54K views
9 bookmarked

Manipulate Excel with ASP.NET

Rate me:
Please Sign up or sign in to vote.
4.60/5 (4 votes)
2 Jun 2011CPOL3 min read
Basic skills summary of manipulating 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=NavigateResponse.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


VB
'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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
New Zealand New Zealand
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
GeneralSpecific to ASP.NET? [modified] Pin
Gebbetje31-May-11 23:40
MemberGebbetje31-May-11 23:40 
Hi SummiG,

It is not completely clear to me how your article (except for step "5. Browse, Download and Delete Excel Files on Server.") is specific to ASP.NET. You basically show how to create and edit an Excel workbook and chart with Visual Studio (on the server side). But may be I'm overlooking something. (In that case it is wise to add this somewhere in your article, probably in section 1.)
Related: What could use some elaboration is, if you require Office to be installed on the server. (The beginning of the text under section "1. Environment Allocation" suggests it does, but later on some doubts are raised.)

Tip: when for the first time in your article you use an abbreviation, like PWS (Personal Web Server) and WPS (Works?), write it out.

Additionally:
The windows versions you used in your tests have reached the end of their lifecycle (for Windows 2000 this was the case on 13 July 2010).

modified on Thursday, June 2, 2011 9:20 AM

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.