Use openpyxl - create a new Worksheet, change sheet property in Python

python
Use openpyxl - create a new Worksheet, change sheet property in Python
Page content

Introduction

In previous article, I showed how to create a new Excel file with openpyxl in Python.

In this article, I create a new Worksheet, change sheet property Excel files in Python.

Environment

Runtime environment is as below.

  • python 3.6
  • openpyxl 2.5.6

Create a new Worksheet

Use create_sheet function to add new Worksheet.

 1from openpyxl.workbook import Workbook
 2
 3wb = Workbook()
 4
 5ws1 = wb.create_sheet("Sheet_A")
 6ws1.title = "Title_A"
 7
 8ws2 = wb.create_sheet("Sheet_B", 0)
 9ws2.title = "Title_B"
10
11wb.save(filename = 'sample_book.xlsx')

The create_sheet function can insert sheet at arbitrary position by giving a number to the second argument. Without arguments, create_sheet function adds sheet to the end of Workbook in default.

insert_sheet

Get all sheet names

To get all sheet names of Workbook, access to sheetnames property in Workbook instance.

1wb.sheetnames
2# Returns all sheet names as list
3# ['Title_B', 'Sheet', 'Title_A']

Using for loop to Workbook, it gets each Worksheet instance in Workbook object.

1for ws in wb:
2    print(ws.title)

Select Worksheet

Workbook object has key-value pairs. To get the Worksheet instance, specify the sheet name as key.

1# wb means Workbook object
2ws1 = wb["Title_A"]

Change Worksheet property

Tab color

The sheet_properties property in Worksheet instance has a tabColor attribute. To change tab color, specify the color code.

1ws1.sheet_properties.tabColor = "1072BA"

tab_color

Filter mode

Setting filterMode to True, apply filter mode to specific Worksheet. The structure of data format in the Workseat must be in a format that can apply filters.

1ws1.sheet_properties.filterMode = True

Other properties

The sheet_properties has other worksheet attribute values in addition.

An example is introduced below.

propertytypemeaning
codeNamestrSpecify CodeName
enableFormatConditionsCalculationboolGets or sets a value that determines whether conditional formatting is applied automatically
publishedboolSave a collection of items or items in the document in a web page format
syncHorizontalboolSynchronize the active sheet when scrolling horizontally
syncVerticalboolSynchronize the active sheet when scrolling vertically

Conclusion

It is available to

  • Create a new Worksheet with create_sheet function
  • Get Worksheet instance in Workbook object using key-value.
  • Set worksheet attributes with sheet_properties

Since there are various kinds of operation of the workseat, it will be summarized.