Creating Excel Spreadsheets with Office Web Components (OWC) By Scott Mitchell

转载 2012年02月24日 00:05:43

Introduction
One of the great things about running an ASP Web site where visitors regularly contribute articles is that, in reading/editing those article submissions, I end up learning a lot of new things! Last week Bret Hern submitted a beautiful article on using Charting with Office Web Components. After poking around Microsoft's site some, I found rather terse, technical documentation on these nifty components and soon discovered that these components can also be used to create Excel spreadsheets via ASP code! These spreadsheets can then be saved as an Excel file for the user to download.

In this article we will look at using the Office Web Components (OWC) to create an Excel spreadsheet via ASP code based on the results from a database query! All of this complexity is encapsulated in a (rather basic) class. In the upcoming weeks I plan to expound on this class. Currently it just dumps the contents of a Recordset into a spreadsheet, but in future weeks I'd like to show how to add nifty formatting, apply formulas, pivot tables, and all that other jazzy stuff.

Licensing Issues
Microsoft has some pretty strict licensing issues on using Office Web Components in the Internet-world (as well as on an intranet). Before you begin using Office Web Components on your Web site be sure to read Microsoft's Licensing Agreement for OWCs.

Getting Started
To get started using Office Web Components you must have (at minimum) the Office Web Components section of Office 2000 installed on the Web server. (If you are wanting to create Excel spreadsheets and graphs without requiring Excel's presence on the Web server be sure to check out SoftArtisan's ExcelWriter component.) (For more on OWC requirements and installation information check out: Requirements for Office Web Components!)

Creating a Spreadsheet
In this article we will look only at the basics of creating a spreadsheet, setting various cell values, and saving the spreadsheet to disk. In future articles we will look at prettying up the display and working with some of the more advanced features...

Since the spreadsheet aspect of the Office Web Components is a simple COM object, you can create an instance of the spreadsheet component through your ASP page just as you would create an instance of any other COM component:

'Create an instance of the Spreadsheet component from OWC
Dim objSpreadsheet
Set objSpreadsheet = Server.CreateObject("OWC.Spreadsheet")

Simple enough. Once you have a Spreadsheet object to work with you can set the values of the spreadsheet's cells using the Cells property of the Spreadsheet object like so:

objSpreadsheet.Cells(RowColumn).Value = SomeValue

Finally, to save the Spreadsheet as an Excel file you must use the Export method of the Worksheet object. (The ActiveSheet property of theSpreadsheet object returns a valid Worksheet object instance.) The Export method expects two parameters: a full physical file name and anSheetExportActionEnum constant. The file name parameter specifies the specific location to save the Excel spreadsheet; the export action indicates if the file should be saved to disk or piped directly to Excel. Since we are running all of this code on the server-side, if we try to pipe the spreadsheet contents directly to Excel, we will be trying to open Excel on the Web server - not what we want to do. In fact, this setting is only useful if you are using the Spreadsheet object as an ActiveX control, since then it will be executing on the client's machine as opposed to on the Web server. Therefore, when using the Export method in server-side script, always specify a value of 0 for the export action, which indicates to the Export method to simply save the spreadsheet to disk and to not try to pipe the contents straight to Excel.

objSpreadsheet.ActiveSheet.Export("C:\Inetpub\wwwroot\FooBar.xls", 0)

Keep in mind that the IUSR_machinename account must have Write permissions on the directory that you wish to write the Excel file to. If the IUSR_machinename account has inadequate permissions you will receive an error when trying to use the Export method... (Check out this FAQ for more information...)

Now that we've covered the basics of creating / filling / saving an Excel spreadsheet through ASP, we're ready to look at a nifty Excel spreadsheet generation class I wrote that will help this process. In Part 2 we'll examine this class in detail! 


In Part 1 we looked at some very basic code for creating, populating, and saving an Excel spreadsheet all from an ASP page! In this part we'll look at the implementation of a class that will allow you to dump the results of a Recordset object to a spreadsheet!  

