自动化办公：2、Python自动化之Excel读取表格+设置样式

Python自动化之Excel

方法一：应用pip执行命令

安装openpyxl模块pip install openpyxl

方法二：在Pycharm中：File->Setting->左侧Project Interpreter，右上角有个 + ，搜openpyxl，再install package

Excel读取

读取对应表格

1. 打开已经存在的Excel表格

from openpyxl import load_workbook

exl = load_workbook(filename = '你说你咋个就不会呢.xlsx')
print(exl.sheetnames)

['不会就是不会']

2. 根据名称获取表格

exl_1 = load_workbook(filename = '你说你咋个就不会呢.xlsx')
# print(exl_1.sheetnames)

sheet = exl_1['不会就是不会'] # 直接取名为“不会就是不会”的sheet

# 若只有一张表则：
sheet = exl_1.active

3. 获取Excel 内容占据的大小

print(sheet.dimensions) #看数据有多少行列

A1:D7

读取单元格

1. 获取某个单元格的具体内容

cell = sheet.cell(row=1,column=2) #指定行列数
print(cell.value)

cell_1 = sheet['A2'] #指定坐标
print(cell_1.value)

销售单价
哆啦A梦

2. 获取单元格对应的行、列和坐标

print(cell_1.row, cell_1.column, cell.coordinate)
# 2 1 是A2单元格所在的位置，B1 是cell所在的位置

2 1 B1

读取多个格子的值

1. 指定坐标范围

cells = sheet['A1:C8'] #A1到C8区域的值

2. 指定行的值

Row = sheet[1] #第1行的值
Rows = sheet[1:2] #第1到2行的值

3. 指定列的值

Column = sheet['A'] #第A列
Columns = sheet['A:C'] #第A到C列

4. 指定范围的值

# 行获取
for row in sheet.iter_rows(min_row = 1, max_row = 5,
                            min_col = 2, max_col = 6):
    print(row)
    # 一列由多个单元格组成，若需要获取每个单元格的值则循环获取即可
    for cell in row:
        print(cell.value)

# 列获取
for col in sheet.iter_cols(min_row = 1, max_row = 5,
                             min_col = 2, max_col = 6):
    print(col)

    for cell in col:
        print(cell.value)

练习题

找出text_1.xlsx中sheet1表中空着的格子，并输出这些格子的坐标

from openpyxl import load_workbook

exl = load_workbook('test_1.xlsx')
sheet = exl.active
for row in sheet.iter_rows(min_row = 1, max_row = 5,
                            min_col = 1, max_col = 4):
                            #具体查看对应表格的行列数
    for cell in row:
        if not cell.value:
            print(cell.coordinate)

备注：实际应用中可以查看有哪些值是空值，找到对应的数据并填充，比如问卷调查。

Excel写入

写入单元格并保存

from openpyxl import load_workbook

exl = load_workbook(filename = '你说你咋个就不会呢.xlsx')
sheet = exl.active
sheet['A1'] = 'hello 我是斗战胜佛'       
#或者cell = sheet['A1'] 
#cell.value = 'hello word'
exl.save(filename = 'test.xlsx') #存入原Excel表中，若创建新文件则可命名为不同名称

写入行数据并保存

1. 写入一行数据并保存

import xlwt
workbook = xlwt.Workbook(encoding = 'utf-8')
# 创建一个sheet
sheet = workbook.add_sheet('你干啥呢')

# 写入excel
# 参数对应 行, 列, 值
sheet.write(1,0,label = '我啥也没干')

# 保存成一个新表
workbook.save('信你才怪.xlsx')

2. 写入多行数据并保存，以下操作如果是已经有相应的表存在，那么就直接覆盖掉原表。

import xlwt
# from openpyxl import load_workbook

book = xlwt.Workbook(encoding = 'utf-8')
# 创建一个sheet
exl = book.add_sheet('只要数字不要文字')

data = [[1,22,1],
        [3,23,3],
        [3,25,2]]
for i in range(len(data)):
    for j in range(len(data[i])):
        exl.write(i,j,data[i][j])
book.save('数学使我快乐.xlsx')

python中使用openpyxl模块时报错： File is not a zip file。

来自网友的经验：

最大的原因就是不是真正的 xlsx文件， 如果是通过 库xlwt 新建的文件，或者是通过自己修改后缀名得到的 xlsx文件，都会报错，我遇到的解决办法基本都是自己使用 office 新建一个xlsx文件，网上说的是由于新版 office 加密的原因，只能通过 office 的软件才能建立真正的xlsx文件。

Python大坑：

• openpyxl和(xlrd,xlwt)不可混用！

• 工程名、包名、文件名、模块名绝对不可重复！

• (xlrd、xlwt)读写过的文件千万不可让openpyxl读写

