A bar chart is a type of graph that represents categorical data using rectangular bars. It is somewhat like a column chart, but with bars that extend horizontally from the Y-axis. The length of each bar corresponds to the value represented by a particular category or group, and changes, trends, or rankings can be quickly identified by comparing the lengths of the bars. In this article, you will learn how to create a clustered or stacked bar chart in Excel in Python using Spire.XLS for Python.

• Create a Clustered Bar Chart in Excel in Python
• Create a Stacked Bar Chart 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

Create a Clustered Bar Chart in Excel in Python

The Worksheet.Chart.Add(ExcelChartType chartType) method provided by Spire.XLS for Python allows to add a chart to a worksheet. To add a clustered bar chart in Excel, you can set the chart type to BarClustered. The following are the steps.

• Create a Workbook object.
• Get a specific worksheet using Workbook.Worksheets[index] property.
• Add chart data to specified cells and set the cell styles.
• Add a clustered bar char to the worksheet using Worksheet.Chart.Add(ExcelChartType.BarClustered) method.
• Set data range for the chart using Chart.DataRange property.
• Set position, title, category axis and value axis for the chart.
• Save the result file using Workbook.SaveToFile() method.

from spire.xls import *

# Create a Workbook instance
workbook = Workbook()

# Get the first sheet and set its name
sheet = workbook.Worksheets[0]
sheet.Name = "ClusteredBar"

# Add chart data to specified cells
sheet.Range["A1"].Value = "Quarter"
sheet.Range["A2"].Value = "Q1"
sheet.Range["A3"].Value = "Q2"
sheet.Range["A4"].Value = "Q3"
sheet.Range["A5"].Value = "Q4"
sheet.Range["B1"].Value = "Team A"
sheet.Range["B2"].NumberValue = 3000
sheet.Range["B3"].NumberValue = 8000
sheet.Range["B4"].NumberValue = 9000
sheet.Range["B5"].NumberValue = 8500
sheet.Range["C1"].Value = "Team B"
sheet.Range["C2"].NumberValue = 7000
sheet.Range["C3"].NumberValue = 2000
sheet.Range["C4"].NumberValue = 5000
sheet.Range["C5"].NumberValue = 4200

# Set cell style
sheet.Range["A1:C1"].RowHeight = 18
sheet.Range["A1:C1"].Style.Color = Color.get_Black()
sheet.Range["A1:C1"].Style.Font.Color = Color.get_White()
sheet.Range["A1:C1"].Style.Font.IsBold = True
sheet.Range["A1:C1"].Style.VerticalAlignment = VerticalAlignType.Center
sheet.Range["A1:C1"].Style.HorizontalAlignment = HorizontalAlignType.Center
sheet.Range["A2:A5"].Style.HorizontalAlignment = HorizontalAlignType.Center
sheet.Range["B2:C5"].Style.NumberFormat = "\"$\"#,##0"

# Add a clustered bar chart to the sheet
chart = sheet.Charts.Add(ExcelChartType.BarClustered)

# Set data range of the chart
chart.DataRange = sheet.Range["A1:C5"]
chart.SeriesDataFromRange = False

# Set position of the chart
chart.LeftColumn = 1
chart.TopRow = 6
chart.RightColumn = 11
chart.BottomRow = 29

# Set and format chart title
chart.ChartTitle = "Team Sales Comparison per Quarter"
chart.ChartTitleArea.IsBold = True
chart.ChartTitleArea.Size = 12

# Set and format category axis
chart.PrimaryCategoryAxis.Title = "Country"
chart.PrimaryCategoryAxis.Font.IsBold = True
chart.PrimaryCategoryAxis.TitleArea.IsBold = True
chart.PrimaryCategoryAxis.TitleArea.TextRotationAngle = 90

# Set and format value axis
chart.PrimaryValueAxis.Title = "Sales (in Dollars)"
chart.PrimaryValueAxis.HasMajorGridLines = False
chart.PrimaryValueAxis.MinValue = 1000
chart.PrimaryValueAxis.TitleArea.IsBold = True

# Show data labels for data points
for cs in chart.Series:
    cs.Format.Options.IsVaryColor = True
    cs.DataPoints.DefaultDataPoint.DataLabels.HasValue = True

# Set legend position
chart.Legend.Position = LegendPositionType.Top

