When a sheet is bound to a data set, columns are assigned to data set fields sequentially. That is, the first data field is assigned to column A, the second to column B, and so on. You can change the assignments to assign any field to any column.
For bound spreadsheets, by default the spreadsheet inherits the width of the columns from the database. To determine your own custom widths, you would either need to set your width after binding the Spread or set DataAutoSizeColumns to false and set your width.
If you have more than one Spread bound to a single data set, you may want to set the AutoGenerateColumns property of the sheet in each Spread to false, so Spread does not bind all the columns. Then you can set the DataField property of the columns in each of the Spreads to the field name from the data set. Then, only that column of the data set is bound to the Spread.
Return to the overall list of tasks in Managing Data Binding.
Using the Properties Window
- Create your data set.
- In the Properties window, select the Spread component.
- Select the DataSource property (for the Spread component) or select the Sheets property and set the DataSource property, and set it equal to the data set.
- If you have not already done so, select the Sheets property for the Spread component.
- Click the button to display the SheetView Collection Editor.
- Click the sheet for which you want to change the column fields.
- Set the sheet’s AutoGenerateColumns property to False, because you want to override the auto-generated column and field mappings.
- In the property list, select the Cells property and click the button to display the Cell, Column, and Row Editor.
- Select the column for which you want to change the column fields.
- Set the DataField property to select one of the available fields, or leave it blank to make the column an unbound column.
- Click OK to close the Cell, Column, and Row Editor.
- Click OK to close the SheetView Collection Editor.
Using a Shortcut
- Create your data set.
- Set the Sheet AutoGenerateColumn property to false, because you want to override the auto-generated column and field mappings.
- Set the FpSpread or Sheet DataSource property equal to the data set.
- For each column in the sheet for which you want to map a field, set the Column’s DataField property to the field name in the data set.
Example
This example code binds the Spread component to a data set then sets the fields to use in the first four columns.
C# | Copy Code |
---|---|
// Turn off automatic column and field mapping. fpSpread1.Sheets[0].AutoGenerateColumns = false; // Bind the component to the data set. fpSpread1.DataSource = dataSet1; // Set the fields for the columns. fpSpread1.Sheets[0].Columns[0].DataField = "Description"; fpSpread1.Sheets[0].Columns[1].DataField = "ID"; fpSpread1.Sheets[0].Columns[2].DataField = "LeadTime"; fpSpread1.Sheets[0].Columns[3].DataField = "Price"; |
VB | Copy Code |
---|---|
' Turn off automatic column and field mapping. FpSpread1.Sheets(0).AutoGenerateColumns = False ' Bind the component to the data set. FpSpread1.DataSource = DataSet1 ' Set the fields for the columns. FpSpread1.Sheets(0).Columns(0).DataField = "Description" FpSpread1.Sheets(0).Columns(1).DataField = "ID" FpSpread1.Sheets(0).Columns(2).DataField = "LeadTime" FpSpread1.Sheets(0).Columns(3).DataField = "Price" |
Using Code
- Create your data set.
- Create a new SheetView object.
- Set the SheetView object AutoGenerateColumn property to false, because you want to override the auto-generated column and field mappings.
- Set the SheetView object DataSource property equal to the data set.
- For each column for which you want to map the fields, set the SheetView object Columns object DataField property to specify the field.
- Assign the SheetView object to a sheet in the component.
Example
This example code creates a bound SheetView object and maps four fields to four columns, then assigns it to a sheet in a Spread component.
C# | Copy Code |
---|---|
// Create a new SheetView object. FarPoint.Win.Spread.SheetView newsheet = new FarPoint.Win.Spread.SheetView(); // Turn off automatic column and field mapping. newsheet.AutoGenerateColumns = false; // Bind the SheetView object to the data set. newsheet.DataSource = dataSet1; // Set the fields for the columns. newsheet.Columns[0].DataField = "Description"; newsheet.Columns[1].DataField = "ID"; newsheet.Columns[2].DataField = "LeadTime"; newsheet.Columns[3].DataField = "Price"; // Assign the SheetView object to the first sheet. fpSpread1.Sheets[0] = newsheet; |
VB | Copy Code |
---|---|
' Create a new SheetView object. Dim newsheet As New FarPoint.Win.Spread.SheetView() ' Turn off automatic column and field mapping. newsheet.AutoGenerateColumns = False ' Bind the SheetView object to the data set. newsheet.DataSource = DataSet1 ' Set the fields for the columns. newsheet.Columns(0).DataField = "Description" newsheet.Columns(1).DataField = "ID" newsheet.Columns(2).DataField = "LeadTime" newsheet.Columns(3).DataField = "Price" ' Assign the SheetView object to the first sheet. FpSpread1.Sheets(0) = newsheet |
Using the Spread Designer
- Create your data set.
- Set the FpSpread or Sheet DataSource property equal to the data set.
- Open the Spread Designer for the Spread component.
- Select the sheet tab for the sheet for which you want to set the column fields.
- Set the AutoGenerateColumn property to false, because you want to override the auto-generated column and field mappings.
- Select the column for which you want to set the data field.
- Set the DataField property to the field you want to display in the selected column.
- Continue to select columns and set their DataField properties until you have set all the columns you want. You can leave some of the columns unbound if you want to do so.
- From the File menu choose Apply and Exit to apply your changes to the component and exit Spread Designer.