Click here to Skip to main content
Click here to Skip to main content

Manipulate Excel with ASP.NET

, 2 Jun 2011
Rate this:
Please Sign up or sign in to vote.
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

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

About the Author

SummiG

New Zealand New Zealand
No Biography provided

Comments and Discussions

 
GeneralSpecific to ASP.NET? [modified] PinmemberGebbetje31-May-11 23:40 

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

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

| Advertise | Privacy | Mobile
Web04 | 2.8.140721.1 | Last Updated 2 Jun 2011
Article Copyright 2011 by SummiG
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid