python中openpyxl和xlsxwriter對Excel的操作方法
更新時間：2021年03月01日 16:35:13 作者：Alfred_|_derflA
這篇文章主要介紹了python中openpyxl和xlsxwriter對Excel的操作方法,本文給大家介紹的非常詳細(xì)，對大家的學(xué)習(xí)或工作具有一定的參考借鑒價值，需要的朋友可以參考下
前幾天，項目中有個小需求：提供Excel的上傳下載功能，使用模塊：openpyxl 和 xlsxwriter，這里簡單記錄一下。
1.簡介
Python中操作Excel的庫非常多，為開發(fā)者提供了多種選擇，如：xlrd、 xlwt、xlutils、xlwings、pandas、 win32com、openpyxl、xlsxwriter等等。
其中：
前三個一般混合使用，對Excel讀寫操作，適合舊版Excel，僅支持 xls 文件；
win32com庫功能豐富，性能強大，適用于Windows；xlwings稍次于前者，但同樣功能豐富；pandas適合處理大量數(shù)據(jù)；xlsxwriter適合大量數(shù)據(jù)的寫操作，支持圖片/表格/圖表/篩選/格式/公式等；openpyxl讀寫均可，簡單易用，功能廣泛，可插入圖表等，類似前者。
以下主要描述一下后兩種（
openpyxl、xlsxwriter）的簡單使用
2.Excel庫的使用
2.1.目標(biāo)
2.2.openpyxl的使用
2.2.1.安裝
pip install openpyxl
2.2.2.寫入Excel
import os
from openpyxl import Workbook
from openpyxl.styles import Alignment, Font, colors, PatternFill
from openpyxl.utils import get_column_letter
FILE_PATH = os.path.join(os.path.dirname(__file__), 'files/')
def write_test():
wb = Workbook()
filename = FILE_PATH + '/openpyxl_test.xlsx'
# 活動sheet
ws1 = wb.active
ws1.title = "Test-1"
# 列表追加
for row in range(1, 10):
ws1.append(range(9))
# 創(chuàng)建sheet
ws2 = wb.create_sheet(title="Test-2")
# 合并單元格
ws2.merge_cells('F5:I5')
# 拆分
# ws2.unmerge_cells('F5:I5')
# 單元賦值
ws2['F5'] = 'hello world'
# 居中
ws2['F5'].alignment = Alignment(horizontal='center', vertical='center')
# sheet標(biāo)簽顏色
ws2.sheet_properties.tabColor = '1072BA'
# 字體樣式
bold_itatic_12_font = Font(name='仿宋', size=12, italic=True, color=BLUE, bold=True)
ws2['F5'].font = bold_itatic_12_font
# 背景顏色
bg_color = PatternFill('solid', fgColor='1874CD')
ws2['F5'].fill = bg_color
# 行高列寬
ws2.row_dimensions[5].height = 40 # 第 5 行
ws2.column_dimensions['F'].width = 30 # F 列
ws3 = wb.create_sheet(title="Test-3")
for row in range(10, 20):
for col in range(10, 20):
ws3.cell(column=col, row=row, value="0}".format(get_column_letter(col)))
print(ws3['S10'].value)
# 保存
wb.save(filename)
2.2.3.讀取Excel
from openpyxl import load_workbook
def read_test(filename):
wb = load_workbook(filename)
print('取得所有工作表的表名 :')
print(wb.sheetnames, '\n')
print('取得某張工作表 :')
# sheet = wb['Sheet1']
# sheet = wb.worksheets[0]
sheet = wb[wb.sheetnames[0]]
print(type(sheet))
print('表名: ' + sheet.title, '\n')
print('取得活動工作表 :')
active_sheet = wb.active
print('表名: ' + active_sheet.title, '\n')
print('獲取工作表的大小:')
print('總行數(shù): ' + str(active_sheet.max_row))
print('總列數(shù): ' + str(active_sheet.max_column))
print('\n獲取單元格數(shù)據(jù):')
for row in range(sheet.max_row):
for col in range(sheet.max_column):
print(f"第 {row + 1} 行 {col + 1} 列：", sheet.cell(row=row + 1, column=col + 1).value)
print('\n獲取行數(shù)據(jù):')
for i, cell_object in enumerate(list(sheet.rows)):
cell_lst = [cell.value for cell in cell_object]
print(f'第 {i + 1} 行：', cell_lst)
2.2.4.案例demo 數(shù)據(jù)源格式
# contents數(shù)據(jù)
contents=[
{
"uid": "1281948912",
"group_name": "測試群-5",
"domain": "ddos5.www.cn",
"user_area": [
{
"num": 1024,
"region": "中國",
"percent": 33.33
},
{
"num": 1022,
"region": "中國香港",
"percent": 33.33
},
{
"num": 1021,
"region": "新加坡",
"percent": 33.33
}
],
"gf_area": [
{
"num": 5680,
"region": "中國香港",
"percent": 97.8
},
{
"num": 60,
"region": "新加坡",
"percent": 0.8
},
{
"num": 55,
"region": "美西",
"percent": 0.8
}
],
"sip_area": {
"waf_ip":["aliyunwaf.com.cn"],
"sip":["13.75.120.253","18.163.46.57"],
"isp_region":[
{
"country": "中國香港",
"isp": "microsoft.com"
},
{
"country": "中國香港",
"isp": "amazon.com"
}
]
}
},
]
寫入Excel
import os
import time
from openpyxl import Workbook, load_workbook
from openpyxl.styles import Alignment, Font, colors, PatternFill
FILE_PATH = os.path.join(os.path.dirname(__file__), 'files/')
# 顏色
BLACK = colors.COLOR_INDEX[0]
WHITE = colors.COLOR_INDEX[1]
RED = colors.COLOR_INDEX[2]
DARKRED = colors.COLOR_INDEX[8]
BLUE = colors.COLOR_INDEX[4]
DARKBLUE = colors.COLOR_INDEX[12]
GREEN = colors.COLOR_INDEX[3]
DARKGREEN = colors.COLOR_INDEX[9]
YELLOW = colors.COLOR_INDEX[5]
DARKYELLOW = colors.COLOR_INDEX[19]
def export_gf_excel_test(filename=None, sheetName=None, contents=None):
filename = filename if filename else 'openpyxl_Test.xlsx'
sheetName = sheetName if sheetName else '測試'
contents = contents if contents else []
# 新建工作簿
wb = Workbook()
ws = wb.worksheets[0]
# 設(shè)置sheet名稱
ws.title = sheetName
# sheet標(biāo)簽顏色
ws.sheet_properties.tabColor = '1072BA'
# 居中
pos_center = Alignment(horizontal='center', vertical='center')
# 字體樣式
bold_12_font = Font(name='仿宋', size=12, italic=False,
color=BLACK, bold=True)
# 背景顏色
bg_color = PatternFill('solid', fgColor='4DCFF6')
# 設(shè)置標(biāo)題
# 合并
merge_lst = [
'A1:A3', 'B1:B3', 'C1:C3', 'D1:R1', 'S1:AA1', 'AB1:AE1',
'D2:F2', 'G2:I2', 'J2:L2', 'M2:O2', 'P2:R2', 'S2:U2', 'V2:X2',
'Y2:AA2', 'AB2:AB3', 'AC2:AC3', 'AD2:AD3', 'AE2:AE3'
]
[ws.merge_cells(c) for c in merge_lst]
# 填充字段
title_dic = {
'A1': 'UID', 'B1': '釘釘群', 'C1': '域名',
'D1': '用戶區(qū)域', 'S1': '高防區(qū)域', 'AB1': '源站區(qū)域',
'D2': 'TOP1', 'G2': 'TOP2', 'J2': 'TOP3', 'M2': 'TOP4', 'P2': 'TOP5',
'S2': 'TOP1', 'V2': 'TOP2', 'Y2': 'TOP3',
'AB2': 'WAF IP', 'AC2': '源站IP', 'AD2': '源站IP區(qū)域', 'AE2': '運營商'
}
line3_v = ['物理區(qū)域', '請求量', '占比'] * 8
line3_k = [chr(i) + '3' for i in range(68, 91)] + ['AA3']
title_dic.update(dict(zip(line3_k, line3_v)))
for k, v in title_dic.items():
ws[k].value = v
ws[k].font = bold_12_font
ws[k].alignment = pos_center
ws[k].fill = bg_color
# 列寬
width_dic = {
'A': 30, 'B': 30, 'C': 30,
'AB': 16, 'AC': 16, 'AD': 16, 'AE': 16
}
for k, v in width_dic.items():
ws.column_dimensions[k].width = v
# 內(nèi)容
for i, dic in enumerate(contents):
user_gf_mod = {'region': '', 'num': '', 'percent': ''}
user_area = dic['user_area']
gf_area = dic['gf_area']
sip_area = dic['sip_area']
# UID+域名
data = [dic['uid'], dic['group_name'], dic['domain']]
# 用戶區(qū)域
if not user_area:
user_area = [user_gf_mod] * 5
else:
user_area = list(
map(lambda item: {
'region': item['region'], 'num': item['num'], 'percent': item['percent']}, user_area)
)
[user_area.append(user_gf_mod) for _ in range(5 - len(user_area))]
[data.extend(user_area[u].values()) for u in range(len(user_area))]
# 高防區(qū)域
if not gf_area:
gf_area = [user_gf_mod] * 3
else:
gf_area = list(
map(lambda item: {
'region': item['region'], 'num': item['num'], 'percent': item['percent']}, gf_area)
)
[gf_area.append(user_gf_mod) for _ in range(3 - len(gf_area))]
[data.extend(gf_area[g].values()) for g in range(len(gf_area))]
# 源站區(qū)域
waf_ip = sip_area['waf_ip']
sip = sip_area['sip']
isp_region = sip_area['isp_region']
data.append(','.join(waf_ip)) if waf_ip else data.append('')
data.append(','.join(sip)) if sip else data.append('')
if not isp_region:
data.extend([''] * 2)
else:
try:
country = ','.join(map(lambda item: item['country'], isp_region))
isp = ','.join(map(lambda item: item['isp'] if item['isp'] else '暫未查到', isp_region))
data.append(country)
data.append(isp)
except Exception as e:
print(e)
print(isp_region)
# 寫入Excel
ws.append(data)
# 保存文件
wb.save(filename=filename)
if __name__ == "__main__":
curTime = ''.join(map(lambda i: str(i) if len(str(i)) >= 2 else '%02d' % i, [i for i in time.localtime()[:-4]]))
filename = os.path.join(FILE_PATH, 'openpyxl_Test_{}.xlsx'.format(curTime))
export_gf_excel_test(filename, contents=contents)
2.3.xlsxwriter的使用
2.3.1.安裝
pip install XlsxWriter
2.3.2.寫入Excel
import os
import time
import json
import xlsxwriter
FILE_PATH = os.path.join(os.path.dirname(__file__), 'files/')
def export_gf_excel_test(filename=None, sheetName=None, contents=None):
filename = filename if filename else 'xlsxwriter_Test.xlsx'
sheetName = sheetName if sheetName else '測試'
contents = contents if contents else []
# 新建
wb = xlsxwriter.Workbook(filename)
ws = wb.add_worksheet(name=sheetName)
# 設(shè)置風(fēng)格
style1 = wb.add_format({
"bold": True,
'font_name': '仿宋',
'font_size': 12,
# 'font_color': '#217346',
'bg_color': '#4DCFF6',
"align": 'center',
"valign": 'vcenter',
'text_wrap': 1
})
style2 = wb.add_format({
# "bold": True,
# 'font_name': '仿宋',
'font_size': 11,
'font_color': '#217346',
'bg_color': '#E6EDEC',
"align": 'center',
"valign": 'vcenter',
# 'text_wrap': 1
})
# 標(biāo)題
ws.set_column('A1:AE1', None, style1)
# 合并單元格: first_row, first_col, last_row, last_col
# 第 1 行
ws.merge_range(0, 0, 2, 0, 'UID')
ws.merge_range(0, 1, 2, 1, '釘釘群')
ws.merge_range(0, 2, 2, 2, '域名')
ws.merge_range(0, 3, 0, 17, '用戶區(qū)域')
ws.merge_range(0, 18, 0, 26, '高防區(qū)域')
ws.merge_range(0, 27, 0, 30, '源站區(qū)域')
# 第 2 行
user_tl2 = ['TOP' + str(i) for i in range(1, 6)]
gf_tl2 = user_tl2[:3]
[ws.merge_range(1, 3 * (i + 1), 1, 3 * (i + 2) - 1, name) for i, name in enumerate(user_tl2 + gf_tl2)]
# 第 3 行
user_gf_tl3 = ['物理區(qū)域', '請求量', '占比'] * 8
sip_tl3 = ['WAF IP', '源站IP', '源站IP區(qū)域', '運營商']
[ws.write(2, 3 + i, name) for i, name in enumerate(user_gf_tl3)]
[ws.merge_range(1, 27 + i, 2, 27 + i, name) for i, name in enumerate(sip_tl3)]
# ws.write(11, 2, '=SUM(1:10)') # 增加公式
# ws.set_default_row(35) # 設(shè)置默認(rèn)行高
# 設(shè)置列寬
ws.set_column(0, 2, 30)
ws.set_column(3, 26, 10)
ws.set_column(27, 30, 16)
# 內(nèi)容
for i, dic in enumerate(contents):
user_gf_mod = {'region': '', 'num': '', 'percent': ''}
user_area = dic['user_area']
gf_area = dic['gf_area']
sip_area = dic['sip_area']
# UID+域名
data = [dic['uid'], dic['group_name'], dic['domain']]
# 用戶區(qū)域
if not user_area:
user_area = [user_gf_mod] * 5
else:
user_area = list(
map(lambda item: {
'region': item['region'], 'num': item['num'], 'percent': item['percent']}, user_area)
)
[user_area.append(user_gf_mod) for _ in range(5 - len(user_area))]
[data.extend(user_area[u].values()) for u in range(len(user_area))]
# 高防區(qū)域
if not gf_area:
gf_area = [user_gf_mod] * 3
else:
gf_area = list(
map(lambda item: {
'region': item['region'], 'num': item['num'], 'percent': item['percent']}, gf_area)
)
[gf_area.append(user_gf_mod) for _ in range(3 - len(gf_area))]
[data.extend(gf_area[g].values()) for g in range(len(gf_area))]
# 源站區(qū)域
waf_ip = sip_area['waf_ip']
sip = sip_area['sip']
isp_region = sip_area['isp_region']
data.append(','.join(waf_ip)) if waf_ip else data.append('')
data.append(','.join(sip)) if sip else data.append('')
if not isp_region:
data.extend([''] * 2)
else:
try:
country = ','.join(map(lambda item: item['country'], isp_region))
isp = ','.join(map(lambda item: item['isp'] if item['isp'] else '暫未查到', isp_region))
data.append(country)
data.append(isp)
except Exception as e:
print(e)
print(isp_region)
# 寫入Excel
ws.write_row('A' + str(i + 4), data, style2)
# 保存關(guān)閉文件
wb.close()
if __name__ == '__main__':
curTime = ''.join(map(lambda i: str(i) if len(str(i)) >= 2 else '%02d' % i, [i for i in time.localtime()[:-4]]))
filename = os.path.join(FILE_PATH, 'xlsxwriter_Test_{}.xlsx'.format(curTime))
export_gf_excel_test(filename, contents=contents)
以上是兩個庫操作Excel的簡單實現(xiàn)。對于一些復(fù)雜需求的處理，可以查看相關(guān)文檔。
到此這篇關(guān)于python中openpyxl和xlsxwriter對Excel的操作方法的文章就介紹到這了,更多相關(guān)python openpyxl和xlsxwriter對Excel操作內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
這個名為PyScript的框架，其核心目標(biāo)是為開發(fā)者提供在標(biāo)準(zhǔn)HTML中嵌入Python代碼的能力，使用?Python調(diào)用JavaScript函數(shù)庫，并以此實現(xiàn)利用Python創(chuàng)建Web應(yīng)用的功能，本文給大家介紹Python?HTML運行的案例解析，感興趣的朋友一起看看吧2022-05-05
這篇文章主要介紹了給Python中的MySQLdb模塊添加超時功能的教程,timeout功能在服務(wù)器的運維當(dāng)中非常有用,需要的朋友可以參考下2015-05-05
Pytorch中的 torch.distributions庫詳解
這篇文章主要介紹了Pytorch中的 torch.distributions庫,本文通過實例代碼給大家介紹的非常詳細(xì)，對大家的學(xué)習(xí)或工作具有一定的參考借鑒價值，需要的朋友可以參考下2023-02-02
python 實現(xiàn)仿微信聊天時間格式化顯示的代碼
這篇文章主要介紹了python 實現(xiàn)仿微信聊天時間格式化顯示，本文通過實例代碼給大家介紹的非常詳細(xì)，對大家的學(xué)習(xí)或工作具有一定的參考借鑒價值，需要的朋友參考下吧2020-04-04
這篇文章主要介紹了Python之列表推導(dǎo)式最全匯總（下篇），本文章內(nèi)容詳細(xì)，通過案例可以更好的理解列表推導(dǎo)式的相關(guān)知識，本模塊分為了三部分，本次為下篇，需要的朋友可以參考下2023-01-01
這篇文章主要為大家詳細(xì)介紹了python繪制柱形圖的方法，文中示例代碼介紹的非常詳細(xì)，具有一定的參考價值，感興趣的小伙伴們可以參考一下2022-04-04
隊列是一種先進先出（First-In-First-Out，F(xiàn)IFO）的數(shù)據(jù)結(jié)構(gòu)。隊列被用在很多地方，比如提交操作系統(tǒng)執(zhí)行的一系列進程、打印任務(wù)池等，一些仿真系統(tǒng)用隊列來模擬銀行或雜貨店里排隊的顧客。下面就介紹了Python中隊列的應(yīng)用實戰(zhàn)，需要的可以參考。2017-02-02
這篇文章主要介紹了python實現(xiàn)的發(fā)郵件功能,結(jié)合實例形式分析了Python使用網(wǎng)易郵箱發(fā)送郵件的相關(guān)操作技巧,需要的朋友可以參考下2019-09-09
Python自定義聚合函數(shù)merge與transform區(qū)別詳解
這篇文章主要介紹了Python自定義聚合函數(shù)merge與transform區(qū)別詳解,文中通過示例代碼介紹的非常詳細(xì)，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值,需要的朋友可以參考下2020-05-05
python?pandas數(shù)據(jù)處理之刪除特定行與列
Pandas是數(shù)據(jù)科學(xué)中的利器,你可能想到的數(shù)據(jù)處理騷操作,貌似用Pandas都能夠?qū)崿F(xiàn),下面這篇文章主要給大家介紹了關(guān)于python?pandas數(shù)據(jù)處理之刪除特定行與列的相關(guān)資料,文中通過實例代碼介紹的非常詳細(xì),需要的朋友可以參考下2022-08-08