Syncfusion XlsIO

Your users need the ability to export your application sdata to Excel. Maybe they don t know it yet, but you should because sooner orlater, they ll be demanding it. Excel is such a powerful and common tool thatit cannot be ignored. It s not difficult to enhance anASP.NET Web site withthe ability to generate Excel spreadsheets if your needs are basic. However, ifyou need fancier features such as highly-functional, fantastic-lookingspreadsheets embedded with useful charts, images, security, and otherimpressive features then a tool such as Essential XlsIO becomesindispensable.

The Problem

Without a tool such as XlsIO, developers historically hadto resort to COM Interop to access Excel s object model directly in order toaccess Excel s more advanced spreadsheet creation capabilities. For Webdevelopment, this is fraught with hazards related to performance, scalability,licensing, support, and reliability. Excel simply wasn t created to be aspreadsheet server; it was created only to be a single-user desktopapplication.

The Solution

XlsIO on the other hand, was indeed created to servespreadsheets quickly and efficiently without requiring Excel to be installed onthe server. XlsIO is a custom spreadsheet engine written in pure C# that readsand writes Excel files in BIFF 8 format, so that end users with any modernversion of Excel (or Microsoft s free Excel viewer) can work with the fileseffortlessly. XlsIO works well with any .NET language, and with any version ofVisual Studio, including the Express editions.

In case you ve already developed some export code usingthe old COM Interop approach previously mentioned, you ll be happy to know thatupgrading to XlsIO should be quick and easy, thanks to its object model ssimilarities with Excel s object model.

The Code

To begin using XlsIO, first add a reference toSyncfusion.XlsIO.Base in any Visual Studio Web application, as shown in Figure1.

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 ImportSyncfusion.XlsIO, then start with these two lines of code, which are alwaysrequired to begin spreadsheet interaction:

'InstantiateDim xl As ExcelEngine = New ExcelEngine()Dim xlApp As IApplication = xl.Excel

The first line instantiates the spreadsheet-creationengine; the second line instantiates the Excel application object.

Figure 2 shows how easy it is to create a new spreadsheetfrom scratch, output some data into it, and send it to the user. This is alldone efficiently in memory without Excel being required on the server andwithout needing to save any files on the server.

'Instantiate the spreadsheet engineDim 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 isall the code it takes to create a new spreadsheet, write text into it, and sendit to the user.

It s just as easy to read from an existing data-filledspreadsheet. The code snippet in Figure 3 shows how to open an existingspreadsheet and treat it as a data source.

'get a reference to an existing data-filled spreadsheetDim 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 objectDim dt As System.Data.DataTabledt = sht.ExportDataTable(sht.UsedRange, _ ExcelExportDataTableOptions.ColumnNames) 

Figure 3: It seasy 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 getstarted with the basic functionality of XlsIO, this product is packed with toomany powerful capabilities to demonstrate here. With only a little more codethan is shown here, you can take advantage of its more sophisticated features. Forexample, you can take advantage of Excel s built-in functions and formulas, aswell as more than 150 built-in functions included with Syncfusion s built-incalculation engine.

There are also a variety of worksheet manipulation capabilitiesprovided, 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 worksheetswith passwords. XlsIO also supports programmatic configuration of a variety ofcommon spreadsheet properties, such as page orientation, paper size, margins,and headers/footers.

It s easy to beautify the spreadsheets created by XlsIO. Forexample, embedded images and Rich Text Format are supported, which provides forfancy formatting capabilities. Conditional formatting is also supported, so youcould (for example) automatically display all negative numbers in red.

It couldn t be any easier to programmatically generate ormanipulate Excel charts, thanks to the handy built-in chart object. Thisintuitive object should be a snap to work with for anyone familiar with chartcreation in Excel.

Figure 4: Generating colorful Exceltables and charts is a breeze with XlsIO, but can be challenging without.

In case you run into any Excel spreadsheet features thatyou 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 hassuch items configured. Such a template can be quite useful, even when notrequired; for example, to visually format a spreadsheet layout at design timethat you plan to fill in with data at run time (Figure 3 shows how to open anexisting spreadsheet template).

The Bottom Line

Syncfusion provides an online knowledgebase, FAQ, andforum to help with any questions that may arise during development. Theirdownloadable free trials contain an abundance of code samples, covering everymajor feature.

The price for XlsIO is reasonable, starting at US$495 fora single-user license. This includes both Windows Forms and Web versions of thecomponent, and unlimited tech support for 60 days. Syncfusion s licensing isliberal; it doesn t anchor you to a single development machine. They understanda lot of developers are rather mobile these days, and might therefore use morethan 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). Youcould save money by purchasing a package deal for multiple licenses or theirfull Essential Studio suite of components.

If you re not familiar with Syncfusion I suggest youcruise their Web site (http://www.syncfusion.com)to learn about their many other unique components, controls, and packages.

Steve C. Orr is anMCSD and a Microsoft MVP in ASP.NET. He s been developing software solutionsfor leading companies in the Seattlearea for more than a decade. When he s not busy designing software systems orwriting about them, he can often be found loitering at local user groups andhabitually lurking in the ASP.NET newsgroup. Find out more about him at http://SteveOrr.netor e-mail him at mailto:[email protected].

Web Site: http://www.syncfusion.com/products/xlsio/

Price: Starts atUS$495