环境信息
python版本:3.7.3
第三方模块:openpyxl
openpyxl安装依赖:openpyxl-2.6.3.tar.gz, jdcal-1.4.1.tar.gz, et_xmlfile-1.0.1.tar.gz
#脚本
下面的脚本可以作为模块拿来即用。脚本包含两个函数:
- excel2list : 用于将excel内容转换为python的list(二维);
- list2excel :用于将python的 二维list转换为excel。
代码
#/usr/bin/python
#! -*- coding:utf-8 -*-
# @CreateTime: 2019/9/19 11:00
# @Auth : WillZhu
# @FileName : pyExcel.py
# @UpdateTime: -
import os
from openpyxl import load_workbook, Workbook
from openpyxl.styles import PatternFill, Border, Side, Alignment, Font
def excel2list(excel_full_name,sheetname_list=[]):
"""
用于将excel的内容转换为python的list
:param excel_full_name: excel的全名(包括路径与文件名)
:param sheetname_list: excel内需要转为list的sheet名称列表,默认全部转换
:return: 格式为(P/F/E,{"sheet名A":二维list形式的数据, "sheet名B":二维list形式的数据},EFmsg)
"""
if not os.path.exists(excel_full_name):
return ('E',None,'文件不存在:%s' % str(excel_full_name))
wb = load_workbook(excel_full_name)
all_sheet_names = wb.sheetnames
if not sheetname_list:
sheetname_list = all_sheet_names
else:
tmp_list = list(set(sheetname_list).difference(set(all_sheet_names))) #sheetname_list中存在,但all_sheet_names中不存在的sheet名
if tmp_list:
return('E', None, '文件%s中不存在sheet:\n%s' % (excel_full_name, ' '.join(tmp_list)))
try:
E2L = {}
for sheet_name in sheetname_list:
ws = wb[sheet_name]
datas = []
if ws.max_row > 0:
for row in ws.rows:
cell_datas = []
for cell in row:
cell_datas.append(str(cell.value)) if cell.value else cell_datas.append('')
datas.append(cell_datas)
E2L[sheet_name] = datas
return ('P', E2L, None )
except Exception as e:
errmsg = '[excel2list]excel转list发生异常:%s' % str(e)
return ('F', None, errmsg)
def list2excel(excel_full_name, sheet_datas, over_write_flag='N'):
"""
用于将二维数组转为excel的某个sheet
:param excel_full_name: excel的全名(包括路径与文件名)
:param sheet_datas:{'sheet名A':二维list形式的数据, 'sheet名B':二维list形式的数据}}
:param over_write_flag:如果同名文件存在,是否覆盖(默认为N:不覆盖)
:return: (P/F/E,errmsg)
"""
#判断文件名称、路径、数据是否存在等
file_path, file_name = os.path.split(excel_full_name)
if not file_path:
pass
elif not os.path.expanduser(file_path):
return ('E','文件保存的目录不存在:%s' % str(file_path))
if str(os.path.splitext(file_name)[-1]).lower() not in ('.xls', '.xlsx', '.csv'):
return ('E', '文件保存的后缀名不正确(必须是xlsx/xls/csv):%s' % str(file_path))
if not sheet_datas:
return ('E', '无数据,不予处理')
if type(sheet_datas) != dict:
return ('E', 'sheet_datas入参格式错误,不予处理!')
if os.path.exists(excel_full_name) and over_write_flag == 'N':
return ('E', "文件【%s】已存在,并且over_write_flag='N'表示不允许覆盖!" % str(file_path))
#设定excel表格格式
title_font = Font(name='微软雅黑', size=10, bold=True, color='FFFFFF')
other_font = Font(name='微软雅黑', size=10)
title_fill = PatternFill("solid", fgColor="002882")
title_alig = Alignment(horizontal='center', vertical='center')
all_border = Border(left=Side(style='thin', color='64cdff'), right=Side(style='thin', color='64cdff'), top=Side(style='thin', color='64cdff'), bottom=Side(style='thin', color='64cdff'))
#开始转换
try:
wb = Workbook()
sheet_idx = 0
for sheet_name in sheet_datas.keys():
sheet_idx += 1
if sheet_idx == 1:
ws = wb.active
ws.title = sheet_name
else:
ws = wb.create_sheet(sheet_name)
this_sheet_datas = sheet_datas[sheet_name]
row_idx = 1
for rec in this_sheet_datas:
column_idx = 1
for c in rec:
ws.cell(row=row_idx, column=column_idx).value = c
ws.cell(row=row_idx, column=column_idx).border = all_border
if row_idx == 1:
ws.cell(row=row_idx, column=column_idx).fill = title_fill
ws.cell(row=row_idx, column=column_idx).font = title_font
ws.cell(row=row_idx, column=column_idx).alignment = title_alig
else:
ws.cell(row=row_idx, column=column_idx).font = other_font
column_idx += 1
row_idx += 1
wb.save(excel_full_name)
return ('P',None)
except Exception as e:
return ('F','数据转excel发生异常:%s' % str(e))
openpyxl模块拓展使用
百分比、合并单元格、使用公式、插入图片、隐藏单元格、画柱状图/饼图、设定表格区域及格式等设置可参见:
https://www.cnblogs.com/pinpin/p/10471732.html
openpyxl简单设置单元格样式可参见:
https://blog.csdn.net/weixin_41595432/article/details/79349995