Manipulate Excel with ASP.NET

This post is from CodeProject. Recently, I was learning how to operate MS Office documents with ASP.NET. This post is very helpful for me so that I share it in my blog and hope that it will be helpful for others who are learning the same thing with me.

If you want to get the original post, please click here:

http://www.codeproject.com/Tips/200318/Manipulate-Excel-with-ASP-NET

Note: I get permissions to share this post from its author.

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 aRange

d) Draw Border on Cell. (Right, Left, Top and Bottom)

e) Set Cell Background Color

f) Merge Cell

g) Insert Row and Column

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(“.”)

        objExcelApp = CreateObject(“Excel.Application”)

        objExcelApp.DisplayAlerts = False

        objExcelApp.Application.Visible = False

        objExcelApp.WorkBooks.Open(strAddr & “\Templet\Null.xls”)

        objExcelBook = objExcelApp.ActiveWorkBook

        objExcelSheets = objExcelBook.Worksheets

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

        objExcelApp = Nothing

        ‘Method to Save table in Web

        ‘For example: ID=MYTABLE in Table

        sub btnExport_onclick()

        Dim objExcel

        On Error Resume Next

        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

Conclusion

This post shows a method to manipulate Excel with ASP.NET, including basic operation, generate DataTable and chart, browse, download and delete Excel on server.

Finally, I want to recommend another tip in CodeProject, which is about how to export data to Excel and other files with ASP.NET.

Please click here to read:

Export Database to Excel, PDF, HTML, RTF, XML, etc. for ASP.NET without Automation