电子表格模块openpyxl

一. 安装模块

　　pip install openpyxl

二. 打开文件

　　注意:用python操作excel文件,该文件应当没有被别的软件打开.否则会报错.

　　2.1. 创建文件

　　　　from openpyxl import Workbook  # W大写

　　　　# 实例化

　　　　wb = Workbook()  # wb是实例化的文件对象

　　　　# 激活当前表

　　　　ws = wb.active  # ws就是获得的文件中的默认打开的表的对象

　　2.2. 打开已有文件

　　　　from openpyxl improt load_workbook

　　　　wb2 = load_workbook('文件名称.xlsx')  # 目前只支持xlsx格式

三. 存储数据

　　3.1 方式一:根据单元格的索引来添加内容,数据可以直接分配到单元格中(可以输入公式)

　　　　ws['A1'] = 42

　　3.2 方式二:根据单元格的位置来添加内容

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

　　3.3 方式三:可以附加行,在表格中已有数据的下一行添加(即添加在表格的末尾,并另起一行)

　　　　ws.append([1, 2, 3]) 

　　　　ws.append([4, 5, 6])  # 1,2,3在一行,4,5,6在下一行,每个数字一个单元格

　　3.4 python类型会被自动转换

　　　　ws['A3'] = datetime.datetime.now().strftime('%Y-%m-%d')

　　3.5 保存数据到硬盘文件中

　　　　wb.save('文件名称.xlsx')

四. 创建表(sheet)

　　4.1 方式一: 插入到最后(default)

　　　　wsNew1 = wb.create_sheet('Mysheet')

　　4.2 根据索引的位置来添加工作表,插入到最开始的位置

　　　　wsNew2 = wb.create_sheet('Mysheet2', 0)

　　4.3 复制工作表

　　　　ws = wb.copy_worksheet('工作表名')

五. 选择表(sheet)

　　5.1 sheet名称可以作为key进行索引

　　　　ws3 = wb['NewTitle']

　　　　ws4 = wb.get_sheet_by_name('NewTitle')

　　　　ws is ws3 is ws4   ----> True

　　5.2 获得当前活动表对象

　　　　ws = wb.active

 

　　5.3 以索引值方式获取工作表

 

　　　　ws = wb.worksheets[0]

 

六. 查看表名(sheet)

　　6.1 显示所有表名

　　　　print(wb.sheetnames)

　　　　----> ['sheet2', 'sheet1', 'NewTitle']

　　6.2 遍历所有表

　　　　for sheet in wb:  # wb为文件对象

　　　　　　print(sheet.title)

　　6.3 获取当前活动表的表名

　　　　ws = wb.active

　　　　print(ws.title)

 

七. 访问单元格(cell)

　　7.1 单一单元格访问

　　　　7.1.1 方法一:

　　　　　　val = ws['A4'].value  # 要获取单元格的值必须要使用value属性

　　　　7.1.2 方法二:

　　　　　　val = ws.cell(row=4, column=3).value

　　　　　　for i in range(1, 101):

　　　　　　　　for j in range(1, 101):

　　　　　　　　　　ws.cell(row=i, column=j)

　　7.2 多单元格访问

　　　　7.2.1 通过切片

　　　　　　cell_range = ws['A1':'C2']  # 获取到a1到c2的矩形区域

　　　　7.2.2 通过行(列)

　　　　　　colc = ws['C']  # C或c都可以,不区分大小写

　　　　　　col_range = ws['c:d']

　　　　　　row10 = ws[10]  # 获取第10行

　　　　　　row_range = ws[5:10]

　　　　7.2.3 通过制定范围(行---->行)

　　　　　　# 起始行列的参数根据需要可以任意省略一个或多个.省略的值就会取默认值.默认最小值是从表格的头开始,最大值到(数据区域)表格的末尾结束.

　　　　　　# 注意表格的末尾可能存在大量的空白行.cell.value的值是None

　　　　　　for row in ws.ite_rows(min_row=1, min_col=1, max_row=10, max_col=15):  # 行列是从1开始计,而不是0,注意区别.

　　　　　　　　for cell in row:

　　　　　　　　　　print(cell)

　　　　　　----> <cell sheet1.A1>

　　　　　　　　<cell sheet1.B1>

　　　　　　　　<cell sheet1.C1>

　　　　　　　　<cell sheet1.A1>

　　　　　　　　　　...

　　　　　　先行后列(1,2,3,4,5,6,7,8,9)

　　　　　　　　　　

　　　　7.2.4 通过制定范围(列---->列)

　　　　　　for col in ws.iter_cols(min_row=1, min_col=1, max_row=10, max_col=15):

　　　　　　　　for cell in col:

　　　　　　　　　　print(cell)

　　　　　　先列后行(1,4,7,2,5,8,3,6,9)

　　　　　　　　　　

　　7.3 遍历所有

　　　　7.3.1 先行后列

　　　　　　print(tuple(ws.rows))

　　　　　　ws.rows为生成器,里面是每一行一行的数据,每一行又由一个tuple包裹着.

　　　　　　----> ((<Cell '第一页'.A1>, <Cell '第一页'.B1>, <Cell '第一页'.C1>), (<Cell '第一页'.A2>,...))

　　　　　　通过两层for循环可以取得每个单元格的值

　　　　　　　　for row in ws.rows:

　　　　　　　　　　for cell in row:

