Python-自动调整Excel文件列的宽度 [英] Python - Automatically adjust width of an excel file's columns

问题描述

新手-我有一个Python脚本，可根据指定的值来调整excel文件不同列的宽度:

Newbie - I have a Python script that adjusts the width of different columns of an excel file, according to the values specified:

import openpyxl
from string import ascii_uppercase

newFile = "D:\Excel Files\abc.xlsx"

wb = openpyxl.load_workbook(filename = newFile)        
worksheet = wb.active

for column in ascii_uppercase:
    if (column=='A'):
        worksheet.column_dimensions[column].width = 30
    elif (column=='B'):
        worksheet.column_dimensions[column].width = 40            
    elif (column=='G'):
        worksheet.column_dimensions[column].width = 45            
    else:
        worksheet.column_dimensions[column].width = 15

wb.save(newFile)

是否可以通过任何方式将每一列的宽度调整为最佳值，而无需为不同的列显式指定它(即，不使用此" if-elif-elif -...". ..- elif-else "结构)? 谢谢！

Is there any way through which we can adjust the width of every column to its most optimum value, without explicitly specifying it for different columns (means, without using this "if-elif-elif-......-elif-else" structure)? Thanks!

推荐答案

for col in worksheet.columns:
     max_length = 0
     column = col[0].column # Get the column name
# Since Openpyxl 2.6, the column name is  ".column_letter" as .column became the column number (1-based) 
     for cell in col:
         try: # Necessary to avoid error on empty cells
             if len(str(cell.value)) > max_length:
                 max_length = len(cell.value)
         except:
             pass
     adjusted_width = (max_length + 2) * 1.2
     worksheet.column_dimensions[column].width = adjusted_width

这也许可以变得更整洁，但是可以完成工作.您将需要根据查看时使用的字体的优点来调整Adjusted_width值.如果使用单型，则可以得到精确的单型，但不是一对一的相关性，因此您仍然需要对其进行一些调整.

This could probably be made neater but it does the job. You will want to play around with the adjusted_width value according to what is good for the font you are using when viewing it. If you use a monotype you can get it exact but its not a one-to-one correlation so you will still need to adjust it a bit.

如果您想花哨且精确无单字，则可以按宽度对字母排序，并为每个宽度分配一个浮点值，然后将其相加.这将需要第三个循环来解析单元格值中的每个字符，并对每列的结果求和，并可能需要一个字典按宽度对字符进行排序，如果这样做的话，可能会适得其反，但很酷.

If you want to get fancy and exact without monotype you could sort letters by width and assign each width a float value which you then add up. This would require a third loop parsing each character in the cell value and summing up the result for each column and probably a dictionary sorting characters by width, perhaps overkill but cool if you do it.

实际上，似乎有一种更好的方法来测量文本的视觉尺寸:链接我个人希望使用matplotlib技术.

Actually there seems to be a better way of measuring visual size of text: link personally I would prefer the matplotlib technique.

希望我能帮上忙，我的第一个stackoverflow答案=)

Hope I could be of help, my very first stackoverflow answer =)

这篇关于Python-自动调整Excel文件列的宽度的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！