Python办公自动化(如何用Python自动化操作Excel)

自动化办公,我相信很多人都有强烈的需求,都希望从繁琐重复的劳动中挣脱出来,把精力用在有意义的事情上。 来自知乎提问 现代办公室里几乎任何一项工作都会用到Excel、Word。在之前文章中...

自动化办公,我相信很多人都有强烈的需求,都希望从繁琐重复的劳动中挣脱出来,把精力用在有意义的事情上。

Python办公自动化(如何用Python自动化操作Excel) 1

来自知乎提问

现代办公室里几乎任何一项工作都会用到Excel、Word。在之前文章中我已分享过Python自动化操作word的文章《Python办公自动化|只需三秒,一键生成数据分析报告》。

私信小编01即可获取大量Python学习资源

今天我教大家如何利用Python自动化操作Excel,包括:介绍操作Excel的工具包、安装方法及操作Excel具体方法。对于每天有大量重复性工作的同学来说,这款工具绝对是福利。

openpyxl是什么

openpyxl是一个Python库,用于读取/写入Excel xlsx / xlsm / xltx / xltm文件。它的诞生是因为缺少可从Python本地读取/写入Office Open XML格式的库。官方文档:

http://yumos.gitee.io/openpyxl3.0

openpyxl安装

使用pip安装openpyxl。建议在不带系统软件包的Python virtualenv中执行此操作:

pip install openpyxl

支持流行的lxml库(如果已安装)。这在创建大文件时特别有用。

openpyxl操作指南

1、创建工作簿

from openpyxl import Workbook
wb = Workbook
ws_00 = wb.active #默认不取名称
ws_00['A1']= 'Python学习与数据挖掘'
ws_01 = wb.create_sheet("new_sheet"%2c 0) # 取一个new_sheet的名称
ws_01['A1']= 23
wb.save('/Users/***/Desktop/document.xlsx')

Python办公自动化(如何用Python自动化操作Excel) 2

2、写工作簿

from openpyxl import Workbook
from openpyxl.utils import get_column_letter
wb = Workbook
dest_filename = '/Users/****/Desktop/empty_book.xlsx'
ws1 = wb.active
ws1.title = "range names"
for row in range(1%2c 40):
    ws1.append(range(600))
ws2 = wb.create_sheet(title="Pi")
ws2['F5'] = 3.14
ws3 = wb.create_sheet(title="Data")
for row in range(10%2c 20):
    for col in range(27%2c 54):
        _ = ws3.cell(column=col%2c row=row%2c value="{0}".format(get_column_letter(col)))
wb.save(filename = dest_filename)

3、插入图片

from openpyxl import Workbook
from openpyxl.drawing.image import Image

wb = Workbook
ws = wb.active
ws['A1'] = 'You should see three logos below'
img = Image('/Users/***/work/logo.png')
ws.add_image(img%2c 'A1')

wb.save('/Users/***/document01.xlsx')

4、删除行和列

删除列F:H

ws.delete_cols(6%2c 3)

5、将工作表转换为数据框

df = DataFrame(ws.values)

6、2D区域图

from openpyxl import Workbook
from openpyxl.chart import (
    AreaChart%2c
    Reference%2c
    Series%2c
)
wb = Workbook
ws = wb.active
rows = [
    ['Number'%2c 'Batch 1'%2c 'Batch 2']%2c
    [2%2c 40%2c 30]%2c
    [3%2c 40%2c 25]%2c
    [4%2c 50%2c 30]%2c
    [5%2c 30%2c 10]%2c
    [6%2c 25%2c 5]%2c
    [7%2c 50%2c 10]%2c
]
for row in rows:
    ws.append(row)
chart = AreaChart
chart.title = "Area Chart"
chart.style = 13
chart.x_axis.title = 'Test'
chart.y_axis.title = 'Percentage'
cats = Reference(ws%2c min_col=1%2c min_row=1%2c max_row=7)
data = Reference(ws%2c min_col=2%2c min_row=1%2c max_col=3%2c max_row=7)
chart.add_data(data%2c titles_from_data=True)
chart.set_categories(cats)
ws.add_chart(chart%2c "A10")
wb.save("area.xlsx")

Python办公自动化(如何用Python自动化操作Excel) 3

6、雷达图

