If you have additional pieces of information to include in your spreadsheet, inserting rows or columns can provide room for these new fields. In addition,
adding blank rows or columns between data sets can also help to effectively separate different categories of information, making them easier to read and analyze. This article will demonstrate how to insert rows and columns in Excel in Python using Spire.XLS for Python.

• Insert a Row and a Column in Excel in Python
• Insert Multiple Rows and Columns in Excel in Python

Install Spire.XLS for Python

This scenario requires Spire.XLS for Python and plum-dispatch v1.7.4. They can be easily installed in your Windows through the following pip command.

pip install Spire.XLS

If you are unsure how to install, please refer to this tutorial: How to Install Spire.XLS for Python on Windows

Insert a Row and a Column in Excel in Python

Spire.XLS for Python provides the Worksheet.InsertRow(rowIndex: int) and Worksheet.InsertColumn(columnIndex: int) methods for inserting a blank row and a blank column in an Excel worksheet. The following are the detailed steps:

• Create a Workbook instance.
• Load a sample Excel file using Workbook.LoadFromFile() method.
• Get a specified worksheet using Workbook.Worksheets[sheetIndex] property.
• Insert a row into the worksheet using Worksheet.InsertRow(rowIndex: int) method.
• Insert a column into the worksheet using Worksheet.InsertColumn(columnIndex: int) method.
• Save the result file using Workbook.SaveToFile() method.

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

inputFile = "input.xlsx"
outputFile = "InsertRowAndColumn.xlsx"

# Create a Workbook instance
workbook = Workbook()

# Load an Excel document 
workbook.LoadFromFile(inputFile)

# Get a specified worksheet
worksheet = workbook.Worksheets[0]

# Insert a blank row as the 5th row in the worksheet 
worksheet.InsertRow(5)

# Insert a blank column as the 4th column in the worksheet 
worksheet.InsertColumn(4)

# Save the result file
workbook.SaveToFile(outputFile, ExcelVersion.Version2016)
workbook.Dispose()

Insert Multiple Rows and Columns in Excel in Python

To insert multiple rows and columns into a worksheet, you can use the Worksheet.InsertRow(rowIndex: int, rowCount: int) and Worksheet.InsertColumn(columnIndex: int, columnCount: int) methods. The following are detailed steps.

• Create a Workbook instance.
• Load a sample Excel file using Workbook.LoadFromFile() method.
• Get a specified worksheet using Workbook.Worksheets[sheetIndex] property.
• Insert multiple rows into the worksheet using Worksheet.InsertRow(rowIndex: int, rowCount: int) method.
• Insert multiple columns into the worksheet using Worksheet.InsertColumn(columnIndex: int, columnCount: int) method.
• Save the result file using Workbook.SaveToFile() method.

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

inputFile = "input.xlsx"
outputFile = "InsertRowsAndColumns.xlsx"

# Create a Workbook instance
workbook = Workbook()

# Load an Excel document 
workbook.LoadFromFile(inputFile)

# Get a specified worksheet
worksheet = workbook.Worksheets[0]

# Insert three blank rows into the worksheet
worksheet.InsertRow(5, 3)

#Insert two blank columns into the worksheet
worksheet.InsertColumn(4, 2)

# Save the result file
workbook.SaveToFile(outputFile, ExcelVersion.Version2016)
workbook.Dispose()

Apply for a Temporary License

If you'd like to remove the evaluation message from the generated documents, or to get rid of the function limitations, please request a 30-day trial license for yourself.

Cell borders play a crucial role in enhancing the visual clarity and organization of data in Excel spreadsheets. Adding borders to cells can help draw attention to specific data points, highlight headers, or create clear boundaries between different sections of your worksheet. On the other hand, removing borders can provide a sleek and seamless appearance, especially when you want to eliminate distractions and focus solely on the data itself.

In this comprehensive guide, we will walk you through the process of adding or removing cell borders in Excel by using the Spire.XLS for Python library.

• Add Borders to a Selected Cell or Cell Range in a Worksheet
• Add Borders to the Cell Range Containing Data in a Worksheet
• Add Left, Top, Right, Bottom, Diagonal Borders to a Cell
• Remove Borders of a Cell or Cell Range in a Worksheet

