Python-处理Excel电子表格（下）

（记录学习第7天）

1、读取表格

1.1从工作簿中读取工作表

1.2从表中取得单元格

1.3 列字母和数字之间的转换

这三部分内容见处理电子表格（上）

1.4从表中取得行和列

import openpyxl

wb = openpyxl.load_workbook('example.xlsx')
sheet = wb['Sheet1']
print(tuple(sheet['A1':'C3']))#注意输出的顺序

for rowOfCellObjects in sheet['A1':'C3']:
    for cellObj in rowOfCellObjects:
        print(cellObj.coordinate, cellObj.value)
    print('---End of row')

外层for循环遍历这个切片中的每一行，然后针对每一行，内层for循环遍历该行中的每个单元格。

输出结果：
((<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>))
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

要访问特定的行或列的单元格的值，可以利用WorkSheet的row和column属性，这些属性必须被list()函数转换为列表，才能使用方括号和索引。

import openpyxl

wb = openpyxl.load_workbook('example.xlsx')
sheet = wb['Sheet1']
print(list(sheet.columns)[1])

for cellObj in list(sheet.columns)[1]:
    print(cellObj.value)

 运行结果：

(<Cell 'Sheet1'.B1>, <Cell 'Sheet1'.B2>, <Cell 'Sheet1'.B3>, <Cell 'Sheet1'.B4>, <Cell 'Sheet1'.B5>, <Cell 'Sheet1'.B6>, <Cell 'Sheet1'.B7>)
Apples
Cherries
Pears
Oranges
Apples
Bananas
Strawberries

2、写入文档

2.1创建并保存Excle文档（工作簿）

openpyxl.Workbook()函数用来创建一个新的空Workbook对象

import openpyxl

wb = openpyxl.Workbook()
print(wb.sheetnames)#输出['Sheet']
sheet = wb.active
sheet.title = 'sheet1'#修改工作表的名称为sheet1
print(wb.sheetnames)#输出['sheet1']
wb.save('test.xlsx')#新工作簿的名称为test.xlsx

 2.2创建和删除工作表

创建：

create_sheet('sheet2')

很有必要判断，如果不判断多次运行，会自动生成其他编号的sheet

import openpyxl

wb = openpyxl.load_workbook('test.xlsx')
print(wb.sheetnames)

if 'sheet2' not in wb.sheetnames:
    wb.create_sheet('sheet2')

wb.save('test.xlsx')
print(wb.sheetnames)

删除 ：

del wb['sheet2']

import openpyxl

wb = openpyxl.load_workbook('test.xlsx')
print(wb.sheetnames)

if 'sheet2' not in wb.sheetnames:
    wb.create_sheet('sheet2')

wb.save('test.xlsx')
print(wb.sheetnames)

if 'sheet2' in wb.sheetnames:
    del wb['sheet2']
    wb.save('test.xlsx')
print(wb.sheetnames)

输出
['sheet1']
['sheet1', 'sheet2']
['sheet1']

2.3将值写入单元格

import openpyxl

wb = openpyxl.load_workbook('test.xlsx')
sheet = wb['sheet1']
sheet['A1'] = 'hello world!'
print(sheet['A1'].value)
wb.save('test.xlsx')

效果：

2.3设置单元格的字体风格

将A1格子里的字体风格设置成24号，斜体

from openpyxl.styles import Font
import openpyxl

wb = openpyxl.load_workbook('test.xlsx')

if 'sheet1' in wb.sheetnames:
    sheet = wb['sheet1']
    italic24Font = Font(size=24, italic=True)
    sheet['A1'].font = italic24Font
    wb.save('test.xlsx')
else:
    print("'sheet1' does not exist in the workbook.")

 将“你好”输入到工作表sheet1的A2格子中，将字体设置成楷体并加粗

from openpyxl.styles import Font
import openpyxl

wb = openpyxl.load_workbook('test.xlsx')
sheet = wb['sheet1']
sheet['A2'] = '你好!'
font1 = Font(name= '楷体', bold=True)
sheet['A2'].font = font1
wb.save('test.xlsx')

2.4公式

import openpyxl

wb = openpyxl.load_workbook('test.xlsx')
sheet = wb['sheet1']
sheet['A3'] = 100.1
sheet['B3'] = 200.2
sheet['C3'] = 300.3
sheet['D3'] = '=SUM(A3:C3)'
wb.save('test.xlsx')

效果显示：公式也自动放进去了

2.5调整行和列

2.5.1设置行高和列宽

import openpyxl

wb = openpyxl.load_workbook('test.xlsx')
sheet = wb['sheet1']
sheet.row_dimensions[1].height = 70
sheet.column_dimensions['B'].width = 20
wb.save('test.xlsx')

 效果

 2.5.2合并和拆分单元格

merge_cells()方法用来合并

unmerge_cells()方法用来拆分

import openpyxl

wb = openpyxl.load_workbook('test.xlsx')
sheet = wb['sheet1']
sheet.merge_cells('A1:B1')
wb.save('test.xlsx')

效果：

2.5.3冻结窗格

freeze_panes属性

import openpyxl

wb = openpyxl.load_workbook('test.xlsx')
sheet = wb['sheet1']
sheet.freeze_panes = 'A2'
wb.save('test.xlsx')

freeze_panes 的设置	冻结的行和列
sheet.freeze_panes = 'A2'	行1、列A和列B
sheet.freeze_panes = 'B1'	列A
sheet.freeze_panes = 'C1'	列A和列B
sheet.freeze_panes = 'C2'	行1、列A和列B
sheet.freeze_panes= 'A1'或	没有冻结窗格
sheet.freeze_panes = None

 2.6图表

openpyxl.charts.BarChart()创建条形图