Python 在 xslxwriter 中模拟自动调整列

Simulate autofit column in xslxwriter

提问by Michael Potter

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

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

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.

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

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

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

1. That URL does not specify what the units are for the third argument to set_column.
2. I can not find a way to measure the width of the item that I want to insert into the cell.
3. xlsxwriter does not appear to have a method to read back a particular cell. This means I need to keep track of each cell width as I write the cell. It would be better if I could just loop through all the cells, that way a generic routine could be written.

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

采纳答案by Cole Diamond

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.

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

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

例如，在使用 pandas 数据帧和 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.

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

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)

回答by Saltz3

I recently ran into this same issue and this is what I came up with:

我最近遇到了同样的问题，这就是我想出的：

r = 0
c = 0
for x in list:
    worksheet.set_column('{0}:{0}'.format(chr(c + ord('A'))), len(str(x)) + 2)
    worksheet.write(r, c, x)
    c += 1

In my example rwould be the row number you are outputting to, cwould be the column number you are outputting to (both 0 indexed), and xwould be the value from listthat you are wanting to be in the cell.

在我的示例中，r将是您要输出到的行号，c将是您要输出到的列号（均为 0 索引），并且x将是list您想要在单元格中的值。

the '{0}:{0}'.format(chr(c + ord('A')))piece takes the column number provided and converts it to the column letter accepted by xlsxwriter, so if c = 0set_columnwould see 'A:A', if c = 1then it would see 'B:B', and so on.

该'{0}:{0}'.format(chr(c + ord('A')))作品采用提供的列号并将其转换为 xlsxwriter 接受的列字母，因此 ifc = 0set_column会看到'A:A'，如果c = 1然后它会看到'B:B'，依此类推。

the len(str(x)) + 2piece determines the length of the string you are trying to output then adds 2 to it to ensure that the excel cell is wide enough as the length of the string does not exactly correlate to the width of the cell. You may want to play with rather you add 2or possibly more depending on your data.

该len(str(x)) + 2部分确定您尝试输出的字符串的长度，然后将其添加 2 以确保 excel 单元格足够宽，因为字符串的长度与单元格的宽度不完全相关。您可能想要2根据您的数据添加或更多。

The units that xlsxwriter accepts is a little harder to explain. When you are in excel and you hover over where you can change the column width you will see Width: 8.43 (64 pixels). In this example the unit it accepts is the 8.43, which I think is centimeters? But excel does not even provide a unit, at least not explicitly.

xlsxwriter 接受的单位有点难以解释。当您在 excel 中并将鼠标悬停在可以更改列宽的位置时，您将看到Width: 8.43 (64 pixels)。在这个例子中，它接受的单位是8.43，我认为是厘米？但是 excel 甚至没有提供单位，至少没有明确提供。

Note:I have only tried this answer on excel files that contain 1 row of data. If you will have multiple rows, you will need to have a way to determine which row will have the 'longest' information and only apply this to that row. But if each column will be roughly the same size regardless of row, then this should work fine for you.

注意：我只在包含 1 行数据的 excel 文件上尝试过这个答案。如果您将有多行，则需要有一种方法来确定哪一行将具有“最长”信息，并且仅将其应用于该行。但是，如果无论行如何，每列的大小都大致相同，那么这对您来说应该可以正常工作。

Good luck and I hope this helps!

祝你好运，我希望这会有所帮助！

回答by dfresh22

I agree with Cole Diamond. I needed to do something very similar, it worked fine for me. where self.columns is my list of columns

我同意科尔戴蒙德的观点。我需要做一些非常相似的事情，它对我来说效果很好。其中 self.columns 是我的列列表

def set_column_width(self):
    length_list = [len(x) for x in self.columns]
    for i, width in enumerate(length_list):
        self.worksheet.set_column(i, i, width)

回答by ascripter

There is another workaround to simulate Autofit that I've found on the Github site of xlsxwriter. I've modified it to return the approximate size of horizontal text (column width) or 90° rotated text (row height):

我在xlsxwriter的Github 站点上找到了另一种模拟Autofit 的解决方法。我已经修改它以返回水平文本（列宽）或 90° 旋转文本（行高）的大致大小：

from PIL import ImageFont

def get_cell_size(value, font_name, font_size, dimension="width"):
    """ value: cell content
        font_name: The name of the font in the target cell
        font_size: The size of the font in the target cell """
    font = ImageFont.truetype(font_name, size=font_size)
    (size, h) = font.getsize(str(value))
    if dimension == "height":
        return size * 0.92   # fit value experimentally determined
    return size * 0.13       # fit value experimentally determined

This doesn't address bold text or other format elements that might affect the text size. Otherwise it works pretty well.