　　　　　　　　　　　　print(cell.value)

　　　　7.3.2 先列后行

　　　　　　print(tuple(ws.columns))

　　　　　　ws.columns类似ws.rows.也是生成器.里面是每一列一列的数据,区别是每一列由一个tuple包裹

　　　　　　---->((<Cell '第一页'.A1>, <Cell '第一页'.A2>, <Cell '第一页'.A3>), (<Cell '第一页'.B1>,...))

　　　　　　通过两层for循环可以取得每个单元格的值

　　　　　　　　for column in ws.columns:

　　　　　　　　　　for cell in column:

　　　　　　　　　　　　print(cell.value)

　　7.4 获取最大行数,最大列数

　　　　print(ws.max_row)    ---->860

　　　　print(ws.max_column)    ---->8

　　7.5 单元格的其它常用属性

　　　　cell.value  # 获取该单元格的值

　　　　cell.row  # 活得该单元格的行号  ---->返回数字(注意:行号从1开始)  注意:以下的三个属性在pycharm中输入时不能自动提示

　　　　cell.column  # 获得该单元格的列号  ---->返回数字(注意:列号从1开始)

　　　　cell.coordinate  # 获得该单元格的编号  ---->返回字母+数字形式的单元格位置 如:A3

八. 根据数字得到字母,根据字母得到数字(列号的字母形式与数字形式的互相转换)

　　# 先导入模块

　　　　for openpyxl.utils import get_column_letter, column_index_from_string

　　# 数字转换为字母

　　　　print(get_column_letter(2))  ---->B

　　# 字母返回数字

　　　　print(column_index_from_string('D'))    ---->4  # 不区分大小写

九. 删除工作表

　　wb.remove(ws)  # wb是excel文件对象,ws是文件中的表对象.注意ws不是单纯的表名

　　wb.save('文件名.xlsx')　 #　删除表对象之后,一定要将文件对象进行再保存.

十. excel表数据的矩阵置换(行---->列) 

　　      

　　

import openpyxl as xl
wb = xl.load_workbook('1.xlsx')
ws = wb.active
wsL = []  # 获取原电子表格数据
for row in ws:
    rowL = []
    for cell in row:
        rowL.append(cell.value)
    wsL.append(rowL)
print(wsL)
print(list(zip(*wsL)))
wsLN = list(zip(*wsL))  # 这里是矩阵转换的关键.生成新电子表格数据
wbNew = xl.Workbook()
wsNew = wbNew.active
for item in wsLN:
    wsNew.append(item)  # 逐行写入数据
wbNew.save('1.xlsx')

十一. 使用excel函数(公式)

　　11.1 计算

import openpyxl as xl
wb = xl.Workbook()
ws = wb.create_sheet('index', 0)
ws['A1'] = 3
ws['A2'] = 4
ws['A3'] = '=sum(A1:A2)'
wb.save('sum.xlsx')

　　11.2 获取计算结果　　

　　　　注意:获取函数的值时的坑.

　　　　　　只能通过手动保存的方式来修改文件后才能获得通过函数计算出来的值

wb2 = xl.load_workbook('sum.xlsx')
ws2 = wb2.active
print(ws2['A3'].value)

# 你想得到的是单元格的值(A3单元格的值应当是7)
# 但实际的输出是 公式
# 获取的结果是 =sum(A1:A2)

　　　　解决方法:第一步,先用excel软件打开该文件,点击保存.退出excel软件.　　第二步:在代码中增加data_only=True

wb2 = xl.load_workbook('sum.xlsx', data_only=True)
ws2 = wb2.active
print(ws2['A3'].value)

# 经过两步操作
# 输出7   如果缺第二步则输出None,如果缺第一步则输出 =sum(A1:A2)

十二. 设置单元格风格

　　12.1 需要导入的类

　　　　 from openpyxl.styles import Font, Color, Alignment

　　12.2 常用类

　　　　Font：来设置文字的大小，颜色和下划线等

　　　　PatternFill：填充图案和渐变色
　　　　Border：单元格的边框
　　　　Alignment：单元格的对齐方式等
　　　　protection：写保护

　　12.3 实例,设置字体,大小,斜体italic,红色,加粗bold

import openpyxl as xl
from openpyxl.styles import Font, Color, Alignment
# 下面的代码指定了"等线"24号,斜体,字体颜色红色,加粗,直接使用cell的font将Font对象赋值给它
bold_itatic_24_font = Font(name='等线', size=24, italic=True, color='FF0000', bold=True)

wb = xl.load_workbook('wen.xlsx')
ws = wb.active
ws['A1'].font = bold_itatic_24_font
wb.save('wen.xlsx')

　　12.4 对齐方式　　　　

# 直接使用cell的属性aligment,这里指定水平居中和垂直居中,除了center,还可以使用right,left等参数
ws['A2'].alignment = Alignment(horizontal='right', vertical='center')

　　12.5 行高和列宽

# 行高
ws.row_dimensions[2].height = 40
# 列宽
ws.column_dimensions['C'].width = 30

　　12.6 合并和拆分单元格

# 合并单元格,往左上角写入数据即可
ws.merge_cells('A1:G1')  # 合并一行中的几个单元格
ws.merge_cells('B1:c3')  # 合并一个矩形区域中的单元格
# 拆分单元格,拆分后值回到左上角的位置A1位置
ws.unmerge_cells('A1:G1')