Install Spire.XLS for Python

This scenario requires Spire.XLS for Python and plum-dispatch v1.7.4. They can be easily installed in your Windows through the following pip command.

pip install Spire.XLS

If you are unsure how to install, please refer to this tutorial: How to Install Spire.XLS for Python on Windows

Add Borders to a Selected Cell or Cell Range in a Worksheet in Python

Borders can be applied to individual cells, groups of cells, or even entire ranges to create clear boundaries and make data stand out. By adding borders, you can effectively organize and structure your data, making it easier to analyze and understand.

With Spire.XLS for Python, accessing specific cells or cell ranges is made easy through the Worksheet.Range[name: str] property. Once you have obtained the desired cell or cell range, you can apply an outside border using the CellRange.BorderAround() method. Additionally, you can apply inside borders to a cell range using the CellRange.BorderInside() method.

To apply borders to a cell or cell range, follow the steps below.

• Create a Workbook object.
• Load an Excel document from the specified path.
• Get a specific worksheet from the workbook.
• Get a cell or cell range using Worksheet.Range[name: str] property.
• Apply outside borders to the cell or cell range using CellRange.BorderAround() method.
• Apply inside borders to the cell range using CellRange.BorderInside() method.
• Save the workbook to a new Excel file.

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

# Create a Workbook object
workbook = Workbook()

# Load an Excel document
workbook.LoadFromFile("C:\\Users\\Administrator\\Desktop\\sample.xlsx")

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

# Get a specific cell 
cell = worksheet.Range["C11"]

# Apply borders to the cell
cell.BorderAround(LineStyleType.Medium, Color.get_Black())

# Get a cell range
cellRange = worksheet.Range["B2:C6"]

# Apply outside borders to the cell range
cellRange.BorderAround(LineStyleType.Thin, Color.get_Black())

# Apply inside borders to the cell range
cellRange.BorderInside(LineStyleType.Thin, Color.get_Black())

# Save the document
workbook.SaveToFile("Output/AddBordersToCellRange.xlsx", ExcelVersion.Version2016)

# Dispose resources
workbook.Dispose()

Add Borders to the Cell Range Containing Data in a Worksheet in Python

The range that contains data in a worksheet is commonly referred to as the "allocated range" or "used range". It represents the rectangular area that encompasses all the cells with data, including text, numbers, formulas, and other types of content.

To retrieve the cell range having data, use the Worksheet.AllocatedRange property. Then, you can easily apply borders to this range using the BorderAround() and BorderInside() methods.

The steps to add borders to the cell range containing data are as follows.

• Create a Workbook object.
• Load an Excel document from the specified path.
• Get a specific worksheet from the workbook.
• Get the cell range that contains data using Worksheet.AllocatedRange property.
• Apply outside borders to the cell or cell range using CellRange.BorderAround() method.
• Apply inside borders to the cell range using CellRange.BorderInside() method.
• Save the workbook to a new Excel file.

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

# Create a Workbook object
workbook = Workbook()

# Load an Excel document
workbook.LoadFromFile("C:\\Users\\Administrator\\Desktop\\sample.xlsx")

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

# Get the cell range that contains data
locatedRange = worksheet.AllocatedRange

# Apply outside borders to the cell range
locatedRange .BorderAround(LineStyleType.MediumDashed, Color.get_Black())

# Apply inside borders to the cell range
locatedRange .BorderInside(LineStyleType.Thin, Color.get_Black())

# Save the document
workbook.SaveToFile("Output/AddBordersToLocatedRange.xlsx", ExcelVersion.Version2016)

# Dispose resources
workbook.Dispose()

Add Left, Top, Right, Bottom, Diagonal Borders to a Cell in Python

In addition to applying outside and inside borders, you have the option to add left, top, right, bottom, and diagonal borders to individual cells or cell ranges. This feature allows you to go beyond basic border customization and provides additional flexibility to highlight important information, separate sections within your worksheet, or provide a visual structure to your data.

Spire.XLS provides convenient access to specific borders, including the left, right, top, bottom, and diagonal borders, through properties such as CellRange.Borders[BordersLineType.EdgeLeft]. Once you have obtained the desired border, you have the flexibility to customize its appearance by utilizing the IBorder.LineStyle property and the IBorder.Color property.

