Using tutorials

1, Working with workbooks

1.1. Create a new workbook

There is no need to create it in the file system in advance The xlsx file only needs to import the Workbook class and start working:

from openpyxl import Workbook
wb = Workbook()

1.2. Load Workbook

openpyxl. load_ The Workbook () method to open an existing workbook

from openpyxl import load_workbook
wb2 = load_workbook('test.xlsx')
print wb2.sheetnames
['Sheet2', 'New Title', 'Sheet1']

1.3 save Workbook

The simplest and safest way is to use the Workbook of the Workbook object Save() method:

wb = Workbook()
wb.save('balances.xlsx')

This operation will overwrite the existing file with the same name in the current directory without any prompt

1.4. Save as data stream

If you want to save a file as a data stream, for example, when you use a Web application such as Pyramid, Flask or Django, you can provide a NamedTemporaryFile():

>>> from tempfile import NamedTemporaryFile
>>> from openpyxl import Workbook
>>> wb = Workbook()
>>> with NamedTemporaryFile() as tmp:
        wb.save(tmp.name)
        tmp.seek(0)
        stream = tmp.read()

This part hasn't been touched yet. Skip it.

2, Methods and properties of the workbook

2.1. Get the default worksheet

After instantiating a workbook, a worksheet will always be created by default. You can use workbook Get it using the active property:

 ws = wb.active

The default pointing index of this worksheet is 0 unless you manually change it to another value. Otherwise, you can only use this method to get the first worksheet

2.2. Create a new worksheet

You can use workbook create_ Sheet () method to create a new worksheet:

ws1 = wb.create_sheet("Mysheet") # Insert at the end (default)
# or
ws2 = wb.create_sheet("Mysheet", 0) # Insert at the front end
# or
ws3 = wb.create_sheet("Mysheet", -1) # Insert in penultimate position

2.3. Worksheet naming

1. )Use worksheet Rename with the title attribute:

ws.title = "New Title"

1. )Color of sheet name label
   The default background color of title is white You can use the sheet_ properties. The tabcolor property assigns different RRGGBB color codes to change the background color of the title:

ws.sheet_properties.tabColor = "1072BA"

2.4. Index the worksheet by name

Once you name the worksheet, you can get it through the key of the workbook:

ws3 = wb["New Title"]

2.5. Get the table name in the workbook

Via workbook Sheetnames property

print(wb.sheetnames)#['Mysheet2', 'New Title', 'Mysheet3', 'Mysheet1']

2.6. Get worksheet in Workbook

Via workbook Worksheets property

print(wb.worksheets)#[<Worksheet "Mysheet2">, <Worksheet "New Title">, <Worksheet "Mysheet3">, <Worksheet "Mysheet1">]

2.7 iteration of each worksheet

for x in wb:    print(x.title, end=" ")print()

2.8. Create a copy of the worksheet:

Workbook.copy_worksheet() method:

source = wb.activetarget = wb.copy_worksheet(source)

The above method only copies cells (including values, styles, hyperlinks, and comments) and some worksheet attributes (including dimensions, formatting, and attributes). All other Workbook / sheet properties are not copied - for example, images, charts.

You cannot copy worksheets between workbooks. Sheets cannot be copied if the workbook is opened in read-only or write only mode

3, Related functions and methods of worksheet

3.1. Access a cell

• You can access cells directly from the coordinates of the worksheet:

  c = ws['A4']
  

  This will return an A4 cell or create one if it does not exist.

• Can be assigned directly:

  ws['A4'] = 4
  

• Through cells value attribute access

  >>> c.value = 'hello, world'>>> print(c.value)'hello, world'>>> d.value = 3.14>>> print(d.value)3.14
  

• Via worksheet Cell() method

  d = ws.cell(row=4, column=2, value=10)
  

3.2. Accessing multiple cells

• You can use tiles to access cells within a specified range: cell_range = ws['A1':'C2']
  The range of rows and columns can also be specified as follows:

  colC = ws['C']col_range = ws['C:D']row10 = ws[10]row_range = ws[5:10]
  

• Use worksheet iter_ The rows() method returns rows in the specified range:

  for col in ws.iter_rows(min_row=1, max_row=2, min_col=1, max_col=3):    for cell in col:        print(cell)"""<Cell Sheet1.A1><Cell Sheet1.B1><Cell Sheet1.C1><Cell Sheet1.A2><Cell Sheet1.B2><Cell Sheet1.C2>"""
  

• Use worksheet iter_ The cols () method will return columns in the specified range:

  for col in ws.iter_cols(min_row=1, max_row=2, min_col=1, max_col=3):    for cell in col:        print(cell)"""<Cell Sheet1.A1><Cell Sheet1.A2><Cell Sheet1.B1><Cell Sheet1.B2><Cell Sheet1.C1><Cell Sheet1.C2>"""
  

  For performance reasons, worksheet iter_ The cols () method is not available in read - only mode

• Cells used in iteration:

  for row in ws.rows:    for col in row:        val = col.value        if val is not None:            print(col.value, end=' ')    print()
  

3.3. Get the largest column and row

You can get the count of rows and columns similar to usedrag

print(ws.max_row)print(ws.max_column)

3.4. Get cell value

• Use worksheet Values attribute

  for row in ws.values:   for value in row:     print(value)
  

• Worksheet.iter_rows() and worksheet iter_ Cols() can be set by setting values_only parameter to return only the value of the cell:

  for row in ws.iter_rows(min_row=1, max_col=3, max_row=2, values_only=True):   print(row)(None, None, None)(None, None, None)
  

3.5. Merge cells

. . . . . . If you can't use it for the time being, skip it first and send some excellent articles. If you use it, continue to sort it out.

Python uses openpyxl to operate Excel (I) - Zhihu (zhihu.com)

On Python_Openpyxl usage (most complete summary)

Openpyxl super detailed notes_ Blog for non serious Python learning - CSDN blog_ openpyxl

openpyxl 3.0.0 document (gitee.io)

Use function

Merge / Unmerge cells

insert graphic images

Collapse (outline)

Using number formats

Insert and delete rows and columns, and move cells within the range

Charts