asp:review
Aspose.Excel
By Steve C. Orr
Your users want control of their data. You can spend weeks designing custom reports for them using such tools as Crystal Reports - and that's all well and good, but in the end they're never fully satisfied. It seems there is always another view of the data they'd like to see. One nice way out of this feature-creep scenario is to have your Web application export the data to Excel. After your users have their data in a powerful tool like Excel they can search it and sort it to their heart's content. They can make charts and graphs and add colors and images that awe their bosses. And once they've adopted the data in such a client-side way they can do anything they want without using your server or network resources.
The October 2003 issue of asp.netPRO magazine featured a variety of ways to export your ASP.NET data to Excel (for examples, see Export to Excel). You may have noticed that none of those custom techniques were perfect for every scenario. Tradeoffs come with every possible method. If one thing you are willing to trade is a little money, then you can end up with an elegant solution to all your Excel exporting needs in the form of Aspose.Excel.
Flexibility Abounds
One way to go about using Aspose.Excel is to first ignore it. Instead, open Excel and build an example of what you want your final spreadsheet to look like. You can set up columns, colors, charts, layouts, images, etc. in advance - but leave out the data. This standard Excel file will act as your template, or as the Aspose documentation names it, a "designer." Then at run time you can use the Aspose.Excel object model to merge the data with your designer to result in a snazzy looking spreadsheet full of juicy data. If you prefer, you can skip the designer and generate every aspect of the spreadsheet at run time. Or you can mix and match the techniques in virtually any way imaginable. Almost every available Excel feature can be specified at design time or run time. You can take advantage of Visual Basic for Applications (VBA) modules, Pivot Tables, Charting, Formulas, and other advanced Excel functionality.
You can output an entire DataTable with one line of code, and you can even reverse the process and generate a DataTable from cells in an Excel spreadsheet. For finer grained control you can put placeholders in your designer to automatically fill in specific cells scattered throughout a spreadsheet.
Outperform
Excel was written years ago in unmanaged code and was designed to be run as a single-user desktop application. Its threading model does not gracefully support use as a server component. On the other hand, Aspose.Excel is written entirely in C# and has no dependencies (other than the .NET Framework, of course). Excel is not required on your server, because Aspose.Excel itself acts as the spreadsheet creation engine. This delivers all kinds of scalability and frees you from the otherwise imposing Microsoft Excel licensing requirements. Your users will need Excel installed on their computers to view the spreadsheets, or they can use the Excel file viewer freely downloadable from Microsoft.
The first spreadsheet requested from Aspose.Excel is generated rather slowly, but this initially sluggish behavior is fairly standard for .NET apps. Once things get cached a bit it has a much snappier response; far faster than trying to use COM automation to deal directly with Excel.
Goodies for You
When you download the free evaluation version of Aspose.Excel and run the intuitive installation, one of the first things you're likely to notice is all the sample code. No matter what kind of spreadsheet you need to generate, you're likely to find similar code already written in their samples that you can modify for your own needs. It took a few awkward steps to get the sample code working, such as setting up the virtual directory in IIS and replacing the Access database that was used for the samples. But once I got up and going I was impressed with what I saw.
The API documentation is thorough and complete. Every object, method, and property is referenced in the help file with example code included in both C# and VB .NET. The local documentation is supplemented by online documentation, including a forum with common questions and answers. Aspose's e-mail technical support has demonstrated impressive responsiveness.
Simplicity Sells
Once you've referenced the Aspose.Excel object library from your new ASP.NET application in Visual Studio .NET, it only takes a few lines of code to output a simple spreadsheet. Import the Aspose.Excel namespace and type a few well placed lines of code, such as this VB .NET example:
Dim excel As Excel = New Excel()
Dim sheet As Worksheet = excel.Worksheets.Item(0)
sheet.Cells.ImportDataTable(MyDataTable, False, 1, 1)
excel.Save("MySpread.xls", SaveType.OpenInBrowser, _
FileFormatType.Default, Me.Response)
The first two lines simply instantiate your Aspose.Excel object and reference the first worksheet. After that the code gets a bit more interesting. The third line simply hands Aspose.Excel a DataTable to output to the spreadsheet in a tabular format. You can specify whether or not to include the column names, and you can specify the starting row and column.
The final line does the heavy lifting. You can specify a filename and save the spreadsheet to your server if you'd like, but this example bypasses the server hard drive and outputs the spreadsheet directly to the Response object. You can have the spreadsheet open within the user's browser or within an independent instance of Excel. You can target a specific version of Excel if you choose, or have it output in CSV format for ultimate compatibility.
Although the above code works flawlessly, the resultant output is ugly. To create a designer, simply open Excel and create a beautiful-looking spreadsheet. Save the spreadsheet (minus the data) and add this line to the above code sample to have the data merge into your preformatted designer:
excel.Open(strDesignerFileNameAndPath)
This tabular technique should work great in most cases - but there's nothing stopping you from writing data to any specific cell(s) in the spreadsheet individually with code like this:
sheet.Cells(1, 2).PutValue("Hello World")
If you're familiar with Excel's object model, code like this should seem somewhat familiar to you. This similarity to the Excel object model allows you to leverage your existing knowledge to get a head start. You'll also find plenty of other useful and familiar classes within Aspose.Excel's object model, such as the Range, Chart, Series, Line, Font, Hyperlink, Style, and Worksheet classes, to name just a few.
The Price Is Right
There are several different editions of Aspose.Excel available: Basic, Standard, Professional, and Corporate. Although comparable products can cost over a thousand dollars, the Aspose.Excel price ranges from under US$300 to around $600 (for a single-site deployment), depending on which edition you get and if you qualify for any of the discounts groups such as education or charity. The fancier editions provide richer support for advanced features such VBA, pivot tables, and add-ins.
Steve C. Orr is a Microsoft MVP in ASP.NET as well as an MCSD. He's been programming in the Seattle area for nearly 20 years. He's worked on numerous projects for Microsoft and currently works with such companies as Able Consulting and The Cadmus Group, Inc. Find him at http://Steve.Orr.net or e-mail him at [email protected].
Rating:
Web Site: http://www.aspose.com/Products/Aspose.Excel/
Price: Starting under US$300
Tell us what you think! Please send any comments about this article to [email protected]. Please include the article title and author.