To add left, top, right, bottom, diagonal borders to a cell, follow the following steps.

• Create a Workbook object.
• Load an Excel document from the specified path.
• Get a specific worksheet from the workbook.
• Get a specific cell using Worksheet.Range property.
• Get the left, top, right, bottom and diagonal borders of the cell using the properties such as CellRange.Borders[BordersLineType.EdgeLeft].
• Set the line style of the border using IBorder.LineStyle property
• Set the color of the border using IBorder.Color property.
• Save the workbook to a new Excel file.

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

# Create a Workbook object
workbook = Workbook()

# Load an Excel document
workbook.LoadFromFile("C:\\Users\\Administrator\\Desktop\\sample.xlsx")

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

# Get a cell
cell = worksheet.Range["B11"]

# Get the left, top, right, bottom border of the cell 
leftBorder = cell.Borders[BordersLineType.EdgeLeft]
topBorder = cell.Borders[BordersLineType.EdgeTop]
rightBorder = cell.Borders[BordersLineType.EdgeRight]
bottomBorder = cell.Borders[BordersLineType.EdgeBottom]

# Set the border type respectively
leftBorder.LineStyle = LineStyleType.Thick
topBorder.LineStyle = LineStyleType.Dotted
rightBorder.LineStyle = LineStyleType.SlantedDashDot
bottomBorder.LineStyle = LineStyleType.Double

# Set the border color respectively
leftBorder.Color = Color.get_Red()
topBorder.Color = Color.get_Brown()
rightBorder.Color = Color.get_DarkGray()
bottomBorder.Color = Color.get_OrangeRed()

# Get a specific cell 
cell = worksheet.Range["C10"]

# Get the diagonal border of the cell
diagonalBorder = cell.Borders[BordersLineType.DiagonalDown]

# Set the border style
diagonalBorder.LineStyle = LineStyleType.Thin

# Save the document
workbook.SaveToFile("Output/BorderOfEdge.xlsx", ExcelVersion.Version2016)

# Dispose resources
workbook.Dispose()

Remove Borders of a Cell or Cell Range in a Worksheet in Python

Borders can be removed from individual cells, groups of cells, or even entire ranges, allowing you to reduce visual noise and clutter, making your data easier to interpret and analyze. Additionally, you can choose to remove borders from specific sides of a cell, such as the left, top, right, or bottom, which can alter the visual appearance and enhance the overall presentation.

To eliminate borders surrounding or within a cell or cell range, you can easily achieve this by setting the CellRange.Borders.LineStyle property to LineStyleType.none. Similarly, if you want to remove a border on a specific side, such as the left side, you can accomplish this by setting the CellRange.Borders[BordersLineType.EdgeLeft].LineStyle property to LineStyleType.none.

The steps to remove borders of a cell or cell range as follows.

• Create a Workbook object.
• Load an Excel document from the specified path.
• Get a specific worksheet from the workbook.
• Get a specific cell or cell range using Worksheet.Range property.
• Remove all borders of the cell or cell range by setting CellRange.Borders.LineStyle property to LineStyleType.none.
• Save the workbook to a new Excel file.

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

# Create a Workbook object
workbook = Workbook()

# Load an Excel document
workbook.LoadFromFile("C:\\Users\\Administrator\\Desktop\\BorderExample.xlsx")

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

# Get a cell 
cell = worksheet.Range["C11"]

# Remove borders by setting line style to none
cell.Borders.LineStyle = LineStyleType.none

# Remove border on a specific side
# cell.Borders[BordersLineType.EdgeLeft].LineStyle = LineStyleType.none

# Get a cell range
cellRange = worksheet.Range["B2:C6"]

# Remove borders by setting line style to none
cellRange.Borders.LineStyle = LineStyleType.none

# Save the document
workbook.SaveToFile("Output/RemoveBorders.xlsx", ExcelVersion.Version2016)

# Dispose resources
workbook.Dispose()

Apply for a Temporary License

If you'd like to remove the evaluation message from the generated documents, or to get rid of the function limitations, please request a 30-day trial license for yourself.

