OfficeWriter Receives Five Star Rating

Read Review on SQL Server Worldwide User Group (October 2006)

Building applications almost always has a component included that entails devouring and then presenting information for end-users to re-use. This comes almost always in the form of reports, but nearly as often, you'll find that there are huge benefits to be had by offering your information in readily re-usable formats. This is where presenting information for easy use in Excel and Word comes in, especially when presented as a point and click option with your application.

People are already using your information in these formats, it's just that, as programmers, we tend to make them go about it in "good enough" or work-around-type ways. The first of course - present the information on a web page, let them copy and paste into Word or Excel. From there, the user can rebuild the formulas (or add new ones of course) and tweak the presentation. The alternative is to add code to the top of your page that will return information with the right MIME-type - letting the browser interpret the page and read the information as a worksheet or document.

Response.ContentType = "application/vnd.ms-excel"

But doing so means you're responsible for the rest of the work to produce the sheet too, including a fairly difficult and obtuse series of things you'll need to do if you plan to have any kind of formatting, specific layout controls and such. In short, it's a tedious and hit-and-miss proposition.

Either way is not a best of class solution, but it's the only solution many of us have had to-date.

SoftArtisans is looking to change this. With OfficeWriter, you have the ability to integrate direct output that fully supports using Office applications and, better yet, gives you options for formatting, layout controls, formulas and other access to the abilities available in these tools. This review looks at what OfficeWriter is about, how it's used and whether it's a good tool for consideration in your own work as you build applications that need to step up to the plate and deliver better information use - beyond a report - for your users.

This is another huge benefit as well, and we'll get to that in the Installation section of this review.

SoftArtisans OfficeWriter is an application layer in that you use to produce native Excel and Word documents from within your application. The software libraries take care of creating the valid objects and work to go beyond just changing the MIME type, as you might have done with prior work. Instead, what you get is object-level control that let's you create an object, work with it to create the document or sheet you need and then present it to the user.

OfficeWriter lets you work with sheets in a much more familiar way - one that lets you quickly see what you're doing, where the information will go, etc. When compared with using tables as output with prior methods, OfficeWriter adds a new finesse to the process:
worksheet.Cells("A1").Value = "Last Name"
worksheet.Cells("B1").Value = "First Name"
worksheet.Cells("C1").Value = "Salary"
worksheet.Cells("A2").Value = "Smith"
worksheet.Cells("B2").Value = "John"
worksheet.Cells("C2").Value = 35000

Many times Visual Basic and ASP (and ASP.NET) have been called self-describing. I won't get into the pros and cons of that here, but clearly the code is more readable and maintainable by other than the original author in this type of layout.

The code is easy to use, understand and implement. I really appreciate the fact that it's also easy to maintain - because you can follow it logically through reading the methods being called.

Installation was easy - the standard setup process is followed with straightforward prompts. The one area that I found that could be confusing and could possibly use some assistance is the installation. I'll say up front that I'm not one to read the manual on setup processes. I'm much more the one to download the setup, run it and read through the prompts.

The suggestion I'd make relating to the installation is that the installation and setup process offer two branches. First, offer a setup for the server, then offer a setup for the developer system. I made several passes through based on where I was installing - and it was aggravating to get to the point of copying files only to be told that Reporting Services wasn't installed, so those components would not be copied.

Ideally, I'd add an option to the process. You're familiar with the "Standard" setup and "Advanced" options. I'd say "Standard Server Installation" and "Standard Developer Installation" - then offer to install only the supported configuration for the system at that time - sniff around a bit and see what's installed and would work, then offer those options as the default.

There is one very significant feature to OfficeWriter - when you're installing it on the server, you do not need to have Microsoft's Office applications on the server. This is great because the licensing savings can be significant. You won't have to have it installed to produce any of the types of documents you'll want from the application. Further, by adding OfficeWriter to a Reporting Services installation, you can (finally) have Word output for Reporting Services.

Other than these suggestions, OfficeWriter was easy to install and the setup process performed well.

I mentioned earlier that the code was easy to follow. Putting it in place will require understanding what you need to do, then determining what the best approach is to getting it done. First you'll be selecting your platform. OfficeWriter supports Classic ASP and ASP.NET. There are two ways to work with OfficeWriter - you either work with a template or you work with the Word/Excel objects and do the layout and formatting yourself.

For those who are design-challenged (like me), the templates give you the option to have someone create a good looking document or worksheet that you then use to plug in values. I'm afraid my reports tend to look more like listings and less like well-designed reports. By using the template, you can also use pre-existing reports as your starting point. Just take the file created by whatever means was previously used and plug in variables where you'll want to put in data from your database or application.

One of the interesting things that we did in testing this was to take a report that we had built in Excel (where things were being pulled manually from data sources) and then replaced the values shown with "Data Markers." Data Markers are OfficeWriter-speak for "put the information here" when the document is built. Data Markers let OfficeWriter know the location, column information and such for your document so you can reference that location for placement of information. If you've built Word merge documents, you've essentially worked with the concept of Data Markers before. It's just a matter of pointing out and naming where you want information to show up.

You call out these with Merge fields in Word, and in Excel you indicate the Markers with "%%=" and "%%=$" notations. Then, in your code, you reference these as follows:
myXLtemplate.DataSource("LastName") = "Smith"
myXLtemplate.DataSource("FirstName") = "John"

I thought the implementation of these abilities with the forms, the fields and the population of the fields was straightforward, easy to use and it made sense. There wasn't a lot of convoluted additional options and things you had to do - it just was a matter of saying "put this here."

