在 xslxwriter 中模拟自动调整列 [英] Simulate autofit column in xslxwriter

问题描述

我想在 Python 的 xlsxwriter 中模拟 Excel 自动调整功能.根据这个网址，不直接支持:http://xlsxwriter.readthedocs.io/worksheet.html

I would like to simulate the Excel autofit function in Python's xlsxwriter. According to this url, it is not directly supported: http://xlsxwriter.readthedocs.io/worksheet.html

但是，循环遍历工作表上的每个单元格并确定列的最大大小并仅使用 worksheet.set_column(row, col, width) 设置宽度应该非常简单.

However, it should be quite straightforward to loop through each cell on the sheet and determine the maximum size for the column and just use worksheet.set_column(row, col, width) to set the width.

阻碍我写这篇文章的复杂因素是:

The complications that is keeping me from just writing this are:

1. 该 URL 未指定 set_column 的第三个参数的单位.
2. 我找不到一种方法来测量要插入到单元格中的项目的宽度.
3. xlsxwriter 似乎没有读回特定单元格的方法.这意味着我需要在编写单元格时跟踪每个单元格的宽度.如果我可以循环遍历所有单元格会更好，这样就可以编写一个通用例程.

推荐答案

作为一般规则，您希望列的宽度略大于列中最长字符串的大小.1 个单位的 xlsxwriter 列的宽度大约等于一个字符的宽度.因此，您可以通过将每一列设置为该列中的最大字符数来模拟自动调整.

As a general rule, you want the width of the columns a bit larger than the size of the longest string in the column. The with of 1 unit of the xlsxwriter columns is about equal to the width of one character. So, you can simulate autofit by setting each column to the max number of characters in that column.

每个例子，我倾向于在使用 pandas 数据帧和 xlsxwriter 时使用下面的代码.

Per example, I tend to use the code below when working with pandas dataframes and xlsxwriter.

它首先找到索引的最大宽度，它始终是熊猫渲染数据帧的左列.然后，它返回所有值的最大值和从左向右移动的每个剩余列的列名.

It first finds the maximum width of the index, which is always the left column for a pandas to excel rendered dataframe. Then, it returns the maximum of all values and the column name for each of the remaining columns moving left to right.

针对您使用的任何数据调整此代码应该不会太困难.

It shouldn't be too difficult to adapt this code for whatever data you are using.

def get_col_widths(dataframe):
    # First we find the maximum length of the index column   
    idx_max = max([len(str(s)) for s in dataframe.index.values] + [len(str(dataframe.index.name))])
    # Then, we concatenate this to the max of the lengths of column name and its values for each column, left to right
    return [idx_max] + [max([len(str(s)) for s in dataframe[col].values] + [len(col)]) for col in dataframe.columns]

for i, width in enumerate(get_col_widths(dataframe)):
    worksheet.set_column(i, i, width)

这篇关于在 xslxwriter 中模拟自动调整列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！