Deleting rows and columns from Excel is crucial for maintaining clean and organized data. For example, when a worksheet accumulates blank rows or columns that serve no purpose and clutter the data, removing them becomes necessary. By deleting these blank rows and columns, you can effectively reduce the file size and enhance the spreadsheet's readability. In this article, we will explain how to delete rows and columns from Excel in Python using Spire.XLS for Python.

• Delete a Specific Row and Column from Excel in Python
• Delete Multiple Rows and Columns from Excel in Python
• Delete Blank Rows and Columns from Excel in Python

Install Spire.XLS for Python

This scenario requires Spire.XLS for Python and plum-dispatch v1.7.4. They can be easily installed in your Windows through the following pip command.

pip install Spire.XLS

If you are unsure how to install, please refer to this tutorial: How to Install Spire.XLS for Python on Windows

Delete a Specific Row and Column from Excel in Python

The Worksheet.DeleteRow(rowIndex) and Worksheet.DeleteColumn(columnIndex) methods provided by Spire.XLS for Python enable you to delete a specific row and column from an Excel worksheet. The detailed steps are as follows.

• Create an object of the Workbook class.
• Load an Excel file using Workbook.LoadFromFile() method.
• Get the desired worksheet using Workbook.Worksheets[sheetIndex] property.
• Delete the desired row from the worksheet by its index (1-based) using Worksheet.DeleteRow(rowIndex) method.
• Delete the desired column from the worksheet by its index (1-based) using Worksheet.DeleteColumn(columnIndex) method.
• Save the result file using Workbook.SaveToFile() method.

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

# Create a Workbook object
workbook = Workbook()
# Load an Excel file
workbook.LoadFromFile("Input.xlsx")

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

# Delete the 9th row
sheet.DeleteRow(9)
# Delete the 3rd column
sheet.DeleteColumn(3)

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

Delete Multiple Rows and Columns from Excel in Python

Spire.XLS for Python enables you to delete multiple adjacent rows and columns from an Excel worksheet at once by using the Worksheet.DeleteRow(startRowIndex, rowCount) and Worksheet.DeleteColumn(startColumnIndex, columnCount) methods. The detailed steps are as follows.

• Create an object of the Workbook class.
• Load an Excel file using Workbook.LoadFromFile() method.
• Get the desired worksheet using Workbook.Worksheets[sheetIndex] property.
• Delete the desired rows from the worksheet using Worksheet.DeleteRow(startRowIndex, rowCount) method.
• Delete the desired columns from the worksheet using Worksheet.DeleteColumn(startColumnIndex, columnCount) method.
• Save the result file using Workbook.SaveToFile() method.

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

# Create a Workbook object
workbook = Workbook()
# Load an Excel file
workbook.LoadFromFile("Input.xlsx")

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

# Delete the 5th, 6th and 7th rows
sheet.DeleteRow(5, 3)
# Delete the 3rd and 4th columns
sheet.DeleteColumn(3, 2)

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

Delete Blank Rows and Columns from Excel in Python

You can use the Worksheet.Row[rowIndex].IsBlank and Worksheet.Column[columnIndex].IsBlank properties to detect whether a specific row and column are blank or not. If the result is True, you can remove them from your woeksheet using the Worksheet.DeleteRow(rowIndex) and Worksheet.DeleteColumn(columnIndex) methods.

The following steps show how to delete the blank rows and columns from an Excel worksheet.

• Create an object of the Workbook class.
• Load an Excel file using Workbook.LoadFromFile() method.
• Get the desired worksheet using Workbook.Worksheets[sheetIndex] property.
• Loop through the used rows in the worksheet.
• Find the blank rows using Worksheet.Row[rowIndex].IsBlank property and then delete them from the worksheet using Worksheet.DeleteRow(rowIndex) method.
• Loop through the used columns in the worksheet.
• Find the blank columns using Worksheet.Column[columnIndex].IsBlank property and then delete them from the worksheet using Worksheet.DeleteColumn(columnIndex) method.
• Save the result file using Workbook.SaveToFile() method.

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

# Create a Workbook object
workbook = Workbook()
# Load an Excel file
workbook.LoadFromFile("Input1.xlsx")

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