#Save the result file
workbook.SaveToFile("ClusteredBarChart.xlsx", ExcelVersion.Version2016)
workbook.Dispose()

Create a Stacked Bar Chart in Excel in Python

To create a stacked bar chart, you just need to change the Excel chart type to BarStacked. The following are the steps.

• Create a Workbook object.
• Get a specific worksheet using Workbook.Worksheets[index] property.
• Add chart data to specified cells and set the cell styles.
• Add a clustered bar char to the worksheet using Worksheet.Chart.Add(ExcelChartType.BarStacked) method.
• Set data range for the chart using Chart.DataRange property.
• Set position, title, category axis and value axis for the chart.
• Save the result file using Workbook.SaveToFile() method.

from spire.xls import *

# Create a Workbook instance
workbook = Workbook()

# Get the first sheet and set its name
sheet = workbook.Worksheets[0]
sheet.Name = "StackedBar"

# Add chart data to specified cells
sheet.Range["A1"].Value = "Quarter"
sheet.Range["A2"].Value = "Q1"
sheet.Range["A3"].Value = "Q2"
sheet.Range["A4"].Value = "Q3"
sheet.Range["A5"].Value = "Q4"
sheet.Range["B1"].Value = "Team A"
sheet.Range["B2"].NumberValue = 3000
sheet.Range["B3"].NumberValue = 8000
sheet.Range["B4"].NumberValue = 9000
sheet.Range["B5"].NumberValue = 8500
sheet.Range["C1"].Value = "Team B"
sheet.Range["C2"].NumberValue = 7000
sheet.Range["C3"].NumberValue = 2000
sheet.Range["C4"].NumberValue = 5000
sheet.Range["C5"].NumberValue = 4200

# Set cell style
sheet.Range["A1:C1"].RowHeight = 18
sheet.Range["A1:C1"].Style.Color = Color.get_Black()
sheet.Range["A1:C1"].Style.Font.Color = Color.get_White()
sheet.Range["A1:C1"].Style.Font.IsBold = True
sheet.Range["A1:C1"].Style.VerticalAlignment = VerticalAlignType.Center
sheet.Range["A1:C1"].Style.HorizontalAlignment = HorizontalAlignType.Center
sheet.Range["A2:A5"].Style.HorizontalAlignment = HorizontalAlignType.Center
sheet.Range["B2:C5"].Style.NumberFormat = "\"$\"#,##0"

# Add a clustered bar chart to the sheet
chart = sheet.Charts.Add(ExcelChartType.BarStacked)

# Set data range of the chart
chart.DataRange = sheet.Range["A1:C5"]
chart.SeriesDataFromRange = False

# Set position of the chart
chart.LeftColumn = 1
chart.TopRow = 6
chart.RightColumn = 11
chart.BottomRow = 29

# Set and format chart title
chart.ChartTitle = "Team Sales Comparison per Quarter"
chart.ChartTitleArea.IsBold = True
chart.ChartTitleArea.Size = 12

# Set and format category axis
chart.PrimaryCategoryAxis.Title = "Country"
chart.PrimaryCategoryAxis.Font.IsBold = True
chart.PrimaryCategoryAxis.TitleArea.IsBold = True
chart.PrimaryCategoryAxis.TitleArea.TextRotationAngle = 90

# Set and format value axis
chart.PrimaryValueAxis.Title = "Sales (in Dollars)"
chart.PrimaryValueAxis.HasMajorGridLines = False
chart.PrimaryValueAxis.MinValue = 1000
chart.PrimaryValueAxis.TitleArea.IsBold = True

# Show data labels for data points
for cs in chart.Series:
    cs.Format.Options.IsVaryColor = True
    cs.DataPoints.DefaultDataPoint.DataLabels.HasValue = True

# Set legend position
chart.Legend.Position = LegendPositionType.Top

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

Locking cells is often used to protect the contents of specific cell ranges in a spreadsheet from accidental modification, which is useful in situations such as sharing a worksheet or protecting specific data. When you lock a cell, no one else can edit it unless they know the password or have the appropriate permissions. This feature is important for data security and integrity. In this article, we will show you how to lock specific cells, columns or rows in Excel on python platforms by using Spire.XLS for Python.

• Lock Specific Cells in Python
• Lock a Specific Column in Python
• Lock a Specific Row 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 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

Lock Specific Cells in Python

