Av rating:
Total votes: 10
Total comments: 14

 CODE DOWNLOAD
 send to a friend
 printer friendly version

OfficeWriter is a reporting tool from Softartisans, which allows you to execute and generate business reports in familiar Excel and Word formats. Here, Ty Anderson, an independent consultant, provides an overview of the tool's capabilities and provides the code you need to start generating Excel reports from your ASP.NET applications.

This weekend I went to the movie "Holiday" with my wife. I won't give anything away but in one of the pivotal scenes, the character portrayed by Jack Black (a musician) isn't paying full attention to what is going on around him. Instead, he is dreaming up theme music appropriate to these events. In my own way I do the same thing, except that I think about building software applications. Throughout my day I work with businesses of all shapes and sizes, and am confronted by all types of situations and problems. As I learn about the problem, I can typically begin to imagine an appropriate workflow, database, class structure, etc. that will solve it. Most of the time, I record these thoughts in my idea notebook for later implementation (either for the client or various pet projects).

As I think about an application, though, I always try to imagine the user reading a report generated from my application. However, reports just aren't sexy and most developers would rather not deal with them. As a result, it is fair to say that reports are too often treated as just an afterthought. Furthermore, reports often require bundling a report designer with your application (i.e. SQL Server Reports, Crystal Reports, etc.), and that designer requires user-training and takes time to learn and gain proficiency.