# Delete blank rows from the worksheet
for i in range(sheet.Rows.Length - 1, -1, -1):
    if sheet.Rows[i].IsBlank:
        sheet.DeleteRow(i + 1)

# Delete blank columns from the worksheet
for j in range(sheet.Columns.Length - 1, -1, -1):
    if sheet.Columns[j].IsBlank:
        sheet.DeleteColumn(j + 1)

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

Apply for a Temporary License

If you'd like to remove the evaluation message from the generated documents, or to get rid of the function limitations, please request a 30-day trial license for yourself.

Hiding or unhiding rows and columns in Excel gives you precise control over the visibility of specific data within a worksheet. By hiding rows or columns, you can temporarily remove irrelevant information from view, reducing visual clutter and creating a cleaner workspace. This makes it easier to work with the data that truly matters and enhances your productivity. On the other hand, unhiding rows or columns allows you to restore visibility and regain access to previously hidden information whenever you need it. This is advantageous when you have hidden data that requires further review, modification, or analysis. In this article, we will explain how to hide or unhide rows and columns in Excel in Python using Spire.XLS for Python.

• Hide Specific Rows and Columns in Excel in Python
• Unhide Specific Hidden Rows and Columns in Excel in Python
• Hide Multiple Rows and Columns at Once in Excel in Python
• Unhide All Hidden Rows and Columns in Excel in Python

Install Spire.XLS for Python

This scenario requires Spire.XLS for Python and plum-dispatch v1.7.4. They can be easily installed in your Windows through the following pip command.

pip install Spire.XLS

If you are unsure how to install, please refer to this tutorial: How to Install Spire.XLS for Python on Windows

Hide Specific Rows and Columns in Excel in Python

Spire.XLS for Python offers the Worksheet.HideRow(rowIndex) and Worksheet.HideColumn(columnIndex) methods to hide a specific row and column in an Excel worksheet. The detailed steps are as follows.

• Create an object of the Workbook class.
• Load an Excel file using Workbook.LoadFromFile() method.
• Get a specific worksheet through Workbook.Worksheets[sheetIndex] property.
• Hide specific rows in the worksheet using Worksheet.HideRow(rowIndex) method.
• Hide Specific columns in the worksheet using Worksheet.HideColumn(columnIndex) method.
• Save the result file using Workbook.SaveToFile() method.

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

# Create a Workbook object
workbook = Workbook()
# Load an Excel file
workbook.LoadFromFile("Input.xlsx")

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

# Hide the 3rd and the 7th rows
sheet.HideRow(3)
sheet.HideRow(7)

# Hide the 3rd and the 6th columns
sheet.HideColumn(3)
sheet.HideColumn(6)

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

Unhide Specific Hidden Rows and Columns in Excel in Python

You can use the Worksheet.ShowRow(rowIndex) and Worksheet.ShowColumn(columnIndex) methods to unhide a specific hidden row and column. The detailed steps are as follows.

• Create an object of the Workbook class.
• Load an Excel file using Workbook.LoadFromFile() method.
• Get a specific worksheet through Workbook.Worksheets[sheetIndex] property.
• Unhide specific hidden rows in the worksheet using Worksheet.ShowRow(rowIndex) method.
• Unhide specific hidden columns in the worksheet using Worksheet.ShowColumn(columnIndex) method.
• Save the result file using Workbook.SaveToFile() method.

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

# Create a Workbook object
workbook = Workbook()
# Load an Excel file
workbook.LoadFromFile("HideRowsAndColumns.xlsx")

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

# Unhide the 3rd and the 7th rows
sheet.ShowRow(3)
sheet.ShowRow(7)

# Unhide the 3rd and the 6th columns
sheet.ShowColumn(3)
sheet.ShowColumn(6)

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

Hide Multiple Rows and Columns at Once in Excel in Python

To hide multiple rows and columns at once, you can use the Worksheet.HideRows(startRowIndex, rowCount) and Worksheet.HideColumns(startColumnIndex, columnCount) methods. The detailed steps are as follows.