Creating the Class
I chose to encapsulate the complexity of creating/populating/saving a spreadsheet into a VBScript class. That means that you will need VBScript version 5.0 or higher installed on your Web server. To find out what version of VBScript you are currently using, check out:Determining the Server-Side Scripting Language and Version. Also, for more information on the ins and outs of classes be sure to read Mark Lidstone's excellent article: Using Classes within VBScript.

Our class contains three private properties: objSpreadsheetiColOffset, and iRowOffset. In the Class_Initialize() event handler, an instance of the Spreadsheet COM component is instantiated and referenced by objSpreadsheetiColOffset and iRowOffset, which specify the how many columns over and rows down we should start inserting the database results, are initialized to values of 2.

Class ExcelGen

  Private objSpreadsheet
  Private iColOffset
  Private iRowOffset

  Sub Class_Initialize()
    Set objSpreadsheet = Server.CreateObject("OWC.Spreadsheet")

    iRowOffset = 2
    iColOffset = 2
  End Sub

  Sub Class_Terminate()
    Set objSpreadsheet = Nothing   'Clean up
  End Sub

  ...
End Class

Next, two Property Let constructs are defined to allow users of this class to programmatically set the row and column offsets. TheseProperty Let statements ensure that the offsets attempted to be set are greater than zero.

Class ExcelGen
    ...

    Public Property Let ColumnOffset(iColOff)
      If iColOff > 0 then
        iColOffset = iColOff
      Else
        iColOffset = 2
      End If
    End Property

    Public Property Let RowOffset(iRowOff)
      If iRowOff > 0 then
        iRowOffset = iRowOff
      Else
        iRowOffset = 2
      End If
    End Property

    ...
End Class

Our ExcelGen class contains only two methods: one to insert the contents of a Recordset into the spreadsheet and another to save the spreadsheet to an Excel file on the Web server's filesystem. We'll examine both of these methods, as well as how to use this class through an ASP page, in Part 3.

In Part 2 we looked at the private member variables of our class as well as our Class_Initialize() and Class_Terminate() event handlers and our Property Let statements. In this final part we'll examine the two methods of the ExcelGen class and look at how to use this class through an ASP page!  

Creating the Methods for the ExcelGen Class
Only two methods are needed for our class. The first one, GenerateWorksheet, accepts a single parameter: a populated Recordset object. This method then loops through the Recordset, transferring its contents to objSpreadsheet's Cells. Note that both the data from the Recordset and the names of the columns in the Recordset are outputted to the Excel spreadsheet.

Class ExcelGen
    ...

    Sub GenerateWorksheet(objRS)
      'Populates the Excel worksheet based on a Recordset's
      'contents.  Check to make sure we have data to show
      If objRS.EOF then Exit Sub

      Dim objField, iCol, iRow
      
      'Set the iCol/iRow vars to the proper offsets
      iCol = iColOffset
      iRow = iRowOffset

      'Display the names of the columns in the Recordset
      For Each objField in objRS.Fields
        objSpreadsheet.Cells(iRow, iCol).Value = objField.Name
        iCol = iCol + 1
      Next 'objField

      'Display all of the data
      Do While Not objRS.EOF
        iRow = iRow + 1
        iCol = iColOffset

        For Each objField in objRS.Fields
          'If the column contains a null value, insert blank string
          If IsNull(objField.Value) then
            objSpreadsheet.Cells(iRow, iCol).Value = ""
          Else
            objSpreadsheet.Cells(iRow, iCol).Value = objField.Value
          End If

          iCol = iCol + 1
        Next 'objField

        objRS.MoveNext     
      Loop
    End Sub    

    ...
End Class

Our last method, SaveWorksheet, accepts a single parameter, strFileName, which specifies the location to save the spreadsheet. This method returns a Boolean value: True if the file is saved successfully, False otherwise. Recall that exporting the Spreadsheet object to a physical Excel file can fail if the IUSR_machinename account has inadequate permissions.

