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