以下代码可以将对应的位置更改相应的数据，其他数据不覆盖

from openpyxl import load_workbook

exl = load_workbook(filename = '数学使我快乐.xlsx')
xml_sheet = exl["只要数字不要文字"]
data = [[1, 22, 3],
        [2, "我是异类",5],
        [3, 255, "异类也是你"]]
for i in range(len(data)):
    for j in range(len(data[i])):
        print(i, j, data[i][j])
        xml_sheet.cell(row=i + 1, column=j + 1).value = data[i][j]
exl.save('数学使我快乐.xlsx')

0 0 1
0 1 22
0 2 3
1 0 2
1 1 我是异类
1 2 5
2 0 3
2 1 255
2 2 异类也是你

将公式写入单元格保存

from openpyxl import load_workbook
exl = load_workbook(filename ='test.xlsx')
sheet =exl.active # 这一步骤很重要，因为要打开这个表才能进行后面的操作
sheet['D2'] = '=B2*C2'
exl.save('test_new.xlsx')

插入列数据

1. 插入一列

from openpyxl import load_workbook
exl = load_workbook(filename ='test.xlsx')
sheet =exl.active 
sheet.insert_cols(idx=2) #idx=2第2列，第2列前插入一列
exl.save('test_11.xlsx')

2. 插入多列

#第2列前插入5列作为举例
sheet.insert_cols(idx=2, amount=5)
exl.save('test_22.xlsx')

插入行数据

第2行前上面插入一行(或多行)

#插入一行
sheet.insert_rows(idx=2)
#插入多行
sheet.insert_rows(idx=2, amount=5)
# 记得save 就可看到对应的变化。

删除

1. 删除多列

sheet.delete_cols(idx=5, amount=2) #第5列前删除2列

2. 删除多行

sheet.delete_rows(idx=2, amount=5)

移动

当数字为正即向下或向右，为负即为向上或向左

move_range(“数据区域”,rows=1,cols=-1,translate=True) 向下移动1行，向左移动1列

在工作表中移动指定范围的单元格：

ws.move_range(“D4:F10”, rows=-1, cols=2)

以上代码将范围 D4：F10 中的单元格向上移动一列，向右移动两列，被移动单元将覆盖现有单元格。
如果单元格包含公式，openpyxl 可以翻译这些公式，由于这并非是想要的结果，默认情况下处于禁用状态。只有被移动单元格中的公式会被翻译，其他单元格或已定义名称对单元格的引用将不会更新，可以使用解析公式转换器执行此操作：

ws.move_range(“G4:H10”, rows=1, cols=1, translate=True)

以上代码将指定移动内单元格范围内公式相对引用移动一行和一列。

from openpyxl import load_workbook
exl = load_workbook(filename ='你说你咋个就不会呢.xlsx')
sheet =exl.active 
sheet.move_range('C1:D4', rows =1, cols=-1)
exl.save(filename='我是一个鬼精灵.xlsx')

Sheet表操作

1. 创建新的sheet

from openpyxl import load_workbook 
# 读取文件的sheet名
exl = load_workbook(filename = '抄表大佬.xlsx')
print(exl.sheetnames)

['是你吗', '是的', '不是']

# 创建新的sheet
exl.create_sheet('哦，我们都不是')

<Worksheet "哦，我们都不是">

2. 复制已有的sheet

ws = exl['是的']
exl.copy_worksheet(ws)

<Worksheet "是的 Copy">

3. 修改sheet表名

sheet = exl['是你吗']
sheet.title = '真的是你吗'

以上都要重新保存才可出现你想要的效果。

创建新的Excel表

from openpyxl import Workbook

wb = Workbook()
sheet = wb.active
wb.save(filename = '露娜不是噜啦.xlsx')

Excel 样式

设置字体样式

1. 设置字体样式

   Font(name=“字体名”,size=字体大小,bold=是否加粗,italic=是否斜体,color=字体颜色)

   • Font 注意首字母大写

from openpyxl import Workbook
from openpyxl.styles import Font
# 创建新表   
workbook = Workbook()
sheet = workbook.active
cell = sheet['A1']
font = Font(name='草书', size=10, bold=True, italic=True, color='FF0000')
cell.font = font
workbook.save('excel样式设置试运行.xlsx')

2. 设置多个格子的字体样式

from openpyxl import Workbook
from openpyxl.styles import Font

workbook = Workbook()
sheet = workbook.active
font = Font(name='隶书', size=10, bold=True, italic=True, color='FF000000')
for x in range(1,5):
    sheet.cell(row=x,column=2).font = font 
    #将第二列的1,2,3,4行样式进行逐一替换
workbook.save('excel样式.xlsx')

想要某列/某行设置同样的格式，如下：

