自动化办公,我相信很多人都有强烈的需求,都希望从繁琐重复的劳动中挣脱出来,把精力用在有意义的事情上。
来自知乎提问
现代办公室里几乎任何一项工作都会用到Excel、Word。在之前文章中我已分享过Python自动化操作word的文章《Python办公自动化|只需三秒,一键生成数据分析报告》。
私信小编01即可获取大量Python学习资源
今天我教大家如何利用Python自动化操作Excel,包括:介绍操作Excel的工具包、安装方法及操作Excel具体方法。对于每天有大量重复性工作的同学来说,这款工具绝对是福利。
openpyxl是一个Python库,用于读取/写入Excel xlsx / xlsm / xltx / xltm文件。它的诞生是因为缺少可从Python本地读取/写入Office Open XML格式的库。官方文档:
http://yumos.gitee.io/openpyxl3.0
使用pip安装openpyxl。建议在不带系统软件包的Python virtualenv中执行此操作:
pip install openpyxl
支持流行的lxml库(如果已安装)。这在创建大文件时特别有用。
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')
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)
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')
删除列F:H
ws.delete_cols(6%2c 3)
df = DataFrame(ws.values)
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")
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利用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')
# -*- 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")
# -*- 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"
# -*- 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")