Resizing Rows and Columns
Worksheets in RadSpreadProcessing's document model consist of cells organized in rows and columns. The API of the model allows you to set the width of each column and the height of each row. Additionally, you can choose to use the autofit feature that sizes the rows and columns based on their current content. This article demonstrates the different options for changing row height and column width.
Row Height
The height of a row is retrieved and changed through an instance of type RowHeight. The class exposes two properties: Value, which holds the height of the row, and IsCustom that indicates whether the height is set by the user. If the IsCustom property is set to false, the row height changes automatically in certain cases, for example when you increase the font size of a cell that contains a number value and its content no longer fits in the available size. However, if you increase the font size and the IsCustom property is set to true, the row height is not going to change and part of the cell content would stay hidden.
In order to change the rows' height, you need to create a RowSelection instance that includes the rows to be resized. The RowSelection class exposes GetHeight(), SetHeight() and ClearHeight() methods that are used to manipulate the height of the selected rows.
The GetHeight() method returns a RangePropertyValue
IsIndeterminate: Indicates whether the value of RowHeight is consistent among all rows in the specified RowSelection. If the RowHeight has one and the same value for all rows, IsIndeterminate is set to false. However, if the value of RowHeight varies throughout the rows in the RowSelection, the IsIndeterminate property is set to true and the Value property of the RangePropertyValue
class returns the default RowHeight. Value: Holds the actual RowHeight. If the IsIndeterminate property is set to false, Value contains the RowHeight for the whole RowSelection region. If the IsIndeterminate property is set to true, this indicates that the RowHeight is not the same for all rows in the RowSelection and the Value property is set to its default value.
The SetHeight() method is used to change the height of rows. It takes a single argument of type RowHeight which specifies the new height. The ClearHeight() method is used to reset the RowHeight of the selected rows to the default height. Note that the default row height can be manipulated through the GetDefaultHeight() and SetDefaultHeight() methods exposed by the Rows class.
Example 1 shows how to retrieve and change the height of several rows.
Example 1: Change row height
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets.Add();
RowSelection rowSelection = worksheet.Rows[5, 7];
double rowsHeight = rowSelection.GetHeight().Value.Value;
rowSelection.SetHeight(new RowHeight(rowsHeight + 50, true));
Auto Fit Rows Height
The autofit feature offers a handy approach for resizing multiple rows so that each of them chooses a height that fits its content. To autofit the height of rows, you need to create a RowSelection instance that contains the rows that need to be resized and invoke the AutoFitHeight() method of the RowSelection object. Example 2 shows how to fit the height of rows with indexes 6, 7 and 8.
Example 2: Fit height of rows
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets.Add();
RowSelection rowSelection = worksheet.Rows[5, 7];
rowSelection.AutoFitHeight();
The expected behavior when calling the AutoFitHeight method on a row that contains merged and wrapped cells is to set the default RowHeight value instead of calculating the row height according to its content. In order to measure the cell content you can check the exposed by the LayoutHelper class methods.
Columns Width
The width of a column is retrieved and changed through an instance of type ColumnWidth. The class exposes two properties: Value, which holds the width of the column, and IsCustom that indicates whether the width is set by the user. If the IsCustom property is set to false, the column width changes automatically in certain cases, for example when you increase the font size of a cell that contains a number value and its content no longer fits in the available size. However, if you increase the font size and the IsCustom property is set to true, the column width is not going to change and part of the cell content would stay hidden.
In order to change the columns' width, you need to create a ColumnSelection instance that includes the columns to be resized. The ColumnSelection class exposes GetWidth(), SetWidth() and ClearWidth() methods that are used to manipulate the width of the selected columns.
The GetWidth() method returns a RangePropertyValue
IsIndeterminate: Indicates whether the value of ColumnWidth is consistent among all columns in the specified ColumnSelection. If the ColumnWidth has one and the same value for all columns, IsIndeterminate is set to false. However, if the value of ColumnWidth varies throughout the columns in the ColumnSelection, the IsIndeterminate property is set to true and the Value property of the RangePropertyValue
class is set to its default value. Value: Holds the actual ColumnWidth. If the IsIndeterminate property is set to false, Value contains the ColumnWidth for the whole ColumnSelection region. If the IsIndeterminate property is set to true, this indicates that the ColumnWidth is not the same for all columns in the ColumnSelection and the Value property is set to its default value.
The SetWidth() method is used to change the width of columns. It takes a single argument of type ColumnWidth that specifies the new width. The ClearWidth() method is used to reset the ColumnWidth of the selected columns to the default width. Note that the default column width can be manipulated through the GetDefaultWidth() and SetDefaultWidth() methods exposed by the Columns class.
Example 3 shows how to retrieve and change the width of several columns.
Example 3: Change columns width
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets.Add();
ColumnSelection columnSelection = worksheet.Columns[5, 7];
double columnWidth = columnSelection.GetWidth().Value.Value;
columnSelection.SetWidth(new ColumnWidth(columnWidth + 50, true));
Auto Fit Columns Width
The autofit feature offers a handy approach for resizing multiple columns so that each of them chooses a width that fits its content. To autofit the columns, you need to create a ColumnSelection instance that holds the columns to be resized, and invoke its AutoFitWidth() method. Example 4 shows how to fit the column width of columns F to H.
Example 4: Fit width of columns
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets.Add();
ColumnSelection columnSelection = worksheet.Columns[5, 7];
columnSelection.AutoFitWidth();
Another way to auto fit column widths is to use the ExpandToFitNumberValuesWidth() method. It affects cells that contain only number values and have a ColumnWidth with IsCustom property set to true. Example 5 demonstrates the alternative way to fit the column width.
Example 5: Fit with ExpandToFitNumberValuesWidth()
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets.Add();
ColumnSelection columnSelection = worksheet.Columns[5, 7];
columnSelection.ExpandToFitNumberValuesWidth();
The unit type used to set the width of the columns and the height of the rows in RadSpreadProcessing is Device Independent Pixels (DIPs).
Telerik RadSpreadProcessing vs MS Excel
In the other hand, MS Excel uses its own measurement unit, so in order to convert between pixel and MS Excel width you can use the following methods of the UnitHelper class in combination with the SetWidth() method:
- PixelWidthToExcelColumnWidth: Converts column width in pixels to MS Excel column width.
Example 6 shows how to convert and set from pixel to MS Excel column width.
Example 6: Convert from pixel column width to MS Excel column width
double pixelWidthToExcelWidth = UnitHelper.PixelWidthToExcelColumnWidth(workbook, 57);
ColumnWidth newColumnWidth = new ColumnWidth(pixelWidthToExcelWidth, isCustom: true);
worksheet.Columns[0].SetWidth(newColumnWidth);
- ExcelColumnWidthToPixelWidth: Converts MS Excel column width to pixels width.
Example 7 shows how to convert and set from MS Excel to pixel column width.
Example 7: Convert from MS Excel column width to pixel column width
double excelWidthToPixelWidth = UnitHelper.ExcelColumnWidthToPixelWidth(workbook, 8.11);
ColumnWidth newColumnWidth = new ColumnWidth(excelWidthToPixelWidth, isCustom: true);
worksheet.Columns[0].SetWidth(newColumnWidth);
LayoutHelper class
The LayoutHelper class exposes the following methods that help to calculate the size of the page content:
- CalculateCellContentSize: Calculates the size of the cell content.
Example 8: Get the Size of the cell content
Size cellContentSize = LayoutHelper.CalculateCellContentSize(worksheet, rowIndex, columnIndex);
- CalculateCellLayoutBox: Creates, arranges and returns the cell layout box.
Example 9: Get the cell layout box
CellLayoutBox cellLayoutBox = LayoutHelper.CalculateCellLayoutBox(worksheet, rowIndex, columnIndex);