 Review: SoftArtisans OfficeWriter v3
by Jesudas
Chinnathampi (Das)
Published: 22-Aug-2005
Abstract:
Softartisans OfficeWriter for the .NET Framework provides developers
with the capability of dynamically creating Microsoft Excel
Spreadsheets and Microsoft Word documents. In this review,
I will talk about how the OfficeWriter may suite your application
requirements.
Article Contents:
Introduction
The winning formula of any software product rests on its independence
from other software. In respect to OfficeWriter, its only dependency
is the .NET Framework. In this review, we will look at the
latest product offered by SoftArtisans, OfficeWriter. For those
who do not know about SoftArtisans, you might want to check
its website at www.softartisans.com.
The File Upload Component released by SoftArtisans way back
in 1997 is still one of the best upload components available
today. Let us see whether SoftArtisans has lived up to everybody’s
expectation with the release of OfficeWriter.
Back to Top
What is OfficeWriter?
SoftArtisans
OfficeWriter, which combines ExcelWriter and WordWriter,
offers pure .NET reporting that generates real Excel and
Word reports from your ASP.NET application, without any dependency
on Microsoft Office. OfficeWriter works without dependency
on the Microsoft Excel and Word objects, which are not required
on the server. This avoids the performance penalties
and instability otherwise associated with instantiating multiple
copies of Excel and Word objects on a web server. OfficeWriter
safely generates thousands of Excel workbooks and Word documents
for an unlimited number of concurrent users each day.
ExcelWriter is not a visual end-user tool like Microsoft Excel.
It is not an ActiveX control that exists on the client. Rather,
it generates native Excel files via server-side code.
With OfficeWriter, creating Excel spreadsheets and Word documents
has never been so easy. With as little as 12 lines of code,
an Excel spreadsheet can be populated with records from a database
table (assuming that the template for the Excel spreadsheet
has been designed and stored on the server).
Back to Top
What do ExcelWriter and WordWriter
have to Offer?
In a nutshell, ExcelWriter supports all advanced Excel features
such as formulas, charts, pivot tables, VBA, macros, multiple
sheets, image insertion, named ranges, page layouts, and more.
With WordWriter you can create documents based on a template
or create a new document. Also, WordWriter preserves all native
Word formatting and styles, and advanced features from Word
97-2003 documents with expanding tables, bulleted lists, and
numbered lists. OfficeWriter is the only reporting software
that delivers fully-functional Excel and Word output, so your
users won't have to sacrifice their macros, pivot tables, and
dynamic charts every time they request new data.
Back to Top
What is a Template?
One of the common terms used in the OfficeWriter product is
the keyword, "template". An OfficeWriter template is an .xls
file that contains data markers or a .doc file that contains
merge fields. A data marker or merge field specifies a database
column, variable, or array to insert in the spreadsheet or
document containing the marker or field. OfficeWriter templates
are created in Microsoft Office and bound to data sources in
code. When you run the code, OfficeWriter populates the template
with values from one or more data sources. Look at the following
example.
Figure 1: An Excel Template
In the above example, three data columns have been marked
in columns A, B, and C, respectively. This will tell the ExcelWriter
to populate the Author Id value in Column A, the Author Firstname
in Column B, and the Author Last Name in column C.
Back to Top
Creating an Excel File Based on
a Template
Using the ExcelWriter, it just takes the following lines of
code, in VB.NET, to create an Excel file based on a template.
Dim oExcelTemplate As New ExcelTemplate
Dim myRdr As SqlDataReader
Dim ConnStr As String = ConfigurationSettings.AppSettings("ConnectionString")
Dim myConnection As SqlConnection = New SqlConnection(ConnStr)
Dim myCommand As SqlCommand = New SqlCommand("sp_authors_sel",
myConnection)
Dim myAdapter As New SqlDataAdapter(myCommand)
Dim AuthDt As New DataTable
myAdapter.Fill(AuthDt)
oExcelTemplate.Open("c:\inetpub\wwwroot\writer\Excel\auth_template.xls")
oExcelTemplate.SetDataSource(AuthDt, "authors")
oExcelTemplate.Process()
oExcelTemplate.Save("c:\inetpub\wwwroot\writer\Excel\auth_output.xls")
It took me around 15 minutes to write the above code. This
included creating the stored procedure, sp_authors_sel, which
simply dumps all rows from the author table under the pubs
database in SQL Server 2000, and creating the template file
shown in Figure 1.
All we are doing here is executing a stored procedure and
creating a new Excel file based on the template, auth_template.xls.
The file path in the above example can also be a virtual path
rather than a physical path. Instead of saving the new file
in the server, you could also stream the output as an HTTP
response to the client machine.
Back to Top
Creating a Word File Based on a
Template
A Word document can be created with the help of following
VB.NET code:
Dim NamesArr As String() = {"Name", "Company", "DateTime"}
' Form an array containg the values to be inserted
Dim ValuesArr As Object() = {recipName, recipCompany, System.DateTime.Now}
' Create an instance of WordTemplate
Dim wt As WordTemplate = New WordTemplate
' Open the template document
Dim templatePath As String = Page.MapPath("templates/BasicTemplate.doc")
wt.Open(templatePath)
' Set the main data source with the Name and Value arrays
wt.SetDataSource(ValuesArr, NamesArr)
' Populate the template to pull in the new values
wt.Process()
' Save the document by streaming it
wt.Save(Page.Response, "BasicWordTemplate.doc", False)
Back to Top
OfficeWriter Editions
OfficeWriter is available in two different editions, built
to produce real Excel spreadsheets and Word documents, for
both standard and enterprise reporting applications.
With OfficeWriter Standard Edition, the following tasks
can be achieved.
- Open and populate existing reports
- Update charts and formulas
- Security with macros
- Reporting Services designed and rendered
The Standard Edition always needs a template file to work
with. So, you cannot create an Excel file without having a
template file. If you want to create a new Excel file based
on the output of a database table, then you need to have a
template file with corresponding markers. OfficeWriter Standard
edition is for high-volume Office applications that do not
require runtime control of intricate Excel and Word features.
Enterprise Edition supports all the features of Standard
Edition, and the following:
- Create new reports programmatically
- Modify file formats and styles
- Read data from Excel files
- Construct charts and formulas
One of the richest features available in Enterprise Edition
is its HotCell Technology. HotCell Technology and OfficeWriter
Assistant provide a live connect back to a server-side data
source directly from an Excel spreadsheet or Word document.
It uses VBA code embedded in the spreadsheet or document on
the client to communicate with code running on a remote Web
server. In the case of OfficeWriter for Excel, the VBA code
detects when changes have been made to worksheet cells. The
value of each modified cell is then submitted to the server
to update the data source. HotCell Technology is supported
only in the Enterprise edition.
The above-mentioned features are only a few of the complete
features supported by the Standard and Enterprise Editions.
Report Design Made Simple
Reporting Services reports can now be designed by end users
directly in Microsoft Excel and Word, avoiding report design
tools, like Visual Studio .NET, that are unfamiliar to business
users. After creating your reports with the OfficeWriter Designer--a
freely-distributed client side toolbar--there is no need to
switch from Microsoft Office to a browser to generate reports.
The OfficeWriter Designer generates true Reporting Services
reports (RDL files) but, unlike Reporting Services’ Excel output,
all Excel features are preserved by OfficeWriter.
Without OfficeWriter, Reporting Services cannot deliver reports
in Microsoft Word format. Documents rendered by OfficeWriter
preserve all of the Word features contained in the user’s existing
Word template.
SQL Server Reporting Services integration is packaged with
OfficeWriter Enterprise Edition. OfficeWriter Standard Edition
includes limited Reporting Services integration functionality.
There are no per-user costs with OfficeWriter. It is licensed
per server CPU.
Developer Editions
OfficeWriterEE Developer offers the same functionality as
OfficeWriter Enterprise Edition, but is limited to only a single
report created at one time, which is typical of the development
environment. Other restrictions may apply. (Pricing is $299
per development server/workstation.)
OfficeWriterSE Developer offers the same functionality as
OfficeWriterSE, but is limited to only a single report created
at one time. Other restrictions may apply. (Pricing is $99
per development server/workstation.)
Evaluation Editions
OfficeWriterFREE edition provides similar functionality to
OfficeWriterSE, but is limited to 500 rows per worksheet (Excel)
and 1 row per data source (Word). Every workbook and document
contains a worksheet or document page with a SoftArtisans OfficeWriter
advertisement
OfficeWriter Enterprise Edition Evaluation is the expiring
evaluation of the full server edition of OfficeWriter Enterprise
Edition. Contact SoftArtisans to receive the full evaluation
of OfficeWriter Enterprise Edition.
Back to Top
Licensing, Pricing, and Discounts
Both the Enterprise Edition and the Standard Edition require
one license for each CPU on the servers where OfficeWriter
is deployed. As of writing the review, the pricing for the
Standard Edition was US $1495 per server CPU. Pricing figures
for OfficeWriter Enterprise Edition are not published. I encourage
evaluators and potential customers to contact SoftArtisans
Sales Department for a customized price quote based on their
server configuration and needs. If you tell the SoftArtisans
sales department that you are buying OfficeWriter after reading
this review, you will get an amazing 20% off the published
price. Based on the above price, if you buy the Standard Edition
OfficeWriter, you will save around $300. To get detailed pricing
information for the Enterprise Edition, please contact SoftArtisans
using any one of the following ways.
Back to Top
Conclusion
The feature that I liked most about the OfficeWriter is that
we do not need the Microsoft Office product to be installed
on the server. The very reason that we can create Excel spreadsheets
and Word documents on the fly without having Microsoft Office
components adds more importance to the OfficeWriter product.
Also, the licensing and pricing model is very competitive.
OfficeWriter is a good product for an Intranet application
that relies on Excel spreadsheets and Word documents. The HotCell
Technology is an unbeatable feature which has a great advantage
in pulling the latest data from the server and updating the
data back to the server without actually transferring any files
back and forth.
Back to Top | 
