|
|
|
  
  |
 |
asp:review
Syncfusion XlsIO Essential XlsIO version 4.3.0.30
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 an ASP.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.
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.
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
Rating: ééééé Web Site: http://www.syncfusion.com/products/xlsio/ Price: Starts at US$495
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
|
|
||||||