Spire.XLS for Python supports users to lock a specified range of cells by setting the Worksheet.Range[].Style.Locked property to "True". Below are the detailed steps.

• Create a Workbook instance and load a sample excel file using Workbook.LoadFromFile() method.
• Get the first worksheet using Workbook.Worksheets[] property.
• Unlock all cells in the used range of the worksheet by setting the Worksheet.Range.Style.Locked property to "False".
• Set text for the specific cells using Worksheet.Range[].Text property and then lock them by setting the Worksheet.Range[].Style.Locked property to "True".
• Protect the worksheet using XlsWorksheetBase.Protect() method.
• Save the result file using Workbook.SaveToFile() method.

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

inputFile = "C:/Users/Administrator/Desktop/Sample.xlsx"
outputFile = "C:/Users/Administrator/Desktop/LockSpecificCells.xlsx"

# Create a Workbook instance and load a sample file
workbook = Workbook()
workbook.LoadFromFile(inputFile)

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

# Unlock all cells in the used range of the worksheet
sheet.Range.Style.Locked = False

# Lock a specific cell in the sheet
sheet.Range["A1"].Text = "Locked"
sheet.Range["A1"].Style.Locked = True

# Lock a specific range of cells in the sheet
sheet.Range["C1:E3"].Text = "Locked"
sheet.Range["C1:E3"].Style.Locked = True

# Protect the worksheet with a password
sheet.Protect("123456", SheetProtectionType.All)

# Save the result file
workbook.SaveToFile(outputFile, ExcelVersion.Version2013)
workbook.Dispose()

Lock a Specific Column in Python

If you want to lock a specific column in the worksheet, please set the Worksheet.Columns[].Style.Locked property to "True". Other steps are similar to the above method. Below are the detailed steps.

• Create a Workbook instance and load a sample excel file using Workbook.LoadFromFile() method.
• Get the first worksheet using Workbook.Worksheets[] property.
• Unlock all cells in the used range of the worksheet by setting the Worksheet.Range.Style.Locked property to "False".
• Set text for the fourth column using the Worksheet.Columns[].Text property and then lock it by setting the Worksheet.Columns[].Style.Locked property to "True".
• Protect the worksheet with a password by calling XlsWorksheetBase.Protect() method.
• Save the result file using Workbook.SaveToFile() method.

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

inputFile = "C:/Users/Administrator/Desktop/Sample.xlsx"
outputFile = "C:/Users/Administrator/Desktop/LockSpecificColumn.xlsx"

# Create a Workbook instance and load a sample file
workbook = Workbook()
workbook.LoadFromFile(inputFile)

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

# Unlock all cells in the used range of the worksheet
sheet.Range.Style.Locked = False

# Lock the fourth column in the sheet
sheet.Columns[3].Text = "Locked"
sheet.Columns[3].Style.Locked = True

# Protect the worksheet with a password
sheet.Protect("123456", SheetProtectionType.All)

# Save the result file
workbook.SaveToFile(outputFile, ExcelVersion.Version2013)
workbook.Dispose()

Lock a Specific Row in Python

Similarly, if you want to lock a certain row, please set the Worksheet.Rows[].Style.Locked property to "True". Here are the detailed steps.

• Create a Workbook instance and load a sample excel file using Workbook.LoadFromFile() method.
• Get the first worksheet using Workbook.Worksheets[] property.
• Unlock all cells in the used range of the worksheet by setting the Worksheet.Range.Style.Locked property to "False".
• Set text for the third row using the Worksheet.Rows[].Text property and then lock it by setting the Worksheet.Rows[].Style.Locked property to "True".
• Protect the worksheet with a password using XlsWorksheetBase.Protect() method.
• Save the result file using Workbook.SaveToFile() method.

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

inputFile = "C:/Users/Administrator/Desktop/Sample.xlsx"
outputFile = "C:/Users/Administrator/Desktop/LockSpecificRow.xlsx"

# Create a Workbook instance and load a sample file
workbook = Workbook()
workbook.LoadFromFile(inputFile)

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

# Unlock all cells in the used range of the worksheet
sheet.Range.Style.Locked = False

# Lock the third row in the worksheet
sheet.Rows[2].Text = "Locked"
sheet.Rows[2].Style.Locked = True

# Protect the worksheet with a password
sheet.Protect("123456", SheetProtectionType.All)

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

