Reordering columns or rows in Excel is a simple process that allows you to change the arrangement of data within your spreadsheet. This can be useful for better organizing your data or aligning it with other columns or rows. You can reorder by using drag-and-drop, cut and paste, or keyboard shortcuts depending on the version of Excel you are using.

This article focus on introducing how to programmatically reorder columns or rows in an Excel worksheet in Python using Spire.XLS for Python.

• Reorder Columns in Excel in Python
• Reorder Rows 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 system 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

Reorder Columns in Excel in Python

Spire.XLS does not provide a straightforward way to reorganize the order of columns or rows within an Excel worksheet. The solution requires creating a duplicate of the target worksheet. Then, you can copy the columns or rows from the copied worksheet and paste them into the original worksheet in the new preferred column or row sequence.

The following are the steps to reorder columns in an Excel worksheet using Python.

• Create a Workbook object.
• Load an Excel document from the specified file path.
• Get the target worksheet using Workbook.Worksheets[index] property.
• Specify the new column order within a list.
• Create a temporary sheet and copy the data from the target sheet into it.
• Copy the columns from the temporary worksheet to the target worksheet in the desired order using Worksheet.Columns[index].Copy() method.
• Remove the temporary sheet.
• Save the workbook to a different Excel document.

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

# Create a Workbook object
workbook = Workbook()

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

# Get a specific worksheet
targetSheet = workbook.Worksheets[0]

# Specify the new column order in a list (the column index starts from 0)
newColumnOrder = [3, 0, 1, 2, 4, 5 ,6, 7]

# Add a temporary worksheet
tempSheet = workbook.Worksheets.Add("temp")

# Copy data from the target worksheet to the temporary sheet
tempSheet.CopyFrom(targetSheet)

# Iterate through the newColumnOrder list
for i in range(len(newColumnOrder)):

    # Copy the column from the temporary sheet to the target sheet in the new order
    tempSheet.Columns[newColumnOrder[i]].Copy(targetSheet.Columns[i], True, True)

    # Reset the column width in the target sheet
    targetSheet.Columns[i].ColumnWidth = tempSheet.Columns[newColumnOrder[i]].ColumnWidth

# Remove the temporary sheet
workbook.Worksheets.Remove(tempSheet)

# Save the workbook to another Excel file
workbook.SaveToFile("output/ReorderColumns.xlsx", FileFormat.Version2016)

# Dispose resources
workbook.Dispose()

Reorder Rows in Excel in Python

Rearranging the rows in an Excel spreadsheet follows a similar approach to reorganizing the columns. The steps to reorder the rows within an Excel worksheet are as outlined below.

• Create a Workbook object.
• Load an Excel document from the specified file path.
• Get the target worksheet using Workbook.Worksheets[index] property.
• Specify the new row order within a list.
• Create a temporary sheet and copy the data from the target sheet into it.
• Copy the rows from the temporary worksheet to the target worksheet in the desired order using Worksheet.Rows[index].Copy() method.
• Remove the temporary sheet.
• Save the workbook to a different Excel document.

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

# Create a Workbook object
workbook = Workbook()

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

# Get a specific worksheet
targetSheet = workbook.Worksheets[0]

# Specify the new row order in a list (the row index starts from 0)
newRowOrder = [0, 2, 3, 1, 4, 5 ,6, 7, 8, 9, 10, 11, 12]

# Add a temporary worksheet
tempSheet = workbook.Worksheets.Add("temp")

# Copy data from the first worksheet to the temporary sheet
tempSheet.CopyFrom(targetSheet)

# Iterate through the newRowOrder list
for i in range(len(newRowOrder)):

    # Copy the row from the temporary sheet to the target sheet in the new order
    tempSheet.Rows[newRowOrder[i]].Copy(targetSheet.Rows[i], True, True)

    # Reset the row height in the target sheet
    targetSheet.Rows[i].RowHeight = tempSheet.Rows[newRowOrder[i]].RowHeight

# Remove the temporary sheet
workbook.Worksheets.Remove(tempSheet)

