一、需求背景
工作需要将列表数据写入到excel中,方便运营同学查看,数据示例如下:
data_0 = [
[
['Name', 'Age', 'Gender'],
['Jack', 22, 'Male'],
['Tom', 34, 'Female']
],
[
['id', 'username', 'mobile', 'chars'],
['111', 'wsj', '123456789', None],
['112', 'zsj', '123854332', None]
]
]
data_1 = [
['Name', 'Age', 'Gender'],
['John', 25, 'Male'],
['Alice', 30, 'Female']
]
data_2 = ['Jenny', 24, 'Female']
二、实现思路
1 数据分析:
1.1 根据上面的数据示例,可以将源数据分为三类:三层嵌套列表(data_0)、双层嵌套列表(data_1)、普通列表(data_2)
1.2 三层嵌套列表包含多个双层嵌套列表,每个双层嵌套列表需要写入到excel文件不同的sheet中
1.3 双层嵌套列表包含列名和值,写入到excel文件的同一个sheet中即可
1.4 普通列表只包含值,可直接写入到excel文件的同一个sheet中
2 预期步骤:
2.1 首先识别出数据类型
2.2 使用xlwt库,创建工作簿(workbook)和工作表(worksheet)
2.3 根据不同数据类型,将列名和值写入工作表
2.4 根据输入的文件名称保存工作簿,即保存为目标excel
三、代码解析
1 识别数据类型
def data_type_handle(data):
"""判断数据类型。0--三层嵌套列表, 1--双层嵌套列表,, 2--普通列表"""
if isinstance(data, list):
if all(isinstance(d, list) for d in data):
if all(isinstance(k, list) for d in data for k in d):
return 0
return 1
return 2
return
if __name__ == '__main__':
print(f"data_0:{data_type_handle(data_0)}")
print(f"data_1:{data_type_handle(data_1)}")
print(f"data_2:{data_type_handle(data_2)}")
运行结果:
2 数据写入工作表
2.1 三层嵌套列表(data_0)
创建不同的worksheet,先写入表头,再写入表格内容
def write_type_0(workbook, data, sheet_name):
"""写入三层嵌套列表数据"""
for index, value in enumerate(data):
sheet = workbook.add_sheet(sheet_name[index], cell_overwrite_ok=True)
for col, val in enumerate(value[0]):
sheet.write(0, col, val)
print(f"写入表头成功!{value[0]}")
for i, v in enumerate(value[1:]):
for col, val in enumerate(v):
sheet.write(i + 1, col, val)
print(f"写入表格内容成功!共{len(value) - 1}行")
2.2 双层嵌套列表(data_1)
将表头和表格内容写入同一个worksheet
def write_type_1(workbook, data, sheet_name):
"""写入双层嵌套列表数据"""
sheet = workbook.add_sheet(sheet_name, cell_overwrite_ok=True)
for col, val in enumerate(data[0]):
sheet.write(0, col, val)
print(f"写入表头成功!{data[0]}")
for index, value in enumerate(data[1:]):
for col, val in enumerate(value):
sheet.write(index + 1, col, val)
print(f"写入表格内容成功!共{len(data) - 1}行")
2.3 普通列表(data_2)
将表格内容写入worksheet
def write_type_2(workbook, data, sheet_name):
"""写入普通列表数据"""
sheet = workbook.add_sheet(sheet_name, cell_overwrite_ok=True)
for col, val in enumerate(data):
sheet.write(0, col, val)
print("写入表格内容成功!共1行")
3 数据保存为excel文件
定义worksheet和excel文件名称,保存已经写入数据的workbook为excel
def list_to_excel(workbook, data, sheet_name=None, file_name=None):
"""写入excel"""
sj = datetime.datetime.now().strftime("%Y-%m-%d %H%M%S")
if data_type_handle(data) == 0:
if sheet_name is None:
sheet_name = ["new_sheet" + str(i + 1) for i in range(len(data))]
write_type_0(workbook, data, sheet_name)
else:
write_type_0(workbook, data, sheet_name)
elif data_type_handle(data) == 1:
if sheet_name is None:
write_type_1(workbook, data, 'new_sheet')
else:
write_type_1(workbook, data, sheet_name)
elif data_type_handle(data) == 2:
if sheet_name is None:
write_type_2(workbook, data, 'new_sheet')
else:
write_type_2(workbook, data, sheet_name)
else:
print(f"{data}:{type(data)}不是list类型")
return f"{data}:{type(data)}不是list类型"
if file_name is None:
save_excel(workbook, sj)
print(f"excel保存成功!【{sj}.xlsx】")
print("=" * 100)
else:
save_excel(workbook, file_name)
print(f"excel保存成功!【{file_name}.xlsx】")
print("=" * 100)
def save_excel(workbook, name):
"""保存excel表格"""
workbook.save(name + ".xlsx")
4 封装完整代码
为使后续方便优化修改以及提高代码的可复用性,将以上代码封装成类
完整代码:
import datetime
import xlwt
class ToExcel:
"""
list数据转换为excel文件
"""
def __init__(self):
"""
初始化创建工作簿
"""
self.wb = xlwt.Workbook(encoding='utf-8')
def list_to_excel(self, data, sheet_name=None, file_name=None):
"""写入excel"""
sj = datetime.datetime.now().strftime("%Y-%m-%d %H%M%S")
if self.data_type_handle(data) == 0:
if sheet_name is None:
sheet_name = ["new_sheet" + str(i + 1) for i in range(len(data))]
self.write_type_0(data, sheet_name)
else:
self.write_type_0(data, sheet_name)
elif self.data_type_handle(data) == 1:
if sheet_name is None:
self.write_type_1(data, 'new_sheet')
else:
self.write_type_1(data, sheet_name)
elif self.data_type_handle(data) == 2:
if sheet_name is None:
self.write_type_2(data, 'new_sheet')
else:
self.write_type_2(data, sheet_name)
else:
print(f"{data}:{type(data)}不是list类型")
return f"{data}:{type(data)}不是list类型"
if file_name is None:
self.save_excel(sj)
print(f"excel保存成功!【{sj}.xlsx】")
print("=" * 100)
else:
self.save_excel(file_name)
print(f"excel保存成功!【{file_name}.xlsx】")
print("=" * 100)
@staticmethod
def data_type_handle(data):
"""判断数据类型。0--三层嵌套列表, 1--双层嵌套列表,, 2--普通列表"""
if isinstance(data, list):
if all(isinstance(d, list) for d in data):
if all(isinstance(k, list) for d in data for k in d):
return 0
return 1
return 2
return
def write_type_0(self, data, sheet_name):
"""写入三层嵌套列表数据"""
for index, value in enumerate(data):
sheet = self.wb.add_sheet(sheet_name[index], cell_overwrite_ok=True)
for col, val in enumerate(value[0]):
sheet.write(0, col, val)
print(f"写入表头成功!{value[0]}")
for i, v in enumerate(value[1:]):
for col, val in enumerate(v):
sheet.write(i + 1, col, val)
print(f"写入表格内容成功!共{len(value) - 1}行")
def write_type_1(self, data, sheet_name):
"""写入双层嵌套列表数据"""
sheet = self.wb.add_sheet(sheet_name, cell_overwrite_ok=True)
for col, val in enumerate(data[0]):
sheet.write(0, col, val)
print(f"写入表头成功!{data[0]}")
for index, value in enumerate(data[1:]):
for col, val in enumerate(value):
sheet.write(index + 1, col, val)
print(f"写入表格内容成功!共{len(data) - 1}行")
def write_type_2(self, data, sheet_name):
"""写入普通列表数据"""
sheet = self.wb.add_sheet(sheet_name, cell_overwrite_ok=True)
for col, val in enumerate(data):
sheet.write(0, col, val)
print("写入表格内容成功!共1行")
def save_excel(self, name):
"""保存excel表格"""
self.wb.save(name + ".xlsx")
if __name__ == '__main__':
ToExcel().list_to_excel(data=data_0, sheet_name=["worksheet1", "worksheet2"])
ToExcel().list_to_excel(data=data_1, file_name="data1")
ToExcel().list_to_excel(data=data_2, file_name="data2")
运行结果:
标签:sheet,name,Python,type,excel,列表,write,data From: https://blog.51cto.com/u_16379005/8736655