OLE enables users to incorporate diverse file types-such as images, charts, documents, and multimedia-directly into Excel workbooks, fostering a more dynamic and comprehensive representation of information. By inserting OLE objects, users can create interactive and engaging spreadsheets that integrate a variety of data formats to simplify analyses and presentations in a single Excel environment. In this article, you will learn how to insert linked or embedded OLE objects to Excel in Python as well as how to extract OLE objects from Excel in Python using Spire.XLS for Python.

• Insert a Linked OLE Object to Excel in Python
• Insert an Embedded Object to Excel in Python
• Extract OLE Objects 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

Insert a Linked OLE Object to Excel in Python

To insert an OLE object to a worksheet, you use the Worksheet.OleObjects.Add(fileName, image, linkType) method, in which:

• the fileName parameter specifies the path of an external file to be inserted,
• the image parameter specifies a thumbnail of the first page or a document icon that the OLE object will be displayed as,
• the linkType parameter determines whether the OLE object is inserted to the document as an embedded source or a linked source.

The following are the steps to insert a linked OEL object to Excel using Spire.XLS for Python.

• Create a Workbook object.
• Get the first worksheet through Workbook.Worksheet[index] property.
• Load an image using Image.FromFile() method.
• Insert an OLE object to the worksheet using Worksheet.OleObjects.Add() method, and specify the link type as OleLinkType.Link.
• Specify the OLE object location through IOleObject.Location property.
• Specify the OLE object type through IOleObject.ObjectType property.
• Save the workbook to an Excel file using Workbook.SaveToFile() method.

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

# Create a Workbook object
workbook = Workbook()

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

# Add text to A1
sheet.Range["A1"].Text = "Here is an OLE Object."

# Load an image to be displayed as an icon of ole object
image = Image.FromFile("C:/Users/Administrator/Desktop/word_icon.png") 
with Stream() as stream:
    image.Save(stream,ImageFormat.get_Png())

    # Add an ole object to the worksheet that links to an external file
    oleObject = sheet.OleObjects.Add("C:/Users/Administrator/Desktop/invoice.docx", stream, OleLinkType.Link)

# Specify ole object location
oleObject.Location = sheet.Range["B3"]

# Specify ole object type
oleObject.ObjectType = OleObjectType.WordDocument

# Save to file
workbook.SaveToFile("output/OleObject.xlsx", ExcelVersion.Version2016)
workbook.Dispose()

Insert an Embedded OLE Object to Excel in Python

To insert an embedded OEL object to Excel, you specify the link type as OleLinkType.Embed while invoking the Worksheet.OleObjects.Add() method. The detailed steps are as follows.

• Create a Workbook object.
• Get the first worksheet through Workbook.Worksheet[index] property.
• Load an image using Image.FromFile() method.
• Insert an OLE object to the worksheet using Worksheet.OleObjects.Add() method, and specify the link type as OleLinkType.Embed.
• Specify the OLE object location through IOleObject.Location property.
• Specify the OLE object type through IOleObject.ObjectType property.
• Save the workbook to an Excel file using Workbook.SaveToFile() method.

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

# Create a Workbook object
workbook = Workbook()

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

# Add text to A1
sheet.Range["A1"].Text = "Here is an OLE Object."

# Load an image that represents ole object
image = Image.FromFile("C:/Users/Administrator/Desktop/screenshot.png") 
with Stream() as stream:
    image.Save(stream,ImageFormat.get_Png())

    # Add an ole object to the worksheet as embedded source
    oleObject = sheet.OleObjects.Add("C:/Users/Administrator/Desktop/invoice.docx", stream, OleLinkType.Embed)

# Specify ole object location
oleObject.Location = sheet.Range["B3"]

# Specify ole object type
oleObject.ObjectType = OleObjectType.WordDocument

# Save to file
workbook.SaveToFile("output/OleObject.xlsx", ExcelVersion.Version2016)
workbook.Dispose()

Extract OLE Objects from Excel in Python

Spire.XLS for Python provides the Worksheet.HasOleObjects property to determine whether a worksheet has OLE objects. If it does, get all the objects through the Worksheet.OleObjects property. Then, determine the type of a particular OEL object and save the OEL as a file of the appropriate document type. The following are the steps to extract OLE objects from Excel using Spire.XLS for Python.

