Python办公自动化｜10个方法，是时候对Excel下手了

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

来自知乎提问

现代办公室里几乎任何一项工作都会用到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 Workbookwb = Workbook()ws_00 = wb.active #默认不取名称ws_00['A1']= 'Python学习与数据挖掘'ws_01 = wb.create_sheet('new_sheet', 0) # 取一个new_sheet的名称ws_01['A1']= 23wb.save('/Users/***/Desktop/document.xlsx')

2、写工作簿

from openpyxl import Workbookfrom openpyxl.utils import get_column_letterwb = Workbook()dest_filename = '/Users/****/Desktop/empty_book.xlsx'ws1 = wb.activews1.title = 'range names'for row in range(1, 40): ws1.append(range(600))ws2 = wb.create_sheet(title='Pi')ws2['F5'] = 3.14ws3 = 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 Workbookfrom openpyxl.drawing.image import Imagewb = Workbook()ws = wb.activews['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 Workbookfrom openpyxl.chart import ( AreaChart, Reference, Series,)wb = Workbook()ws = wb.activerows = [ ['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 = 13chart.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 Workbookfrom openpyxl.chart import (    RadarChart,    Reference,)wb = Workbook()ws = wb.activerows = [    ['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 = 26chart.title = 'Garden Centre Sales'chart.y_axis.delete = Truews.add_chart(chart, 'A17')wb.save('radar.xlsx')

7、使用公式

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

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

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

# -*- coding: utf-8 -*-from openpyxl import Workbookfrom openpyxl.styles import colorsfrom openpyxl.styles import Fontwb = Workbook()ws = wb.activea1 = ws['A1']d4 = ws['D4']ft = Font(color=colors.RED)  # color='FFBB00'，颜色编码也可以设定颜色a1.font = ftd4.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 A1a1.value = 'abc'# Save the filewb.save('/Users/***/Desktop/document01.xlsx')

9、设定字体和大小

# -*- coding: utf-8 -*-from openpyxl import Workbookfrom openpyxl.styles import colorsfrom openpyxl.styles import Fontwb = Workbook()ws = wb.activea1 = ws['A1']d4 = ws['D4']a1.value = 'abc'from openpyxl.styles import Fontfrom copy import copyft1 = Font(name=u'宋体', size=14)ft2 = copy(ft1) #复制字体对象ft2.name = 'Tahoma'

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

# -*- coding: utf-8 -*-from openpyxl import Workbookfrom openpyxl.styles import Fontfrom openpyxl.styles import NamedStyle, Font, Border, Side,PatternFillwb = Workbook()ws = wb.activehighlight = 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 filewb.save('/Users/***/Desktop/document01.xlsx')