Set Column Width and Row Height in Excel – Python Tutorial

When working with Excel files, a well-formatted spreadsheet can greatly enhance readability and give your reports a professional appearance. Whether you need neatly aligned columns or rows tall enough to display multi-line content, being able to adjust column width and row height flexibly is an essential skill. In this tutorial, you’ll learn how to quickly set column widths and row heights in Excel using Python, so you can make your spreadsheets clean, organized, and easy to read.

Installing Spire.XLS for Python

To get started, install the Spire.XLS library using pip:

pip install Spire.XLS

Spire.XLS is a powerful and professional Python library for working with Excel files. It allows you to read, edit, and convert spreadsheets to formats like PDFs, and also supports advanced tasks such as setting column widths and row heights, which we’ll cover in this tutorial.

Set Excel Column Width and Row Height in Python

Whether you create your own Excel spreadsheets or receive them from others, you may often encounter issues where the column widths and row heights don’t match the actual data. For example, long numbers might appear as a series of hash symbols (#####) if the columns are too narrow.

Fortunately, with the help of Spire.XLS, you can easily adjust both column widths and row heights at once to ensure your data is displayed clearly.

• Load the Excel file.

• Get a worksheet.

• Use the Worksheet.SetRowHeight() method to set the height of the first row.

• Use the Worksheet.SetColumnWidth() method to set the width of the second column.

• Save the modified file.

The following code demonstrates how to adjust the height of the first row and the width of the second column using Python:

from spire.xls import *
from spire.xls.common import *

# Load the Excel document
workbook = Workbook()
workbook.LoadFromFile("/Population.xlsx")

# Get the first worksheet
worksheet = workbook.Worksheets[0]

# Set the height of the first row
worksheet.SetRowHeight(1, 18)

# Set the width of the second column
worksheet.SetColumnWidth(2, 18)

# Save the result file
workbook.SaveToFile("/SetColumnWidthAndRowHeight.xlsx", ExcelVersion.Version2016)
workbook.Dispose()

Set AutoFit Column Width and Row Height in Excel with Python

In most cases, Excel files contain various types of data, which often requires adjusting the size of each column and row accordingly. In such scenarios, enabling automatic adjustment of column widths and row heights can resolve the entire worksheet’s layout issues at once, eliminating the need to manually set each column or row. Next, let’s explore how to achieve auto-fit column widths and row heights using Python.

Steps to set autofit column width and row height in Excel using Python

• Load the Excel file.

• Get a worksheet, and get the used cell range.

• Use the CellRange.AutoFitRows() method to make row height autofit data.

• Use the CellRange.AutoFitColumns() method to make column width fit data automatically.

• Save the modified file.

The following code demonstrates how to use Python to automatically adjust column widths and row heights in the first worksheet of an Excel file:

from spire.xls import *
from spire.xls.common import *

# Load the Excel document
workbook = Workbook()
workbook.LoadFromFile("/Population.xlsx")

# Get the first worksheet
sheet = workbook.Worksheets[0]

# Get the used range in the worksheet
range = sheet.AllocatedRange

# Auto-fit row heights
range.AutoFitRows()

# Auto-fit column widths
range.AutoFitColumns()

# Save the result file
workbook.SaveToFile("/AutofitColumnWidthRowHeight.xlsx", ExcelVersion.Version2016)
workbook.Dispose()

The Conclusion

With just a few lines of code, you can easily adjust column widths and row heights in Excel using Python. This not only makes your spreadsheets more readable but also gives them a professional look. Try it out in your own projects to streamline your Excel formatting tasks.