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.

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.

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:

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.

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.

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:

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.

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.