自动化办公，我相信很多人都有强烈的需求，都希望从繁琐重复的劳动中挣脱出来，把精力用在有意义的事情上。
来自知乎提问
现代办公室里几乎任何一项工作都会用到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 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')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)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')删除列f:h
ws.delete_cols(6, 3)df = dataframe(ws.values)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")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")业务中需要批量处理的操作，我们可以代码化。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')# -*- 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")# -*- 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"# -*- 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")
