分享11個(gè)Python自動(dòng)化操作Excel的方法
前言:
今天我教大家如何利用Python自動(dòng)化操作Excel,包括:介紹操作Excel的工具包、安裝方法及操作Excel具體方法。對(duì)于每天有大量重復(fù)性工作的同學(xué)來(lái)說(shuō),這款工具絕對(duì)是福利。
一、openpyxl是什么
openpyxl是一個(gè)Python庫(kù),用于讀取/寫入Excel xlsx / xlsm / xltx / xltm
文件。它的誕生是因?yàn)槿鄙倏蓮腜ython本地讀取/寫入Office Open XML格式的庫(kù)。官方文檔
二、openpyxl安裝
使用pip安裝openpyxl
。建議在不帶系統(tǒng)軟件包的Python virtualenv中執(zhí)行此操作:
pip install openpyxl
支持流行的lxml
庫(kù)(如果已安裝)。這在創(chuàng)建大文件時(shí)特別有用。
三、openpyxl操作指南
1、創(chuàng)建工作簿
from openpyxl import Workbook wb = Workbook() ws_00 = wb.active #默認(rèn)不取名稱 ws_00['A1']= 'Python學(xué)習(xí)與數(shù)據(jù)挖掘' ws_01 = wb.create_sheet("new_sheet", 0) # 取一個(gè)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、將工作表轉(zhuǎn)換為數(shù)據(jù)框
df = DataFrame(ws.values)
6、2D區(qū)域圖
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")
7、雷達(dá)圖
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")
8、使用公式
業(yè)務(wù)中需要批量處理的操作,我們可以代碼化。Python
利用Excel
的公式功能來(lái)處理數(shù)據(jù),可以達(dá)到事半功倍的效果。
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')
9、給單元格設(shè)定字體顏色
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",顏色編碼也可以設(shè)定顏色 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")
10、設(shè)定字體和大小
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) ? #復(fù)制字體對(duì)象 ft2.name = "Tahoma"
11、設(shè)定單元格的邊框、字體、顏色、大小和邊框背景色
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")
到此這篇關(guān)于分享11 個(gè)Python自動(dòng)化操作Excel的方法的文章就介紹到這了,更多相關(guān)Python自動(dòng)化操作Excel方法內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!
相關(guān)文章
Python庫(kù)Gym開發(fā)和比較強(qiáng)化學(xué)習(xí)算法使用探究
這篇文章主要介紹了Python庫(kù)Gym開發(fā)和比較強(qiáng)化學(xué)習(xí)算法使用探究,有需要的朋友可以借鑒參考下,希望能夠有所幫助,祝大家多多進(jìn)步,早日升職加薪2024-01-01如何在python中實(shí)現(xiàn)ECDSA你知道嗎
這篇文章主要為大家介紹了python中實(shí)現(xiàn)ECDSA,具有一定的參考價(jià)值,感興趣的小伙伴們可以參考一下,希望能夠給你帶來(lái)幫助,希望能夠給你帶來(lái)幫助2021-11-11