前几天，项目中有个小需求：提供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操作内容请搜索www.887551.com以前的文章或继续浏览下面的相关文章希望大家以后多多支持www.887551.com！
黄山市民网：https://www.huangshanshimin.com/
