python保存excel文件列宽自适应解决方案

背景

python使用pandas库或其他工具处理excel文件有一个很尴尬的问题,就是保存成的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 = []  # 储存由用户自定义的列的列名,这些列不再参与自动计算列宽
        if path.exists(filename):
            # 如果文件存在,就直接打开,添加Sheet
            self.wb = load_workbook(filename)
            self.sheet = self.wb.create_sheet(sheet_name)
        else:
            # 如果文件不存在,就创建表格
            self.wb = Workbook()
            self.sheet = self.wb.active
            self.sheet.title = sheet_name
        # 将df的内容复制给sheet
        self.df = df_in.copy()
        self.sheet.append(list(self.df.columns))
        for row in range(0, len(list(self.df.index))):
            for col in range(0, len(list(self.df.columns))):
                self.sheet.cell(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
                else:
                    length += 2
            len_list.append(length)
        return max(len_list)

    def __auto_width(self):
        cols_list = list(self.df.columns)  # 获取列名
        for i in range(0, len(cols_list)):
            col = cols_list[i]
            if col in self.user_def:
                continue
            self.sheet.cell(1, i+1).font = Font(bold=True)  # 加粗表头
            letter = chr(i+65)  # 由ASCII值获得对应的列字母
            max_len = self.get_maxlength(self.df[col].astype(str), col)
            if max_len <= 12:
                self.sheet.column_dimensions[letter].width = 12
            elif max_len <= 50:
                self.sheet.column_dimensions[letter].width = max_len + 2
            else:
                self.sheet.column_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(self.df.columns).index(col_name)
        letter = chr(index+65)
        self.sheet.column_dimensions[letter].width = width
        self.user_def.append(col_name)
        
    def set_color(self, col_name, color, rule):
        # 提供设置颜色的接口,rule:规则函数
        index = list(self.df.columns).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(self.df.columns).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(self.df.columns).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:
                    j -= 1
                    break
                else:
                    j += 1
                if j > self.sheet.max_row:
                    j -= 1
                    break
            if j-i >= 1 and cell.value != '' and cell.value:
                # 如果有连续两格以上的单元格内容相同,进行融合
                msg = '%s%d:%s%d'%(letter,i,letter,j)
                self.sheet.merge_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)
    df.to_excel(writer,sheet_name = sheet_name,startrow=startrow,startcol=startcol,index=False)
    writer.save()#需要加上这一行,否则无法生成文件
  • 21
    点赞
  • 140
    收藏
    觉得还不错? 一键收藏
  • 29
    评论
好的,我来回答你的问题。首先,可以使用openpyxl模块来设置Excel单元格的宽度和行高。 设置单元格宽度可以使用column_dimensions属性中的width属性: ```python from openpyxl import Workbook # 创建Workbook对象 wb = Workbook() # 获取第一个工作表 ws = wb.active # 设置第一列的宽度为20 ws.column_dimensions['A'].width = 20 # 保存Excel文件 wb.save('example.xlsx') ``` 设置行高可以使用row_dimensions属性中的height属性: ```python from openpyxl import Workbook # 创建Workbook对象 wb = Workbook() # 获取第一个工作表 ws = wb.active # 设置第一行的高度为30 ws.row_dimensions[1].height = 30 # 保存Excel文件 wb.save('example.xlsx') ``` 注意,设置的宽度和高度都是以磅为单位,1磅=1/72英寸。 如果要设置列宽自适应,则可以使用worksheet模块中的column_dimensions属性中的auto_size属性: ```python from openpyxl import Workbook from openpyxl.utils import get_column_letter # 创建Workbook对象 wb = Workbook() # 获取第一个工作表 ws = wb.active # 写入数据 for i in range(1, 6): col_letter = get_column_letter(i) ws[col_letter + '1'] = 'Column ' + str(i) # 设置列宽自适应 for col in ws.columns: max_length = 0 column = col[0].column # 获取列名 for cell in col: try: if len(str(cell.value)) > max_length: max_length = len(cell.value) except: pass adjusted_width = (max_length + 2) * 1.2 ws.column_dimensions[column].width = adjusted_width # 保存Excel文件 wb.save('example.xlsx') ``` 这里使用了get_column_letter函数将列索引转换为列字母。然后遍历每一列,计算每一列的最大宽度,并根据最大宽度设置列宽。这里使用了一个简单的公式:(max_length + 2) * 1.2,其中2是为了留出一些空隙,1.2是一个调整因子,可以根据实际情况进行调整。 希望这些代码能帮到你。

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 29
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值