Customizing Columns and Rows

Spread provides many options for customizing the column and row appearance and the way your user can interact with Spread.

Insert/Delete Columns and Rows | Set the Width of Columns | Set the Height of Rows | Resize a Column to Fit the Widest Text | Resize a Row to Fit the Tallest Text | Prevent Resizing of Columns and Rows | Set the Max. Number of Columns and Rows | Set the Number of Columns and Rows to the Last Column or Rows that Contains Data | Hide Columns and Rows | Freeze Columns and Rows from Scrolling | Set Odd/Even Row and Selection Colors | Specify Header Text | Hide the Column and Row Headers | Display a Specified Number of Columns and Rows | Designate the Leftmost Column to Display | Designate the Topmost Row to Display | Providing Column IDs

Insert/Delete Columns and Rows
You can easily insert or delete columns and rows when needed. The following example inserts one column beginning at column 2 and one row beginning at row 2: fpSpread1.InsertCols 2, 2 fpSpread1.InsertRows 2, 2

Set the Width of Columns
Your user can change the width of a column using the mouse or you can set it programatically and optionally prevent them from resizing. The following example sets the width for column 2 equal to 10: fpSpread1.ColWidth(2) = 10

Set the Height of Rows
Your user can change the height of a row using the mouse or you can set it programatically and optionally prevent them from resizing. The following example sets the height for row 2 equal to 20. fpSpread1.RowHeight(2) = 20

Resize a Column to Fit the Widest Text
You can have Spread automatically calculate the needed width of the column to display all of the text. The following example resizes the width of column two to display all the text in that column. fpSpread1.ColWidth(2) = fpSpread1.MaxTextColWidth(2)

Resize a Row to Fit the Tallest Text
You can have Spread automatically calculate the needed height of the row to display all of the text. This is especially useful when you allow multiple lines of text in a cell and want to automatically resize the height of the row to display all of the text contained within the cell. The following example resizes the height of row two to display all the text in that row. fpSpread1.RowHeight(2) = fpSpread1.MaxTextRowHeight(2)

Prevent Resizing of Columns and Rows
By default, users can resize both columns and rows with the mouse at run time. You can prevent resizing of columns, rows, or both. You can also prevent resizing of individual columns or rows. The following example prevents the user from being able to resize the columns using the mouse. You can also use the UserResizeCol and UserResizeRow properties to prevent individual rows and columns from being resized. fpSpread1.UserResize = UserResizeRows

Set the Maximum Number of Columns and Rows
Spread supports up to two billion rows and two billion columns. You can set the maximum number of columns and rows to whatever your application needs. The following example sets the maximum numbers of columns to two and rows to five. fpSpread1.MaxCols = 2 fpSpread1.MaxRows = 5

Set the Number of Columns and Rows to the Last Column or Rows that Contains Data
There are many occasions where you will want to set the maximum number of columns and rows equal to the amount of data read in, for example, from a database. Spread provides an easy way to find the last row and column that contains data. The following example sets the maximum number of columns and rows equal to the last column and row that contain data. fpSpread1.MaxCols = fpSpread1.DataColCnt fpSpread1.MaxRows = fpSpread1.DataRowCnt

Hide Columns and Rows
You can hide a column or row in a spreadsheet so it is not visible to your user. You can also hide the column headers and row headers. The following example hides column 2 (LastName column) and row 2. 'Hide Column 2 fpSpread1.Col = 2 fpSpread1.ColHidden = True ' Hide Row 2 fpSpread1.Row = 2 fpSpread1.RowHidden = True

Freeze Columns and Rows from Scrolling
You can freeze (make nonscrollable) any number of columns and rows, allowing you to always display these on the screen. The frozen columns are always the far left columns. The frozen rows are always the top rows. The following example freezes the first two rows and columns from scrolling, always showing row 1 and 2 and the ID and LastName columns. fpSpread1.ColsFrozen = 2 fpSpread1.RowsFrozen = 2

Set Odd/Even Row and Selection Colors
You can set the background and text colors for odd and even rows and change the highlight color of the selected row. The following example sets the spreadsheet to display different colors for odd and even rows. It also specifies the background and foreground colors to display for a row or column when the row or column is selected. ' Set odd/even row colors x = fpSpread1.SetOddEvenRowColor(&HC0FFFF, &H808000, &HC0E0FF, &H800000) ' Set selection background color fpSpread1.SelBackColor = &HC00000; ' Set selection foreground color fpSpread1.SelForeColor = &HFFFFFF

Specify Header Text
You can display custom text in the column headers and the row headers (instead of displaying sequential numbers or letters). The following example sets custom column and row header text fpSpread1.SetText 2, 0, "col text" fpSpread1.SetText 0, 2, "row text"

Hide the Column and Row Headers
By default, the fpSpread control displays the column headers and the row headers. You can hide the column headers, the row headers, or both. The following example hides both the column and row headers. fpSpread1.DisplayColHeaders = False fpSpread1.DisplayRowHeaders = False

Designate the Leftmost Column to Display
You can programatically set the number of the column to position as the leftmost column in the displayed spreadsheet. The following example positions column 5 to be the leftmost displayed column. fpSpread1.LeftCol = 5

Designate the Topmost Row to Display
You can programatically set the row to position as the topmost row in the displayed spreadsheet. The following example sets the top row of the spreadsheet to 5. fpSpread1.TopRow = 5

Providing Column IDs
You can provide a column identification string, or ID, for any or all columns in the spreadsheet. You might want to provide column IDs if your spreadsheet has numerous columns. For example, you might want to set the column IDs to be the field names used in a database you are binding to the control. Thereafter, you can refer to the columns by their IDs instead of by their numbers. The following example names column two 'LastName' fpSpread1.Col = 2 fpSpread1.ColID = "LastName"

Customizing Columns and Rows