Explanation of python openpyxl

openpyxl Features

openpyxl (read-write excel table) deals with xlsx files produced by Excel 2007 and above. xls and xlsx conversion is easy to notice: If the text code is "gb2312", it will show garbled code after reading, please switch to Unicode first

1. When openpyxl reads and writes a cell, the starting value of the coordinate position of the cell is (1, 1), that is, the minimum value of the subscript is 1, otherwise, an error is reported!

 tableTitle = ['userName', 'Phone', 'age', 'Remark']

 #  Maintenance header 
 #  if row < 1 or column < 1:
 #   raise ValueError("Row or column values must be at least 1")
 #  As above, openpyxl  First row, first column of   Yes   ( 1,1 ) instead of ( 0,0 ), if the coordinate input contains less than 1 The value of, prompting   : Row or column values must be at least 1 That is, the minimum value is 1.
 for col in range(len(tableTitle)):
  c = col + 1
  ws.cell(row=1, column=c).value = tableTitle[col]

2. openpyxl supports direct ordinate access, such as A1 and B2...

ws['A4'] = 4 # Direct assignment 

openpyxl Operation excel

Excel File 3 Objects

workbook: Workbook, 1 excel file containing multiple sheet. sheet: Worksheet, one workbook has multiple, and table names are identified, such as "sheet1", "sheet2", etc. cell: Cells that store data objects

1. Create a new table

A workbook Create at least one worksheet.

worksheet is obtained by openpyxl. workbook. Workbook. active ()

wb = Workbook(encoding='UTF-8')

ws = wb.active

Note:

This method uses the _active_sheet_index attribute, which sets 0 by default, which is the first worksheet. Unless you modify it manually, the first worksheet is obtained by using the active method.

You can also create worksheets by using the openpyxl. workbook. Workbook. create_sheet () method:

 ws = wb.create_sheet("Mysheet") # Insert to last (default)
# Or 
 ws = wb.create_sheet("Mysheet", 0) # Insert into the starting position 

The name of the created sheet will be automatically created, automatically grown according to sheet, sheet1 and sheet2, and its name can be modified through title attribute.

ws.title = "New Title"
ws = wb.create_sheet(title="Pip")

The tab of the default sheet is white, and the color of the sheet tab button can be modified by modifying the sheet_properties. tabColor property with the RRGGBB color:

ws.sheet_properties.tabColor = "1072BA"

When you set the name of sheet, you can think of it as an key in workbook. You can also use the openpyxl. workbook. Workbook. get_sheet_by_name () method

Cell assignment

# Set the value of the cell, 3 A kind of way 
sheet.cell(row=2,column=5).value=99
sheet.cell(row=3,column=5,value=100)
ws['A4'] = 4 #write 

Line-by-line writing

 ws.append(iterable)
 # Add 1 Row to current sheet That is, append line by line from the bottom of the 1 Line start)  iterable Must be list,tuple,dict,range,generator Type of.  1, If it is list, Will list Add sequentially from beginning to end.  2 If it is dict, Add the corresponding key value according to the corresponding key. 
 ws.append([ ' This is A1',  ' This is B1',  ' This is C1'])
 ws.append({ ' A' :  ' This is A1',  ' C' :  ' This is C1'})
 ws.append({1 :  ' This is A1', 3 :  ' This is C1'})

2. Reading table operation

# Open a file: 
from openpyxl import load_workbook
excel=load_workbook('E:/test.xlsx')
# Get sheet : 
table = excel.get_sheet_by_name('Sheet1')  # Get by table name  
# Gets the number of rows and columns: 
rows=table.max_row  # Get the number of rows 
cols=table.max_column  # Get the number of columns 
# Get the cell value: 
Data=table.cell(row=row,column=col).value # Gets the contents of the table, which is obtained from the 1 Line number 1 Columns are derived from 1 At first, be careful not to throw it away  .value

# By name 
  ws = wb["frequency"] 
  # Equivalent to  ws2 = wb.get_sheet_by_name('frequency')
  # I don't know the name index
  sheet_names = wb.get_sheet_names()
  ws = wb.get_sheet_by_name(sheet_names[index])# index For 0 Be the first 1 Table  
# Or 
  ws =wb.active
  #  Equivalent to  ws = wb.get_active_sheet() # Pass _active_sheet_index Set the read table, default 0 Read the first 1 Table 
  # Active table name 
  wb.get_active_sheet().title

Cell usage

ws['A4'] = 4 # Direct assignment 

0

Line-by-line reading

ws['A4'] = 4 # Direct assignment 

1

Shows how many tables there are

ws['A4'] = 4 # Direct assignment 

2

Calculation of formulae by formula

ws["A1"] = "=SUM(1, 1)"
ws["A1"] = "=SUM(B1:C1)"

3. Operation examples

ws['A4'] = 4 # Direct assignment 

4

ws['A4'] = 4 # Direct assignment 

5