Content controls play an important role in Excel, providing powerful functionality for data input, display, and user interaction. These controls include text boxes, radio buttons, checkboxes, drop-down lists, and more. They offer users more efficient, intuitive, and flexible ways of handling data, making Excel a powerful tool for data management and analysis. This article will introduce how to use Spire.XLS for Python to add content controls to Excel documents or edit content controls in Excel documents using Python.
- Add Content Controls to an Excel document in Python
- Edit Content Controls in an Excel document 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
Add Content Controls to an Excel document in Python
Spire.XLS for Python allows you to add content controls supported by Excel, such as text boxes, radio buttons, drop-down lists (also known as combo boxes), checkboxes, and more. The following are the detailed steps:
- Create an object of the Workbook class.
- Use the Workbook.LoadFromFile() method to load an Excel data document.
- Use the Workbook.Worksheets[] property to retrieve the desired worksheet.
- Add a text box using the Worksheet.TextBoxes.AddTextBox() method.
- Add a radio button using the Worksheet.RadioButtons.Add() method.
- Add a combo box using the Worksheet.ComboBoxes.AddComboBox() method.
- Add a checkbox using the Worksheet.CheckBoxes.AddCheckBox() method.
- Use the Workbook.SaveToFile() method to save the resulting file.
- Python
from spire.xls.common import *
from spire.xls import *
# Create a new Workbook object
workbook = Workbook()
# Load an existing Excel file
workbook.LoadFromFile("Data/Sample01.xlsx")
# Select the first worksheet
worksheet = workbook.Worksheets[0]
# Set the height of the text box (unit: points)
height = 40
# Add a text box to the worksheet
textbox = worksheet.TextBoxes.AddTextBox(3, 2, height, 400)
textbox.Line.ForeKnownColor = ExcelColors.Black
textbox.Text = "Andy"
# Add radio buttons to the worksheet
radioButton1 = worksheet.RadioButtons.Add(5, 2, height, 60)
radioButton1.Text = "Male"
radioButton1.CheckState=CheckState.Checked
radioButton2 = worksheet.RadioButtons.Add(5, 3, height, 60)
radioButton2.Text = "Female"
# Assign a data range from another worksheet to a combo box
dataSheet = workbook.Worksheets[1]
comboBoxShape = worksheet.ComboBoxes.AddComboBox(7, 2, 30, 200)
comboBoxShape.ListFillRange = dataSheet.Range["A1:A7"]
comboBoxShape.SelectedIndex=1
# Add check boxes to the worksheet
checkBox = worksheet.CheckBoxes.AddCheckBox(9, 2, height, 60)
checkBox.CheckState = CheckState.Checked
checkBox.Text = "Sing"
checkBox = worksheet.CheckBoxes.AddCheckBox(9, 3, height, 60)
checkBox.CheckState = CheckState.Unchecked
checkBox.Text = "Dance"
checkBox = worksheet.CheckBoxes.AddCheckBox(9, 4, height, 60)
checkBox.CheckState = CheckState.Checked
checkBox.Text = "Exercise"
checkBox = worksheet.CheckBoxes.AddCheckBox(9, 5, height, 100)
checkBox.CheckState = CheckState.Unchecked
checkBox.Text = "Musical Instruments"
# Save the modified workbook to a new file
workbook.SaveToFile("AddContentControls.xlsx", ExcelVersion.Version2016)
# Clean up and release the workbook object
workbook.Dispose()
Edit Content Controls in an Excel document in Python
Spire.XLS for Python can also modify the properties of existing content controls in an Excel document, such as changing the text of a text box, resetting the selected item of a drop-down list, hiding a specific content control, and more. Here are the detailed steps:
- Create an object of the Workbook class.
- Use the Workbook.LoadFromFile() method to load an Excel document.
- Use the Workbook.Worksheets[] property to retrieve the desired worksheet.
- Modify the display content of a text box using the Worksheet.TextBoxes[].Text property.
- Set whether to display a specific text box using the Worksheet.TextBoxes[].Visible property.
- Set whether a radio button is checked using the Worksheet.RadioButtons[].CheckState property.
- Set the selected item of a combo box using the Worksheet.ComboBoxes[].SelectedIndex property.
- Set whether a checkbox is checked using the Worksheet.CheckBoxes[].CheckState property.
- Use the Workbook.SaveToFile() method to save the resulting file.
- Python
from spire.xls.common import *
from spire.xls import *
# Create a Workbook object
workbook = Workbook()
# Load Excel data from file
workbook.LoadFromFile("Data/Sample02.xlsx")
# Get the first worksheet
worksheet = workbook.Worksheets[0]
# Set the text content of the first textbox to "Gary"
worksheet.TextBoxes[0].Text = "Gary"
# Set the text content of the second textbox to "gary.li@gmail.com"
worksheet.TextBoxes[1].Text = "gary.li@gmail.com"
# Hide the fourth textbox
worksheet.TextBoxes[3].Visible = False
# Check the first radio button
worksheet.RadioButtons[0].CheckState = CheckState.Checked
# Set the selected index of the first combobox to 0 (the first option)
worksheet.ComboBoxes[0].SelectedIndex = 0
# Check the first checkbox
worksheet.CheckBoxes[0].CheckState = CheckState.Checked
# Uncheck the third checkbox
worksheet.CheckBoxes[2].CheckState = CheckState.Unchecked
# Save the modified workbook to a new file
workbook.SaveToFile("EditContentControls.xlsx", ExcelVersion.Version2016)
# Clean up and release the workbook object
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.
