本篇文章给大家分享的是有关怎么在python中利用openpyxl和xlsxwriter对Excel进行操作，小编觉得挺实用的，因此分享给大家学习，希望大家阅读完这篇文章后可以有所收获，话不多说，跟着小编一起来看看吧。
1.简介
Python中操作Excel的库非常多，为开发者提供了多种选择，如：xlrd、 xlwt、xlutils、xlwings、pandas、 win32com、openpyxl、xlsxwriter等等。
其中：
前三个一般混合使用，对Excel读写操作，适合旧版Excel，仅支持 xls 文件；
win32com 库功能丰富，性能强大，适用于Windows；
xlwings稍次于前者，但同样功能丰富；pandas适合处理大量数据；
xlsxwriter 适合大量数据的写操作，支持图片/表格/图表/筛选/格式/公式等；
openpyxl 读写均可，简单易用，功能广泛，可插入图表等，类似前者。
以下主要描述一下后两种（
openpyxl、xlsxwriter）的简单使用
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)以上就是怎么在python中利用openpyxl和xlsxwriter对Excel进行操作，小编相信有部分知识点可能是我们日常工作会见到或用到的。希望你能通过这篇文章学到更多知识。更多详情敬请关注亿速云行业资讯频道。
亿速云「云服务器」，即开即用、新一代英特尔至强铂金CPU、三副本存储NVMe SSD云盘，价格低至29元/月。点击查看>>
免责声明：本站发布的内容（图片、视频和文字）以原创、转载和分享为主，文章观点不代表本网站立场，如果涉及侵权请联系站长邮箱：is@yisu.com进行举报，并提供相关证据，一经查实，将立刻删除涉嫌侵权内容。
