A workbook can consist of one or more worksheets, and each worksheet is independent. When dealing with an existing Excel file or creating a new Excel file from scratch, we can add worksheets as needed to better manage and analyze data. In this article, we will show you how to add worksheets to Excel programmatically by using Spire.XLS for Python.
- Add a Worksheet to an Existing Excel file
- Add a Worksheet to a New Excel file
- Add Multiple Worksheets to a New Excel file
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 a Worksheet to an Existing Excel file
Spire.XLS for Python allows users to add a new worksheet to an existing Excel file by using Workbook.Worksheets.Add(sheetName) method. The following are detailed steps.
- Create an object of Workbook class.
- Load an Excel file using Workbook.LoadFromFile() method.
- Add a new sheet to this file using Workbook.Worksheets.Add(sheetName) method.
- Add desired text to cell A1 by Worksheet.Range[cellName].Text property.
- Save the result file using Workbook.SaveToFile() method.
- Python
from spire.xls import *
from spire.xls.common import *
inputFile = "sample.xlsx"
outputFile = "AddWorksheet.xlsx"
#Create an object of Workbook class
workbook = Workbook()
#Load an Excel file from disk
workbook.LoadFromFile(inputFile)
#Add a new worksheet to this file
sheet = workbook.Worksheets.Add("New Sheet")
#Add desired text to cell A1
sheet.Range["A1"].Text = "This is a new sheet."
#Save the result file
workbook.SaveToFile(outputFile, ExcelVersion.Version2013)
workbook.Dispose()
Add a Worksheet to a New Excel file
In addition to adding worksheets to existing Excel files, you can also add worksheets to a newly created Excel files with the same method. You just need to clear the default worksheet before adding by calling Workbook.Worksheets.Clear() method. The following are detailed steps.
- Create an object of Workbook class.
- Clear the default worksheets using Workbook.Worksheets.Clear() method.
- Add a new sheet to the new workbook by using Workbook.Worksheets.Add(sheetName) method.
- Add desired text to cell A1 by Worksheet.Range[cellName].Text property.
- Save the result file using Workbook.SaveToFile() method.
- Python
from spire.xls import *
from spire.xls.common import *
outputFile = "AddWorksheet.xlsx"
#Create an object of Workbook class
workbook = Workbook()
#Clear the default sheets
workbook.Worksheets.Clear()
#Add a new worksheet to the new file
sheet = workbook.Worksheets.Add("New Sheet")
#Add desired text to cell A1
sheet.Range["A1"].Text = "This is a new sheet."
#Save the result file
workbook.SaveToFile(outputFile, ExcelVersion.Version2013)
workbook.Dispose()
Add Multiple Worksheets to a New Excel file
If you want to add multiple worksheets to a newly created Excel file, you can use Workbook.CreateEmptySheets(sheetCount) method to add a specified number of worksheets. The following are detailed steps.
- Create an object of Workbook class.
- Add three sheets to this file by using Workbook.CreateEmptySheets(sheetCount) method.
- Loop through the added worksheets and add text to cell A1 in each worksheet by Worksheet.Range[cellName].Text property.
- Save the result file using Workbook.SaveToFile() method.
- Python
from spire.xls import *
from spire.xls.common import *
outputFile = "AddWorksheet.xlsx"
#Create an object of Workbook class
workbook = Workbook()
#Add three sheets to this file
sheetCount = 3
workbook.CreateEmptySheets(sheetCount)
#Loop through the added worksheets
for i in range(sheetCount):
#Add text to cell A1 in each worksheet
sheet = workbook.Worksheets[i]
sheet.Range["A1"].Text = "This is Sheet{}".format(i+1)
#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.
