Skip to main content Skip to footer

Convert a CellRange into a Table using Spread

Spread for Silverlight/WPF provides you a very handy feature where you can convert a cell range in a sheet into a table. By table I mean you can sort this cell range without needing to sort the whole column, you can filter it and you can also modify the appearance for this cell range or should I say table. Lets see how this table looks like in a sheet. Creating tables from a cell range is really simple and easy with Spread. All you need to do is to call the AddTable method for a sheet and pass the required parameters such as table name, cell range and table style. Something like:

gcSpreadSheet1.Sheets[0].AddTable(tableName, row, column, rowCount, columnCount, tableStyle);  

You can have default or custom styles for these tables. You can use TableStyle class to create a custom style for the table. Here is the code to create a custom style :

   GrapeCity.Windows.SpreadSheet.Data.TableStyleInfo tablestyle = new GrapeCity.Windows.SpreadSheet.Data.TableStyleInfo();  
   tablestyle.Background = new SolidColorBrush(Colors.Azure);  
   tablestyle.BorderBottom = new GrapeCity.Windows.SpreadSheet.Data.BorderLine(System.Windows.Media.Colors.DarkOrchid);  
   tablestyle.BorderTop = new GrapeCity.Windows.SpreadSheet.Data.BorderLine(System.Windows.Media.Colors.DarkSlateBlue);  
   tablestyle.BorderLeft = new GrapeCity.Windows.SpreadSheet.Data.BorderLine(System.Windows.Media.Colors.Crimson, GrapeCity.Windows.SpreadSheet.Data.BorderLineStyle.Thick);  
   tablestyle.BorderRight = new GrapeCity.Windows.SpreadSheet.Data.BorderLine(System.Windows.Media.Colors.Crimson, GrapeCity.Windows.SpreadSheet.Data.BorderLineStyle.Thick);  
   GrapeCity.Windows.SpreadSheet.Data.TableStyleInfo tablestylerow = new GrapeCity.Windows.SpreadSheet.Data.TableStyleInfo();  
   tablestylerow.BorderBottom = new GrapeCity.Windows.SpreadSheet.Data.BorderLine(System.Windows.Media.Colors.Crimson, GrapeCity.Windows.SpreadSheet.Data.BorderLineStyle.Thick);  
   GrapeCity.Windows.SpreadSheet.Data.TableStyle testtable = new GrapeCity.Windows.SpreadSheet.Data.TableStyle();  
   testtable.WholeTableStyle = tablestyle;  
   testtable.HeaderRowStyle  = tablestylerow;  

Once you are ready with the TableStyle you can add the table using AddTable() method for Sheet:

   gcSpreadSheet1.Sheets[0].AddTable("Table1", 1, 1, 5, 2, testtable);  

Here, "Table1" is the name of the table and (1, 1, 5, 2) is the range of cells to form a table with "testtable" style. There are a whole lot of actions that can be performed on this table like frozen rows and columns, sparklines, conditional formatting, cell spans, etc other than filtering and sorting. You can use a cell range or the DataSource property for the table data. Take a look at the code given below for an example of how to use an external data as data source for this table :

   lname[] arr = new lname[] { new lname("Smith", 100), new lname("Fender", 3), new lname("Gill", 5) };//lname is some class two properties "name" and "value".  
   gcSpreadSheet1.Sheets[0].AddTable("Table1", 0, 0,3, 3, arr);  

You can also perform cut, copy or paste operations to and from the table. The row count for the table includes the header and footer rows. You can move this table to some other location on the sheet. For example I can move the table created above to cell(3, 3) from cell(0,0).

   GrapeCity.Windows.SpreadSheet.Data.SheetTable ntable = new GrapeCity.Windows.SpreadSheet.Data.SheetTable();  
   ntable = gcSpreadSheet1.Sheets[0].FindTable("Table1");  
   gcSpreadSheet1.Sheets[0].MoveTable(ntable, 3, 3);  

You can also remove the table using RemoveTable( ) method. e.g.


To resize a table you can call the ResizeTable( ) method like:

   gcSpreadSheet1.Sheets[0].ResizeTable("Table1",2 ,2 ); // where 2,2 is the new row and column count  

Most importantly, these tables are completely compatible with Excel. It means you can export and import these tables along with your sheet to and from excel. Please download the sample application for demonstration. Download C# Sample Download VB.Net Sample


comments powered by Disqus