from openpyxl import load_workbook
from openpyxl.styles import Font
exl = load_workbook(filename ='你说你咋个就不会呢.xlsx')
sheet =exl.active 
cells = sheet[2]
font = Font(name='字体', size=10, bold=True, italic=True, color='FF000000')
for cell in cells:
    cell.font = font
exl.save('看看我变化了没.xlsx')

---------------------------------------------------------------------------

1.设置对齐样式

水平对齐：distributed, justify, center, left, fill, centerContinuous, right, general

垂直对齐：bottom, distributed, justify, center, top

from openpyxl import Workbook
from openpyxl.styles import Alignment

workbook = Workbook()
sheet = workbook.active
cell = sheet['A1']
alignment = Alignment(horizontal="center",vertical="center",text_rotation=30,wrap_text=True)
cell.alignment = alignment
workbook.save('对齐样式.xlsx')

2. 设置单元格边框样式

Side(style=边线样式，color=边线颜色),颜色只能是16进制的颜色名

Border(left=左边线样式,right=右边线样式,top=上边线样式,bottom=下边线样式)

边线样式：double, mediumDashDotDot, slantDashDot, dashDotDot, dotted, hair, mediumDashed, dashed, dashDot, thin, mediumDashDot, medium, thick

from openpyxl import Workbook
from openpyxl.styles import Side,Border

workbook = Workbook()
sheet = workbook.active
cell = sheet['D6']
side_left = Side(style='double', color='FF0000')
side_right = Side(style='dashDot', color='FF000000')
#先定好side的格式
border = Border(left=side_left, right=side_right, top=side_right, bottom=side_left)
#代入边线中
cell.border = border
workbook.save('单元格边框样式.xlsx')

3. 设置单元格填充样式

• PatternFill(fill_type=填充样式,fgColor=填充颜色)
• GradientFill(stop=(渐变颜色1,渐变颜色2,…))

from openpyxl import Workbook
from openpyxl.styles import PatternFill,GradientFill

workbook = Workbook()
sheet = workbook.active

cell1 = sheet['B3']
cell2 = sheet['B4']

#单色填充
pattern_fill = PatternFill(fill_type='solid', fgColor ="FF0000")
cell1.fill = pattern_fill
                           
#渐变填充
gradient_fill = GradientFill(stop=('FFFFFF', '99ccff','000000'))
cell2.fill = gradient_fill

workbook.save(filename='单元格填充样式.xlsx')

4.设置行高与列宽

• row_dimensions[行编号].height = 行高
• column_dimensions[列编号].width = 列宽

from openpyxl import Workbook 
workbook = Workbook()
sheet = workbook.active

sheet.row_dimensions[1].height = 50
sheet.column_dimensions['C'].width = 20 

workbook.save(filename='行高列宽设置.xlsx')

5.合并单元格

• merge_cells(待合并的格子编号)
• merage_cells(start_row=起始行号,start_column=起始列号,end_row=结束行号,end_column=结束列号)

from openpyxl import Workbook 
workbook = Workbook()
sheet = workbook.active

sheet.merge_cells('A1:B2')
sheet.merge_cells(start_row=1, start_column=3,end_row=2, end_column=4)

workbook.save(filename='合并单元格.xlsx')

6.取消单元格合并

• unmerge_cells(待取消合并格子编号)
• unmerge_cells(start_row=起始行号,start_column=起始列号,end_row=结束行号,end_column=结束列号)

from openpyxl import load_workbook
workbook = load_workbook(filename="合并单元格.xlsx")
sheet = workbook.active

sheet.unmerge_cells('A1:B2')
sheet.unmerge_cells(start_row=1, start_column=3,end_row=2, end_column=4)

workbook.save(filename='取消合并单元格.xlsx')

练习题

打开“你说你咋个就不会呢.xlsx”文件，找出文件中单价超过5的行，并对其标红、加粗、附上边框。

from openpyxl import load_workbook
from openpyxl.styles import Font, Side, Border 

workbook = load_workbook('你说你咋个就不会呢.xlsx') 
sheet = workbook.active
price = sheet['B'] 

row_lst = []
for cell in price:
    if isinstance(cell.value, int) and cell.value > 5: 
        row_lst.append(cell.row)

side_left = Side(style='mediumDashDotDot', color='FF0000')
side_right = Side(style='mediumDashDotDot', color='6C91FC')
side_top = Side(style='thin', color='3E7AAB')
side_bottom = Side(style='double', color='3E7AAB')

border = Border(left=side_left, right=side_right, top=side_top, bottom=side_bottom) 
font = Font(bold=True, color='FF0000')

for row in row_lst:
    for cell in sheet[row]: 
        cell.font = font 
        cell.border = border
        
workbook.save('练习题.xlsx')