python中openpyxl和xlsxwriter對Excel的操作方法
前幾天,項目中有個小需求:提供Excel的上傳下載功能,使用模塊:openpyxl
和 xlsxwriter
,這裡簡單記錄一下。
1.簡介
Python中操作Excel的庫非常多,為開發者提供瞭多種選擇,如:xlrd
、 xlwt
、xlutils
、xlwings
、pandas
、 win32com
、openpyxl
、xlsxwriter
等等。
其中:
前三個一般混合使用,對Excel讀寫操作,適合舊版Excel,僅支持 xls 文件;
win32com
庫功能豐富,性能強大,適用於Windows;xlwings
稍次於前者,但同樣功能豐富;pandas
適合處理大量數據;xlsxwriter
適合大量數據的寫操作,支持圖片/表格/圖表/篩選/格式/公式等;openpyxl
讀寫均可,簡單易用,功能廣泛,可插入圖表等,類似前者。
以下主要描述一下後兩種(
openpyxl
、xlsxwriter
)的簡單使用
2.Excel庫的使用
2.1.目標
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)) # 創建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標簽顏色 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('總行數: ' + str(active_sheet.max_row)) print('總列數: ' + str(active_sheet.max_column)) print('\n獲取單元格數據:') 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獲取行數據:') 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 數據源格式
# contents數據 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] # 設置sheet名稱 ws.title = sheetName # sheet標簽顏色 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') # 設置標題 # 合並 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': '用戶區域', 'S1': '高防區域', 'AB1': '源站區域', 'D2': 'TOP1', 'G2': 'TOP2', 'J2': 'TOP3', 'M2': 'TOP4', 'P2': 'TOP5', 'S2': 'TOP1', 'V2': 'TOP2', 'Y2': 'TOP3', 'AB2': 'WAF IP', 'AC2': '源站IP', 'AD2': '源站IP區域', 'AE2': '運營商' } line3_v = ['物理區域', '請求量', '占比'] * 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 # 內容 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']] # 用戶區域 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))] # 高防區域 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))] # 源站區域 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) # 設置風格 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 }) # 標題 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, '用戶區域') ws.merge_range(0, 18, 0, 26, '高防區域') ws.merge_range(0, 27, 0, 30, '源站區域') # 第 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 = ['物理區域', '請求量', '占比'] * 8 sip_tl3 = ['WAF IP', '源站IP', '源站IP區域', '運營商'] [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) # 設置默認行高 # 設置列寬 ws.set_column(0, 2, 30) ws.set_column(3, 26, 10) ws.set_column(27, 30, 16) # 內容 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']] # 用戶區域 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))] # 高防區域 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))] # 源站區域 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) # 保存關閉文件 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的簡單實現。對於一些復雜需求的處理,可以查看相關文檔。
到此這篇關於python中openpyxl和xlsxwriter對Excel的操作方法的文章就介紹到這瞭,更多相關python openpyxl和xlsxwriter對Excel操作內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet!
推薦閱讀:
- Python 操作 Excel 之 openpyxl 模塊
- Python Excel處理庫openpyxl詳解
- python之openpyxl模塊的安裝和基本用法(excel管理)
- Python openpyxl模塊學習之輕松玩轉Excel
- 詳解Python中openpyxl模塊基本用法