自動化辦公，我相信很多人都有強烈的需求，都希望從繁瑣重復的勞動中掙脫出來，把精力用在有意義的事情上，
來自知乎提問
現代辦公室里幾乎任何一項作業都會用到Excel、Word，在之前文章中我已分享過Python自動化操作word的文章《Python辦公自動化｜只需三秒，一鍵生成資料分析報告》，
很多人學習蟒蛇，不知道從何學起， 很多人學習尋找python，掌握了基本語法之后，不知道在哪里案例上手， 很多已經可能知道案例的人，卻不怎么去學習更多高深的知識， 這三類人，我給大家提供一個好的學習平臺，免費獲取視頻教程，電子書，以及課程的源代碼！ QQ群：101677771 歡迎加入，一起討論學習
今天我教大家如何利用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", 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=https://www.cnblogs.com/sn5200/p/"{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 = https://www.cnblogs.com/sn5200/p/Reference(ws, min_col=2, min_row=1, max_col=3, max_row=7)
chart.add_data(data, titles_from_data=https://www.cnblogs.com/sn5200/p/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=https://www.cnblogs.com/sn5200/p/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 = https://www.cnblogs.com/sn5200/p/"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 = https://www.cnblogs.com/sn5200/p/"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")轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/412881.html
標籤：Python