Infragistics Excel Library – new features and enhancements

Good news for everyone that needs to use the Infragistics Excel library! Due to increased popularity and demand, the 11.2 release provides a number of improvements on this product. Here is a list with those new additions:

·         Shapes Support

·         Data Validation

·         Print Titles

·         Print Area

·         Page Breaks

This article with tell you how to use those new features and will show you code examples.

Let’s start with the shapes support. The way to add shapes to your excel file is straight-forward. You get a reference to the worksheet in the workbook you will be working with and populate the Shapes property, which is of type WorksheetShapeCollection, with the shapes you want to add. There are several predefined shapes that are currently implemented. All of them are located under the Infragistics.Documents.Excel.PredefinedShapes namespace. Here is a list of those predefined shapes:

·         DiamondShape

·         EllipseShape

·         HeartShape

·         IrregularSeal1Shape

·         IrregularSeal2Shape

·         LightningBoltShape

·         LineShape

·         PentagonShape

·         RectangleShape

·         RightTriangleShape

·         StraightConnector1Shape

The following snippet for a Button Click event handler shows you a how to create a new workbook with a worksheet containing a line spanning from cell A1 to cell C3 when you click a button and record the resulting excel file using the SaveFileDialog.

        using Infragistics.Documents.Excel;

        using Infragistics.Documents.Excel.PredefinedShapes;

 

        ...

 

        private void SaveClicked(object sender, RoutedEventArgs e)

        {                                   

            Workbook wb = new Workbook();

            wb.Worksheets.Add("Worksheet");

            Worksheet ws = wb.Worksheets["Worksheet"];

 

            LineShape shape = new LineShape();

            shape.TopLeftCornerCell = ws.GetCell("A1");

            shape.BottomRightCornerCell = ws.GetCell("C3");

            ws.Shapes.Add(shape);

 

            SaveFileDialog save = new SaveFileDialog();

            if (save.ShowDialog().Value == true)

            {

                var stream = save.OpenFile();

                wb.Save(stream);

                stream.Close();

            }

        }

 

Here is how the file that we just created looks like when opened in Excel.

Each shape has properties that are specific to it and once the 11.2 release is officially out you can consult the documentation or the samples browser to see the full details.

The second major improvement to the Excel library is the fact that it now supports Data Validation. The feature is organized similarly to the Shapes. The Worksheet exposes a DataValidationRules property of type DataValidationRuleCollection, where we add the rules. Rules can also be added on a per cell basis by setting the DataValidationRule property of the WorksheetCell class. Here is a list with all currently supported rules:

Let’s say we want to set a rule on column A, that allows only numbers between 1 and 10. If we use the code snippet above that creates the worksheet with a shape, here is how the method should look like:

        private void SaveClicked(object sender, RoutedEventArgs e)

        {

            Workbook wb = new Workbook();

            wb.Worksheets.Add("Worksheet");

            Worksheet ws = wb.Worksheets["Worksheet"];

 

            TwoConstraintDataValidationRule rule = new TwoConstraintDataValidationRule();

            rule.SetLowerConstraint(1.0);

            rule.SetUpperConstraint(10.0);

            ws.DataValidationRules.Add(rule, ws.GetRegion("A:A"));

 

            SaveFileDialog save = new SaveFileDialog();

            if (save.ShowDialog().Value == true)

            {

                var stream = save.OpenFile();

                wb.Save(stream);

                stream.Close();

            }

        }

 

The rest of the new features (Print Titles, Print Area and Page Breaks) are all available through the PrintOptions property of the Worksheet class. To add a page break you need to invoke the InsertPageBreak method and pass the row or column that you want to be used as page break. If you want to remove the page breaks invoke the ClearPageBreaks method. The PrintAreas collection holds WorksheetRegions that define print areas. Create a new instance of the WorksheetRegion class with the appropriate parameters and add it to the collection to define a print area. And the last feature – print titles – is used throught the ColumnsToRepeatAtLeft and RowsToRepeatAtTop properties. Both of them are of type RepearTitleRange who’s constructor takes the StartIndex and EndIndex parameters.

The following code demonstrates how to use the mentioned properties:

        private void SaveClicked(object sender, RoutedEventArgs e)

        {

            Workbook wb = new Workbook();

            wb.Worksheets.Add("Worksheet");

            Worksheet ws = wb.Worksheets["Worksheet"];

            ws.PrintOptions.PrintAreas.Add(new WorksheetRegion(ws, 3, 3, 5, 5));

            ws.PrintOptions.InsertPageBreak(ws.Rows[6]);

            ws.PrintOptions.InsertPageBreak(ws.Columns[6]);

            ws.PrintOptions.ColumnsToRepeatAtLeft = new RepeatTitleRange(3, 5);

            ws.PrintOptions.RowsToRepeatAtTop = new RepeatTitleRange(3, 5);

            SaveFileDialog save = new SaveFileDialog();

            if (save.ShowDialog().Value == true)

            {

                var stream = save.OpenFile();

                wb.Save(stream);

                stream.Close();

            }

        }

I hope this has been useful and interesting information. Have a great day.