Show All

Hide All

To set up the grid and populate the grid with the sales data we want to analyze, set the layout properties and styles either in the designer or in code, and use the GetDataSource method to populate the grid.

1. Set up the grid layout and styles either in the designer or in code.

   In the Designer

   • In the Properties window, set the following properties:

     Property	Setting
     AllowEditing	False
     AllowSorting	None
     AllowMerging	Nodes
     ExtendLastCol	True
     SelectionMode	Cell
     Tree.Style	Simple
     Tree.Column	1

   • Open the C1FlexGrid Style Editor by selecting Styles from the C1FlexGrid Tasks menu. For more information on accessing the C1FlexGrid Style Editor, see Accessing the C1FlexGrid Style Editor.
   • Select Normal from the list of Built-In Styles.
   • Set the Border.Style property to None and the Trimming property to EllipsisCharacter.
   • Select Subtotal0 from the list of Built-In Styles.
   • Set the BackColor property to Gold, and the ForeColor property to Black.
   • Set the properties for Subtotal1 and Subtotal2 to the following:

     Subtotal1
     BackColor	Khaki
     ForeColor	Black
     Subtotal2
     BackColor	LightGoldenrodYellow
     ForeColor	Black

   • Click OK to close the editor.

   In Code

   Add the following code to the Form_Load event to set up the grid layout and styles:

   To write code in Visual Basic

   Visual Basic

   Copy Code

   Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As EventArgs) Handles MyBase.Load ' Set up the grid layout/behavior. C1FlexGrid1.AllowEditing = False C1FlexGrid1.AllowSorting = AllowSortingEnum.None C1FlexGrid1.AllowMerging = AllowMergingEnum.Nodes C1FlexGrid1.SelectionMode = SelectionModeEnum.Cell C1FlexGrid1.ExtendLastCol = True C1FlexGrid1.Cols(0).Width = C1FlexGrid1.Cols.DefaultSize / 4 C1FlexGrid1.Tree.Style = TreeStyleFlags.Simple C1FlexGrid1.Tree.Column = 1 ' Set up grid styles. C1FlexGrid1.Styles.Normal.Border.Style = BorderStyleEnum.None C1FlexGrid1.Styles.Normal.Trimming = StringTrimming.EllipsisCharacter Dim s As CellStyle = C1FlexGrid1.Styles(CellStyleEnum.GrandTotal) s.BackColor = Color.Black s.ForeColor = Color.White s = C1FlexGrid1.Styles(CellStyleEnum.Subtotal0) s.BackColor = Color.Gold s.ForeColor = Color.Black s = C1FlexGrid1.Styles(CellStyleEnum.Subtotal1) s.BackColor = Color.Khaki s.ForeColor = Color.Black s = C1FlexGrid1.Styles(CellStyleEnum.Subtotal2) s.BackColor = Color.LightGoldenrodYellow s.ForeColor = Color.Black End Sub

   To write code in C#

   C#

   Copy Code

   private void Form1_Load( System.object sender, EventArgs e) { // Set up the grid layout/behavior. c1FlexGrid1.AllowEditing = false; c1FlexGrid1.AllowSorting = AllowSortingEnum.None; c1FlexGrid1.AllowMerging = AllowMergingEnum.Nodes; c1FlexGrid1.SelectionMode = SelectionModeEnum.Cell; c1FlexGrid1.ExtendLastCol = true; c1FlexGrid1.Cols[0].Width = c1FlexGrid1.Cols.DefaultSize / 4; c1FlexGrid1.Tree.Style = TreeStyleFlags.Simple; c1FlexGrid1.Tree.Column = 1; // Set up grid styles. c1FlexGrid1.Styles.Normal.Border.Style = BorderStyleEnum.None; c1FlexGrid1.Styles.Normal.Trimming = StringTrimming.EllipsisCharacter; CellStyle s = c1FlexGrid1.Styles[CellStyleEnum.GrandTotal]; s.BackColor = Color.Black; s.ForeColor = Color.White; s = c1FlexGrid1.Styles[CellStyleEnum.Subtotal0]; s.BackColor = Color.Gold; s.ForeColor = Color.Black; s = c1FlexGrid1.Styles[CellStyleEnum.Subtotal1]; s.BackColor = Color.Khaki; s.ForeColor = Color.Black; s = c1FlexGrid1.Styles[CellStyleEnum.Subtotal2]; s.BackColor = Color.LightGoldenrodYellow; s.ForeColor = Color.Black; }

   The routine starts by setting up the grid layout and some styles.

2. Bind C1FlexGrid to a data source by adding the following code to the Form_Load event:

   To write code in Visual Basic

   Visual Basic	Copy Code
   ' Bind C1FlexGrid to the data source. C1FlexGrid1.DataSource = GetDataSource()

   To write code in C#

   C#	Copy Code
   // Bind C1FlexGrid to the data source. c1FlexGrid1.DataSource = GetDataSource();

   The routine binds it to a data source created by the GetDataSource method, listed below.

3. Lock the last three columns in place by setting their AllowDragging property to False. This is done to prevent the user from grouping the data in these columns (the values in these columns are distinct for each row). This property can be set either in the designer or at in code.

   In the Designer

   • Select Column 4 in the grid. This will open the Column Tasks menu for Column 4.
   • Uncheck the Allow Dragging check box.
   • Repeat for Column 5 and Column 6.

   Alternatively, the AllowDragging property can also be set using the C1FlexGrid Column Editor:

   • Open the C1FlexGrid Column Editor by selecting Designer in the C1FlexGrid Tasks menu. For details on how to access the C1FlexGrid Column Editor, see Accessing the C1FlexGrid Column Editor.
   • Select Column 4 in the right pane.
   • In the left pane, set the AllowDragging property to False.
   • Set the AllowDragging property to False for Column 5 and Column 6.
   • Do not close the editor.

   In Code

   Add the following code to the Form_Load event:

   To write code in Visual Basic

   Visual Basic	Copy Code
   ' Prevent the user from dragging last three columns. C1FlexGrid1.Cols(4).AllowDragging = False C1FlexGrid1.Cols(5).AllowDragging = False C1FlexGrid1.Cols(6).AllowDragging = False

   To write code in C#

   C#	Copy Code
   // Prevent the user from dragging last three columns. c1FlexGrid1.Cols[4].AllowDragging = false; c1FlexGrid1.Cols[5].AllowDragging = false; c1FlexGrid1.Cols[6].AllowDragging = false;

4. Set the Format property of the Sales Amount column so that the amounts are displayed as currency values. This can be done either in the designer or in code.

   In the Designer

   • Select Column 6 in the grid.
   • Click the ellipsis button in the Format String box to open the Format String dialog box.
   • Under Format type select Currency.
   • Click OK to close the Format String dialog box.

   Alternatively, the Format property can also be set using the C1FlexGrid Column Editor:

   • In the C1FlexGrid Column Editor, select Column 6 in the right pane.
   • In the left pane, click the ellipsis button next to the Format property to open the Format String dialog box.
   • Under Format type select Currency.
   • Click OK to close the Format String dialog box.
   • Click OK to close the editor.

   In Code

   To write code in Visual Basic

   Visual Basic	Copy Code
   ' Display currency values in the Sales Amount column. C1FlexGrid1.Cols(6).Format = "c"

   To write code in C#

   C#	Copy Code
   // Display currency values in the Sales Amount column. c1FlexGrid1.Cols[6].Format = "c";

5. The GetDataSource method creates the data table that is displayed by the grid. The routine is very basic, except for the SQL statement that retrieves the data. Most people don't write these SQL statements manually, but use visual designers such as the one in Visual Studio or Microsoft Access to do that.

   Add the following code to the form. Note that you may have to change the connection string slightly, because it has a reference to the NorthWind database and that file might be in a different folder in your system:

   To write code in Visual Basic

   Visual Basic

   Copy Code

   Private Function GetDataSource() As DataTable ' Set up the connection string. Dim conn As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=C:\Users\<User Name>\Documents\ComponentOne Samples\Common\C1NWind.mdb" ' Set up the SQL statement. Dim rs As String = _ "SELECT Employees.LastName,Orders.ShipCountry," & _ "Categories.CategoryName,Products.ProductName,Orders.OrderDate," & _ "[Quantity]*[Products].[UnitPrice] AS [Sale Amount] " & _ "FROM (Categories INNER JOIN Products " & _ "ON Categories.CategoryID = Products.CategoryID) " & _ "INNER JOIN ((Employees INNER JOIN Orders " & _ "ON Employees.EmployeeID = Orders.EmployeeID) " & _ "INNER JOIN [Order Details] " & _ "ON Orders.OrderID = [Order Details].OrderID) " & _ "ON Products.ProductID = [Order Details].ProductID " & _ "ORDER BY Employees.LastName,Orders.ShipCountry," & _ "Categories.CategoryName;" ' Retrieve the data into the DataSet. Dim da As OleDbDataAdapter = New OleDbDataAdapter(rs, conn) Dim ds As DataSet = New DataSet() da.Fill(ds) ' Return the data table. GetDataSource = ds.Tables(0) End Function

   To write code in C#

   C#

   Copy Code

   private DataTable GetDataSource() { // Set up the connection string. string conn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=C:\\Users\\Windows 8.1\\Documents\\ComponentOne Samples\\Common\\C1NWind.mdb"; // Set up the SQL statement. string rs = "SELECT Employees.LastName,Orders.ShipCountry," + "Categories.CategoryName,Products.ProductName,Orders.OrderDate," + "[Quantity]*[Products].[UnitPrice] AS [Sale Amount] " + "FROM (Categories INNER JOIN Products " + "ON Categories.CategoryID = Products.CategoryID) " + "INNER JOIN ((Employees INNER JOIN Orders " + "ON Employees.EmployeeID = Orders.EmployeeID) " + "INNER JOIN [Order Details] " + "ON Orders.OrderID = [Order Details].OrderID) " + "ON Products.ProductID = [Order Details].ProductID " + "ORDER BY Employees.LastName,Orders.ShipCountry," + "Categories.CategoryName;"; // Retrieve the data into the DataSet. OleDbDataAdapter da = new OleDbDataAdapter(rs, conn); DataSet ds = new DataSet(); da.Fill(ds); // Return the data table. return ds.Tables[0]; }

Run the program and observe the following:

You will see a plain-looking grid that allows you to move columns around and browse through the data. However, the data is not structured in a clear way, and this table contains a couple of thousand records, so it is pretty difficult to get an overview of what the data means.

Copyright © GrapeCity, inc. All rights reserved.