• 欢迎光临~

# openpyxl模块

openpyxl写操作

``````# d = {"name": "tony", "age": 16, "address": "北京"}
d2= [{"name": "tony", "age": 16, "address": "北京"},{"name": "tom", "age": 18, "address": "安徽"},{"name": "Bob", "age": 24, "address": "未知"}]
from openpyxl import Workbook

wb = Workbook()  # 创建一个工作表
ws = wb.active  # ws操作sheet页
ws1 = wb.create_sheet('Mysheetfirst', 0)  #创建sheet

# 表头
ws1['A1'] = 'name'
ws1['B1'] = 'age'

# dict_items = d.items()

# 写数据
index = 1
for dic in d2:
index+=1
ws1.cell(index, 1).value = dic.get("name")
ws1.cell(index, 2).value = dic.get("age")

wb.save('test.xlsx')

``````

``````# 使用索引定位单元格然后复制
sheet['A1'] = 'hello word'
print(sheet['A1'].value)

``````
``````#循环遍历给每行单元格赋值
sheet1 = wb.create_sheet('range name')   # 首先创建一个工作表
for row in range(1, 50):   # 定义输入行数有50行
sheet1.append(range(10))   # 给每行的数据为0-9

``````
``````# 循环给每一行添加自然数序列
sheet1 = wb.create_sheet('range name')
for row in range(1, 50):
rows = sheet1.append(range(10))

``````
``````#通过一个列表给单元格添加数据
sheet2 = wb.create_sheet('List')
row_list = [
['Number', 'step1', 'step2'],
[2, 50, 30],
[3, 40, 50],
[4, 25, 20],
[5, 15, 9],
[6, 12, 31]
]
for row in row_list:
sheet2.append(row)

``````
``````#指定行列范围写入数据

sheet3 = wb.create_sheet(title='data sheet')
for row in range(3, 20):
for col in range(10, 30):
sheet3.cell(column=col, row=row, value=get_column_letter(col))

``````
``````#直接使用save()函数
wb.save('filename')

# 如果文件是加载进来的，建议保存的时候另外起一个名称，不要损坏源文件
wb.save('other filename')

# 默认创建的表单，是直接追加在末尾的
wb.create_sheet('Lastsheet')
print(wb.sheetnames)
['mysheet', 'Lastsheet']

# 我们也可以指定索引
wb.create_sheet('First sheet', index=0)
wb.create_sheet('Middle sheet', index=1)
print(wb.sheetnames)
['First sheet', 'Middle sheet', 'my sheet']

# 删除表单
wb.remove_sheet(wb.get_sheet_by_name('Middle sheet'))
print(wb.sheetnames)
['First sheet', 'Middle sheet', 'my sheet']
['First sheet', 'my sheet']

# 虽然也可以运行成功，但是编译器还是报了一点错
# 也可以直接使用del删除
del wb['Middle sheet']

``````

openpyxl读操作

``````load_workbook()函数接受文件名，返回一个 workbook 数据类型的值。这个 workbook 对象代表这个 Excel 文件，有点类似 File 对象代表一个打开的文本文件。
>>> import openpyxl
>>> type(wb)
<class 'openpyxl.workbook.workbook.Workbook'>

``````

#### get_sheet_names()

``````>>> import openpyxl
>>> wb.get_sheet_names()
['Sheet1', 'Sheet2', 'Sheet3']

``````

#### get_sheet_by_name()

``````>>> sheet = wb.get_sheet_by_name('Sheet3')
>>> sheet
<Worksheet "Sheet3">

``````

#### 属性：active

``````>>> anotherSheet = wb.active
>>> anotherSheet.title
'城市交通连通性'

``````

#### 对象：cell

``````>>> import openpyxl
>>> sheet = wb.get_sheet_by_name('Sheet1')
>>> sheet['A1'] <Cell Sheet1.A1>
>>> sheet['A1'].value datetime.datetime(2015, 4, 5, 13, 34, 2)
>>> print(sheet['A1'].value) 2015-04-05 13:34:02
>>> c = sheet['B1']
>>> c.value 'Apples'
>>> 'Row ' + str(c.row) + ', Column ' + str(c.column) + ' is ' + c.value
'Row 1, Column 2 is Apples'
>>> 'Cell ' + c.coordinate + ' is ' + c.value
'Cell B1 is Apples'
>>> sheet['C1'].value
73

``````
• `row``cell`对象的行
• `column``cell`对象的列
• `coordinate``cell`对象的位置信息

#### cell()

``````>>> sheet.cell(row=1, column=2)
<Cell Sheet1.B1>
>>> sheet.cell(row=1, column=2).value
'Apples'
>>> for i in range(1, 8, 2):
print(i, sheet.cell(row=i, column=2).value)
1 Apples
3 Pears
5 Apples
7 Strawberries

``````

#### 属性：max_row和max_column

``````>>> sheet.max_row
7
>>> sheet.max_column
3

``````

#### column_index_from_string()

``````>>> from openpyxl.utils import get_column_letter, column_index_from_string
>>> column_index_from_string('AA')
27

``````

#### get_column_letter()

``````>>> get_column_letter(1)
'A'
>>> get_column_letter(900)
'AHP'
>>> sheet = wb.get_sheet_by_name('Sheet1')
>>> get_column_letter(sheet.max_column)
'C'

``````

#### Worksheet 对象切片操作

``````>>> import openpyxl
>>> sheet = wb.get_sheet_by_name('Sheet1')
>>> tuple(sheet['A1':'C3'])
((<Cell Sheet1.A1>, <Cell Sheet1.B1>, <Cell Sheet1.C1>), (<Cell Sheet1.A2>, <Cell Sheet1.B2>, <Cell Sheet1.C2>), (<Cell Sheet1.A3>, <Cell Sheet1.B3>, <Cell Sheet1.C3>))
>>> sheet['A1':'C3']
((<Cell 'Sheet1'.A1>, <Cell 'Sheet1'.B1>, <Cell 'Sheet1'.C1>), (<Cell 'Sheet1'.A2>, <Cell 'Sheet1'.B2>, <Cell 'Sheet1'.C2>), (<Cell 'Sheet1'.A3>, <Cell 'Sheet1'.B3>, <Cell 'Sheet1'.C3>))
>>> for rowOfCellObjects in sheet['A1':'C3']:
for cellObj in rowOfCellObjects:
print(cellObj.coordinate, cellObj.value)
print('--- END OF ROW ---')
A1 2015-04-05 13:34:02
B1 Apples
C1 73
--- END OF ROW ---
A2 2015-04-05 03:41:23
B2 Cherries
C2 85
--- END OF ROW ---
A3 2015-04-06 12:46:51
B3 Pears
C3 14
--- END OF ROW ---

``````

#### 属性：columns 和 rows

``````sheet.columns` 和 `sheet.rows` 是 `generator
``````
``````for cellObj in list(sheet.columns)[1]:
print(cellObj.value)
Apples
Cherries
Pears
Oranges
Apples
Bananas
Strawberries

``````

#### Workbook()

``````>>> import openpyxl
>>> wb = openpyxl.Workbook()
>>> wb.get_sheet_names()
['Sheet']

``````

``````from openpyxl.drawing.image import Image
img_file = ".//images//1.jpg"
img = Image(img_file)
sheet.column_dimensions['B'].width = 15.0
sheet.row_dimensions[row].height = 80
img.width = 100.0
img.height = 100.0