Efficiently emphasizing critical data within Excel workbooks is essential for swift analysis. This process not only draws immediate attention to the most relevant information but also aids in identifying trends, anomalies, and key metrics. By using Python to handle Excel workbooks, users can automate the search and highlight functions, enhancing productivity and ensuring precision. This article explores how to leverage Python for finding and highlighting data in Excel worksheets using Spire.XLS for Python library.
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: How to Install Spire.XLS for Python on Windows
Find and Highlight Data in Excel Worksheets
Using Spire.XLS for Python, we can find all cells containing a specific string and return them as a list by using the Worksheet.FindAllString(stringValue: str, formula: bool, formulaValue: bool) method. After that, we can iterate through the found cells and apply a highlight color by setting it via the CellRange.Style.Color property.
The detailed steps for finding and highlighting data in an Excel worksheet are as follows:
- Create an instance of Workbook class and load an Excel workbook using Workbook.LoadFromFile() method.
- Get a worksheet using Workbook.Worksheets.get_Item() method.
- Find all the cells containing the string to be highlighted using Worksheet.FindAllString() method.
- Iterate through the results to highlight the cells by setting a fill color through CellRange.Style.Color property.
- Save the workbook using Workbook.SaveToFile() method.
- Python
Python
from spire.xls 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)
# Find the data to be highlighted
cellRanges = sheet.FindAllString("Urgent", False, True)
# Iterate through the found ranges
for cellRange in cellRanges:
# Highlight the data
cellRange.Style.Color = Color.get_LightYellow()
# Save the workbook
workbook.SaveToFile("output/FindHighlightDataExcel.xlsx")
workbook.Dispose()
Find and Highlight Data in a Specific Cell Range
In addition to searching for data across the entire worksheet, we can use the CellRange.FindAllString(stringValue: str, formula: bool, formulaValue: bool) method to find and highlight data within a specified cell range. The detailed steps are as follows:
- Workbook.LoadFromFile() method.
- Get a worksheet using Workbook.Worksheets.get_Item() method.
- Get a cell range through Worksheet.Range[] property.
- Find all the cells containing the string to be highlighted using CellRange.FindAllString() method.
- Iterate through the results to highlight the cells by setting a fill color through CellRange.Style.Color property.
- Save the workbook using Workbook.SaveToFile() method.
- Python
from spire.xls 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 the cell range
findRange = sheet.Range["C1:C11"]
# Find the data to be highlighted
cellRanges = findRange.FindAllString("Urgent", False, True)
# Iterate the found ranges
for cellRange in cellRanges:
# Highlight the data
cellRange.Style.Color = Color.get_LightYellow()
# Save the workbook
workbook.SaveToFile("output/FindHighlightRange.xlsx")
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.