Class ExcelGen
  ...

  Function SaveWorksheet(strFileName)
    'Save the worksheet to a specified filename
    On Error Resume Next
    Call objSpreadsheet.ActiveSheet.Export(strFileName, 0)

    'Return True if everthing went OK, False otherwise
    SaveWorksheet = (Err.Number = 0)
  End Function
End Class

Using the ExcelGen Class from an ASP Page
Now that we've looked at the contents of our class, let's examine how to use it through an ASP page to create a spreadsheet containing the contents of a Recordset! It is highly recommended that you place the ExcelGen class in an include file and then use a server-side include on those ASP pages that need to utilize the class's functionality. (To learn more about server-side includes be sure to read: The Low-Down on#include.) For this example we'll assume that the ExcelGen class has been placed in the file /scripts/ExcelGen.class.asp.

To use this class, then, we'll use a server-side include to import the contents of /scripts/ExcelGen.class.asp. Next, we'll create an instance of the class using the New keyword. Once we've created and populated a Recordset, we can call the .SaveWorksheet method to dump the Recordset's contents into an Excel spreadsheet. Finally, we need to save the contents of the spreadsheet using the .SaveWorksheet method.

<%
  Dim objRS
  Set objRS = Server.CreateObject("ADODB.Recordset")
  objRS.Open "SELECT * FROM titles", "DSN=FooBar"

  Dim objExcel
  Set objExcel = New ExcelGen

  objExcel.RowOffset = 4
  objExcel.ColumnOffset = 1

  objExcel.GenerateWorksheet(objRS)
  If objExcel.SaveWorksheet(Server.MapPath("foo.xls")) then
    Response.Write "Worksheet saved.  " & _
                   "Download"
  Else
    Response.Write "Error in saving worksheet!"
  End If

  Set objExcel = Nothing

  objRS.Close
  Set objRS = Nothing
%>

If the spreadsheet is saved successfully the user is presented with a hyperlink to download the Excel file.

Conclusion / Caveats
One annoying thing with the ExcelGen class is that a user must go through a two-phase step to view the contents of a Recordset through an Excel file. First, he must visit an ASP page that creates the Recordset; next, he must click on the link to the Excel file. This is a pain and something I plan on fixing in the next article on this topic (which will serve, basically, as an enhancement to the ExcelGen class).

One major concern that should also be quickly apparent is that in the above example the Excel spreadsheet is always saved to the same file. Urg. This is bad since multiple users will be trying to access the same file and, most likely, they would be running different kinds of database queries producing varrying output. One approach is to create a unique file for every user visiting the page... but then how to we clean up old spreadsheets? We'll examine this topic in more detail in a future article...

In the mean time, play with the code here, create your own spreadsheets, and poke around the Microsoft documentation. Happy Programming!

By Scott Mitchell

Attachments:

Download the ExcelGen class (in text format) 
Visit the technical docs
Read Enhancing the ExcelGen Class (for Creating Excel Spreadsheets) 
 


