python:办公自动化之：Excel

Excel数据的类型及组织方式

素材：https://download.csdn.net/download/lm19770429/12503288

每一个Excel数据文件从上至下分为三个层级的对象：
workbook：每一个Excel文件就是一个workbook。
sheet：每一个workbook中可以包含多个sheet，具体就对应Excel中我们在左下脚所看到的“sheet1”,“sheet2”等。
cell：每一个sheet就是我们通常所看到的一个表格，可以含有m行，n列，每个确定的行号，列号所对应的一个格子就是一个cell。

---

Excel模块对比

---

pip install openpyxl

打开及读取excel内容：

load_workbook(filename=表格文件名称)  #不能创建，只能打开已有

from openpyxl import load_workbook

workbook=load_workbook(filename='   ')

print(workbook.sheetnames)

sheet=workbook[sheet名称 ]            #使用sheet表, 也可以sheet=workbook.active

print(sheet.dimensions)       #sheet范围

cell=sheet['A1']

print(cell.value)

print(cell.row,cell.column,cell.coordinate)

也可以通过分别指定行列号获取cell：

获取一系列cell：

cell=sheet['A1:A5']    #返回一个元组

sheet['A']     #取回A列

sheet['A:C']

sheet[5:6]

注意返回的是行

.iter_cols    #注意返回时按列返回

返回所有行：.rows

把空cell找出来，参考代码：

import openpyxl
workbook=openpyxl.load_workbook(filename='阿里云天池电商婴儿用户数据.xlsx')
sheet=workbook['(sample)sam_tianchi_mum_baby_tr']

for row in sheet.rows:
    for cell in row:
        if cell.value is None:
            print(cell.coordinate)

---

修改cell内容

sheet['A1'].value=value

一定要保存一下：

workbook.save(filename='  ')

sheet.append(python列表)       #在表格后增加一行

 插入公式：

sheet['F1002']='=AVERAGE(F2:F1001)'

workbook.save(filename='  ')

查看openpyxl支持的公式：

from openpyxl.utils import FORMULAE

print(FORMULAE)

插入一空列：

sheet.insert_cols(idx=数字编号)

要一次多插入几列：sheet.insert_cols(idx=2,amount=3)

插入一空列：

sheet.insert_rows(idx=数字编号)

删除列：

sheet.delete_cols(idx=2,amount=3)

移动cell：

sheet.move_range("C1:D4",rows=2,cols=-2)   #正表示向下、向右，负表示向上、向左

创建、删除一个新sheet

workbook.create_sheet('名字')

workbook.remove(sheet)

复制sheet

workbook.copy_worksheet(sheet)

sheet.title='name' #改sheet名称

创建新的Excel表格文件：

from openpyxl import Workbook

workbook=Workbook()

sheet=workbook.active

sheet['A1'].value='......'

workbook.save(filename=.......)

冻结窗格：

sheet.freeze_panes='B2'

添加 筛选操作：

sheet.auto_filter.ref=sheet.dimensions  #对整个表格进行筛选，首行出现筛选标记

 

 

---

批量调整字体、样式

Font（name=,size=,bold=,italic=,color=）

font=Font(name='宋体',size=12,bold=True,italic=True,color='ff0000')

cell.font=font

获取cell字体

font=cell.font

print(font.name,font.size,font.bold,font.italic)

设置对齐：

alignment=Alignment(horizontal='center',vertical='center',text_rotation=True)

cell.Alignment=alignment

设置边框样式：

from openpyxl.styles import Boder,Side

side=Side(style='thin',color='ff0000000')

border=Border(left=side,rgiht=side,top=side,bottom=side)

cell.border=border

边框样式：

填充样式：

 

设置行高和列宽：

合并单元格：

取消合并单元格：

 

 

---

插入图片：

插入柱状图：

chart=BarChart()

data=Reference(worksheet=sheet,min_row=1,max_row=7,min_col=2,max_col=3)

categories=Reference(sheet,min_col=1,min_row=1,max_row=7)

chart.add_data(data,titles_from_data=True)

chart.add_categories(categories)

sheet.add_chart(chart,'E2')

条形图：

 

from_rows=True   #说明数据是横向的