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操作内容请搜索猪先飞以前的文章或继续浏览下面的相关文章希望大家以后多多支持猪先飞!
相关文章
- 这篇文章主要介绍了python-opencv-画外接矩形框的实例代码,代码简单易懂,对大家的学习或工作具有一定的参考借鉴价值,需要的朋友可以参考下...2021-09-04
Python astype(np.float)函数使用方法解析
这篇文章主要介绍了Python astype(np.float)函数使用方法解析,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友可以参考下...2020-06-08- 2022虎年新年即将来临,小编为大家带来了一个利用Python编写的虎年烟花特效,堪称全网最绚烂,文中的示例代码简洁易懂,感兴趣的同学可以动手试一试...2022-02-14
- 在本篇文章里小编给大家分享的是一篇关于python中numpy.empty()函数实例讲解内容,对此有兴趣的朋友们可以学习下。...2021-02-06
python-for x in range的用法(注意要点、细节)
这篇文章主要介绍了python-for x in range的用法,具有很好的参考价值,希望对大家有所帮助。一起跟随小编过来看看吧...2021-05-10- 这篇文章主要介绍了Python 图片转数组,二进制互转操作,具有很好的参考价值,希望对大家有所帮助。一起跟随小编过来看看吧...2021-03-09
- 这篇文章主要介绍了Python中的imread()函数用法说明,具有很好的参考价值,希望对大家有所帮助。一起跟随小编过来看看吧...2021-03-16
- 这篇文章主要介绍了python如何实现b站直播自动发送弹幕,帮助大家更好的理解和学习使用python,感兴趣的朋友可以了解下...2021-02-20
python Matplotlib基础--如何添加文本和标注
这篇文章主要介绍了python Matplotlib基础--如何添加文本和标注,帮助大家更好的利用Matplotlib绘制图表,感兴趣的朋友可以了解下...2021-01-26- 这篇文章主要介绍了解决python 使用openpyxl读写大文件的坑,具有很好的参考价值,希望对大家有所帮助。一起跟随小编过来看看吧...2021-03-13
- 这篇文章主要为大家详细介绍了SpringBoot实现excel文件生成和下载,文中示例代码介绍的非常详细,具有一定的参考价值,感兴趣的小伙伴们可以参考一下...2021-02-09
- 今天小编就为大家分享一篇python 计算方位角实例(根据两点的坐标计算),具有很好的参考价值,希望对大家有所帮助。一起跟随小编过来看看吧...2020-04-27
- 这篇文章主要为大家详细介绍了python实现双色球随机选号,文中示例代码介绍的非常详细,具有一定的参考价值,感兴趣的小伙伴们可以参考一下...2020-05-02
- 在本篇文章里小编给大家整理的是一篇关于python中使用np.delete()的实例方法,对此有兴趣的朋友们可以学习参考下。...2021-02-01
- 这篇文章主要介绍了使用Python的pencolor函数实现渐变色功能,本文通过实例代码给大家介绍的非常详细,对大家的学习或工作具有一定的参考借鉴价值,需要的朋友可以参考下...2021-03-09
- 这篇文章主要介绍了python自动化办公操作PPT的实现,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友们下面随着小编来一起学习学习吧...2021-02-05
Python getsizeof()和getsize()区分详解
这篇文章主要介绍了Python getsizeof()和getsize()区分详解,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友们下面随着小编来一起学习学习吧...2020-11-20- 这篇文章主要介绍了c#读取excel方法,实例分析了C#读取excel文件的原理与相关技巧,需要的朋友可以参考下...2020-06-25
- 这篇文章主要介绍了解决python 两个时间戳相减出现结果错误的问题,具有很好的参考价值,希望对大家有所帮助。一起跟随小编过来看看吧...2021-03-12
- 这篇文章主要为大家详细介绍了python实现学生通讯录管理系统,文中示例代码介绍的非常详细,具有一定的参考价值,感兴趣的小伙伴们可以参考一下...2021-02-25