# Save the workbook to another Excel file
workbook.SaveToFile("output/ReorderRows.xlsx", FileFormat.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.

Document properties provide additional information about an Excel file, such as author, title, subject, and other metadata associated with the file. Retrieving these properties from Excel can help users gain insight into the file content and history, enabling better organization and management of files. At times, users may also need to remove document properties to protect the privacy and confidentiality of the information contained in the file. In this article, you will learn how to read or remove document properties in Excel in Python using Spire.XLS for Python.

• Read Standard and Custom Document Properties in Excel
• Remove Standard and Custom Document Properties in Excel

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

Read Standard and Custom Document Properties in Excel in Python

Excel properties are divided into two main categories:

• Standard Properties: These are predefined properties that are built into Excel files. They typically include basic details about the file such as title, subject, author, keywords, etc.
• Custom Properties: These are user-defined attributes that can be added to Excel to track additional information about the file based on your specific needs.

Spire.XLS for Python allows to read both the standard and custom document properties of an Excel file. The following are the detailed steps:

• Create a Workbook instance.
• Load an Excel file using Workbook.LoadFromFile() method.
• Create a StringBuilder instance.
• Get a collection of all standard document properties using Workbook.DocumentProperties property.
• Get specific standard document properties using the properties of the BuiltInDocumentProperties class and append them to the StringBuilder instance.
• Get a collection of all custom document properties using Workbook.CustomDocumentProperties property.
• Iterate through the collection.
• Get the name, type, and value of each custom document property using ICustomDocumentProperties[].Name, ICustomDocumentProperties[].PropertyType and ICustomDocumentProperties[].Value properties.
• Determine the specific property type, and then convert the property value to the value of the corresponding data type.
• Append the property name and converted property value to the StringBuilder instance using StringBuilde.append() method.
• Write the content of the StringBuilder instance into a txt file.

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

def AppendAllText(fname: str, text: List[str]):
    fp = open(fname, "w")
    for s in text:
        fp.write(s + "\n")
    fp.close()

inputFile = "Budget Template.xlsx"
outputFile = "GetExcelProperties.txt"

# Create a Workbook instance
workbook = Workbook()

# Load an Excel document from disk
workbook.LoadFromFile(inputFile)

# Create a StringBuilder instance
builder = []

# Get a collection of all standard document properties
standardProperties = workbook.DocumentProperties

# Get specific standard properties and append them to the StringBuilder instance
builder.append("Standard Document Properties:")
builder.append("Title: " + standardProperties.Title)
builder.append("Subject: " + standardProperties.Subject)
builder.append("Category: " + standardProperties.Category)
builder.append("Keywords: " + standardProperties.Keywords)
builder.append("Comments: " + standardProperties.Comments)
builder.append("")

# Get a collection of all custom document properties
customProperties = workbook.CustomDocumentProperties

builder.append("Custom Properties:")
# Iterate through the collection
for i in range(len(customProperties)):
    
    # Get the name, type, and value of each custom document property
    name = customProperties[i].Name
    type = customProperties[i].PropertyType
    obj = customProperties[i].Value

    # Determine the specific property type, and then convert the property value to the value of the corresponding data type
    value = None
    if type == PropertyType.Double:
        value = Double(obj).Value
    elif type == PropertyType.DateTime:
        value = DateTime(obj).ToShortDateString()
    elif type == PropertyType.Bool:
        value = Boolean(obj).Value
    elif type == PropertyType.Int:
        value = Int32(obj).Value
    elif type == PropertyType.Int32:
        value = Int32(obj).Value
    else:
        value = String(obj).Value

    # Append the property name and converted property value to the StringBuilder instance   
    builder.append(name + ": " + str(value))

# Write the content of the StringBuilder instance into a text file
AppendAllText(outputFile, builder)
workbook.Dispose()

Remove Standard and Custom Document Properties in Excel in Python

You can easily delete standard document properties from an Excel file by setting their values as empty. For custom document properties, you can use the ICustomDocumentProperties.Remove() method to delete them. The following are the detailed steps:

• Create a Workbook instance.
• Load a sample Excel file using Workbook.LoadFromFile() method.
• Get a collection of all standard document properties using Workbook.DocumentProperties property.
• Set the values of specific standard document properties as empty through the corresponding properties of the BuiltInDocumentProperties class.
• Get a collection of all custom document properties using Workbook.CustomDocumentProperties property.
• Iterate through the collection.
• Delete each custom property from the collection by its name using ICustomDocumentProperties.Remove() method.
• Save the result file using Workbook.SaveToFile() method.

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

inputFile = "Budget Template.xlsx"
outputFile = "RemoveExcelProperties.xlsx"

# Create a Workbook instance
workbook = Workbook()

# Load an Excel document from disk
workbook.LoadFromFile(inputFile)

# Get a collection of all standard document properties
standardProperties = workbook.DocumentProperties

# Set the value of each standard document property as empty
standardProperties.Title = ""
standardProperties.Subject = ""
standardProperties.Category = ""
standardProperties.Keywords = ""
standardProperties.Comments = ""

# Get a collection of all custom document properties
customProperties = workbook.CustomDocumentProperties

# Iterate through the collection
for i in range(len(customProperties) - 1, -1, -1):
    # Delete each custom document property from the collection by its name
    customProperties.Remove(customProperties[i].Name)

# 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.

In Microsoft Excel, text alignment and text orientation are crucial formatting options for optimizing the presentation of text within cells. Text alignment determines the horizontal or vertical positioning of text within a cell, while text orientation controls the tilt angle or display direction of the text. By flexibly utilizing these formatting options, you can customize the appearance of text within cells to create professional and visually appealing spreadsheets. In this article, we will demonstrate how to set text alignment and orientation in Excel 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

Set Text Alignment and Orientation in Excel in Python

Spire.XLS for Python provides the CellRange.Style.HorizontalAlignment and CellRange.Style.VerticalAlignment properties that enable you to customize the horizontal and vertical alignment of text in a single cell or range of cells. Additionally, it allows you to change the orientation of text by applying rotation to cells using the CellRange.Style.Rotation property. The detailed steps are as follows:

• Create an object of the Workbook class.
• Load an Excel file using the Workbook.LoadFromFile() method.
• Get a specific worksheet using the Workbook.Worksheets[index] property.
• Set the horizontal alignment for text in specific cells to Left, Center, Right, or General using the CellRange.Style.HorizontalAlignment property.
• Set the vertical alignment for text in specific cells to Top, Center, or Bottom using the CellRange.Style.VerticalAlignment property.
• Change the orientation for text in specific cells using the CellRange.Style.Rotation property.
• Save the result file using the Workbook.SaveToFile() method.

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

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

# Load an Excel file
workbook.LoadFromFile("Example.xlsx")

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

# Set the horizontal alignment for text in a specific cell to Left
sheet.Range["B1"].Style.HorizontalAlignment = HorizontalAlignType.Left
# Set the horizontal alignment for text in a specific cell to Center
sheet.Range["B2"].Style.HorizontalAlignment = HorizontalAlignType.Center
# Set the horizontal alignment for text in a specific cell to Right
sheet.Range["B3"].Style.HorizontalAlignment = HorizontalAlignType.Right
# Set the horizontal alignment for text in a specific cell to General
sheet.Range["B4"].Style.HorizontalAlignment = HorizontalAlignType.General

# Set the vertical alignment for text in a specific cell to Top
sheet.Range["B5"].Style.VerticalAlignment = VerticalAlignType.Top
# Set the vertical alignment for text in a specific cell to Center
sheet.Range["B6"].Style.VerticalAlignment = VerticalAlignType.Center
# Set the vertical alignment for text in a specific cell to Bottom
sheet.Range["B7"].Style.VerticalAlignment = VerticalAlignType.Bottom

# Change the text orientation in specific cells by applying rotation
sheet.Range["B8"].Style.Rotation = 45
sheet.Range["B9"].Style.Rotation = 90

# Save the result file
workbook.SaveToFile("TextAlignmentAndOrientation.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.

The AutoFit feature in Microsoft Excel is a handy tool that allows you to automatically adjust the height of rows or the width of columns in an Excel spreadsheet to fit the content within them. This feature is particularly useful when you have data that may vary in length or when you want to ensure that all the content is visible without having to manually adjust the column widths or row heights. In this article, we will explain how to AutoFit rows and columns in Excel in Python using Spire.XLS for Python.

• AutoFit a Specific Row and Column in Python
• AutoFit 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

AutoFit a Specific Row and Column in Python

To AutoFit a specific row and column in an Excel worksheet, you can use the Worksheet.AutoFitRow() and Worksheet.AutoFitColumn() 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 using Workbook.Worksheets[index] property.
• AutoFit a specific row and column in the worksheet by its index (1-based) using Worksheet.AutoFitRow(rowIndex) and Worksheet.AutoFitColumn(columnIndex) methods.
• Save the result file using Workbook.SaveToFile() method.

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

# Create an object of the Workbook class
workbook = Workbook()
# Load an Excel file
workbook.LoadFromFile("Sample.xlsx")
 
# Get the first worksheet
sheet = workbook.Worksheets[0]
 
# Automatically adjust the height of the 3rd row in the worksheet 
sheet.AutoFitRow(3)
# Automatically adjust the width of the 4th column in the worksheet
sheet.AutoFitColumn(4)
 
# Save the resulting file
workbook.SaveToFile("AutoFitSpecificRowAndColumn.xlsx", ExcelVersion.Version2016)
workbook.Dispose()

AutoFit Multiple Rows and Columns in Excel in Python

To AutoFit multiple rows and columns within a cell range, you can use the CellRange.AutoFitRows() and CellRange.AutoFitColumns() methods. The following are the detailed steps.

• Create an object of the Workbook class.
• Load an Excel file using Workbook.LoadFroFmFile() method.
• Get a specific worksheet using Workbook.Worksheets[index] property.
• Get a specific cell range in the worksheet using Worksheet.Range[] property.
• AutoFit the rows and columns in the cell range using CellRange.AutoFitRows() and CellRange.AutoFitColumns() methods.
• Save the result file using Workbook.SaveToFile() method.

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

# Create an object of the Workbook class
workbook = Workbook()
# Load an Excel file
workbook.LoadFromFile("Sample.xlsx")
 
# Get the first worksheet
sheet = workbook.Worksheets[0]

# Get a specific cell range in the worksheet
range = sheet.Range["A1:E14"]
# Or get the used cell range in the worksheet
# range = sheet.AllocatedRange

# Automatically adjust the heights of all rows in the cell range
range.AutoFitRows()
# Automatically adjust the widths of all columns in the cell range
range.AutoFitColumns()
 
# Save the resulting file
workbook.SaveToFile("AutoFitMultipleRowsAndColumns.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.