AutoFit Rows and Columns

Microsoft Excel lets users auto size the width and height of cells according to its content. This feature is also available through Aspose.Cells for Python via .NET so developers can auto size the dimensions of a cell at runtime.

Auto Fitting

Aspose.Cells for Python via .NET provides a Workbook class that represents a Microsoft Excel file. The Workbook class contains a worksheets collection that allows access to each worksheet in an Excel file. A worksheet is represented by the Worksheet class. The Worksheet class provides a wide range of properties and methods for managing a worksheet. This article looks at using the Worksheet class to autofit rows or columns.

AutoFit Row - Simple

The most straight-forward approach to auto-sizing the width and height of a row is to call the Worksheet class auto_fit_row method. The auto_fit_row method takes a row index (of the row to be resized) as a parameter.

	from aspose.cells import Workbook

	# For complete examples and data files, please go to https:# github.com/aspose-cells/Aspose.Cells-for-.NET
	# The path to the documents directory.
	dataDir = RunExamples.GetDataDir(".")
	InputPath = dataDir + "Book1.xlsx"
	# Creating a file stream containing the Excel file to be opened
	fstream = open(InputPath, "rb")
	# Opening the Excel file through the file stream
	workbook = Workbook(fstream)
	# Accessing the first worksheet in the Excel file
	worksheet = workbook.worksheets[0]
	# Auto-fitting the 3rd row of the worksheet
	worksheet.auto_fit_row(1)
	# Saving the modified Excel file
	workbook.save(dataDir + "output.xlsx")
	# Closing the file stream to free all resources
	fstream.close()

view raw RowsColumns-AutofitRowsandColumns-1.py hosted with ❤ by GitHub

How to AutoFit Row in a Range of Cells

A row is composed of many columns. Aspose.Cells for Python via .NET allows developers to auto-fit a row based on the content in a range of cells within the row by calling an overloaded version of the auto_fit_row method. It takes the following parameters:

Row index, the index of the row about to be auto-fitted.
First column index, the index of the row’s first column.
Last column index, the index of the row’s last column.

The auto_fit_row method checks the contents of all the columns in the row and then auto-fits the row.

	from aspose.cells import Workbook

	# For complete examples and data files, please go to https:# github.com/aspose-cells/Aspose.Cells-for-.NET
	# The path to the documents directory.
	dataDir = RunExamples.GetDataDir(".")
	InputPath = dataDir + "Book1.xlsx"
	# Creating a file stream containing the Excel file to be opened
	fstream = open(InputPath, "rb")
	# Opening the Excel file through the file stream
	workbook = Workbook(fstream)
	# Accessing the first worksheet in the Excel file
	worksheet = workbook.worksheets[0]
	# Auto-fitting the 3rd row of the worksheet
	worksheet.auto_fit_row(1, 0, 5)
	# Saving the modified Excel file
	workbook.save(dataDir + "output.xlsx")
	# Closing the file stream to free all resources
	fstream.close()

view raw RowsColumns-AutofitRowinSpecificRange-1.py hosted with ❤ by GitHub

How to AutoFit Column in a Range of Cells

A column is composed of many rows. It is possible to auto-fit a column based on the content in a range of cells in the column by calling an overloaded version of auto_fit_column method that takes the following parameters:

Column index, the index of the column about to be auto-fitted.
First row index, the index of the column’s first row.
Last row index, the index of the column’s last row.

The auto_fit_column method checks the contents of all rows in the column and then auto-fits the column.

	from aspose.cells import Workbook

	# For complete examples and data files, please go to https:# github.com/aspose-cells/Aspose.Cells-for-.NET
	# The path to the documents directory.
	dataDir = RunExamples.GetDataDir(".")
	InputPath = dataDir + "Book1.xlsx"
	# Creating a file stream containing the Excel file to be opened
	fstream = open(InputPath, "rb")
	# Opening the Excel file through the file stream
	workbook = Workbook(fstream)
	# Accessing the first worksheet in the Excel file
	worksheet = workbook.worksheets[0]
	# Auto-fitting the Column of the worksheet
	worksheet.auto_fit_column(4, 4, 6)
	# Saving the modified Excel file
	workbook.save(dataDir + "output.xlsx")
	# Closing the file stream to free all resources
	fstream.close()

view raw RowsColumns-AutofitColumninSpecificRange-1.py hosted with ❤ by GitHub

How to AutoFit Rows for Merged Cells

With Aspose.Cells for Python via .NET it is possible to autofit rows even for cells that have been merged using the AutoFitterOptions API. AutoFitterOptions class provides auto_fit_merged_cells_type property that can be used to autofit rows for merged cells. auto_fit_merged_cells_type accepts AutoFitMergedCellsType enumerable which has the following members.

NONE: Ignore merged cells.
FIRST_LINE: Only expands the height of the first row.
LAST_LINE: Only expands the height of the last row.
EACH_LINE: Only expands the height of each row.

	from aspose.cells import AutoFitMergedCellsType, AutoFitterOptions, Workbook

	# For complete examples and data files, please go to https:# github.com/aspose-cells/Aspose.Cells-for-.NET
	# Output directory
	outputDir = RunExamples.Get_OutputDirectory()
	# Instantiate a new Workbook
	wb = Workbook()
	# Get the first (default) worksheet
	_worksheet = wb.worksheets[0]
	# Create a range A1:B1
	range = _worksheet.cells.create_range(0, 0, 1, 2)
	# Merge the cells
	range.merge()
	# Insert value to the merged cell A1
	_worksheet.cells.get(0, 0).value = "A quick brown fox jumps over the lazy dog. A quick brown fox jumps over the lazy dog....end"
	# Create a style object
	style = _worksheet.cells.get(0, 0).get_style()
	# Set wrapping text on
	style.is_text_wrapped = True
	# Apply the style to the cell
	_worksheet.cells.get(0, 0).set_style(style)
	# Create an object for AutoFitterOptions
	options = AutoFitterOptions()
	# Set auto-fit for merged cells
	options.auto_fit_merged_cells_type = AutoFitMergedCellsType.EACH_LINE
	# Autofit rows in the sheet(including the merged cells)
	_worksheet.auto_fit_rows(options)
	# Save the Excel file
	wb.save(outputDir + "AutofitRowsforMergedCells.xlsx")

view raw RowsColumns-AutofitRowsforMergedCells-1.py hosted with ❤ by GitHub

You may also try to use the overloaded versions of auto_fit_rows & auto_fit_columns methods accepting a range of rows/columns and an instance of AutoFitterOptions to auto-fit the selected rows/columns with your desired AutoFitterOptions accordingly.

The signatures of the aforesaid methods are as follow:

auto_fit_rows(start_row, end_row, options options)
auto_fit_columns(first_column, last_column, options)

Important to Know

If a cell is merged then the AutoFit methods will not be applied, which is the same behavior as in Microsoft Excel. You can get around this by using the auto filter API. Moreover, if the text in a cell is wrapped, the auto_fit_column method will not be applied either. Another thing you need to know is that the AutoFit methods are time-consuming. So, you should call these methods as seldom as possible to ensure the efficiency of your application.

Advance topics

AutoFit Rows for Merged Cells

Convert Text to Columns using Aspose.Cells