Python 操作excel表格 openpyxl的基本使用

在工作中时常会遇到将部分数据导入到excel表格的情况，这时Python的好处可见一斑。Python操作数据表格大多使用openpyxl模块，下面介绍一下其基本使用。

零.首先

当然先先安装该模块了

pip install openpyxl

一.工作簿

0.创建工作簿

from  openpyxl  import Workbook
wb=Workbook()

1.保存工作簿
注意:若是已经存在同名文件则覆盖

wb.save("E:/save.xlsx")

2.文件加载

from openpyxl import load_workbook
wb2 = load_workbook('test.xlsx')
print(wb2.sheetnames)

3.文件存为流

from tempfile import NamedTemporaryFile
from openpyxl import Workbook
wb = Workbook()
with NamedTemporaryFile() as tmp:
        wb.save(tmp.name)
        tmp.seek(0)
        stream = tmp.read()

您可以指定属性template = True，以将工作簿另存为模板：

wb=load_workbook('document.xlsx')
wb.template=True
wb.save('document_template.xltx')

或将此属性设置为False（默认）以另存为文档

 wb = load_workbook('document_template.xltx')
 wb.template = False
 wb.save('document.xlsx', as_template=False)

二.工作表

0.创建工作表
有了工作簿后就可要创建工作表了工作簿默认至少有一张表我们可以这样获取

ws=wb.active

如果要创建表可以这样创建

ws1=wb.create_sheet("sheet01")#创建新表默认末尾
ws2=wb.create_sheet("sheet02",0)#创建新表位置是首位

保存一下工作簿

wb.save("E:/save.xlsx")

查看效果，sheet02排在首位，Sheet01排在末尾

1.拷贝(复制)工作表
注意点：仅能在单个工作簿中进行操作，不能跨工作簿操作

source=wb.active
target=wb.copy_worksheet(source)

2.修改工作表名

ws.title="modify name"

3.获取工作表对象
可以用工作表名和工作簿对象获取工作表对象

ws=wb["modify name"]

4.获取当前的工作表列表

print(wb.sheetnames)

5.样式

• 修改工作表标签颜色

ws.sheet_properties.tabColor="DC143C"

三.单元格

0.获取单元格并赋值
方式一

 c=ws['A4']
 ws["A4"]=5

方式二

c=ws.cell(row=4,column=1)
c.value=5
#or
ws.cell(row=4,column=1,value=5)

第二种可以直接遍历赋值

for c in range(1,21):
    vc=chr(64 + c)
    for r in range(1,21):
        v=vc+str(r)
        ws.cell(row=r,column=c,value=v)

结果：

1.访问多个单元格
切片访问

cell_range =ws['A1':'C2']

即如图所示区域

2.行或列的范围

 colC = ws['C']#获取C列
 col_range = ws['C:D']#获取C、D两列
 row10 = ws[10]#获取第十行
 row_range = ws[5:10]#获取五到十行

同时其又提供了 Worksheet.iter_rows()和Worksheet.iter_cols()方法供我们选择使用：

for row in ws.iter_rows(min_row=1, max_col=3, max_row=2):
    for cell in row:
        print(cell)
#or
for col in ws.iter_cols(min_row=1, max_col=3, max_row=2):
    for cell in col:
    	print(cell)

3.合并/取消合并单元格
合并单元格：

ws.merge_cells('A1:G1')

拆分单元格：

ws.unmerge_cells('A1:G1')

4.增加/删除行/列

ws.insert_rows(7)#在原7行前插入一空白行

ws.delete_rows(7)#删除第7行

ws.delete_cols(1)#删除第1列

ws.insert_cols(1)#增加第1列

5.移动单元格

ws.move_range("C2:D10", rows=-1, cols=2)
#将选中的单元格向上移动一行再向右移动两列

如图所示

6.值
直接按行取值

for row in ws.values:
	 for value in row:
	 	print(value)

没有限制直接打印全部

for row in ws.iter_rows(values_only=True):
        print(row)

同理iter_cols()也是一样

时间格式

ws['A1']=datetime.datetime(2019,1,11)
ws.number_format
'yyyy-mm-dd hh:mm:ss'

使用公式

ws['A1']='=SUM(2,3)'

7.样式
默认样式

from openpyxl.styles import PatternFill, Border, Side, Alignment, Protection, Font
 font = Font(name='Calibri',
                 size=11,
                 bold=False,
                 italic=False,
                 vertAlign=None,
                 underline='none',
                 strike=False,
                 color='FF000000')
 fill = PatternFill(fill_type=None,
                 start_color='FFFFFFFF',
                 end_color='FF000000')
 border = Border(left=Side(border_style=None,
                           color='FF000000'),
                 right=Side(border_style=None,
                            color='FF000000'),
                 top=Side(border_style=None,
                          color='FF000000'),
                 bottom=Side(border_style=None,
                             color='FF000000'),
                 diagonal=Side(border_style=None,
                               color='FF000000'),
                 diagonal_direction=0,
                 outline=Side(border_style=None,
                              color='FF000000'),
                 vertical=Side(border_style=None,
                               color='FF000000'),
                 horizontal=Side(border_style=None,
                                color='FF000000')
                )
 alignment=Alignment(horizontal='general',
                     vertical='bottom',
                     text_rotation=0,
                     wrap_text=False,
                     shrink_to_fit=False,
                     indent=0)
 number_format = 'General'
 protection = Protection(locked=True,
                         hidden=False)

有两种类型的样式：单元样式和命名样式，也称为样式模板。
单元样式

from openpyxl.styles import Font
ft = Font(color="FF0000")
ws["A1"].font=ft
ws["C1"].font=Font(color="000080",italic=True)

样式复制

ft = Font(color="FF0000")
ft1=copy(ft)

单元格填充

ws["A2"].fill=PatternFill("solid", fgColor="7F7F7F")