For data analysis and reporting, visual aesthetics play a significant role in presenting information effectively. When working with Excel worksheets, the ability to set background colors and images enhances the overall readability and impact of the data. By leveraging the power of Python, developers can effortlessly manipulate Excel files and customize the appearance of their worksheets. This article demonstrates how to use Spire.XLS for Python to set the background color and image for Excel worksheets with 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 Background Color for an Excel Worksheet
With Spire.XLS for Python, developers can set the background color for a specified cell range through CellRange.Style.Color property. The detailed steps for setting the background color for the used cell range in a worksheet are as follows:
- Create an instance of Workbook class.
- Load an Excel workbook using Workbook.LoadFromFile() method.
- Get a worksheet using Workbook.Worksheets.get_Item() method.
- Get the used range in the worksheet through Worksheet.AllocatedRange property.
- Set the background color of the used range through CellRange.Style.Color property.
- Save the workbook using Workbook.SaveToFile() method.
- Python
from spire.xls import *
from spire.xls.common import *
# Create an instance of Workbook class
wb = Workbook()
# Load an Excel file
wb.LoadFromFile("Sample.xlsx")
# Get a worksheet
sheet = wb.Worksheets.get_Item(0)
# Get the used range of the worksheet
usedRange = sheet.AllocatedRange
# Set the background color of the used range to a light and soft color
usedRange.Style.Color = Color.FromRgb(144, 238, 144)
# Save the workbook
wb.SaveToFile("output/ExcelBackgroundColor.xlsx", FileFormat.Version2016)
wb.Dispose()
Set the Background Image for an Excel Worksheet
Setting a background image for an Excel worksheet can be accomplished through PageSetup class. Using the Worksheet.PageSetup.BackgroundImage property, developers can set the image background for the entire worksheet. Detailed steps are as follows:
- Create an instance of Workbook class.
- Load an Excel workbook using Workbook.LoadFromFile() method.
- Get a worksheet using Workbook.Worksheets.get_Item() method.
- Load an image using Image.FromFile() method.
- Set the background image of the worksheet through Worksheet.PageSetup.BackgroundImage property.
- Save the workbook using Workbook.SaveToFile() method.
- Python
from spire.xls import *
from spire.xls.common import *
# Create an instance of Workbook class
wb = Workbook()
# Load an Excel file
wb.LoadFromFile("Sample.xlsx")
# Get a worksheet
sheet = wb.Worksheets.get_Item(0)
# Load an image
image = Image.FromFile("BackgroundImage.jpg")
# Set the background of the worksheet
sheet.PageSetup.BackgoundImage = image
# Save the workbook
wb.SaveToFile("output/ExcelBackgroundImage.xlsx", FileFormat.Version2016)
wb.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.