• Create a Workbook object.
• Get a specific worksheet through Workbook.Worksheet[index] property.
• Determine if the worksheet contains OLE objects through Worksheet.HasOleObjects property.
• Get all the OLE objects from the worksheet through Worksheet.OleObjects property.
• Determine the type of a particular OEL object and save the OEL as a file of the appropriate document type.

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

# Write data to file
def WriteAllBytes(fname:str,data):
    fp = open(fname,"wb")
    for d in data:
        fp.write(d)
    fp.close()

# Create a Workbook object
workbook = Workbook()

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

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

# Determine if the worksheet has ole objects
if sheet.HasOleObjects:

    # Iterate through the found objects
    for obj in sheet.OleObjects:

        # If the object type is a Word document, save it to a .docx file
        type = obj.ObjectType
        if type is OleObjectType.WordDocument:
            WriteAllBytes("output/ExtractedFiles/Word-Extracted.docx", obj.OleData)

        # If the object type is an Adobe Acrobat document, save it to a .pdf file
        if type is OleObjectType.AdobeAcrobatDocument:
            WriteAllBytes("output/ExtractedFiles/PDF-Extracted.pdf", obj.OleData)

        # If the object type is a PowerPoint document, save it to a .pptx file
        if type is OleObjectType.PowerPointPresentation:
            WriteAllBytes("output/ExtractedFiles/PPT-Extracted.pptx", obj.OleData)
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 Find and Replace feature in Excel allows you to quickly find specific values and perform targeted replacements based on specific requirements. With it, all occurrences of a specific value can be updated at once, which can significantly improve productivity when working with large data sets. In this article, you will learn how to programmatically find and replace data in Excel in Python using Spire.XLS for Python.

• Find and Replace Data in a Worksheet in Excel
• Find and Replace Data in a Specific Cell Range 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

Find and Replace Data in an Excel Worksheet in Python

Spire.XLS for Python offers the Worksheet.FindAllString() method to find the cells containing specific data values in an Excel worksheet. Once the cells are found, you can use the CellRange.Text property to update their values with new values. The detailed steps are as follows:

• Create a Workbook object.
• Load an Excel file using Workbook.LoadFromFile() method.
• Get a specific worksheet using Workbook.Worksheets[] property.
• Find the cells containing a specific value in the worksheet using Worksheet.FindAllString() method.
• Iterate through the found cells.
• Replace the value of each found cell with another value using CellRange.Text property.
• Set a background color to highlight the cell using CellRange.Style.Color property.
• Save the result file using Workbook.SaveToFile() method.

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

# Create a Workbook object
workbook = Workbook()

# Load an Excel document from disk
workbook.LoadFromFile("input.xlsx")

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

# Find the cells with the specific string value “Total” in the worksheet
ranges = worksheet.FindAllString("Total", False, False)

# Iterate through the found cells
for range in ranges:
    # Replace the value of the cell with another value
    range.Text = "Sum"

    # Set a background color for the cell
    range.Style.Color = Color.get_Yellow()

# Save the result file
workbook.SaveToFile("FindAndReplaceData.xlsx", ExcelVersion.Version2016)
workbook.Dispose()

Find and Replace Data in a Specific Cell Range in Excel in Python

Spire.XLS for Python also allows you to find the cells containing a specific value in a cell range through the CellRange.FindAllString() method. Then you can update the value of each found cell with another value using the CellRange.Text property. The detailed steps are as follows:

• Create a Workbook object.
• Load an Excel file using Workbook.LoadFromFile() method.
• Get a specific worksheet using Workbook.Worksheets[] property.
• Get a specific cell range of the worksheet using Worksheet.Range[] property.
• Find the cells with a specific value in the cell range using CellRange.FindAllString() method.
• Iterate through the found cells.
• Replace the value of each found cell with another value using CellRange.Text property.
• Set a background color to highlight the cell using CellRange.Style.Color property.
• Save the result file using Workbook.SaveToFile() method.

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

# Create a Workbook object
workbook = Workbook()

# Load an Excel document from disk
workbook.LoadFromFile("input.xlsx")

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

# Get a specific cell range
range = sheet.Range["A6:C13"]

# Find the cells with the specific value "Total" in the cell range
cells = range.FindAllString("Total", False, False)

# Iterate through the found cells
for cell in cells:

    # Replace the value of the cell with another value
    cell.Text = "Sum"

    # Set a background color for the cell
    cell.Style.Color = Color.get_Yellow()

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