Hướng dẫn dùng ws2 python

Introduction

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

Nội dung chính

• Introduction
• Environment
• Get all sheet names
• Select Worksheet
• Change Worksheet property
• Filter mode
• Other properties
• Get all sheet names. Find the sheetwise maximum row and column in a particular sheet

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

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.

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"

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.

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.

Consider:

import openpyxl
wb = openpyxl.load_workbook('D:\excel.xlsx')
wb.get_sheet_names()

After this, I can see the worksheets (85) that the Excel file has. Now I want to go into each sheet, edit data in 2-3 cells (which is same for all the sheets) and then save the file.

asked Aug 18, 2017 at 12:32

2

In your case I see the easiest is probably

import openpyxl

n = 0
wb = openpyxl.load_workbook('D:\excel.xlsx')
sheets = wb.sheetnames
ws = wb[sheets[n]]

Where n is the sheetnumber (0 is the first). Put that in a loop and increase n when you want to change the sheet.

answered Aug 18, 2017 at 12:41

When mentioning the path, use forward slash rather than backslash.

Get all sheet names. Find the sheetwise maximum row and column in a particular sheet

import openpyxl

workBook = load_workbook(filename='D:/excel.xlsx')
sheets = workBook.sheetnames
i = 1
for s_name in sheets:
    print(s_name)
    sheet = workBook[s_name]
    m_row = sheet.max_row
    m_col = sheet.max_column
    print(m_row)
    print(m_col)

answered Nov 8, 2019 at 11:01

1