Recently (read: the latest release) the Infragistics Excel engine got a set of neat enhancements to further improve the control you have over exported or imported files. For those that are not yet familiar – it’s a library that allows you to create and interact with Microsoft® Excel® documents. It is completely independent (so no automation going on) and it provides you with a elaborate Workbook object model, along with all the well-known things you’d expect to find in it – worksheets, columns, cells and formulas. With the engine’s independent nature you can enable your applications to reliably read and create the popular format with support for a wide range of versions of the office product. In case you’ve been to our documentation, it’s worth mentioning that this library can be found under the platform specific section and there’s a good reason for that – the Silverlight version runs entirely on the client, so no round trips to request and download from the server!

Now lets see what new is added to this already very useful engine.

Format Support

Infragistics Excel is now more capable of delivering the file with just the right looks to aid you user or just improve the overall experience with generated files. That is achieved with support for styles and formatting options.

Styles

The Workbook’s Styles collection now comes prepopulated with over 60 standard styles – the different accents, titles, headings and, of course, the good, bad and neutral ones. Once create your workbook object

you can pick rows, columns or singular cells and apply those styles to them:

And the result from the above in the generated file is as expected:

The very same collection also exposes another very interesting property – the so called Normal style. This is the one that would affect everything in the workbook that does not have otherwise specified style. The useful side of this is that you can modify this style and affect the entire workbook. It contains things like text alignment, border styles and colours, fills, fonts, the bunch. That means you can preset the default fort for the workbook of the default background or color:

And these would apply as default everywhere :

Two more neat tricks that provide more control the IWorksheetCellFormat interface (also implemented by the row and column base class) now has Style property that would returns a reference the parent style for rows, columns and cell formats and the very same interface also provides a GetResolvedCellFormat method to get the current style.

Format

The engine now supports accessing format options and also mimics some Microsoft Excel behaviours regarding those. The very same interface also provides a property called FormatOptions that takes a flagged enumeration value. When there are non-default values applied, the enumeration includes options that define which ones of them should be applied. That means you can define not just the rules, but also exceptions to them. The options in the enum include pretty much the checkboxes you would see in the Modify Style dialog:

The important part here is that even if the cell or style format have special Fill or Border values defined, for this particular cell the Options will only apply the Font and alignment, while the rest will resolve to the default values.

A new color model

The color model object received some Excel 2007 style updates. It brings a new Workbook property called Palette. What is does is it lets you modify the Color set that gets saved with the workbook (you can find that in Excel under Colors in Save options). What is does is what the name suggests – a palette of standard colors that would be used for closest match when displaying in previous versions of Excel. So lets see how we can preserve the color we used above to apply to the font:

As you can tell you will have to swap one color for another and it can get somewhat messy at some point, but not to worry - it can be reset and comes with a built-in GetIndexOfNearestColor method you can use to see what color would be seen by users in 2003 version.

The color model also provides means to apply cell fill styles and they are plentiful – the obvious solid fill, patterned and gradients. Of course, when we talk gradients, they would be visible only in Excel 2007 and up and for previous versions such cell will be displayed with solid fill based on the first gradient stop. Here’s how you can for example apply a patterned fill to a cell:

The first colour is the background, the second is for the pattern itself and the third property is on of the styles you can pick (there’s more than a dozen of them). The result would be the very first cell looking somewhat odd due to to the bad colors I picked:

Tables!

Seems like we can never get enough of them and the latest version of the Infragistics Excel Engine comes with support for named tables inside your worksheets! Table in Microsoft Excel® terms would be a rectangular region of cells with it’s data organized into columns. The table can also have headers and a totals rows. The library allows not just to create but also to find such regions by name.

Adding a named table

In this example we would take data from a Northwind and populate cell values with it and once that is done we would define the just filled area as named region and format it as a table and give it a name. Also we would populate the very first row with the property names of our data model to become headers and when formatting the regions as table, passing true would signify our table has headers included:

 

 

For the example I’ll add another table with Suppliers data for the purposes of the demo, the code for it is basically the same as above, sans the row indexes.

Styles

Once that is done you can give your tables some style, quite literally. For that goal the access to the table can be done via the Tables collection exposed off the Worksheet object and the styles collection in the Workbook class. The latter is not the same as the default styles described above, but a separate collection of again about 60 styles specific to tables, which again mirror the ones you can find in Excel when designing tables.

 

 

As you can tell or already know, table styles are in 3 groups light, medium and dark(or full colors).

Filtering and sorting exported tables

A feature of the Excel tables is providing functionality to better analyse the data – such as applying filters and sorting. Those are features of the Columns in the table (each table exposes Columns collection) and are applied to them respectively. The Infragistics Excel Engine allows you to define such features for the exported tables. Both actions come with pretty much all the conditions you’d expect and the ability to add some customization as well. For filtering you would find above and below average, specific date periods, fixed values and custom filtering. Here’s how you can use the custom one to see only the countries beginning with ‘U’:

 

 

For sorting you can rely on the standard ordering by value, but you can also define your own list of values in the order you want them to be and you can also sort based on cell fill style! For example, a simple ordering would look like so:

 

 

The filtering was applied to the Customers table and the sorting to the Suppliers table – so now when your user opens up the generated file he would see:

Those are the Customers and Suppliers tables as created above, their names appear in the Table designer along with the Styles we picked for them and you can clearly see the Country column has a filter applied and the Fax column below is sorted.

Accessing by name

Of course, since you have named table to complete the functionality you can access those by their name without even having to guess where they are as the Workbook object (rather than the actual containing worksheet) exposes a new GetTable method you can use to get reference to your table with just a name, so the above code for applying styles can transform into:

 

 

Furthermore you can assign names to cells too! Everything is kept in a Named References collection in the workbook. Lets assign a name to the cell we used to set style above and then get it by name to apply the style:

The idea is this can happen in separate methods that don’t share variables or you can find that cell just by name in an imported from file workbook! And obviously the same can be said for the tables and that provides for some very interesting

useful implementations!

What you can do is import an Excel File using the Workbook’s static Load method and you can find your cell in there, get it’s text using the all new GetText method to get it’s actual displayed value( the real one the user would see – after formatting, column width and formulas are taken into account). You can also separate the tables in different view , should the file contain multiples, or offer the user the choice to only display a certain table. If you are wondering how to import Excel data to a XamGrid you can check this sample and you can find more in the documentation for both Silverlight and WPF. I would also use a XamDialogWindow control to display modal dialog and let the user pick the table:

 

 

So when in this project’s demos you import the very same Excel file we generated with two tables you will see the following:

I believe this can prove really useful to some of you, so find the full code in the demos below.

Conclusion

The Infragistics Excel Engine got some very useful and eye-pleasing additions – from various formatting options and styles to tables and named references for the same and getting the actual final text the user would see in a cell – all making an already powerful Excel Document object model that much more useful and giving you extra control to bring the files you generate closer to what can be done with the actual software and also closer to the functionality you desire! It’s event totally client side for Silverlight application, so the possibility for some offline action is there, too!

An elaborate model like this however is what you could call vast – plenty of knobs to tweak, so to speak. For that reason i strongly suggest you pay a visit to our Documentation and also try the Samples.

Here are the links for the demos – a Silverlight project and a WPF project (besides the assembly names, there are barely any differences between the code regarding the Excel Engine). Keep in mind you will need at least a Trial versions of NetAdvantage 2012 vol.1 for Silverlight or WPF respectively to successfully build and run those projects.

Follow us on Twitter @DamyanPetev and @Infragistics and stay in touch on Facebook!