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.安装
pipinstallopenpyxl
2.2.2.写入Excel
importos
fromopenpyxlimportWorkbook
fromopenpyxl.stylesimportAlignment,Font,colors,PatternFill
fromopenpyxl.utilsimportget_column_letter
FILE_PATH=os.path.join(os.path.dirname(__file__),'files/')
defwrite_test():
wb=Workbook()
filename=FILE_PATH+'/openpyxl_test.xlsx'
#活动sheet
ws1=wb.active
ws1.title="Test-1"
#列表追加
forrowinrange(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']='helloworld'
#居中
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")
forrowinrange(10,20):
forcolinrange(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
fromopenpyxlimportload_workbook
defread_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获取单元格数据:')
forrowinrange(sheet.max_row):
forcolinrange(sheet.max_column):
print(f"第{row+1}行{col+1}列：",sheet.cell(row=row+1,column=col+1).value)
print('\n获取行数据:')
fori,cell_objectinenumerate(list(sheet.rows)):
cell_lst=[cell.valueforcellincell_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
importos
importtime
fromopenpyxlimportWorkbook,load_workbook
fromopenpyxl.stylesimportAlignment,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]
defexport_gf_excel_test(filename=None,sheetName=None,contents=None):
filename=filenameiffilenameelse'openpyxl_Test.xlsx'
sheetName=sheetNameifsheetNameelse'测试'
contents=contentsifcontentselse[]
#新建工作簿
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)forcinmerge_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':'WAFIP','AC2':'源站IP','AD2':'源站IP区域','AE2':'运营商'
}
line3_v=['物理区域','请求量','占比']*8
line3_k=[chr(i)+'3'foriinrange(68,91)]+['AA3']
title_dic.update(dict(zip(line3_k,line3_v)))
fork,vintitle_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
}
fork,vinwidth_dic.items():
ws.column_dimensions[k].width=v
#内容
fori,dicinenumerate(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']]
#用户区域
ifnotuser_area:
user_area=[user_gf_mod]*5
else:
user_area=list(
map(lambdaitem:{
'region':item['region'],'num':item['num'],'percent':item['percent']},user_area)
)
[user_area.append(user_gf_mod)for_inrange(5-len(user_area))]
[data.extend(user_area[u].values())foruinrange(len(user_area))]
#高防区域
ifnotgf_area:
gf_area=[user_gf_mod]*3
else:
gf_area=list(
map(lambdaitem:{
'region':item['region'],'num':item['num'],'percent':item['percent']},gf_area)
)
[gf_area.append(user_gf_mod)for_inrange(3-len(gf_area))]
[data.extend(gf_area[g].values())forginrange(len(gf_area))]
#源站区域
waf_ip=sip_area['waf_ip']
sip=sip_area['sip']
isp_region=sip_area['isp_region']
data.append(','.join(waf_ip))ifwaf_ipelsedata.append('')
data.append(','.join(sip))ifsipelsedata.append('')
ifnotisp_region:
data.extend(['']*2)
else:
try:
country=','.join(map(lambdaitem:item['country'],isp_region))
isp=','.join(map(lambdaitem:item['isp']ifitem['isp']else'暂未查到',isp_region))
data.append(country)
data.append(isp)
exceptExceptionase:
print(e)
print(isp_region)
#写入Excel
ws.append(data)
#保存文件
wb.save(filename=filename)
if__name__=="__main__":
curTime=''.join(map(lambdai:str(i)iflen(str(i))>=2else'%02d'%i,[iforiintime.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.安装
pipinstallXlsxWriter
2.3.2.写入Excel
importos
importtime
importjson
importxlsxwriter
FILE_PATH=os.path.join(os.path.dirname(__file__),'files/')
defexport_gf_excel_test(filename=None,sheetName=None,contents=None):
filename=filenameiffilenameelse'xlsxwriter_Test.xlsx'
sheetName=sheetNameifsheetNameelse'测试'
contents=contentsifcontentselse[]
#新建
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)foriinrange(1,6)]
gf_tl2=user_tl2[:3]
[ws.merge_range(1,3*(i+1),1,3*(i+2)-1,name)fori,nameinenumerate(user_tl2+gf_tl2)]
#第3行
user_gf_tl3=['物理区域','请求量','占比']*8
sip_tl3=['WAFIP','源站IP','源站IP区域','运营商']
[ws.write(2,3+i,name)fori,nameinenumerate(user_gf_tl3)]
[ws.merge_range(1,27+i,2,27+i,name)fori,nameinenumerate(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)
#内容
fori,dicinenumerate(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']]
#用户区域
ifnotuser_area:
user_area=[user_gf_mod]*5
else:
user_area=list(
map(lambdaitem:{
'region':item['region'],'num':item['num'],'percent':item['percent']},user_area)
)
[user_area.append(user_gf_mod)for_inrange(5-len(user_area))]
[data.extend(user_area[u].values())foruinrange(len(user_area))]
#高防区域
ifnotgf_area:
gf_area=[user_gf_mod]*3
else:
gf_area=list(
map(lambdaitem:{
'region':item['region'],'num':item['num'],'percent':item['percent']},gf_area)
)
[gf_area.append(user_gf_mod)for_inrange(3-len(gf_area))]
[data.extend(gf_area[g].values())forginrange(len(gf_area))]
#源站区域
waf_ip=sip_area['waf_ip']
sip=sip_area['sip']
isp_region=sip_area['isp_region']
data.append(','.join(waf_ip))ifwaf_ipelsedata.append('')
data.append(','.join(sip))ifsipelsedata.append('')
ifnotisp_region:
data.extend(['']*2)
else:
try:
country=','.join(map(lambdaitem:item['country'],isp_region))
isp=','.join(map(lambdaitem:item['isp']ifitem['isp']else'暂未查到',isp_region))
data.append(country)
data.append(isp)
exceptExceptionase:
print(e)
print(isp_region)
#写入Excel
ws.write_row('A'+str(i+4),data,style2)
#保存关闭文件
wb.close()
if__name__=='__main__':
curTime=''.join(map(lambdai:str(i)iflen(str(i))>=2else'%02d'%i,[iforiintime.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的操作方法的文章就介绍到这了,更多相关pythonopenpyxl和xlsxwriter对Excel操作内容请搜索毛票票以前的文章或继续浏览下面的相关文章希望大家以后多多支持毛票票！
声明：本文内容来源于网络，版权归原作者所有，内容由互联网用户自发贡献自行上传，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任。如果您发现有涉嫌版权的内容，欢迎发送邮件至：czq8825#qq.com（发邮件时，请将#更换为@）进行举报，并提供相关证据，一经查实，本站将立刻删除涉嫌侵权内容。