自动化办公,我相信很多人都有强烈的需求,都希望从繁琐重复的劳动中挣脱出来,把精力用在有意义的事情上。
来自知乎提问
现代办公室里几乎任何一项工作都会用到Excel、Word。在之前文章中我已分享过Python自动化操作word的文章《Python办公自动化|只需三秒,一键生成数据分析报告》。
今天我教大家如何利用Python自动化操作Excel,包括:介绍操作Excel的工具包、安装方法及操作Excel具体方法。对于每天有大量重复性工作的同学来说,这款工具绝对是福利。
openpyxl是什么
openpyxl是一个Python库,用于读取/写入Excel xlsx / xlsm / xltx / xltm文件。它的诞生是因为缺少可从Python本地读取/写入Office Open XML格式的库。官方文档:
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", 0) # 取一个new_sheet的名称 ws_01['A1']= 23 wb.save('/Users/***/Desktop/document.xlsx')
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, 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, 20): for col in range(27, 54): _ = ws3.cell(column=col, row=row, 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, 'A1') wb.save('/Users/***/document01.xlsx')
4、删除行和列
删除列F:H
ws.delete_cols(6, 3)
5、将工作表转换为数据框
df = DataFrame(ws.values)
6、2D区域图
from openpyxl import Workbook from openpyxl.chart import ( AreaChart, Reference, Series, ) wb = Workbook() ws = wb.active rows = [ ['Number', 'Batch 1', 'Batch 2'], [2, 40, 30], [3, 40, 25], [4, 50, 30], [5, 30, 10], [6, 25, 5], [7, 50, 10], ] 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, min_col=1, min_row=1, max_row=7) data = Reference(ws, min_col=2, min_row=1, max_col=3, max_row=7) chart.add_data(data, titles_from_data=True) chart.set_categories(cats) ws.add_chart(chart, "A10") wb.save("area.xlsx")
6、雷达图
from openpyxl import Workbook from openpyxl.chart import ( RadarChart, Reference, ) wb = Workbook() ws = wb.active rows = [ ['Month', "Bulbs", "Seeds", "Flowers", "Trees & shrubs"], ['Jan', 0, 2500, 500, 0,], ['Feb', 0, 5500, 750, 1500], ['Mar', 0, 9000, 1500, 2500], ['Apr', 0, 6500, 2000, 4000], ['May', 0, 3500, 5500, 3500], ['Jun', 0, 0, 7500, 1500], ['Jul', 0, 0, 8500, 800], ['Aug', 1500, 0, 7000, 550], ['Sep', 5000, 0, 3500, 2500], ['Oct', 8500, 0, 2500, 6000], ['Nov', 3500, 0, 500, 5500], ['Dec', 500, 0, 100, 3000 ], ] for row in rows: ws.append(row) chart = RadarChart() chart.type = "filled" labels = Reference(ws, min_col=1, min_row=2, max_row=13) data = Reference(ws, min_col=2, max_col=5, min_row=1, max_row=13) chart.add_data(data, 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, "A17") wb.save("radar.xlsx")
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, you need to reassign it:: #italic 倾斜字体 a1.font = Font(color=colors.RED, 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'宋体', 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, Font, Border, Side,PatternFill wb = Workbook() ws = wb.active highlight = NamedStyle(name="highlight") highlight.font = Font(bold=True, size=20,color= "ff0100") highlight.fill = PatternFill("solid", fgColor="DDDDDD")#背景填充 bd = Side(style='thick', color="000000") highlight.border = Border(left=bd, top=bd, right=bd, bottom=bd) print dir(ws["A1"]) ws["A1"].style =highlight # Save the file wb.save("/Users/***/Desktop/document01.xlsx")
知道你对python感兴趣,所以给你准备了下面的资料~
这份完整版的Python全套学习资料已经上传,朋友们如果需要可以点击链接免费领取或者滑到最后扫描二v码【保证100%免费】
python学习资源免费分享,保证100%免费!!!
需要的话可以点击这里👉[CSDN大礼包:《python学习路线&全套学习资料》免费分享]
文末有福利领取哦~
一、Python所有方向的学习路线
Python所有方向的技术点做的整理,形成各个领域的知识点汇总,它的用处就在于,你可以按照上面的知识点去找对应的学习资源,保证自己学得较为全面。
二、Python必备开发工具
三、Python视频合集
观看零基础学习视频,看视频学习是最快捷也是最有效果的方式,跟着视频中老师的思路,从基础到深入,还是很容易入门的。
四、实战案例
光学理论是没用的,要学会跟着一起敲,要动手实操,才能将自己的所学运用到实际当中去,这时候可以搞点实战案例来学习。(文末领读者福利)
五、Python练习题
检查学习结果。
六、面试资料
我们学习Python必然是为了找到高薪的工作,下面这些面试题是来自阿里、腾讯、字节等一线互联网大厂最新的面试资料,并且有阿里大佬给出了权威的解答,刷完这一套面试资料相信大家都能找到满意的工作。
👉这份完整版的Python全套学习资料已经上传,朋友们如果需要可以扫描下方CSDN官方认证二维码或者点击链接免费领取【保证100%免费】
CSDN大礼包:《python学习路线&全套学习资料》免费分享https://mp.weixin.qq.com/s/6L7QLmFc5Dly6UCw_RMDWw