Working between Word and Excel is very similar - once you understand the object model, you'll find that you can move between the different output options pretty easily. The big difference is the "identifier" bits - the characters that indicate the merge field. In Word, you'll see the standard Merge indicators as the "<>" type of information.

You can bind the different variables to a data source directly to your database as well and have them rendered when the document or sheet is created.

Hot Cells are an intriguing offering when working with information in a worksheet. Hot Cells let you link cells to the back-end database and make them update-able (assuming permissions allow) and let the user do these updates from within Excel. The implementation does require VBA, which may be an issue, especially when downloading a sheet from a site, only due to security and protections many systems have in place. That said, this isn't something you'd be posting to a public Internet site, but more likely an intranet site, where security can be tightly defined to allow the embedded code.

Hot Cells are interesting because they allow you give the user update ability. It's supported in a couple of different ways. The first is having the updates sent to the server as they happen (triggered by that VBA code in the workbook), and the second option is to have the workbook posted back to the server and then the server will save the changes to the database as needed. Both require code on the server (a form to post to and manage the changes) and code in the workbook to manage the data interaction. While these are not what I'd call simple to implement, they're not complex either, and simply require some thoughtful work to integrate into your application.

One of these uses we were able to see happening right off the bat is that of working against a reporting server - a server where the data had been summarized and posted (and updated frequently) with the specific intention of allowing users access to the information and the ability to manipulate it for reporting and analysis. In this type of environment, this could be a very strong offering and one that could be great for your users.

I don't think I'd suggest using this (Hot Cells) as your main tool for updating the system, however, and I don't believe that's the intent here at all. I'm sure someone, somewhere will find a way to build an application in Excel and push this approach to its limits, but it's just not the application environment appropriate for that. In our work with this, and in talking with a couple of different shops about this, the most interesting application of this was the ability to have OfficeWriter build a sheet of information for review, then allow "findings" to be posted back for others to see. This is a very cool capability, especially when integrated with the balance of OfficeWriter's automation abilities.

OfficeWriter also works as an add-on for Reporting Services. RS doesn't have native abilities to create Word documents - OfficeWriter adds this ability. OfficeWriter also greatly enhances the ability to create much more meaningful Excel sheets. One of the most stand-out features in this area is the ability to have live graphs in your worksheet, rather than a static image. If you've created a graph using Reporting Services, you've noticed the charts are "dead" - not linked. You can fix this with OfficeWriter and have it build real sheets with referenced columns, formulas and the like.

You can also use OfficeWriter to produce reports from within Excel or Word. You won't have to go to your Reporting Services portal to execute the reports and pull them into Word/Excel. Interestingly, the OfficeWriter Designer, the add-in for Word and Excel, is freely distributable. This means you can give your users some reporting capabilities from within Word/Excel and at the same time not require Visual Studio on those systems. Depending on the requirements and your user base, this can make the reporting tools much more accessible.

There is a lot of talk, and a lot of work being done, that relates to giving more power to users in terms of what data they have access to, how they pull reports, what data can be selected, how it can be used... this is particularly true when it comes to compliance-related reports. While giving access to reports, allowing some design capabilities and possibly even update abilities sounds great, be cautious and deliberate in what information you give out.

The best possible solution would be to give this type of access to your users against known and protected data sources. This isn't an issue with OfficeWriter, but rather an issue overall with the better and better reporting tools that are available. Whether people are using Analysis Services to analyze data and find new relationships or using OfficeWriter to provide new, live data ways to review that information, there can be significant considerations for compliance. We've talked in prior editorials on the site about the fact that one of the most overlooked compliance areas is worksheets. People rely on them for everything from crude databases to reports that let them present data in a specific way that is then used to drive the company. Be aware of these usage points and educate people about best practices.

I have to say, while reviewing OfficeWriter, I kept looking for the "yeah but... " points. Put simply, I really enjoyed this product. I've worked with other libraries to produce document types and reports. They're typically so convoluted you end up wanting to throw darts at the companies that built the products.

This simply isn't the case with OfficeWriter. There are a lot of things that weigh into a solid development tool, one that you can hang your hat on.

Documentation - check. The SoftArtisans site (http://officewriter.softartisans.com/) is simply one of the best resources I've seen in a very long time. It used to be that programmer documentation was comprehensive, lots of working examples and samples. Then companies got away from that for a bit and posted only a couple of lines of samples - you were left to figure out the rest. Not so here. You can go as abstract or as detailed as needed and it was a pleasure to use and review.

It works - check. I know it sounds odd, but how many tools have you purchased/used that simply didn't perform? Not an issue here.

Ongoing Support - check. While they do offer additional paid support contracts and per-incident help, they also offer the support forums on the site, where you can browse through issues other have posted about and see the solutions. In addition, there is a knowledge base with extensive and specific examples.

Here are the ratings and brief commentary for the product.

Installation (4.25 stars) My only suggestions would be as outlined above for the "Developer" install and the "Server Installation" or more graceful recovery if a component was not found - allow the installation to continue with those parts that were supportable.

Usage (5 stars) Very clear methods, nice approach, comprehensive toolset with access to the full capabilities of Word and Excel.

Support (5 stars) Outstanding support site. One of the best I've used in a very, very long time.

Real-World Usefulness (5 stars) If this is your environment - that of providing reports and data in Word and Excel - you should seriously consider and look at OfficeWriter. It's an outstanding tool that can take your reports and data presentation to the next level, while also giving your users the utility they expect from within Word and Excel.

Licensing There are production and development licenses, and there is a Standard Edition available, as well as an Enterprise Edition. More information is available here.