Knowledgebase (2330)
Children categories
A clustered column chart and a stacked column chart are two variants of column chart. The clustered column chart enables straightforward comparison of values across different categories, while the stacked column chart displays both the total for each category and the proportion of its individual components. In this article, you will learn how to create clustered or stacked column charts 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
Create a Clustered Column Chart in Excel in Python
To add a chart to a worksheet, use Worksheet.Chart.Add(ExcelChartType chartType) method. The ExcelChartType enumeration includes various chart types predefined in MS Excel. The following are the steps to add a clustered column chart in Excel using Spire.XLS for Python.
- Create a Workbook object.
- Get a specific worksheet through Workbook.Worksheets[index] property.
- Write data into the specified cells.
- Add a clustered column char to the worksheet using Worksheet.Chart.Add(ExcelChartType.ColumnClustered) method.
- Set the chart data through Chart.DataRange property.
- Set the position, title, and other attributes of the chart through the properties under the Chart object.
- Save the workbook to an Excel file using Workbook.SaveToFile() method.
- Python
from spire.xls import *
from spire.xls.common import *
# Create a Workbook object
workbook = Workbook()
# Get the first sheet
sheet = workbook.Worksheets[0]
# Set chart data
sheet.Range["A1"].Value = "Product"
sheet.Range["A2"].Value = "Diet Coke"
sheet.Range["A3"].Value = "Mountain Dew"
sheet.Range["A4"].Value = "Diet Pesi"
sheet.Range["A5"].Value = "Cherry Coke"
sheet.Range["B1"].Value = "Store A"
sheet.Range["B2"].NumberValue = 35000
sheet.Range["B3"].NumberValue = 46000
sheet.Range["B4"].NumberValue = 28000
sheet.Range["B5"].NumberValue = 51000
sheet.Range["C1"].Value = "Store B"
sheet.Range["C2"].NumberValue = 41000
sheet.Range["C3"].NumberValue = 32000
sheet.Range["C4"].NumberValue = 38000
sheet.Range["C5"].NumberValue = 40000
# Set cell style
sheet.Range["A1:C1"].RowHeight = 15
sheet.Range["A1:C1"].Style.Color = Color.get_Black()
sheet.Range["A1:C1"].Style.Font.Color = Color.get_White()
sheet.Range["A1:C1"].Style.VerticalAlignment = VerticalAlignType.Center
sheet.Range["A1:C1"].Style.HorizontalAlignment = HorizontalAlignType.Center
sheet.AutoFitColumn(1)
# Add a chart to the sheet
chart = sheet.Charts.Add(ExcelChartType.ColumnClustered)
# Set data range of chart
chart.DataRange = sheet.Range["A1:C5"]
chart.SeriesDataFromRange = False
# Set position of the chart
chart.LeftColumn = 5
chart.TopRow = 1
chart.RightColumn = 14
chart.BottomRow = 21
# Set chart title
chart.ChartTitle = "Store Wise Soda Soft Drink Sales"
chart.ChartTitleArea.IsBold = True
chart.ChartTitleArea.Size = 12
# Set axis title
chart.PrimaryCategoryAxis.Title = "Product"
chart.PrimaryCategoryAxis.Font.IsBold = True
chart.PrimaryCategoryAxis.TitleArea.IsBold = True
chart.PrimaryValueAxis.Title = "Sales"
chart.PrimaryValueAxis.HasMajorGridLines = False
chart.PrimaryValueAxis.TitleArea.IsBold = True
chart.PrimaryValueAxis.TitleArea.TextRotationAngle = 90
# Set series color, overlap, gap width and data labels
series = chart.Series
for i in range(len(series)):
cs = series[i]
cs.Format.Options.IsVaryColor = True
cs.Format.Options.Overlap = -50
cs.Format.Options.GapWidth = 350
cs.DataPoints.DefaultDataPoint.DataLabels.HasValue = True
# Set legend position
chart.Legend.Position = LegendPositionType.Top
# Save the document
workbook.SaveToFile("ClusteredColumnChart.xlsx", ExcelVersion.Version2016)
Create a Stacked Column Chart in Excel in Python
The process of creating a stacked column chart is similar to that of creating a clustered column chart. The only difference is that you must change the Excel chart type from ColumnClustered to ColumnStacked.
- Create a Workbook object.
- Get a specific worksheet through Workbook.Worksheets[index] property.
- Write data into the specified cells.
- Add a clustered column char to the worksheet using Worksheet.Chart.Add(ExcelChartType.ColumnStacked) method.
- Set the chart data through Chart.DataRange property.
- Set the position, title, and other attributes of the chart through the properties under the Chart object.
- Save the workbook to an Excel file using Workbook.SaveToFile() method.
- Python
from spire.xls import *
from spire.xls.common import *
# Create a Workbook object
workbook = Workbook()
# Get the first sheet
sheet = workbook.Worksheets[0]
# Set chart data
sheet.Range["A1"].Value = "Product"
sheet.Range["A2"].Value = "Diet Coke"
sheet.Range["A3"].Value = "Mountain Dew"
sheet.Range["A4"].Value = "Diet Pesi"
sheet.Range["A5"].Value = "Cherry Coke"
sheet.Range["B1"].Value = "Store A"
sheet.Range["B2"].NumberValue = 35000
sheet.Range["B3"].NumberValue = 46000
sheet.Range["B4"].NumberValue = 28000
sheet.Range["B5"].NumberValue = 51000
sheet.Range["C1"].Value = "Store B"
sheet.Range["C2"].NumberValue = 41000
sheet.Range["C3"].NumberValue = 32000
sheet.Range["C4"].NumberValue = 38000
sheet.Range["C5"].NumberValue = 40000
# Set cell style
sheet.Range["A1:C1"].RowHeight = 15
sheet.Range["A1:C1"].Style.Color = Color.get_Black()
sheet.Range["A1:C1"].Style.Font.Color = Color.get_White()
sheet.Range["A1:C1"].Style.VerticalAlignment = VerticalAlignType.Center
sheet.Range["A1:C1"].Style.HorizontalAlignment = HorizontalAlignType.Center
sheet.AutoFitColumn(1)
# Add a chart to the sheet
chart = sheet.Charts.Add(ExcelChartType.ColumnStacked)
# Set data range of chart
chart.DataRange = sheet.Range["A1:C5"]
chart.SeriesDataFromRange = False
# Set position of the chart
chart.LeftColumn = 5
chart.TopRow = 1
chart.RightColumn = 14
chart.BottomRow = 21
# Set chart title
chart.ChartTitle = "Store Wise Soda Soft Drink Sales"
chart.ChartTitleArea.IsBold = True
chart.ChartTitleArea.Size = 12
# Set axis title
chart.PrimaryCategoryAxis.Title = "Product"
chart.PrimaryCategoryAxis.Font.IsBold = True
chart.PrimaryCategoryAxis.TitleArea.IsBold = True
chart.PrimaryValueAxis.Title = "Sales"
chart.PrimaryValueAxis.HasMajorGridLines = False
chart.PrimaryValueAxis.TitleArea.IsBold = True
chart.PrimaryValueAxis.TitleArea.TextRotationAngle = 90
# Set series color, gap width and data labels
series = chart.Series
for i in range(len(series)):
cs = series[i]
cs.Format.Options.IsVaryColor = True
cs.Format.Options.GapWidth = 270
cs.DataPoints.DefaultDataPoint.DataLabels.HasValue = True
cs.DataPoints.DefaultDataPoint.DataLabels.Position = DataLabelPositionType.Inside
# Set legend position
chart.Legend.Position = LegendPositionType.Top
# Save the document
workbook.SaveToFile("StackedColumnChart.xlsx", ExcelVersion.Version2016)
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.
Spire.Doc for Python is a Python library for reading, creating, editing and converting Word (.doc & .docx) files in any Python application. This article shows you how to install Spire.Doc for Python on Windows.
Step 1
Download the latest version of Python and install it on your computer. If you have already installed it, skip to step 2.
Step 2
Click "Extensions" in VS Code, search for "Python" and then install it.
Step 3
Click "Explorer" - "NO FOLRDER OPENED" - "Open Folder".
Choose an existing folder as the workspace, or you can create a new folder and then select it.
Add a .py file to the folder you just added (Python folder in this case), and name it whatever you like.
Step 4
Click "Terminal" and then "New Terminal".
Input the following pip command to install Spire.Doc for Python and plum-dispatch v1.7.4.
pip install Spire.Doc
Alternatively, you can download Spire.Doc for Python from our website, and unzip it to get two .whl files from the "lib" folder. They're for Linux and Windows systems, respectively.
Then, install Spire.Doc for Python and plum-dispatch v1.7.4 by running the following commands.
pip install E:\Library\Python\spire.doc.python_11.8.0\lib\Spire.Doc_for_Python-11.8.0-py3-none-win_amd64.whl
Step 5
Add the following code snippet to the "HelloWorld.py" file.
- Python
from spire.doc.common import *
from spire.doc import *
document = Document()
section = document.AddSection()
paragraph = section.AddParagraph()
paragraph.AppendText("Hello World!")
document.SaveToFile("output.docx", FileFormat.Docx)
document.Close()
Once you run the Python file, you'll see the result Word document in the "EXPORER" panel.
Copying worksheets in Excel is a critical skill to have, especially when you need to create new worksheets based on existing ones. By utilizing the copy function, you can avoid potential errors that may arise from manually recreating the same content. This not only saves time and effort but also ensures that your data remains accurate and reliable. In this article, we will demonstrate how to copy worksheets in Excel in Python using Spire.XLS for Python.
- Copy a Worksheet in the Same Excel Workbook
- Copy a Worksheet to Another Excel Workbook
- Copy Visible Worksheets to a New Excel Workbook
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 a Worksheet in the Same Excel Workbook in Python
You can copy a worksheet within the same workbook by adding a new worksheet to the workbook and then copying the data from the original worksheet to the new one.
The following steps demonstrate how to copy a worksheet within the same workbook:
- Initialize an instance of the Workbook class.
- Load an Excel workbook using the Workbook.LoadFromFile() method.
- Get a specific worksheet using the Workbook.Worksheets[int index] property.
- Add a new worksheet to the workbook using the Workbook.Worksheets.Add() method.
- Copy the data from the specific worksheet to the new worksheet using the Worksheet.CopyFrom(Worksheet worksheet) method.
- Save the resulting workbook to another file using the Workbook.SaveToFile() method.
- Python
from spire.xls import *
from spire.xls.common import *
#Initialize an instance of the Workbook class
workbook = Workbook()
#Load an Excel workbook
workbook.LoadFromFile("Input.xlsx")
#Get the first worksheet
sourceSheet = workbook.Worksheets[0]
sheetName = sourceSheet.Name + "_Copy"
#Add a new worksheet with a specific name to the workbook
destSheet = workbook.Worksheets.Add(sheetName)
#Copy the first worksheet to the newly added worksheet
destSheet.CopyFrom(sourceSheet)
#Save the result workbook to another file
workbook.SaveToFile("CopyInSameWorkbook.xlsx", ExcelVersion.Version2013)
workbook.Dispose()
Copy a Worksheet to Another Excel Workbook in Python
To copy a worksheet from one workbook to another, you need to add a new worksheet to the destination workbook and then copy the data of the worksheet from the source workbook to the new worksheet of the destination workbook. If you want to maintain the source formatting, you can copy the theme of the source workbook to the destination workbook.
The following steps demonstrate how to copy a worksheet from one workbook to another:
- Initialize an instance of the Workbook class.
- Load the source workbook using the Workbook.LoadFromFile() method.
- Get a specific worksheet using the Workbook.Worksheets[int index] property.
- Initialize an instance of the Workbook class.
- Load the destination workbook using the Workbook.LoadFromFile() method.
- Add a new worksheet to the destination workbook using the Workbook.Worksheets.Add() method.
- Copy the specific worksheet of the source workbook to the new worksheet of the destination workbook using the Worksheet.CopyFrom(Worksheet worksheet) method.
- Copy the theme from the source workbook to the destination workbook using the Workbook.CopyTheme (Workbook srcWorkbook) method.
- Save the resulting workbook to another file using the Workbook.SaveToFile() method.
- Python
from spire.xls import *
from spire.xls.common import *
#Initialize an instance of the Workbook class
sourceWorkbook = Workbook()
#Load the source Excel workbook
sourceWorkbook.LoadFromFile("CopyWorksheets-Input.xlsx")
#Get the first worksheet of the source workbook
sourceSheet = sourceWorkbook.Worksheets[0]
#Get the name of the first worksheet
sheetName = sourceSheet.Name + "_Copy"
#Initialize an instance of the Workbook class
destWorkbook = Workbook()
#Load the destination Excel workbook
destWorkbook.LoadFromFile("CopyWorksheets-Sample.xlsx")
#Add a new worksheet with a specific name to the destination workbook
destSheet = destWorkbook.Worksheets.Add(sheetName)
#Copy the first worksheet of the source workbook to the new worksheet of the destination workbook
destSheet.CopyFrom(sourceSheet)
#Copy the theme from the source workbook to the destination workbook
destWorkbook.CopyTheme(sourceWorkbook)
#Save the destination workbook to another file
destWorkbook.SaveToFile("CopyToAnotherWorkbook.xlsx", ExcelVersion.Version2013)
sourceWorkbook.Dispose()
destWorkbook.Dispose()
Copy Visible Worksheets to a New Excel Workbook in Python
If you only want to share visible worksheets rather than the entire workbook with others, you can copy the visible worksheets to a new workbook.
The following steps demonstrate how to copy visible worksheets from a workbook to a new workbook:
- Initialize an instance of the Workbook class.
- Load the source workbook using the Workbook.LoadFromFile() method.
- Initialize an instance of the Workbook class to create a new workbook, then clear the default worksheets in the new workbook using the Workbook.Worksheets.Clear() method.
- Iterate through all the worksheets in the source workbook.
- Check if the current worksheet is visible using the Worksheet.Visibility property.
- If the result is true, add a new worksheet to the new workbook using the Workbook.Worksheets.Add() method.
- Copy the worksheet from the source workbook to the new worksheet of the new workbook using the Worksheet.CopyFrom(Worksheet worksheet) method.
- Copy the theme from the source workbook to the new workbook using the Workbook.CopyTheme(Workbook srcWorkbook) method.
- Save the resulting workbook to another file using the Workbook.SaveToFile() method.
- Python
from spire.xls import *
from spire.xls.common import *
#Initialize an instance of the Workbook class
sourceWorkbook = Workbook()
#Load the source Excel workbook
sourceWorkbook.LoadFromFile("CopyWorksheets-Input.xlsx")
#Initialize an instance of the Workbook class to create a new workbook
newWorkbook = Workbook()
newWorkbook.Version = ExcelVersion.Version2013
#Clear the default worksheets in the new workbook
newWorkbook.Worksheets.Clear()
#Iterate through all the worksheets in the source workbook
for sourceSheet in sourceWorkbook.Worksheets:
#Check if the current worksheet is visible
if sourceSheet.Visibility == WorksheetVisibility.Visible:
sheetName = sourceSheet.Name + "_Copy"
#Add a new worksheet with a specific name to the new workbook
destSheet = newWorkbook.Worksheets.Add(sheetName)
#Copy the worksheet from the source workbook to the new worksheet of the new workbook
destSheet.CopyFrom(sourceSheet)
#Copy the theme from the source workbook to the new workbook
newWorkbook.CopyTheme(sourceWorkbook)
#Save the new workbook to another file
newWorkbook.SaveToFile("CopyVisibleSheetsToNewWorkbook.xlsx", ExcelVersion.Version2013)
sourceWorkbook.Dispose()
newWorkbook.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.
