python保存excel⽂件列宽⾃适应解决⽅案
背景
python使⽤pandas库或其他⼯具处理excel⽂件有⼀个很尴尬的问题，就是保存成的excel⽂件⼏乎没有⼀点排版，⽐如这样的:
每⼀列都是⼀样宽，有些列内容太长会直接溢出单元格，很不美观。少量的表格还可以耐⼼的⼿动调整列宽，处理的表格多了就显得很⿇烦。所以我想了⼀种办法解决这个问题。
思路
这个问题的关键在于获得每⼀列应有的列宽，并调整它。通常只需要获得每⼀列内容的最⼤长度就可以达到⽬标。这⾥的长度不应该是字符串的长度，⽽是⼀串字符在excel表格中占的长度。经过观察可以发现，⼀个汉字的“长度”约为2，⼀个字母或数字的长度为1。由于⼤部分内容都是由汉字字母数字构成的，所以知道这点就⾜够完成我的程序了，这⾥使⽤了ord函数获得字符的ASCII值简单地判断⼀下是不是汉字。
核⼼代码：
for elem in str_list:
elem_split =list(elem)
length =0
for c in elem_split:
if ord(c)<=256:
length +=1
else:
length +=2
len_list.append(length)
完整实现
当然仅仅调整列宽还是不够的，我还希望可以实现当字符串过长时实现⾃动换⾏、合并单元格、涂⾊等功能，所以最终我将其封装成了⼀个类，根据不同的需求可实现各种功能。
包括： 保存⽂件时列宽⾃适应、添加sheet⽽不是覆盖原xlsx⽂件、合并相同内容的单元格等
2020-04-18代码更新
# -*- coding:utf8 -*-
from openpyxl.styles import Alignment
from openpyxl.styles import PatternFill
from openpyxl.styles import Font
from openpyxl import Workbook, load_workbook
from os import remove, path
class XlsxSaver:
"""
⼀个将DataFrame转换成格式化excel的⼯具
"""
def__init__(self, df_in, filename='a.xlsx', sheet_name='Sheet1'):
"""
df_in : 从⼀个DataFrame对象获取表格内容
filename : ⽂件名
sheet_name : 表名
"""
self.filename = filename  # 保存的xlsx⽂件的名字
self.user_def =[]# 储存由⽤户⾃定义的列的列名，这些列不再参与⾃动计算列宽
ists(filename):
# 如果⽂件存在，就直接打开，添加Sheet
self.wb = load_workbook(filename)
self.sheet = ate_sheet(sheet_name)
else:
# 如果⽂件不存在，就创建表格
self.wb = Workbook()
self.sheet = self.wb.active
self.sheet.title = sheet_name
# 将df的内容复制给sheet
self.df = py()
self.sheet.append(list(lumns))
for row in range(0,len(list(self.df.index))):
for col in range(0,len(list(lumns))):
ll(row+2, col+1).value = self.df.iloc[row, col]# 注意：sheet⾏列从1开始计数
def remove_file(self):
remove(self.filename)
def set_sheet_name(self, sheet_name):
self.sheet.title = sheet_name
def set_filename(self, filename):
self.filename = filename
def get_maxlength(self, series_in, col):
"""
获取⼀个类型为object的Series中的最⼤占位长度，⽤于确定导出的xlsx⽂件的列宽
col : 表头，也参与⽐较，解决有时候表头过长的问题
"""
series = series_in.fillna('-')# 填充空值，防⽌出现nan
str_list =list(series)
len_list =[]
for elem in str_list +[col]:
elem_split =list(elem)
length =0
for c in elem_split:
if ord(c)<=256:
length +=1
length +=2
len_list.append(length)
return max(len_list)
def__auto_width(self):
cols_list =list(lumns)# 获取列名
for i in range(0,len(cols_list)):
col = cols_list[i]
if col in self.user_def:
continue
ll(1, i+1).font = Font(bold=True)# 加粗表头
letter =chr(i+65)# 由ASCII值获得对应的列字母
max_len = _maxlength(self.df[col].astype(str), col)
if max_len <=12:
lumn_dimensions[letter].width =12
elif max_len <=50:
lumn_dimensions[letter].width = max_len +2
else:
lumn_dimensions[letter].width =50
for cell in self.sheet[letter]:
cell.alignment = Alignment(wrap_text=True)
def set_width(self, col_name, width):
# 提供调整列宽的接⼝
index =list(lumns).index(col_name)
letter =chr(index+65)
lumn_dimensions[letter].width = width
self.user_def.append(col_name)
def set_color(self, col_name, color, rule):
# 提供设置颜⾊的接⼝，rule：规则函数
index =list(lumns).index(col_name)
letter =chr(index+65)
for cell in self.sheet[letter]:
if rule(cell.value):
cell.fill = PatternFill(fill_type="solid", start_color=color, end_color=color)
def set_center_alignment(self, col_name):
index =list(lumns).index(col_name)
letter =chr(index+65)
for cell in self.sheet[letter]:
cell.alignment = Alignment(wrap_text=True, horizontal='center')
def save(self):
# ⾃动调整列宽，并保存
self.__auto_width()
self.wb.save(self.filename)
def set_merge(self, col_name):
self.user_def.append(col_name)# 设置为⾃定义列
# 设置⼀列合并单元格
index =list(lumns).index(col_name)
letter =chr(index+65)
i =1
while True:
if i >= self.sheet.max_row:
# 结束条件：单元格到底
break
cell = self.sheet[letter+str(i)]
j = i+1# 第⼀步指向下⼀个单元格
while True:
# 这个过程对j进⾏试探，最终j指向的单元格是与i连续相同的最后⼀个
cell_next = self.sheet[letter+str(j)]
if cell_next.value != cell.value:
break
else:
j +=1
if j > self.sheet.max_row:
j -=1
break
if j-i >=1and cell.value !=''and cell.value:
getsavefilename
# 如果有连续两格以上的单元格内容相同，进⾏融合
msg ='%s%d:%s%d'%(letter,i,letter,j)
_cells(msg)
# 控制⼀下格式
self.sheet[letter+str(i)].alignment = Alignment(horizontal='center',
vertical='top',
wrap_text=True)
i = j+1# 继续指向下个单元格
使⽤⽰例
df = pd.read_sql(SQL, conn)  # 从数据库获取⼀张表
xlsx = XlsxSaver(df, 'output.xlsx', 'sheet1')  # 初始化⼀个对象, 设定保存后的⽂件名和表名xlsx.set_width('col1', 30)  # ⼿动指定某列列宽
xlsx.set_merge('col2')  # 设定col2列⾃动合并相邻相同的单元格
xlsx.save()  # 储存, 此时除了col2列被⼿动指定了列宽，其他列都执⾏⾃适应的列宽
注: 如果对格式没有特别的要求的话没必要这么⼤费周章了，可以使⽤以下函数
def save_as_excel(df,xlsx_name='a.xlsx',sheet_name='sheet1',startrow=0,startcol=0):
"""
功能是保存⼀个df对象为excel⽂件(xlsx)
df：要保存的dataFrame对象
xlsx_name：要保存的名字，注意加上后缀
sheet_name：要保存的表的名字
"""
writer=pd.ExcelWriter(xlsx_name)
<_excel(writer,sheet_name = sheet_name,startrow=startrow,startcol=startcol,index=False)    writer.save()#需要加上这⼀⾏，否则⽆法⽣成⽂件

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。