&lt;%  Option Explicit  Class ExcelGen    Private objSpreadsheet    Private iColOffset    Private iRowOffset    Sub Class_Initialize()      Set objSpreadsheet = Server.CreateObject(&quot;OWC.Spreadsheet&quot;)      iRowOffset = 2      iColOffset = 2    End Sub    Sub Class_Terminate()      Set objSpreadsheet = Nothing   'Clean up    End Sub    Public Property Let ColumnOffset(iColOff)      If iColOff &gt; 0 then        iColOffset = iColOff      Else        iColOffset = 2      End If    End Property    Public Property Let RowOffset(iRowOff)      If iRowOff &gt; 0 then        iRowOffset = iRowOff      Else        iRowOffset = 2      End If    End Property    Sub GenerateWorksheet(objRS)      'Populates the Excel worksheet based on a Recordset's contents      'Start by displaying the titles      If objRS.EOF then Exit Sub      Dim objField, iCol, iRow      iCol = iColOffset      iRow = iRowOffset      For Each objField in objRS.Fields        objSpreadsheet.Cells(iRow, iCol).Value = objField.Name        iCol = iCol + 1      Next 'objField      'Display all of the data      Do While Not objRS.EOF        iRow = iRow + 1        iCol = iColOffset        For Each objField in objRS.Fields          If IsNull(objField.Value) then            objSpreadsheet.Cells(iRow, iCol).Value = &quot;&quot;          Else            objSpreadsheet.Cells(iRow, iCol).Value = objField.Value          End If          iCol = iCol + 1        Next 'objField        objRS.MoveNext           Loop    End Sub        Function SaveWorksheet(strFileName)      'Save the worksheet to a specified filename      On Error Resume Next      Call objSpreadsheet.ActiveSheet.Export(strFileName, 0)      SaveWorksheet = (Err.Number = 0)    End Function  End Class%&gt;&lt;%  Dim objRS  Set objRS = Server.CreateObject(&quot;ADODB.Recordset&quot;)  objRS.Open &quot;SELECT * FROM titles&quot;, &quot;DSN=FooBar&quot;  Dim objExcel  Set objExcel = New ExcelGen  objExcel.RowOffset = 4  objExcel.ColumnOffset = 1  objExcel.GenerateWorksheet(objRS)  If objExcel.SaveWorksheet(Server.MapPath(&quot;foo.xls&quot;)) then    Response.Write &quot;Worksheet saved.  &lt;a href=&quot;&quot;foo.xls&quot;&quot;&gt;Download&lt;/a&gt;&quot;  Else    Response.Write &quot;Error in saving worksheet!&quot;  End If  Set objExcel = Nothing  objRS.Close  Set objRS = Nothing%&gt;

OWC11/Office Web Components11

  • 2011年06月14日 17:50
  • 6.68MB
  • 下载

Learning C++ by Creating Games With UE4(15.05.04)-2(目录)

下面是我所要翻译>这本书的目录,因为本人有一定的C++基础,所以我翻译的顺序会从第1章和第8章同时开始,这样一方面方便有基础的人快速学习UE4中如何使用C++构建我们整个功能,另一方面没有基础的朋友可...

OWC11/Office Web Components11

  • 2011年06月14日 17:51
  • 3.88MB
  • 下载

Learning C++ by Creating Games With UE4(15.05.08)-3(Chapter 1)

Chapter 1.Coding with C++   如果你是第一次学习编程,那么你需要学习的内容有很多了!   学术学习中经常会描述和介绍编程的概念和理论,向来会留下一些实践拓展给学习者,...

微软OWC(web office com)使用方法

  • 2008年11月24日 10:09
  • 99KB
  • 下载

Learning C++ by Creating Games With UE4(15.05.21)(Chapter 11-3)Monster

Chapter11-3   下面我们需要使用蓝图来制作怪物攻击的效果(蓝图这部分基础讲解我不再涉及)   首先我们需要找到控制这个怪物的动画,我这边是这个,为了防止我们破坏原有的动画系统,我们...

Learning C++ by Creating Games With UE4(15.05.18)-1(Chapter 9-1)Coding

由于最近腾讯的比赛进入了复赛,所以本来计划从开头翻译就先搁置下来,我们需要迅速把整个虚幻4的C++大概的使用方式掌握,这让比较方便我们整个作品的完整性。因此接着第八章我们开始学习第九章   Cha...

Caused by: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 's

问题:整合Hibernate和Spring,测试过程中报错:Caused by: org.springframework.beans.factory.BeanCreationException: Er...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:Creating Excel Spreadsheets with Office Web Components (OWC) By Scott Mitchell
举报原因:
原因补充:

(最多只允许输入30个字)