 Creating Excel and Word Reports for .NET Applications Using OfficeWriter
By Ty Anderson
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 attention to the events surrounding him. Instead he is thinking up theme music appropriate for what is occurring. 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. 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 loads of developers would rather not deal with them. As a result, it is fair to say that reports are too often 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 within Excel and Word…OfficeWriter by SoftArtisans (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 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 in your solutions. As a bonus, OfficeWriter also fills a hole in SQL Server Reporting Services by providing full-fidelity Excel and Word support. However, in this article, we’ll focus on using OfficeWriter as a stand-alone product.
From my perspective OfficeWriter offers two key benefits to any solution I build:
- The ability to quickly build meaningful reports in formats my users prefer: For example, I was able to build 4 reports for an internal application at my company. The total time to build the reports was less than 2 hours. The majority of that time was looking up syntax in the help file so with practice, the time to write a new report will decrease.
- 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 limited to 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.
The first benefit is that you can take any Excel file already in use within your organization and turn it into a report template. All that’s required is to insert content placeholder tokens anywhere you want to insert data during run-time. All of the placeholders are accessible by name in your code which allows you to avoid having to navigate through Excel’s rows & columns to find the cell you want. This approach 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 a method for manipulating an Excel file solely with code. This is accomplished 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 options selected by the user.
Build an Excel Report – ASP.NET Application
To demonstrate just how easily you can incorporate OfficeWriter into your applications, I will walk you through creating a simple web page that builds a report using the OfficeWriter Excel Application object. This example uses the Time Tracker ASP.NET Starter Kit. To use this example, you will need to download and install this starter kit here: http://www.asp.net/downloads/starterkits/default.aspx?tabid=62.
Mock-Up Excel File
Before creating the web page, you need to create an Excel file and format it. 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 do it within Excel, at least for a “base level” of formatting. You can always insert additional formatting to highlight key data elements etc. To set this file up properly, add the following:
- Add “Time Tracker” to cell A1
- Add “Project Time Summary” to cell A2
- Add “Project Name:” to cell A4. This cell is a label for the value we will insert into cell B4 later
- Add “Category Name”, “Est. Hours”, “Act. Hours”, & “Difference” to cells A6-D6. These cells will be the headers for the report data.
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. For this sample, you need the following functions:
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 GetProjectName function returns the listing of projects in the Time Tracker application. These values fill the web page’s DropDownList. The GetTimeData function 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
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
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 dtTime’s row and column counts allows you to dynamically determine the size the area requires at run-time. The method inserts the time entries into the Excel file by passing the dtTime data table 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.
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.
About the Author
Ty Anderson is a partner at Cogent Company, a Microsoft-based consultancy in Dallas that helps organizations use Microsoft technologies to grow their business. Ty is a regular contributor to Devx.com, wrote Office Programming 2003: Real World Applications (Apress), and loves to wear his yellow A's hat. |
