Your users need the ability to export your application s data to Excel. Maybe they don t know it yet, but you should because sooner or later, they ll be demanding it. Excel is such a powerful and common tool that it cannot be ignored. It s not difficult to enhance anASP.NET Web site with the ability to generate Excel spreadsheets if your needs are basic. However, if you need fancier features such as highly-functional, fantastic-looking spreadsheets embedded with useful charts, images, security, and other impressive features then a tool such as Essential XlsIO becomes indispensable.
The Problem
Without a tool such as XlsIO, developers historically had to resort to COM Interop to access Excel s object model directly in order to access Excel s more advanced spreadsheet creation capabilities. For Web development, this is fraught with hazards related to performance, scalability, licensing, support, and reliability. Excel simply wasn t created to be a spreadsheet server; it was created only to be a single-user desktop application.
The Solution
XlsIO on the other hand, was indeed created to serve spreadsheets quickly and efficiently without requiring Excel to be installed on the server. XlsIO is a custom spreadsheet engine written in pure C# that reads and writes Excel files in BIFF 8 format, so that end users with any modern version of Excel (or Microsoft s free Excel viewer) can work with the files effortlessly. XlsIO works well with any .NET language, and with any version of Visual Studio, including the Express editions.
In case you ve already developed some export code using the old COM Interop approach previously mentioned, you ll be happy to know that upgrading to XlsIO should be quick and easy, thanks to its object model s similarities with Excel s object model.
The Code
To begin using XlsIO, first add a reference to Syncfusion.XlsIO.Base in any Visual Studio Web application, as shown in Figure 1.
Figure 1: To begin using XlsIO,
first add a reference to XlsIO.Base in your Web application.
Add a line at the top of the page to Import Syncfusion.XlsIO, then start with these two lines of code, which are always required to begin spreadsheet interaction:
'Instantiate Dim xl As ExcelEngine = New ExcelEngine() Dim xlApp As IApplication = xl.Excel
The first line instantiates the spreadsheet-creation engine; the second line instantiates the Excel application object.
Figure 2 shows how easy it is to create a new spreadsheet from scratch, output some data into it, and send it to the user. This is all done efficiently in memory without Excel being required on the server and without needing to save any files on the server.
'Instantiate the spreadsheet engine
Dim xl As ExcelEngine = New ExcelEngine()
Using xl
'instantiate excel application object
Dim xlApp As IApplication = xl.Excel
'create a new workbook with 2 worksheets
Dim wkbk As IWorkbook = xl.Excel.Workbooks.Create(2)
'get a reference to both worksheets
Dim sht1 As IWorksheet = wkbk.Worksheets(0)
Dim sht2 As IWorksheet = wkbk.Worksheets(1)
'add data to the first cell of each worksheet
sht1.Range("A1").Text = "Hello World"
sht2.Range("A1").Text = "Hello World 2"
'render the spreasheet directly to the response stream
wkbk.SaveAs("HelloWorld.xls", ExcelSaveType.SaveAsXLS, _
Response, ExcelDownloadType.PromptDialog)
End Using
Figure 2: This is all the code it takes to create a new spreadsheet, write text into it, and send it to the user.
It s just as easy to read from an existing data-filled spreadsheet. The code snippet in Figure 3 shows how to open an existing spreadsheet and treat it as a data source.
'get a reference to an existing data-filled spreadsheet
Dim workbook As IWorkbook = _
xl.Excel.Workbooks.Open(Server.MapPath("MyData.xls"))
Dim sht As IWorksheet = workbook.Worksheets(0)
'access the data cells individually...
Response.Write(sht.Range("A1").Text)
'or retrieve the data into a standard DataTable object
Dim dt As System.Data.DataTable
dt = sht.ExportDataTable(sht.UsedRange, _
ExcelExportDataTableOptions.ColumnNames)
Figure 3: It s easy to open a pre-existing spreadsheet to use it as a template or data source.
The Features
Although these simple examples show how you can easily get started with the basic functionality of XlsIO, this product is packed with too many powerful capabilities to demonstrate here. With only a little more code than is shown here, you can take advantage of its more sophisticated features. For example, you can take advantage of Excel s built-in functions and formulas, as well as more than 150 built-in functions included with Syncfusion s built-in calculation engine.
There are also a variety of worksheet manipulation capabilities provided, such as copying worksheets between workbooks, grouping and hiding, and row/column sizing and insertion. You can also programmatically lock cells, use named ranges, add comments or hyperlinks to any cell, and secure worksheets with passwords. XlsIO also supports programmatic configuration of a variety of common spreadsheet properties, such as page orientation, paper size, margins, and headers/footers.
It s easy to beautify the spreadsheets created by XlsIO. For example, embedded images and Rich Text Format are supported, which provides for fancy formatting capabilities. Conditional formatting is also supported, so you could (for example) automatically display all negative numbers in red.
It couldn t be any easier to programmatically generate or manipulate Excel charts, thanks to the handy built-in chart object. This intuitive object should be a snap to work with for anyone familiar with chart creation in Excel.
Figure 4: Generating colorful Excel
tables and charts is a breeze with XlsIO, but can be challenging without.
In case you run into any Excel spreadsheet features that you can t directly create using XlsIO (such as embedded VBA code or macros), you can always start with a pre-existing Excel spreadsheet that already has such items configured. Such a template can be quite useful, even when not required; for example, to visually format a spreadsheet layout at design time that you plan to fill in with data at run time (Figure 3 shows how to open an existing spreadsheet template).
The Bottom Line
Syncfusion provides an online knowledgebase, FAQ, and forum to help with any questions that may arise during development. Their downloadable free trials contain an abundance of code samples, covering every major feature.
The price for XlsIO is reasonable, starting at US$495 for a single-user license. This includes both Windows Forms and Web versions of the component, and unlimited tech support for 60 days. Syncfusion s licensing is liberal; it doesn t anchor you to a single development machine. They understand a lot of developers are rather mobile these days, and might therefore use more than one computer. If you d like access to XlsIO s efficiently written C# source code, it is available (although the price increases to US$895). You could save money by purchasing a package deal for multiple licenses or their full Essential Studio suite of components.
If you re not familiar with Syncfusion I suggest you cruise their Web site (http://www.syncfusion.com) to learn about their many other unique components, controls, and packages.
Steve C. Orr is an MCSD and a Microsoft MVP in ASP.NET. He s been developing software solutions for leading companies in the Seattle area for more than a decade. When he s not busy designing software systems or writing about them, he can often be found loitering at local user groups and habitually lurking in the ASP.NET newsgroup. Find out more about him at http://SteveOrr.net or e-mail him at mailto:[email protected].
Web Site: http://www.syncfusion.com/products/xlsio/
Price: Starts at US$495