• Create an object of the Workbook class.
• Load an Excel file using Workbook.LoadFromFile() method.
• Get a specific worksheet through Workbook.Worksheets[sheetIndex] property.
• Hide multiple rows in the worksheet using the Worksheet.HideRows(startRowIndex, rowCount) method.
• Hide multiple columns in the worksheet using Worksheet.HideColumns(startColumnIndex, columnCount) method.
• Save the result file using Workbook.SaveToFile() method.

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

# Create a Workbook object
workbook = Workbook()
# Load an Excel file
workbook.LoadFromFile("Input.xlsx")

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

# Hide 3, 4 and 5 rows
sheet.HideRows(3, 3)

# Hide 5, 6 and 7 columns
sheet.HideColumns(5, 3)

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

Unhide All Hidden Rows and Columns in Excel in Python

To unhide all hidden rows and columns, you first need to loop through the used rows and columns in the worksheet. Next, find the hidden rows and columns using Worksheet.GetRowIsHide(rowIndex) and Worksheet.GetColumnIsHide(columnIndex) methods, and then unhide them using Worksheet.ShowRow(rowIndex) and Worksheet.ShowColumn(columnIndex) methods. The detailed steps are as follows.

• Create an object of the Workbook class.
• Load an Excel file using Workbook.LoadFromFile() method.
• Get a specific worksheet through Workbook.Worksheets[sheetIndex] property.
• Iterate through the used rows in the worksheet and find the hidden rows using Worksheet.GetRowIsHide(rowIndex) method.
• Unhide every hidden row using Worksheet.ShowRow(rowIndex) method.
• Iterate through the used columns in the worksheet and find the hidden columns using Worksheet.GetColumnIsHide(columnIndex) method.
• Unhide every hidden column using Worksheet.ShowColumn(columnIndex) method.
• Save the result file using Workbook.SaveToFile() method.

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

# Create a Workbook object
workbook = Workbook()
# Load an Excel file
workbook.LoadFromFile("HideMultipleRowsAndColumns.xlsx")

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

# Iterate through the used rows in the worksheet
for i in range(1, sheet.LastRow + 1):
    # Check if the current row is hidden
    if sheet.GetRowIsHide(i):
        # Unhide the hidden row
        sheet.ShowRow(i)

# Iterate through the used columns in the worksheet
for j in range(1, sheet.LastColumn + 1):
    # Check if the current column is hidden
    if sheet.GetColumnIsHide(j):
        # Unhide the hidden column
        sheet.ShowColumn(j)

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

Apply for a Temporary License

If you'd like to remove the evaluation message from the generated documents, or to get rid of the function limitations, please request a 30-day trial license for yourself.

Proper row height and column width are essential for optimizing data readability and ensuring that all content is fully visible in Excel. While the default row height and column width settings may be suitable in some cases, they may not be sufficient when dealing with lengthy text, large numbers, or complex formulas. In such cases, it becomes necessary to set appropriate row heights and column widths. In this article, we will explain how to set row height and column width in Excel in Python using Spire.XLS for Python.

• Set Row Height in Excel in Python
• Set Column Width in Excel in Python

Install Spire.XLS for Python

This scenario requires Spire.XLS for Python and plum-dispatch v1.7.4. They can be easily installed in your Windows through the following pip command.

pip install Spire.XLS

If you are unsure how to install, please refer to this tutorial: How to Install Spire.XLS for Python on Windows

Set Row Height in Excel in Python

Spire.XLS for Python provides the Worksheet.SetRowHeight() method to set the height for a specific row.  The detailed steps are as follows:

• Create a Workbook object.
• Load an Excel file using Workbook.LoadFromFile() method.
• Get a specific worksheet using Workbook.Worksheets[] property.
• Set the height of a specific row using Worksheet.SetRowHeight() method.
• Save the resulting file using Workbook.SaveToFile() method.

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

# Create a Workbook object
workbook = Workbook()
# Load an Excel file
workbook.LoadFromFile("Sample.xlsx")

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

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

# Save the resulting file
workbook.SaveToFile("SetRowHeight.xlsx", ExcelVersion.Version2016)
workbook.Dispose()

Set Column Width in Excel in Python

To set the width of a specific column, you can use the Worksheet.SetColumnWidth() method. The detailed steps are as follows:

