自动化办公: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、Python自动化之Excel读取表格+设置样式

  1. 根据名称或去表格
exl_1 = load_workbook(filename = '你说你咋个就不会呢.xlsx')
# print(exl_1.sheetnames)

sheet = exl_1['不会就是不会'] # 直接取名为“不会就是不会”的sheet
# 若只有一张表则:
sheet = exl_1.active
  1. 获取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梦
  1. 获取单元格对应的行、列和坐标
print(cell_1.row, cell_1.column, cell.coordinate)
# 2 1 是A2单元格所在的位置,B1 是cell所在的位置
2 1 B1

读取多个格子的值

  1. 指定坐标范围
cells = sheet['A1:C8'] #A1到C8区域的值
  1. 指定行的值
Row = sheet[1] #第1行的值
Rows = sheet[1:2] #第1到2行的值
  1. 指定列的值
Column = sheet['A'] #第A列
Columns = sheet['A:C'] #第A到C列
  1. 指定范围的值
# 行获取
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表中,若创建新文件则可命名为不同名称

自动化办公:2、Python自动化之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、Python自动化之Excel读取表格+设置样式

  1. 写入多行数据并保存,以下操作如果是已经有相应的表存在,那么就直接覆盖掉原表。
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')

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

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')

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

插入列数据

  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')
  1. 插入多列
#第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列
  1. 删除多行
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')

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

Sheet表操作

  1. 创建新的sheet
from openpyxl import load_workbook 
# 读取文件的sheet名
exl = load_workbook(filename = '抄表大佬.xlsx')
print(exl.sheetnames)
['是你吗', '是的', '不是']
# 创建新的sheet
exl.create_sheet('哦,我们都不是')
<Worksheet "哦,我们都不是">
  1. 复制已有的sheet
ws = exl['是的']
exl.copy_worksheet(ws)
<Worksheet "是的 Copy">
  1. 修改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、Python自动化之Excel读取表格+设置样式

  1. 设置多个格子的字体样式
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')

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

想要某列/某行设置同样的格式,如下:

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')

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

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

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、Python自动化之Excel读取表格+设置样式

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')

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

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')

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

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')

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

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')

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

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')

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

上一篇:Python自动化之Excel


下一篇:pandas无法打开.xlsx文件,xlrd.biffh.XLRDError: Excel xlsx file; not supported