Aspose.Excel

asp:review

Aspose.Excel

By Steve C.Orr

Yourusers want control of their data. You can spend weeks designing custom reportsfor 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 anotherview of the data they'd like to see. One nice way out of this feature-creepscenario is to have your Web application export the data to Excel. After yourusers have their data in a powerful tool like Excel they can search it and sortit to their heart's content. They can make charts and graphs and add colors andimages that awe their bosses. And once they've adopted the data in such aclient-side way they can do anything they want without using your server ornetwork resources.

TheOctober 2003 issue of asp.netPRO magazine featured a variety of ways toexport your ASP.NET data to Excel (for examples, see Exportto Excel). You may have noticed that none of those custom techniques wereperfect for every scenario. Tradeoffs come with every possible method. If onething you are willing to trade is a little money, then you can end up with anelegant solution to all your Excel exporting needs in the form of Aspose.Excel.

FlexibilityAbounds

One wayto go about using Aspose.Excel is to first ignore it. Instead, open Excel andbuild an example of what you want your final spreadsheet to look like. You canset up columns, colors, charts, layouts, images, etc. in advance - but leaveout the data. This standard Excel file will act as your template, or as theAspose documentation names it, a "designer." Then at run time you can use theAspose.Excel object model to merge the data with your designer to result in asnazzy looking spreadsheet full of juicy data. If you prefer, you can skip thedesigner and generate every aspect of the spreadsheet at run time. Or you canmix and match the techniques in virtually any way imaginable. Almost everyavailable Excel feature can be specified at design time or run time. You cantake advantage of Visual Basic for Applications (VBA) modules, Pivot Tables,Charting, Formulas, and other advanced Excel functionality.

You canoutput an entire DataTable with one line of code, and you can even reverse theprocess and generate a DataTable from cells in an Excel spreadsheet. For finergrained control you can put placeholders in your designer to automatically fillin specific cells scattered throughout a spreadsheet.

Outperform

Excelwas written years ago in unmanaged code and was designed to be run as asingle-user desktop application. Its threading model does not gracefullysupport use as a server component. On the other hand, Aspose.Excel is writtenentirely in C# and has no dependencies (other than the .NET Framework, ofcourse). Excel is not required on your server, because Aspose.Excel itself actsas the spreadsheet creation engine. This delivers all kinds of scalability andfrees you from the otherwise imposing Microsoft Excel licensing requirements.Your users will need Excel installed on their computers to view thespreadsheets, or they can use the Excel file viewer freely downloadable fromMicrosoft.

Thefirst spreadsheet requested from Aspose.Excel is generated rather slowly, butthis initially sluggish behavior is fairly standard for .NET apps. Once thingsget cached a bit it has a much snappier response; far faster than trying to useCOM automation to deal directly with Excel.

Goodies forYou

When youdownload the free evaluation version of Aspose.Excel and run the intuitiveinstallation, one of the first things you're likely to notice is all the samplecode. No matter what kind of spreadsheet you need to generate, you're likely tofind similar code already written in their samples that you can modify for yourown needs. It took a few awkward steps to get the sample code working, such assetting up the virtual directory in IIS and replacing the Access database thatwas used for the samples. But once I got up and going I was impressed with whatI saw.

The APIdocumentation is thorough and complete. Every object, method, and property isreferenced in the help file with example code included in both C# and VB .NET.The local documentation is supplemented by online documentation, including aforum with common questions and answers. Aspose's e-mail technical support hasdemonstrated impressive responsiveness.

SimplicitySells

Onceyou've referenced the Aspose.Excel object library from your new ASP.NETapplication in Visual Studio .NET, it only takes a few lines of code to outputa simple spreadsheet. Import the Aspose.Excel namespace and type a few wellplaced lines of code, such as this VB .NET example:

Dim excel AsExcel = New Excel()

Dim sheet AsWorksheet = excel.Worksheets.Item(0)

sheet.Cells.ImportDataTable(MyDataTable,False, 1, 1)

excel.Save("MySpread.xls",SaveType.OpenInBrowser, _

FileFormatType.Default,Me.Response)

Thefirst two lines simply instantiate your Aspose.Excel object and reference thefirst worksheet. After that the code gets a bit more interesting. The thirdline simply hands Aspose.Excel a DataTable to output to the spreadsheet in atabular format. You can specify whether or not to include the column names, andyou can specify the starting row and column.

Thefinal line does the heavy lifting. You can specify a filename and save thespreadsheet to your server if you'd like, but this example bypasses the serverhard drive and outputs the spreadsheet directly to the Response object. You canhave the spreadsheet open within the user's browser or within an independentinstance of Excel. You can target a specific version of Excel if you choose, orhave it output in CSV format for ultimate compatibility.

Althoughthe above code works flawlessly, the resultant output is ugly. To create adesigner, simply open Excel and create a beautiful-looking spreadsheet. Savethe spreadsheet (minus the data) and add this line to the above code sample tohave the data merge into your preformatted designer:

excel.Open(strDesignerFileNameAndPath)

Thistabular technique should work great in most cases - but there's nothingstopping you from writing data to any specific cell(s) in the spreadsheetindividually with code like this:

sheet.Cells(1,2).PutValue("Hello World")

If you'refamiliar with Excel's object model, code like this should seem somewhatfamiliar to you. This similarity to the Excel object model allows you toleverage your existing knowledge to get a head start. You'll also find plentyof other useful and familiar classes within Aspose.Excel's object model, suchas the Range, Chart, Series, Line, Font, Hyperlink, Style, and Worksheetclasses, to name just a few.

The PriceIs Right

Thereare 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 asingle-site deployment), depending on which edition you get and if you qualifyfor any of the discounts groups such as education or charity. The fanciereditions provide richer support for advanced features such VBA, pivot tables,and add-ins.

Steve C.Orr is a MicrosoftMVP in ASP.NET as well as an MCSD. He's been programming in the Seattle area fornearly 20 years. He's worked on numerous projects for Microsoft and currentlyworks with such companies as Able Consulting and The Cadmus Group, Inc. Findhim at http://Steve.Orr.net or e-mail him at [email protected].

Rating:

WebSite: http://www.aspose.com/Products/Aspose.Excel/

Price: Starting under US$300

Tell us what you think! Please send any comments aboutthis article to [email protected].Please include the article title and author.