 |
|
Now, you may be thinking just how much training is this going to require for my staff? If your users are familiar with how to use Microsoft Excel then additional training is not necessary. One of the major benefits of using ExcelWriter is that users can sort, manipulate and alter spreadsheets on their computer in the Microsoft Excel environment that they are already comfortable with. ExcelWriter Versus Microsoft Excel There are three main advantages to using ExcelWriter instead of Microsoft Excel as your server-side Web reporting application. First, ExcelWriter opens and creates spreadsheets on the server faster and more efficiently than Microsoft Excel. Using ExcelWriter with more than just a few users shows a significant performance gain over Microsoft Excel. Second, ExcelWriter is a scalable application that can handle creating spreadsheets for just a few users or for thousands of users, while Microsoft Excel only works well for a small number of users. The difference in scalability is easily noticed when multiple users want to view the same spreadsheet simultaneously. And not having Microsoft Excel on your server can spare valuable server resources, which are normally lost when multiple users access a spreadsheet at the same time. According to Microsoft, Office Applications such as Excel, were never intended for use server-side and by using Excel you are taking risks with the stability of your overall solution. See "Considerations for Server-Side Automation of Office (Q257757)" at: http://support.microsoft.com/default.aspx?scid=kb;en-us;Q257757. Finally, if you are going to run Microsoft Office on the server, all of the clients that access Excel spreadsheets from the server will require a Microsoft Office license. Again, according to Microsoft, it is impossible to run the Office Web Components (OWC) on a Web server that is accessible to users on the Internet or extranet because there is no way to guarantee that all users accessing the OWCs have valid licenses. See "Licensing the Office 2000 Web Components and Office Server Extensions" at: http://support.microsoft.com/default.aspx?scid=kb;en-us;q243006. On the other hand, using ExcelWriter as your server-side application does not require a Microsoft Excel license on the client. To view and modify spreadsheets you can use free programs like the free Microsoft Excel Viewer or Star Office. Operation and Features I found it very easy to create Web reports in the form of spreadsheets in ExcelWriter using just a few lines of code in ASP script. Developers that are familiar with Excel VBA can begin writing advanced spreadsheet reports immediately. You can generate the report data in ASP script or retrieve it from a data source. An interesting note is that the reports created with ExcelWriter are spreadsheets and not Web pages. You cannot add HTML tags or use the Response.WriteLine on the page. However, you can customize your spreadsheet to make it look professional by adding formulas, formatted text, column width and alignment, borders, color and many more options. With ExcelWriter, you can quickly make new files by inserting values into template spreadsheets. Any experienced Excel user can create the template spreadsheet using Data Markers to place database columns in the desired location for the report. Then a developer only has to add a few lines of code to the template. Utilizing templates can save time by using them to create multiple spreadsheets. Macros can be added to template spreadsheets using VBA script to perform functions in the Excel spreadsheet on the client. Full Excel Functionality ExcelWriter gives you the ability to use all of Microsoft Excel's major formulas and functions in your spreadsheet reports. This is where ExcelWriter flexes its muscles simplifying Web reports with the power of spreadsheet formulas. The amount of code required to write an impressive-looking report with ExcelWriter is minimal compared to ASP script alone. Just as with Excel you can create multiple worksheets and access their cells in your formulas. You can create Microsoft Excel spreadsheets from ASP script by setting the content type to application/vnd.ms-excel, but with this method, you cannot use any format properties or formulas. ExcelWriter allows you to modify the visual aspect of your report. Formatting can be added to a cell by setting the font, hidden, horizontal alignment, locked, number, orientation, vertical alignment, and text wrap properties. You can create styles that contain color, font and other format options for use in multiple cells or ranges. Pivot Tables are an uncommon feature in Web reporting software packages, but always a welcomed feature. Using Pivot Tables, ExcelWriter allows you to analyze related totals, especially when you have a long list of figures to sum and you want to compare several facts about each figure. The interactive summary table allows you to change your view of the data. This is an excellent feature because the spreadsheet performs the calculations for you automatically. ExcelWriter offers good printing flexibility, allowing you to set the page layout properties for header, footer, alignment, margins, paper size, orientation, print area and print gridlines. Any Page layout settings you make in an existing spreadsheet or a template spreadsheet are kept when you open it in ExcelWriter. Or you could use ExcelWriter´s PageSetup object to set layout properties in ASP script. Having a page layout already set up for users, who will need a hard copy of the report, can save time for both you and them. Database Reporting Database reporting has never been so simple - 20 lines of code are all you need to place a table or query in a spreadsheet. By changing the connection string and the SQL query you can reuse the included sample for any table or query. ExcelWriter also allows for formatting and the use of formulas that can make your reports look impressive. You can use many different types of data sources including databases, spreadsheets and comma separated text files. ExcelWriter can import into a parsed XML file into a spreadsheet as a data source. Version 4 New Features ExcelWriter Version 4 includes SoftArtisans exclusive HotCell Technology, which provides users the ability to revise a data source on the server directly from the spreadsheet report on the end user's PC. One of the code samples provided allows you to access an updateable spreadsheet from a database. The sample uses a page with ASP script to load a database query into a template spreadsheet and open it in the client's Web browser. Then, when a cell is changed, VBA code in the spreadsheet on the client detects this and sends it to a destination page with ASP script that, in turn, updates the database. The HotCell sample is a valuable tool that can be easily reused to make updateable reports based on database tables or queries. Here is how to reuse the sample. First, change the database table and connection information in the ASP script of the page that loads the template spreadsheet. Next, change the URL of the destination page, table name and primary key in the template spreadsheet's initialize module and add a column for each column in your database table or query that you want to display. The primary key has to be one of the columns that you use for updates to work. Finally, add all of the column names to the array in the destination page's ASP script. Multilingual environments can easily be supported using ExcelWriter methods to translate characters from Ansi to Unicode and Unicode to Ansi between server and client. This is a perfect fit for reporting environments having a server using one language and some of the clients using a different language. ExcelWriter would be a top choice for a global enterprise with clients using multiple languages. .NET Support ExcelWriter V4 has added on to the original .NET support with two new methods. The first method is the ability to place an ADO.NET DataSet containing a single table into a spreadsheet. The second method allows you to bind a data source to a template spreadsheet that uses data markers to place the database columns you wish into the spreadsheet. This is an improvement over the previous version of ExcelWriter, which required that you move the ADO.NET DataSet into an array and then iterate through the data to place it in the spreadsheet. The PostOne Financial Corporation is a fictitious company created by SoftArtisans to demonstrate the functionality of SoftArtisans ExcelWriter. The PostOne demonstrations include four complex spreadsheet applications created with SoftArtisans ExcelWriter: checking account statement, mortgage calculator, investment portfolio, and monthly sales report. These demonstrations give you a real look at what you can accomplish with ExcelWriter. For example, the checking account statement demonstrates the ability of templates to load different data into the same spreadsheet by having you pick a month for your report that contains a chart and a pivot table. CodeWizard The developer editions of ExcelWriter include an automatic code generator named CodeWizard that creates scripts from existing spreadsheets. The CodeWizard takes a Microsoft Excel spreadsheet and generates ASP, ASP.NET, or Visual Basic code to create an identical spreadsheet. System Requirements Server Requirements: Windows NT4/2000/XP, a COM client including Active Server Pages or Visual Basic, MDAC 2.0 or later. Microsoft Excel is not required on the server.
Client Requirements Spreadsheet:
Browser:
Installation You have your choice of downloading the software instantly or you can choose one of the various shipping options to have a CD and printed documentation delivered. The software automatically installs and creates a virtual directory called ExcelWriter that contains the documentation and samples. Downloading the ExcelWriter software was simple and trouble-free and the automatic installation process performed well. Shortcuts are installed for both the help file and the online documentation with code samples. I was very pleased to see the amount of information included in both the online documentation and the help file. Each of ExcelWriter's features is thoroughly explained and includes samples of how they can be used. These samples can be run in the online documentation. An ExcelWriter programmer's reference includes detailed information on the object model and functions. Even though SoftArtisans provides extensive troubleshooting information, it proved unnecessary while using the application. The documentation shows how to configure Internet Information Server (IIS) so that a spreadsheet can be streamed directly to the client without an Open or Save dialog box being displayed. This allows the spreadsheet to open in the browser window. ExcelWriter's documentation discusses which features of the application need security permissions to function correctly and how the different levels of ASP security affect these functions. Top New Features in Version 4
Summary ExcelWriter is a powerful Web reporting solution that allows you to distribute information over the Internet in Microsoft Excel format that end users are familiar with. Using ExcelWriter allows you to quickly deliver reports that are visually appealing and contain powerful Microsoft Excel formulas and charts. ExcelWriter was designed as a scalable Web application that can handle a large volume of users that can modify and save reports via a Web browser. ExcelWriter gives you the ability to generate Web-based reports in a multiple language environment. ExcelWriter not only saves you time by speeding up delivery of reports, but saves you money by circumventing the need for training all users. Some of ExcelWriter's main features are that it requires a small amount of code to open a spreadsheet in the client's browser, it supports ADO.NET, allows you to make templates for standardization of reports and allows for modification, but still retains all Microsoft Excel Page Setup settings. The addition of the HotCell Technology in version 4 allows you to use the same familiar Microsoft Excel Interface to update a data source in the server using a spreadsheet in the user's Web browser. The developer editions of ExcelWriter include the ExcelWriter CodeWizard, an automatic code generator that creates scripts from existing spreadsheets. The CodeWizard takes a Microsoft Excel spreadsheet and generates ASP, ASP.NET, or Visual Basic code to create an identical spreadsheet. ExcelWriter 4 is a well-rounded and reliable program that delivers exactly what it promises. I was impressed with the documentation and the help file that were full of useful information. Because of the simplicity of the setup program, the troubleshooting section of the help file was not even needed. I can honestly say that I have not seen anything on the market that even comes close to doing what ExcelWriter 4 does in such a fast and easy manner. To see if ExcelWriter 4 will work as a Web reporting solution for you, download a free demo from the SoftArtisans Web site (http://www.softartisans.com/). Once you start using it, I believe you will want to make it a permanent part of your Web application development kit. About the Author Andrew Mooney is an ASPAlliance columnist and a software developer. He specializes in Microsoft database technologies, especially SQL Server 2000 and Access 2000. He holds all of the Brainbench BCIP certifications, including Web Developer Database, Web Developer Client-Side, and Web Developer Server-Side. You can visit his ASPAlliance column at: http://www.aspalliance.com/andrewmooney.
| |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
SoftArtisans ExcelWriter
