← Back to all samples

ExcelBook

This sample contains two projects: a reusable C1FlexGridBook class that extends the C1FlexGrid and provides Excel-like features, and an ExcelBook project that shows the C1FlexGridBook class in action.

The reusable C1FlexGridBook class provides the following features:

  • Multi-sheet tabbed interface:

The sample is called "ExcelBook" because it shows a grid with multiple sheets. The sheets are represented by tabs that appear below the grid, to the left of the grid's horizontal scrollbar.

  • Save and Load XLSX files:

The sample uses the C1.Silverlight.Excel component to load and save multi-sheet, styled workbooks in XLSX format.

  • Load TXT/CSV files:

Add support to load txt and csv file.

  • Formula support:

The C1FlexGridBook control has a "CalcEngine" class that parses and evaluates Excel-style formulas. The engine currently supports 69 functions and is easily extensible. The engine also evaluates properties of the grid's DataContext. For example:

  
// show the amount of taxes owed by a customer in cell "A1"
// (assuming the tax is calculated as 8.5% of the value of the
// Customer.Amount property)
_flex.DataContext = new Customer();
_flex[0, 0] = "= Amount * 8.25%";

All strings starting with an equals sign are treated as formulas. To display an actual string that starts with an equals sign, precede it with a single quote (as in Excel). For example:

  
_flex[0,0] = "'==============";
  • Formula editing:

While editing formulas, you may click on cells to insert references to those cells into the formula. This is done by the PreviewMouseLeftButtonDown event handler. The handler checks to see if a cell is being edited and if the editor contains a string that starts with an equals sign. In that case, it assumes the user is editing a formula and adds the address of the range that was clicked to the formula.

  • Hyperlink support:

The C1FlexGridBook supports the HYPERLINK function. If you enter a formula such as "=HYPERLINK('https://www.grapecity.com/en/componentone', 'ComponentOne')" in a cell, then the grid will create a HyperlinkButton element in the cell. The element can be clicked to navigate to the URL provided, and it can be edited and formatted like any other cell.

  • ColorScheme:

The C1FlexGridBook control has a ColorScheme property that allows you to select color schemes that match the Microsoft Office schemes: Blue, Silver, and Black.

  • Row and Column Headers:

The C1FlexGridBook control has a custom CellFactory that generates labels for the column and row headers. Column headers display letters and row headers display the row index.

  • Custom Outlines:

The C1FlexGridBook control CellFactory generates images for expanded and collapsed nodes that look like the icons used in Excel.

  • Pdf Export:

The C1FlexGridBook control uses the C1.Silverlight.Pdf assembly and the GetPageImages method to export the grid to Pdf files.

  • Undo/Redo:

The C1FlexGridBook control implements a flexible and extensible undo/redo stack.

  • Range Sorting:

The C1FlexGridBook control provides range-based, unbound sorting.

  • Enhanced Filtering:

The C1FlexGridBook control extends the standard column filtering capabilities in the C1FlexGrid to provide the following additional features:

  
- sort control
- filtered value selection in the value filter editor
- resizable editor
- Excel appearance
  • AutoComplete

The sample sets the AutoComplete property to true. This causes the grid to search for similar items in the same column as the user edits cells. This behavior is the default in Excel.

  • Advanced Freezing

The C1FlexGrid allows you to freeze a number of rows and columns, starting from zero. The sample goes beyond this by allowing users to freeze rows and columns within the current view range (as Excel does). For example, if you scroll down until row 1000 is at the top of the view range, then move the cursor down by a couple of rows and press the freeze button, the grid will show a frozen area that includes rows 1000-1003, and a scrolling area below with rows 1004 to the end of the grid.

To accomplish this, the sample hides rows and columns above and to the left of the current view range when freezing. Even though the frozen ranges still start from the first row/column, the user sees arbitrary frozen ranges on the screen.