That said, I have recently discovered a reports tool that allows for quick report authoring in Excel and Word format, namely OfficeWriter by SoftArtisans (http://www.softartisans.com/). If you know your way around Excel or Word, and can write a line of code, then you already know 99% of what's required to use this simple yet powerful tool.

OfficeWriter overview

OfficeWriter is an enterprise capable application for writing and executing reports on the server. The key differentiator here is that OfficeWriter produces reports in the two most familiar Microsoft Office applications – Excel & Word – without requiring the installation of either on the server. OfficeWriter creates report files using the supported binary file types of Excel and Word without ever needing access to the Office application model. This is significant because Microsoft has never supported a server-based scenario for the Excel and Word client applications, making it difficult to automate Word and Excel document processing on the server. This doesn't mean it can't be done, but those of us who have tried it know that automating Microsoft Office applications on the server is not a scalable solution. This is exactly the hole OfficeWriter seeks to fill. As a bonus, OfficeWriter also adds full Excel and Word support to SQL Server Reporting Services. However, in this article, we'll focus on using OfficeWriter as a stand-alone product.

From my perspective OfficeWriter offers the following two key benefits to any solution I build:

• The ability to quickly build meaningful reports in the formats my users prefer: For example, in less than 2 hours, I was able to build four reports for an internal application at my company.. The majority of that time was spent looking up syntax in the help file. With practice, the time to write a new report decreases even further.
• The ability to scale report generation as my user base grows: Since OfficeWriter does not rely on Excel or Word to generate report files, it is not affected by the scalability problems associated with Microsoft Office applications. OfficeWriter was built to run on a server and is optimized to handle thousands of concurrent requests.

You can take any Excel file in use within your organization and turn it into a report template. All you need to do is insert content placeholder tokens anywhere that data is to be inserted at run-time. In your code, all of these placeholders are accessible by name which means you don't have to navigate through Excel's rows & columns to find the cell you want. This is known as the Template approach and it is dependant upon pre-existing Excel Templates that you must create and design prior to manipulating them.

OfficeWriter also provides for a means to manipulate an Excel file solely with code, using the OfficeWriter Excel Application object. Using the Application object, you can create Excel files on the fly and manipulate them however you wish – you can create Ranges, Areas, Worksheets, PivotTables, Charts, Formulas…everything you would expect. The Application object is very well suited for scenarios where the format and structure of the reports is dynamic and changes depending on the options selected by the user.

Build an Excel report from your ASP.NET application

To demonstrate just how easy it is to incorporate OfficeWriter into your applications, I will walk you through the creation of a simple ASP.NET web page that builds a report using the OfficeWriter Excel Application object. This example will generate an Excel report on time spent on each of the various stages of a software development project. It uses the Time Tracker ASP.NET Starter Kit, so you will need to download and install this starter kit from:

http://www.asp.net/downloads/starterkits/default.aspx?tabid=62.

Note: I also included a copy of the Time Tracker database with this article's source code. If you prefer, you can restore this database to your instance of SQL Server 2005 or SQL Express.

Mock-Up Excel file

Before creating the web page, you need to create an Excel file and format that looks similar to that shown in Figure 1. The idea here is to add enough formatting to be helpful to a user. Although we can do this sort of formatting using the OfficeWriter Excel Application, I think it is quicker to establish at least a "base level" of formatting within Excel. You can always later insert additional formatting to highlight key data elements etc. To set this file up properly, add the following:

• "Time Tracker" to cell A1
• "Project Time Summary" to cell A2
• "Project Name:" to cell A4. This cell is a label for the value we will insert into cell B4 later
• "Category Name", "Est. Hours", "Act. Hours", & "Difference" to cells A6-D6. These cells will be the headers for the report data.

Figure 1. The Excel file with formatting

Save this file and name it "ReportFile.xls".

Create report generation web page

Open Visual Studio 2005 and create a new web site project. Add the ReportFile.xls Excel file to the web site. Doing so makes it easier to reference as it will be within the web site's file structure. Open the Default.aspx file and add the following:

• SQLDataSource control – Set the ConnectionString property to point the Time Tracker database on your system. In my case the string is "Data Source=localhost;Initial Catalog=TimeTracker;Integrated Security=True". This control will provide a list of projects at run-time. Therefore, set the SelectQuery property to "SELECT ProjectId, ProjectName FROM aspnet_starterkits_Projects"
• Label Control – Set the Text property to "Time Tracker Extended Reports Portal". It's an Extended Reports Portal because I am providing more reports than the TimeTracker web application provides…just in case you wondered.
• DropDownList – Set the DataSourceID property to the SQLDataSource control created just a second ago (SQLDataSource1 if you didn't change its name). The DataTextField is "ProjectName" and the DataValueField is "ProjectID".
• Button – Set the Text property to "Build Report"

Write code to build a report

Now that the page exists, you can be up and running with less than 100 lines of code. Open default.aspx.vb and add the following declarations:

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports SoftArtisans.OfficeWriter.ExcelWriter

This is also a good time to add a reference to the OfficeWriter assembly. Open the Add Reference dialog box and navigate to C:\Program Files\SoftArtisans\OfficeWriter\bin. The file you want is named SAXW6NET.dll.

Helper functions

The web page requires two helper functions that query the database, stuff that data inside DataTable objects, and return the filled DataTables for further use by the calling function. The first of these is the GetProjectName function, which returns the listing of projects in the Time Tracker application. These values fill the web page's DropDownList.

Private Function GetProjectName() As DataTable
    Dim strSQL As String = "SELECT ProjectName
                            from aspnet_starterkits_Projects
                            where ProjectID = @ProjectID"
 
        Dim dt As DataTable = New DataTable
        Dim conn As SqlConnection = New SqlConnection(cnnString)
        Try
            Dim cmd As SqlCommand = New SqlCommand(strSQL, conn)
            cmd.Parameters.AddWithValue("@ProjectId", Me.Projects.Text)
            Dim adpt As New SqlDataAdapter(cmd)
            adpt.Fill(dt)
 
        Finally
            If Not conn Is Nothing Then
                conn.Dispose()
            End If
        End Try
 
        Return dt
    End Function

The second is the GetTimeData function. It is very similar except it returns a set of time entries recorded by all users for a selected ProjectID value.

Private Function GetTimeData() As DataTable
    '--- SQL Query for orders
    Dim strSQL As String =
      "SELECT category.CategoryName, category.CategoryEstimateDuration,
          SUM(timeEntry.TimeEntryDuration) AS CategoryActualDuration
       FROM aspnet_starterkits_ProjectCategories 
       AS category INNER JOIN aspnet_starterkits_Projects
       ON category.ProjectId = aspnet_starterkits_Projects.ProjectId
       LEFT OUTER JOIN aspnet_starterkits_TimeEntry
       AS timeEntry ON category.CategoryId = timeEntry.CategoryId
       WHERE category.ProjectId = @ProjectId)
       GROUP BY category.CategoryId, category.CategoryName,
                category.ParentCategoryId, category.ProjectId,
                category.CategoryAbbreviation,
                category.CategoryEstimateDuration,
                aspnet_starterkits_Projects.ProjectName
      ORDER BY category.CategoryId;"
 
    Dim dt As DataTable = New DataTable
 
    Dim conn As SqlConnection = New SqlConnection(cnnString)
    Try
      Dim cmd As SqlCommand = New SqlCommand(strSQL, conn)
      'cmd.CommandType = CommandType.StoredProcedure
      cmd.Parameters.AddWithValue("ProjectID", CInt(Me.Projects.Text))
      Dim adpt As New SqlDataAdapter(cmd)
      adpt.Fill(dt)
    Finally
      If Not conn Is Nothing Then
        conn.Dispose()
      End If
    End Try
 
    Return dt
  End Function

NOTE:
I certainly could have merged these into a single method and passed different SQL Statements, but I didn't. I was moving fast. I say this to point out I know good coding practices but that I don't always follow them when writing articles. That's just how it goes ;)..

The last code chunk contains the actual OfficeWriter code which is the whole point of this example. The Button1_Click event looks like this:

Protected Sub Button2_Click(ByVal sender As Object, ByVal e
                               As System.EventArgs) Handles   
  Button2.Click
  
  Dim strSQL As String = "Select ProjectName,
    ProjectDescription, ProjectEstimateDuration, ProjectCompletionDate
    FROM dbo.aspnet_starterkits_Projects;"
 
    Dim cnn As New SqlConnection("Data Source=localhost;
                                   Initial Catalog=TimeTracker;
                                   uid=sa; pwd=cog3nt;")
    Dim dtTime As DataTable = GetTimeData()
    Dim dtProject As DataTable = GetProjectName()
 
    '//Open the Report File
    Dim xla As ExcelApplication = New ExcelApplication
    Dim FilePath As String = Page.MapPath("ReportFile.xls")
    Dim wb As Workbook = xla.Open(FilePath)
 
    Dim ws As Worksheet = wb.Worksheets("Sheet1")
 
    '//Import & Insert the data from the database
 
    Dim targetArea As Area = ws.CreateArea(6, 0, dtTime.Rows.Count,
                                                dtTime.Columns.Count)
    Dim importedValues As Area = targetArea.ImportData(dtTime)
    ws.Cells("B4").Value = Projects.SelectedItem.ToString
 
 
    'Call ExcelTemplate.Save to generate a new spreadsheet
    xla.Save(wb, Page.Response, "NewReport1.xls", False)
  End Sub

After connecting to the database and creating all the required database objects, this method creates an OfficeWriter Excel Application object. Using this object, the method opens the ReportFile.xls file and builds a reference to the worksheet, containing the formatting you added earlier.

From here, inserting data is simple. The first step is to create an area in the worksheet for inserting time entries. The code will insert data starting in row 7, column 1. The code offsets by 6 rows and 0 columns to arrive at cell A7. Using row and column counts from the DataTable (dtTime) you can dynamically determine the size the area requires at run-time. The method inserts the time entries into the Excel file by passing the dtTime to targetArea.ImportData. The ImportData method loops through the data and builds out the spreadsheet. The last two lines insert the name of the selected project into cell B4 and then outputs the page as the HTTP response.

Figure 2. The completed report inside Excel.

Summary

OfficeWriter makes building useful reports for your application a simpler process that users will enjoy, because they won't be waiting an eternity for the non-scalable Office application to process documents on the server side. Since reports are rendered in the Excel and Word binary formats, you can skip the step of writing the report for HTML output and then providing an export to Excel link. Instead, just write the report and send it to the user in the format they know and love. Doing so not only eases your report writing burden, it also allows users to manipulate the data for their own analysis.