这不会解决可能影响文本大小的粗体文本或其他格式元素。否则它工作得很好。

To find the width for your columns for autofit:

要查找用于自动调整的列的宽度：

def get_col_width(data, font_name, font_size, min_width=1):
    """ Assume 'data' to be an iterable (rows) of iterables (columns / cells)
    Also, every cell is assumed to have the same font and font size.
    Returns a list with the autofit-width per column """
    colwidth = [min_width for col in data[0]]
    for x, row in enumerate(data):
        for y, value in enumerate(row):
            colwidth[y] = max(colwidth[y], get_cell_size(value, font_name, font_size))
    return colwidth    

回答by Sebastian

Here is a version of code that supports MultiIndex for row and column - it is not pretty but works for me. It expands on @cole-diamond answer:

这是一个支持行和列多索引的代码版本 - 它不漂亮但对我有用。它扩展了@cole-diamond 答案：

def _xls_make_columns_wide_enough(dataframe, worksheet, padding=1.1, index=True):
    def get_col_widths(dataframe, padding, index):
        max_width_idx = []
        if index and isinstance(dataframe.index, pd.MultiIndex):
            # Index name lengths
            max_width_idx = [len(v) for v in dataframe.index.names]

            # Index value lengths
            for column, content in enumerate(dataframe.index.levels):
                max_width_idx[column] = max(max_width_idx[column],
                                            max([len(str(v)) for v in content.values]))
        elif index:
            max_width_idx = [
                max([len(str(s))
                     for s in dataframe.index.values] + [len(str(dataframe.index.name))])
            ]

        if isinstance(dataframe.columns, pd.MultiIndex):
            # Take care of columns - headers first.
            max_width_column = [0] * len(dataframe.columns.get_level_values(0))
            for level in range(len(dataframe.columns.levels)):
                values = dataframe.columns.get_level_values(level).values
                max_width_column = [
                    max(v1, len(str(v2))) for v1, v2 in zip(max_width_column, values)
                ]

            # Now content.
            for idx, col in enumerate(dataframe.columns):
                max_width_column[idx] = max(max_width_column[idx],
                                            max([len(str(v)) for v in dataframe[col].values]))

        else:
            max_width_column = [
                max([len(str(s)) for s in dataframe[col].values] + [len(col)])
                for col in dataframe.columns
            ]

        return [round(v * padding) for v in max_width_idx + max_width_column]

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

回答by karolyi

My version that will go over the one worksheet and autoset the field lengths:

我的版本将遍历一个工作表并自动设置字段长度：

from typing import Optional
from xlsxwriter.worksheet import (
    Worksheet, cell_number_tuple, cell_string_tuple)


def get_column_width(worksheet: Worksheet, column: int) -> Optional[int]:
    """Get the max column width in a `Worksheet` column."""
    strings = getattr(worksheet, '_ts_all_strings', None)
    if strings is None:
        strings = worksheet._ts_all_strings = sorted(
            worksheet.str_table.string_table,
            key=worksheet.str_table.string_table.__getitem__)
    lengths = set()
    for row_id, colums_dict in worksheet.table.items():  # type: int, dict
        data = colums_dict.get(column)
        if not data:
            continue
        if type(data) is cell_string_tuple:
            iter_length = len(strings[data.string])
            if not iter_length:
                continue
            lengths.add(iter_length)
            continue
        if type(data) is cell_number_tuple:
            iter_length = len(str(data.number))
            if not iter_length:
                continue
            lengths.add(iter_length)
    if not lengths:
        return None
    return max(lengths)


def set_column_autowidth(worksheet: Worksheet, column: int):
    """
    Set the width automatically on a column in the `Worksheet`.
    !!! Make sure you run this function AFTER having all cells filled in
    the worksheet!
    """
    maxwidth = get_column_width(worksheet=worksheet, column=column)
    if maxwidth is None:
        return
    worksheet.set_column(first_col=column, last_col=column, width=maxwidth)

just call set_column_autowidthwith the column.

只需set_column_autowidth用列调用即可。

回答by Soumitra

Cole Diamond's answeris awesome. I just updated the subroutine to handle multiindex rows and columns.

科尔戴蒙德的回答很棒。我刚刚更新了子程序来处理多索引行和列。

def get_col_widths(dataframe):
    # First we find the maximum length of the index columns   
    idx_max = [max([len(str(s)) for s in dataframe.index.get_level_values(idx)] + [len(str(idx))]) for idx in dataframe.index.names]
    # 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(str(x)) for x in col] if dataframe.columns.nlevels > 1 else [len(str(col))]) for col in dataframe.columns]