• Create a Workbook object.
• Load an Excel file using Workbook.LoadFromFile() method.
• Get a specific worksheet using Workbook.Worksheets[] property.
• Set the width of a specific column using Worksheet.SetColumnWidth() method.
• Save the resulting file using Workbook.SaveToFile() method.

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

# Create a Workbook object
workbook = Workbook()
# Load an Excel file
workbook.LoadFromFile("Sample.xlsx")

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

# Set the width of the third column
worksheet.SetColumnWidth(3, 15)

# Save the resulting file
workbook.SaveToFile("SetColumnWidth.xlsx", ExcelVersion.Version2016)
workbook.Dispose()

Apply for a Temporary License

If you'd like to remove the evaluation message from the generated documents, or to get rid of the function limitations, please request a 30-day trial license for yourself.

Text wrapping and unwrapping are powerful formatting options in Microsoft Excel that offer flexibility in displaying text within cells. When text wrapping is enabled, long text is automatically wrapped into multiple lines within a cell, which ensures that the entire content is visible without truncation. This feature is particularly useful for presenting lengthy descriptions, notes, or paragraphs within a confined cell space. On the other hand, text unwrapping allows you to remove line breaks and display the text in a single line within the cell. This can be beneficial in scenarios where you need to fit the text into a specific layout or when exporting data to other applications or file formats that may not handle wrapped text correctly. In this article, we will demonstrate how to wrap or unwrap text in Excel cells in Python using Spire.XLS for Python.

Install Spire.XLS for Python

This scenario requires Spire.XLS for Python and plum-dispatch v1.7.4. They can be easily installed in your Windows through the following pip command.

pip install Spire.XLS

If you are unsure how to install, please refer to this tutorial: How to Install Spire.XLS for Python on Windows

Wrap or Unwrap Text in Excel Cells in Python

Spire.XLS for Python provides the CellStyle.WrapText property to control whether the text should be wrapped or unwrapped within a cell. If you want to wrap text in a cell, you can set the property as True. Conversely, if you want to unwrap text in a cell, you can set the property as False.

The following steps explain how to wrap or unwrap text in an Excel cell using Spire.XLS for Python:

• Create a Workbook object.
• Load a sample Excel file using Workbook.LoadFromFile() method.
• Get a specified worksheet using Workbook.Worksheets[] property.
• Get a specified cell using Worksheet.Range[] property.
• Get the style of the specified cell using CellRange.Style property.
• Wrap the text in the cell by setting the CellStyle.WrapText property to True. Or unwrapping the text in the cell by setting the CellStyle.WrapText property to False.
• Save the resulting file using Workbook.SaveToFile() method.

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

# Create a Workbook object
workbook = Workbook()
# Load a sample Excel file
workbook.LoadFromFile("Sample.xlsx")

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

# Wrap the text in cell B3
sheet.Range["B3"].Style.WrapText = True

# Unwrap the text in cell B7
sheet.Range["B7"].Style.WrapText = False

#Save the resulting file
workbook.SaveToFile("WrapOrUnwrapTextInCells.xlsx", ExcelVersion.Version2013)
workbook.Dispose()

Apply for a Temporary License

If you'd like to remove the evaluation message from the generated documents, or to get rid of the function limitations, please request a 30-day trial license for yourself.

Merging cells means combining multiple adjacent cells into a larger one. The merged cell will inherit all the properties and contents of the original cells. This feature is particularly useful when you need to create a larger cell to accommodate more content or create a header row. Unmerging cells, on the other hand, involves reverting the merged cells back to the original multiple cells. The unmerged cells will revert back to their original independent state, and you can input different content into each individual cell. Merging and unmerging cells are common operations in spreadsheet software, allowing you to adjust the layout and structure of a table as needed, making the data clearer and easier to understand. In this article, you will learn how to merge or unmerge cells in Excel in Python by using Spire.XLS for Python.

• Merge the Cells of the Specified Row or Column
• Merge Ranges of Cells
• Unmerge the Cells of the Specified Row or Column
• Unmerge Ranges of Cells

Install Spire.XLS for Python

This scenario requires Spire.XLS for Python and plum-dispatch v1.7.4. They can be easily installed in your Windows through the following pip command.

pip install Spire.XLS

If you are unsure how to install, please refer to this tutorial: How to Install Spire.XLS for Python on Windows

