Cells (14)
Formatting plays a crucial role in making your Excel spreadsheets clean, organized, and visually appealing. Often, you may want to apply the same formatting to multiple cells or ranges in your workbook. Instead of manually formatting each cell individually, Excel provides a convenient feature called "Copy Cell Formatting" that allows you to quickly replicate the formatting from one cell to others.
Here in this article, you will learn how to programmatically copy cell formatting in Excel 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 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
Copy Formatting from One Cell to Another in Python
You can access a specific cell by using the Worksheet.Range[row:int, column:int] property. The formatting of that cell can be retrieved through the CellRange.Style property, and this formatting can then be applied to a different cell.
The steps to copy formatting from one to cell to anther are as follows.
- Create a Workbook object.
- Load an Excel document from a give path.
- Get a specific worksheet within the workbook.
- Get a specific cell through Worksheet.Range[row:int, column:int] property.
- Get the cell formatting through CellRange.Style property, and apply it to another cell through the same property.
- Save the workbook to a different Excel file.
This code example loads an existing Excel document, copies the formatting (style) from the cells in the second column to the cells in the fourth column for rows 2 through 14, and then saves the modified workbook to a new Excel file.
- Python
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\\Input.xlsx")
# Get a specific worksheet
worksheet = workbook.Worksheets[0]
# Loop through the selected rows
for i in range(2, 15):
# Get style (formatting) of a specific cell
style = worksheet.Range[i, 2].Style
# Apply the style to a different cell
worksheet.Range[i, 4].Style = style
# Save the workbook to file
workbook.SaveToFile("output/CopyFormatting.xlsx",ExcelVersion.Version2016)
# Dispose resources
workbook.Dispose()
Copy Formatting from One Cell to a Cell Range in Python
Once you get the style (formatting) of a certain cell, you can apply it to a cell rang which is retrieved through the Worksheet.Range[row:int, column:int, endRow:int, endColumn:int] property.
Here are the steps to copy formatting from once cell to a cell range.
- Create a Workbook object.
- Load an Excel document from a give path.
- Get a specific worksheet within the workbook.
- Get a specific cell through Worksheet.Range[row:int, column:int] property.
- Get the formatting of the cell through CellRange.Style property.
- Get a cell range through Worksheet.Range[row:int, column:int, endRow:int, endColumn:int] property.
- Apply the formatting to the cell range through CellRange.Style property.
- Save the workbook to a different Excel file.
This code example loads an existing Excel document, retrieves the style of a cell located in the third row and first column, and then applies that style to a range of cells from the third row, fourth column to the fourth row, sixth column.
- Python
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\\Input.xlsx")
# Get a specific worksheet
worksheet = workbook.Worksheets[0]
# Get style (formatting) of a specific cell
style = worksheet.Range[3, 1].Style
# Apply the style to a cell range
worksheet.Range[3, 4, 4, 6].Style = style
# Save the workbook to file
workbook.SaveToFile("output/ApplyFormatToCellRange.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.
Setting the number format for cells in Excel worksheets is crucial for data management and presentation, which enhances readability, ensures consistency, and facilitates accurate data analysis. Proper number formatting allows users to distinguish between different types of numerical data, such as currency, percentages, dates, and scientific notations, making complex datasets more comprehensible at a glance. In this article, we will explore how to automate the process of setting the number format for cells in Excel worksheets with Spire.XLS for Python in Python programs.
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: How to Install Spire.XLS for Python on Windows
Set the Number Format for Cells in Excel Worksheets
In an Excel workbook, the number format of a cell is determined by its format code. Developers can utilize various symbols in format code to define how numerical data, date and time, currency, etc. are displayed. Below are some commonly used symbols in number format codes:
- #: Represents a digit placeholder that displays only non-zero digits.
- 0: Represents a digit placeholder and always occupies at least one position.
- ; (semicolon): Separates formats for positive numbers, negative numbers, and zero.
- / (slash): In date formats, separates year, month, and day.
- $: Currency symbol, used for representing monetary values, adaptable to system regional settings.
- () (parentheses): Formats negative numbers by enclosing them in parentheses.
- [ ] (square brackets): Utilized in conditional formatting, such as color settings [Red] or conditions like [<=100]"Low";[>100]"High".
Spire.XLS for Python provides the CellRange.NumberValue property to set the number value of a cell and the CellRange.NumberFormat property to set the number format with format code. Below are the steps for setting the number format for cells in Excel worksheets with Python:
- Create an instance of Workbook class to create an Excel workbook.
- Get the first default worksheet using Workbook.Worksheets.get_Item() method.
- Add text to header row through Worksheet.Range[].Text property.
- Add number value to cells through Worksheet.Range[].NumberValue property and set the number format for the cells with format code through Worksheet.Range[].NumberFormat property.
- Save the Excel workbook using Workbook.SaveToFile() method.
- Python
from spire.xls import *
from spire.xls.common import *
# Create an instance of Workbook
workbook = Workbook()
# Get the first worksheet
sheet = workbook.Worksheets.get_Item(0)
# Set the header row
sheet.Range["B9"].Text = "Number Format"
sheet.Range["C9"].Text = "Value"
sheet.Range["D9"].Text = "Display"
# Number with thousands separator and decimal places
sheet.Range["B10"].Text = "Number with thousands separator and decimal places"
sheet.Range["C10"].Text = "-1234.5678"
sheet.Range["D10"].NumberValue = -1234.5678
sheet.Range["D10"].NumberFormat = "#,##0.00"
# Number in red color
sheet.Range["B11"].Text = "Number in red color"
sheet.Range["C11"].Text = "12345.12345"
sheet.Range["D11"].NumberValue = 12345.12345
sheet.Range["D11"].NumberFormat = "[Red]#,##0.00"
# Percentage with two decimal places
sheet.Range["B12"].Text = "Percentage with two decimal places"
sheet.Range["C12"].Text = "0.12345"
sheet.Range["D12"].NumberValue = 0.12345
sheet.Range["D12"].NumberFormat = "0.00%"
# Number with brackets
sheet.Range["B13"].Text = "Number with brackets"
sheet.Range["C13"].Text = "-1234.5678"
sheet.Range["D13"].NumberValue = -1234.5678
sheet.Range["D13"].NumberFormat = "(#,##0.00;(#,##0.00))"
# Date
sheet.Range["B14"].Text = "Date"
sheet.Range["C14"].Text = "36526"
sheet.Range["D14"].NumberValue = 36526
sheet.Range["D14"].NumberFormat = "m/d/yyyy"
# Time
sheet.Range["B15"].Text = "Time"
sheet.Range["C15"].Text = "0.5"
sheet.Range["D15"].NumberValue = 0.5
sheet.Range["D15"].NumberFormat = "h:mm:ss AM/PM"
# Currency in US format
sheet.Range["B16"].Text = "Currency in US format"
sheet.Range["C16"].Text = "1234.56"
sheet.Range["D16"].NumberValue = 1234.56
sheet.Range["D16"].NumberFormat = "$#,##0.00"
# Scientific notation
sheet.Range["B18"].Text = "Scientific notation"
sheet.Range["C18"].Text = "1234.5678"
sheet.Range["D18"].NumberValue = 1234.5678
sheet.Range["D18"].NumberFormat = "0.00E+00"
# Date and time
sheet.Range["B19"].Text = "Date and time"
sheet.Range["C19"].Text = "36526"
sheet.Range["D19"].NumberValue = 36526
sheet.Range["D19"].NumberFormat = "m/d/yyyy h:mm:ss AM/PM"
# Number with text
sheet.Range["B20"].Text = "Number with text"
sheet.Range["C20"].Text = "1234.56"
sheet.Range["D20"].NumberValue = 1234.5678
sheet.Range["D20"].NumberFormat = "\"USD \"#,##0.00"
# Set the font size and autofit rows and columns
sheet.AllocatedRange.Style.Font.Size = 13
sheet.AllocatedRange.AutoFitRows()
sheet.AllocatedRange.AutoFitColumns()
# Save the file
workbook.SaveToFile("output/SetNumberFormatExcel.xlsx", FileFormat.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.
Copying data in Excel is a fundamental feature that allows you to quickly and efficiently reproduce data. It can be especially valuable when building spreadsheets with similar structures, or needing to propagate the same information across multiple areas of your workbook. By mastering the art of copying in Excel, you can boost your productivity and reduce the risk of manual data entry errors. In this article, we will explain how to copy rows, columns and cells 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
Copy Rows in Excel in Python
You can use the Worksheet.CopyRow(sourceRow, destSheet, destRowIndex, copyOptions) method provided by Spire.XLS for Python to easily copy a row in the same or between different worksheets in Excel. The detailed steps are as follows.
- Create an object of the Workbook class.
- Load an Excel file using the Workbook.LoadFromFile() method.
- Get the source worksheet and the destination worksheet using the Workbook.Worksheets[index] property.
- Get the desired row that you want to copy using the Worksheet.Rows[index] property.
- Copy the row and its format from the source worksheet to the destination worksheet using the Worksheet.CopyRow(sourceRow, destSheet, destRowIndex, copyOptions) method.
- Copy the column widths of cells in the source row to the corresponding cells in the destination row.
- Save the workbook to a file using the Workbook.SaveToFile() method.
- Python
from spire.xls import *
from spire.xls.common import *
# Create a Workbook object
workbook = Workbook()
# Load an Excel file
workbook.LoadFromFile("ContactList.xlsx")
# Get the source worksheet
sheet1 = workbook.Worksheets[0]
# Get the destination worksheet
sheet2 = workbook.Worksheets[1]
# Get the desired row that you want to copy
row = sheet1.Rows[0]
# Copy the row from the source worksheet to the first row of the destination worksheet
sheet1.CopyRow(row, sheet2, 1, CopyRangeOptions.All)
columns = sheet1.Columns.Length
# Copy the column widths of the cells in the source row to the corresponding cells in the destination row
for i in range(columns):
column_width = row.Columns[i].ColumnWidth
sheet2.Rows[0].Columns[i].ColumnWidth = column_width
# Save the workbook to a file
workbook.SaveToFile("CopyRow.xlsx", ExcelVersion.Version2016)
workbook.Dispose()
Copy Columns in Excel in Python
To copy a column in an Excel worksheet, you can use the Worksheet.CopyColumn(sourceColumn, destSheet, destColIndex, copyOptions) method. The detailed steps are as follows.
- Create an object of the Workbook class.
- Load an Excel file using the Workbook.LoadFromFile() method.
- Get the source worksheet and the destination worksheet using the Workbook.Worksheets[index] property.
- Get the desired column that you want to copy using the Worksheet.Columns[index] property.
- Copy the column and its format from the source worksheet to the destination worksheet using the Worksheet.CopyColumn(sourceColumn, destSheet, destColIndex, copyOptions) method.
- Copy the row heights of cells in the source column to the corresponding cells in the destination column.
- Save the workbook to a file using the Workbook.SaveToFile() method.
- Python
from spire.xls import *
from spire.xls.common import *
# Create a Workbook object
workbook = Workbook()
# Load an Excel file
workbook.LoadFromFile("ContactList.xlsx")
# Get the source worksheet
sheet1 = workbook.Worksheets[0]
# Get the destination worksheet
sheet2 = workbook.Worksheets[1]
# Get the desired column that you want to copy
column = sheet1.Columns[0]
# Copy the column from the source worksheet to the first column of the destination worksheet
sheet1.CopyColumn(column, sheet2, 1, CopyRangeOptions.All)
rows = column.Rows.Length
# Copy the row heights of cells in the source column to the corresponding cells in the destination column
for i in range(rows):
row_height = column.Rows[i].RowHeight
sheet2.Columns[0].Rows[i].RowHeight = row_height
# Save the workbook to a file
workbook.SaveToFile("CopyColumn.xlsx", ExcelVersion.Version2016)
workbook.Dispose()
Copy Cells in Excel in Python
In addition to copying entire rows and columns, you are also able to copy an individual cell or a range of cells using the CellRange.Copy(destRange, copyOptions) method. The detailed steps are as follows.
- Create an object of the Workbook class.
- Load an Excel file using the Workbook.LoadFromFile() method.
- Get the source worksheet and the destination worksheet using the Workbook.Worksheets[index] property.
- Get the source cell range and the destination cell range using the Worksheet.Range[] property.
- Copy the source cell range and its format from the source worksheet to the destination cell range in the destination worksheet using the CellRange.Copy(destRange, copyOptions) method.
- Copy the row heights and column widths of the source cell range to the destination cell range.
- Save the workbook to a file using the Workbook.SaveToFile() method.
- Python
from spire.xls import *
from spire.xls.common import *
# Create a Workbook object
workbook = Workbook()
# Load an Excel file
workbook.LoadFromFile("ContactList.xlsx")
# Get the source worksheet
sheet1 = workbook.Worksheets[0]
# Get the destination worksheet
sheet2 = workbook.Worksheets[1]
# Get the source cell range
range1 = sheet1.Range["A1:E7"]
# Get the destination cell range
range2 = sheet2.Range["A1:E7"]
# Copy the source cell range from the source worksheet to the destination cell range in the destination worksheet
range1.Copy(range2, CopyRangeOptions.All)
# Copy the row heights and column widths of the source cell range to the destination cell range
for i, row in enumerate(range1.Rows):
for j, column in enumerate(row.Columns):
range2.Rows[i].Columns[j].ColumnWidth = column.ColumnWidth
range2.Rows[i].RowHeight = row.RowHeight
# Save the workbook to a file
workbook.SaveToFile("CopyCells.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.
Drop-down lists in Excel worksheets are an indispensable tool for enhancing data accuracy, efficiency, and usability in spreadsheet management. By offering pre-defined options within a cell, they not only streamline data entry processes but also enforce consistency, reducing the likelihood of input errors. This feature is particularly valuable when working with large datasets or collaborative projects where maintaining uniformity across multiple entries is crucial. This article demonstrates how to create customized drop-down lists within Excel worksheets using Spire.XLS for Python, empowering users to create organized and user-friendly worksheets.
- Create Drop-Down Lists Based on Cell Values Using Python
- Create Drop-Down Lists Based on Strings Using 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: How to Install Spire.XLS for Python on Windows
Create Drop-Down Lists Based on Cell Values Using Python
In Excel worksheets, creating drop-down lists is accomplished through the data validation feature. With Spire.XLS for Python, developers can use the CellRange.DataValidation.DataRange property to create drop-down lists within cells and use the data from the specified cell range as list options.
The detailed steps for creating a drop-down list based on cell values are as follows:
- Create an instance of Workbook class.
- Load an Excel file using Workbook.LoadFromFile() method.
- Get a worksheet using Workbook.Worksheets.get_Item() method.
- Get a specific cell range through Worksheet.Range[] property.
- Set the data range for data validation of the cell range through CellRange.DataValidation.DataRange property to create drop-down lists with cell values.
- Save the workbook using Workbook.SaveToFile() method.
- Python
from spire.xls import *
from spire.xls.common import *
# Create an instance of Workbook
workbook = Workbook()
# Load an Excel file
workbook.LoadFromFile("Sample.xlsx")
# Get the first worksheet
sheet = workbook.Worksheets.get_Item(0)
# Get a specific cell range
cellRange = sheet.Range["C3:C7"]
# Set the data range for data validation to create drop-down lists in the cell range
cellRange.DataValidation.DataRange = sheet.Range["F4:H4"]
# Save the workbook
workbook.SaveToFile("output/DropDownListExcel.xlsx", FileFormat.Version2016)
workbook.Dispose()
Create Drop-Down Lists Based on String Using Python
Spire.XLS for Python also provides the CellRange.DataValidation.Values property to create drop-down lists in cells directly using string lists.
The detailed steps for creating drop-down lists based on values are as follows:
- Create an instance of Workbook class.
- Load an Excel file using Workbook.LoadFromFile() method.
- Get a worksheet using Workbook.Worksheets.get_Item() method.
- Get a specific cell range through Worksheet.Range[] property.
- Set a string list as the values of data validation in the cell range through CellRange.DataValidation.Values property to create drop-down lists based on strings.
- Save the workbook using Workbook.SaveToFile() method.
- Python
from spire.xls import *
from spire.xls.common import *
# Create an instance of Workbook
workbook = Workbook()
# Load an Excel file
workbook.LoadFromFile("Sample.xlsx")
# Get the first worksheet
sheet = workbook.Worksheets.get_Item(0)
# Get a cell range
cellRange = sheet.Range["D3:D7"]
# Set the value for data validation to create drop-down lists
cellRange.DataValidation.Values = ["Starter", "Technician", "Director", "Executive"]
# Save the workbook
workbook.SaveToFile("output/ValueDropDownListExcel.xlsx", FileFormat.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.
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.
- Python
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.
- Python
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.
Python: Add Fill Color, Gradient or Pattern to Excel Cells
2024-04-01 07:51:45 Written by support iceblueExcel is a powerful tool for organizing and analyzing data, but the default black-and-white spreadsheet can make it difficult to interpret information at a glance. By adding fill colors, gradients, or patterns to specific cells, you can highlight important data, separate different categories of information, and make the spreadsheet more visually appealing. In this article, you will learn how to set a fill color, gradient or pattern in Excel cells using Spire.XLS for Python.
- Add Fill Color to Excel Cells with Python
- Add Gradient Fill to Excel Cells with Python
- Add Fill Pattern to Excel Cells with 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
Add Fill Color to Excel Cells with Python
The CellRange.Style.Color property provided by Spire.XLS for Python allows to add a solid background color to Excel cells. 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[] property.
- Get a specified cell or cell range using Worksheet.Range[] property.
- Add a background color to the cells using CellRange.Style.Color property.
- Save the result file using Workbook.SaveToFile() method.
- Python
from spire.xls import * from spire.xls.common import * inputFile = "Cost.xlsx" outputFile = "CellFillColor.xlsx" # Create a Workbook instance workbook = Workbook() # Load an Excel file from disk workbook.LoadFromFile(inputFile) # Get the first worksheet worksheet = workbook.Worksheets[0] # Set fill color for a cell or cell range worksheet.Range["A1:D1"].Style.Color = Color.get_Green() worksheet.Range["A5"].Style.Color = Color.get_Yellow() # Save the result file workbook.SaveToFile(outputFile, ExcelVersion.Version2016) workbook.Dispose()
Add Gradient Fill to Excel Cells with Python
To apply gradient fill in Excel, you first need to set the cell fill pattern type to gradient, and then specify two colors and the shading style of the gradient fill. 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[] property.
- Get the cell style of a specified cell or cell range using Worksheet.Range[].Style property.
- Get the interior cell style using CellStyle.Interior property.
- Set cell fill effect to gradient through ExcelInterior.FillPattern property.
- Set the background and foreground colors of the gradient fill using ExcelInterior.Gradient.BackColor and ExcelInterior.Gradient.ForeColor properties.
- Set the gradient shading style using ExcelInterior.Gradient.GradientStyle property.
- Save the result file using Workbook.SaveToFile() method.
- Python
from spire.xls import * from spire.xls.common import * inputFile = "Cost.xlsx" outputFile = "CellGradientFill.xlsx" # Create a Workbook instance workbook = Workbook() # Load an Excel file from disk workbook.LoadFromFile(inputFile) # Get the first worksheet worksheet = workbook.Worksheets[0] # Get the cell style of a specified range cellStyle = worksheet.Range["A1:A12"].Style # Set cell fill pattern type to gradient cellStyle.Interior.FillPattern = ExcelPatternType.Gradient # Set the background and foreground colors of the gradient fill cellStyle.Interior.Gradient.BackColor = Color.get_SkyBlue() cellStyle.Interior.Gradient.ForeColor = Color.get_White() # Set the gradient shading style cellStyle.Interior.Gradient.GradientStyle = GradientStyleType.From_Center # Save the result file workbook.SaveToFile(outputFile, ExcelVersion.Version2016) workbook.Dispose()
Add Fill Pattern to Excel Cells with Python
You can also add predefined patterns such as different styles of stripes, dots, and crosshatch to specific cells through the CellRange.Style.FillPattern property. 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[] property.
- Get a specified cell or cell range using Worksheet.Range[] property.
- Add a background pattern to the cells using CellRange.Style.FillPattern property.
- Save the result file using Workbook.SaveToFile() method.
- Python
from spire.xls import * from spire.xls.common import * inputFile = "Cost.xlsx" outputFile = "CellFillPattern.xlsx" # Create a Workbook instance workbook = Workbook() # Load an Excel file from disk workbook.LoadFromFile(inputFile) # Get the first worksheet worksheet = workbook.Worksheets[0] #Set cell fill pattern for a cell or cell range worksheet.Range["A7:C12"].Style.FillPattern = ExcelPatternType.Percent125Gray worksheet.Range["D5"].Style.FillPattern = ExcelPatternType.ThinDiagonalStripe # 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, freezing panes is a practical feature that allows you to lock specific rows or columns in place while navigating through a spreadsheet. When you freeze panes, the selected rows or columns remain visible on the screen, even as you scroll through the rest of the worksheet. This feature proves especially useful when dealing with large datasets where headers, titles, or other important reference points must stay visible to provide context.
Unfreezing panes, on the other hand, allows you to release the locked rows or columns. It is beneficial when you no longer need certain sections to remain fixed and want to regain the ability to navigate the entire spreadsheet without any restrictions. In this article, we will demonstrate how to freeze and unfreeze panes 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 commands.
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
Freeze Panes in Excel in Python
Rows and columns can be frozen by using the Worksheet.FreezePanes() method. This method takes two parameters - rowIndex (1-based) and columnIndex (1-based). The first parameter represents the index of the row above which all rows will be frozen. The second parameter represents the index of the column to the left of which all columns will be frozen.
The following steps show you how to freeze specific rows and/or columns in Excel using Spire.XLS for Python.
- Create an instance of the Workbook class.
- Load an Excel file using Workbook.LoadFromFile() method.
- Get a specific worksheet using Workbook.Worksheets[index] property.
- Freeze specific rows and/or columns in the worksheet using Worksheet.FreezePanes(rowIndex, columnIndex) method.
- Save the result Excel file using Workbook.SaveToFile() method.
- Python
from spire.xls import * from spire.xls.common import * # Specify the paths for the input and output Excel files inputFile = "Sample.xlsx" outputFile = "FreezePanes.xlsx" # Create a Workbook instance workbook = Workbook() # Load an Excel file workbook.LoadFromFile(inputFile) # Get the first worksheet in the file sheet = workbook.Worksheets[0] # Freeze specific rows and/or columns # Freeze the top row sheet.FreezePanes(2, 1) # Or freeze the first column # sheet.FreezePanes(1, 2) # Or freeze the top row and the first column # sheet.FreezePanes(2, 2) # Save the result file workbook.SaveToFile(outputFile, ExcelVersion.Version2013) workbook.Dispose()
Unfreeze Panes in Excel in Python
To unfreeze rows and columns in Excel, use the Worksheet.RemovePanes() method. The detailed steps are as follows.
- Create an instance of the Workbook class.
- Load an Excel file using Workbook.LoadFromFile() method.
- Get a specific worksheet using Workbook.Worksheets[index] property.
- Unfreeze rows and columns in the worksheet using Worksheet.RemovePanes() method.
- Save the result Excel file using Workbook.SaveToFile() method.
- Python
from spire.xls import * from spire.xls.common import * # Specify the paths for the input and output Excel files inputFile = " FreezePanes.xlsx" outputFile = "UnfreezePanes.xlsx" # Create a Workbook instance workbook = Workbook() # Load an Excel file workbook.LoadFromFile(inputFile) # Get the first worksheet in the file sheet = workbook.Worksheets[0] # Unfreeze rows and/or columns in the worksheet sheet.RemovePanes() # Save the result 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.
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.
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.
- Python
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.
- Python
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.
- Python
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.
- Python
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.
- Python
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.
- Python
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.
- Python
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.
- Python
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.
- Python
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.
