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.

propertytypemeaning

codeName	str	Specify CodeName
enableFormatConditionsCalculation	bool	Gets or sets a value that determines whether conditional formatting is applied automatically
published	bool	Save a collection of items or items in the document in a web page format
syncHorizontal	bool	Synchronize the active sheet when scrolling horizontally
syncVertical	bool	Synchronize 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.

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

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