Merge the Cells of the Specified Row or Column

With Spire.XLS for Python, users are able to effortlessly merge the cells of the specific column or row in Excel, thereby enhancing their data manipulation capabilities. The following are the detailed steps.

• Create an object of Workbook class.
• Load a sample Excel file using Workbook.LoadFromFile() method.
• Get the desired worksheet by using Workbook.Worksheets[] property.
• Access the cells of the specific column or row and merge them by calling Worksheet.Columns[].Merge() or Worksheet.Rows[].Merge() methods.
• Save the result file using Workbook.SaveToFile() method.

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

inputFile = "Sample.xlsx"
outputFile = "MergeRowColumn.xlsx"

#Create an object of Workbook class
workbook = Workbook()

#Load a sample Excel file from disk
workbook.LoadFromFile(inputFile)

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

#Merge the first column in Excel 
#sheet.Columns[0].Merge()

#Merge the first row in Excel 
sheet.Rows[0].Merge()

#Save the result file
workbook.SaveToFile(outputFile, ExcelVersion.Version2013)
workbook.Dispose()

Merge Ranges of Cells

In addition to merging the specific column or row, Spire.XLS for Python also supports users to merge the specified cell ranges. The following are the detailed steps.

• Create an object of Workbook class.
• Load a sample Excel file using Workbook.LoadFromFile() method.
• Get the desired worksheet by using Workbook.Worksheets[] property.
• Access the specific range of cells and merge them together by calling Worksheet.Range[].Merge() method.
• Save the result file using Workbook.SaveToFile() method.

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

inputFile = "Sample.xlsx"
outputFile = "MergeCellRange.xlsx"

#Create an object of Workbook class
workbook = Workbook()

#Load a sample Excel file from disk
workbook.LoadFromFile(inputFile)

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

#Merge the particular cell range in Excel 
sheet.Range["B6:G6"].Merge()

#Save the result file
workbook.SaveToFile(outputFile, ExcelVersion.Version2013)
workbook.Dispose()

Unmerge the Cells of the Specified Row or Column

Additionally, users are also allowed to unmerge the merged cells of the specific column or row at any time with Spire.XLS for Python. The following are the detailed steps.

• Create an object of Workbook class.
• Load a sample Excel file using Workbook.LoadFromFile() method.
• Get the desired worksheet by using Workbook.Worksheets[] property.
• Access the merged cells of the specific column or row and unmerge them by calling Worksheet.Columns[].UnMerge() and Worksheet.Rows[].UnMerge() methods.
• Save the result file using Workbook.SaveToFile() method.

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

inputFile = "MergeRowColumn.xlsx"
outputFile = "UnmergeRowColumn.xlsx"

#Create an object of Workbook class
workbook = Workbook()

#Load a sample file from disk
workbook.LoadFromFile(inputFile)

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

#Unmerge the first column in Excel
#sheet.Columns[0].UnMerge()

#Unmerge the first column in Excel
sheet.Rows[0].UnMerge()

#Save to file.
workbook.SaveToFile(outputFile, ExcelVersion.Version2013)
workbook.Dispose()

Unmerge Ranges of Cells

What's more, users are also able to unmerge the specified cell ranges using Spire.XLS for Python. The following are the detailed steps.

• Create an object of Workbook class.
• Load a sample Excel file using Workbook.LoadFromFile() method.
• Get the desired worksheet by using Workbook.Worksheets[] property.
• Access the specific cell ranges and unmerge them by calling Worksheet.Range[].UnMerge() method.
• Save the result file using Workbook.SaveToFile() method.

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

inputFile = "MergeCellRange.xlsx"
outputFile = "UnmergeCellRange.xlsx"

#Create an object of Workbook class
workbook = Workbook()

#Load a sample file from disk
workbook.LoadFromFile(inputFile)

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

#Unmerge the particular cell range in Excel
sheet.Range["B6:G6"].UnMerge()

#Save to file.
workbook.SaveToFile(outputFile, ExcelVersion.Version2013)
workbook.Dispose()

Apply for a Temporary License

If you'd like to remove the evaluation message from the generated documents, or to get rid of the function limitations, please request a 30-day trial license for yourself.