from openpyxl import Workbook
from openpyxl.chart import (
    RadarChart%2c
    Reference%2c
)
wb = Workbook
ws = wb.active
rows = [
    ['Month'%2c "Bulbs"%2c "Seeds"%2c "Flowers"%2c "Trees & shrubs"]%2c
    ['Jan'%2c 0%2c 2500%2c 500%2c 0%2c]%2c
    ['Feb'%2c 0%2c 5500%2c 750%2c 1500]%2c
    ['Mar'%2c 0%2c 9000%2c 1500%2c 2500]%2c
    ['Apr'%2c 0%2c 6500%2c 2000%2c 4000]%2c
    ['May'%2c 0%2c 3500%2c 5500%2c 3500]%2c
    ['Jun'%2c 0%2c 0%2c 7500%2c 1500]%2c
    ['Jul'%2c 0%2c 0%2c 8500%2c 800]%2c
    ['Aug'%2c 1500%2c 0%2c 7000%2c 550]%2c
    ['Sep'%2c 5000%2c 0%2c 3500%2c 2500]%2c
    ['Oct'%2c 8500%2c 0%2c 2500%2c 6000]%2c
    ['Nov'%2c 3500%2c 0%2c 500%2c 5500]%2c
    ['Dec'%2c 500%2c 0%2c 100%2c 3000 ]%2c
]
for row in rows:
    ws.append(row)
chart = RadarChart
chart.type = "filled"
labels = Reference(ws%2c min_col=1%2c min_row=2%2c max_row=13)
data = Reference(ws%2c min_col=2%2c max_col=5%2c min_row=1%2c max_row=13)
chart.add_data(data%2c titles_from_data=True)
chart.set_categories(labels)
chart.style = 26
chart.title = "Garden Centre Sales"
chart.y_axis.delete = True
ws.add_chart(chart%2c "A17")
wb.save("radar.xlsx")

Python办公自动化(如何用Python自动化操作Excel) 4

7、使用公式

业务中需要批量处理的操作,我们可以代码化。Python利用Excel的公式功能来处理数据,可以达到事半功倍的效果。

from openpyxl import Workbook
from openpyxl import load_workbook
wb = load_workbook('/Users/***/work/document01.xlsx')
ws1=wb.active
ws1["F2"] = "=SUM(B2:E2)"   # 使用公式
# Save the file
wb.save('/Users/***/Desktop/document01.xlsx')

8、给单元格设定字体颜色

# -*- coding: utf-8 -*-
from openpyxl import Workbook
from openpyxl.styles import colors
from openpyxl.styles import Font
wb = Workbook
ws = wb.active
a1 = ws['A1']
d4 = ws['D4']
ft = Font(color=colors.RED)  # color="FFBB00",颜色编码也可以设定颜色
a1.font = ft
d4.font = ft
# If you want to change the color of a Font%2c you need to reassign it::
#italic 倾斜字体
a1.font = Font(color=colors.RED%2c italic=True) # the change only affects A1
a1.value = "abc"
# Save the file
wb.save("/Users/***/Desktop/document01.xlsx")

9、设定字体和大小

# -*- coding: utf-8 -*-
from openpyxl import Workbook
from openpyxl.styles import colors
from openpyxl.styles import Font
wb = Workbook
ws = wb.active
a1 = ws['A1']
d4 = ws['D4']
a1.value = "abc"
from openpyxl.styles import Font
from copy import copy
ft1 = Font(name=u'宋体'%2c size=14)
ft2 = copy(ft1)   #复制字体对象
ft2.name = "Tahoma"

10、设定单元格的边框、字体、颜色、大小和边框背景色

# -*- coding: utf-8 -*-
from openpyxl import Workbook
from openpyxl.styles import Font
from openpyxl.styles import NamedStyle%2c Font%2c Border%2c Side%2cPatternFill
wb = Workbook
ws = wb.active
highlight = NamedStyle(name="highlight")
highlight.font = Font(bold=True%2c size=20%2ccolor= "ff0100")
highlight.fill = PatternFill("solid"%2c fgColor="DDDDDD")#背景填充
bd = Side(style='thick'%2c color="000000")
highlight.border = Border(left=bd%2c top=bd%2c right=bd%2c bottom=bd)
print dir(ws["A1"])
ws["A1"].style =highlight
# Save the file
wb.save("/Users/***/Desktop/document01.xlsx")
  • 发表于 2022-01-30 12:04:44
  • 阅读 ( 178 )
  • 分类:科技

0 条评论

请先 登录 后评论
白思特
白思特

31681 篇文章

你可能感兴趣的文章

相关问题