course ：
>>> from openpyxl import Workbook
# Create a work order
>>> wb = Workbook()
# Create a default worksheet
>>> ws = wb.active
###########################################################################
# One 、 Worksheet
# 1. Create a new worksheet Workbook.create_sheet()
>>> ws1 = wb.create_sheet("Mysheet") # Insert... At the end ( Default )
# or
>>> ws2 = wb.create_sheet("Mysheet", 0) # Insert... In the first position
# or
>>> ws3 = wb.create_sheet("Mysheet", -1) # Insert... In the penultimate position
# 2. Change the name of the worksheet Worksheet.title
>>> ws1.title = "New Title"
# 3. Change the background color of worksheet name cells Worksheet.sheet_properties.tabColor
>>> ws.sheet_properties.tabColor = "1072BA"
# 4. View all sheet names Workbook.sheetname
>>> print(wb.sheetnames)
# or
>>> for sheet in wb:
... print(sheet.title)
# 5. Create a copy of the worksheet Workbook.copy_worksheet(), The name of the replicated copy is " old name Copy"
>>> source = wb.active
>>> target = wb.copy_worksheet(source)
# Copy only cells （ Include value 、 style 、 Hyperlinks and comments ） And some worksheet properties （ Include dimensions 、 Format and properties ）. All other workbooks will not be copied / Sheet properties - Such as images 、 Chart .
########################################
# Two 、 Cell data
# 1. establish A4 Cell , And the assignment 4
>>> c = ws['A4']
>>> ws['A4'] = 4
>>> print(c.value) # Output A4 The value of the cell
# or Worksheet.cell(),row That's ok column Column value value
>>> ws.cell(row=5, column=2, value=10)
#
>>> for x in range(1,101):
... for y in range(1,101):
... ws.cell(row=x, column=y,value=x * y)
# 2. Access cell range
# Slicing method
>>> cell_range = ws['A1':'C2']
# or Get the range of rows or columns
>>> colC = ws['C']
>>> col_range = ws['C:D']
>>> row10 = ws[10]
>>> row_range = ws[5:10]
# Worksheet.iter_rows() Method returns a row ,row That's ok col Column , Only return cell value parameters "values_only=True"
>>> for row in ws.iter_rows(min_row=1, max_col=3, max_row=2):
... for cell in row:
... print(cell)
<Cell Sheet1.A1>
<Cell Sheet1.B1>
<Cell Sheet1.C1>
<Cell Sheet1.A2>
<Cell Sheet1.B2>
<Cell Sheet1.C2>
# Worksheet.iter_cols() Method returns the column , This method is not available in read-only mode .
>>> for col in ws.iter_cols(min_row=1, max_col=3, max_row=2):
... for cell in col:
... print(cell)
<Cell Sheet1.A1>
<Cell Sheet1.A2>
<Cell Sheet1.B1>
<Cell Sheet1.B2>
<Cell Sheet1.C1>
<Cell Sheet1.C2>
# Traverse all rows or columns Worksheet.rows 、Worksheet.columns（ Column method read-only mode is not available ）
>>> ws = wb.active
>>> ws['C9'] = 'hello world'
>>> tuple(ws.rows)
((<Cell Sheet.A1>, <Cell Sheet.B1>, <Cell Sheet.C1>),
(<Cell Sheet.A2>, <Cell Sheet.B2>, <Cell Sheet.C2>),
(<Cell Sheet.A3>, <Cell Sheet.B3>, <Cell Sheet.C3>),
(<Cell Sheet.A4>, <Cell Sheet.B4>, <Cell Sheet.C4>),
(<Cell Sheet.A5>, <Cell Sheet.B5>, <Cell Sheet.C5>),
(<Cell Sheet.A6>, <Cell Sheet.B6>, <Cell Sheet.C6>),
(<Cell Sheet.A7>, <Cell Sheet.B7>, <Cell Sheet.C7>),
(<Cell Sheet.A8>, <Cell Sheet.B8>, <Cell Sheet.C8>),
(<Cell Sheet.A9>, <Cell Sheet.B9>, <Cell Sheet.C9>))
### 3. Returns the value of the cell Worksheet.values
# Traverse the values of all rows in the worksheet
for row in ws.values:
for value in row:
print(value)
# Worksheet.iter_rows() and Worksheet.iter_cols() You can use 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)
# 4. Save the file Workbook.save(), Will overwrite the existing file
>>> wb = Workbook()
>>> wb.save('balances.xlsx')
# 5. Open an existing job , Load from file
>>> from openpyxl import load_workbook
>>> wb2 = load_workbook('test.xlsx')
>>> print(wb2.sheetnames)
['Sheet2', 'New Title', 'Sheet1']
######################################
# 3、 ... and 、 Merge / Cancel merging cells
>>> from openpyxl.workbook import Workbook
>>> wb = Workbook()
>>> ws = wb.active
>>> ws.merge_cells('A2:D2')
>>> ws.unmerge_cells('A2:D2')
# or Again
>>> ws.merge_cells(start_row=2, start_column=1, end_row=4, end_column=4)
>>> ws.unmerge_cells(start_row=2, start_column=1, end_row=4, end_column=4)
# Four 、 Insert a picture
>>> from openpyxl import Workbook
>>> from openpyxl.drawing.image import Image
>>> wb = Workbook()
>>> ws = wb.active
>>> ws['A1'] = 'You should see three logos below'
>>> img = Image('logo.png')
>>> ws.add_image(img, 'A1')
# Use number format
>>> import datetime
>>> from openpyxl import Workbook
>>> wb = Workbook()
>>> ws = wb.active
>>> # set date using a Python datetime
>>> ws['A1'] = datetime.datetime(2010, 7, 21)
>>>
>>> ws['A1'].number_format
'yyyy-mm-dd h:mm:ss'
# Use the formula
# Please note that , You must use the English name as the function , And function parameters must be separated by commas , Not other punctuation marks , For example, semicolon .
>>> from openpyxl import Workbook
>>> wb = Workbook()
>>> ws = wb.active
>>> # add a simple formula
>>> ws["A1"] = "=SUM(1, 1)"
>>> wb.save("formula.xlsx")
# openpyxl Never calculate formulas , But you can check the name of the formula ：
>>> from openpyxl.utils import FORMULAE
>>> "HEX2DEC" in FORMULAE
True
