Python xlwt - 访问现有单元格内容，自动调整列宽 [英] Python xlwt - accessing existing cell content, auto-adjust column width

问题描述

我正在尝试创建一个 Excel 工作簿，我可以在其中自动设置或在保存工作簿之前自动调整列的宽度.

I am trying to create an Excel workbook where I can auto-set, or auto-adjust the widths of the columns before saving the workbook.

我一直在阅读 Python-Excel 教程，希望能在 xlwt 中找到一些模拟 xlrd 的函数(例如 sheet_names()、cellname(row, col)、cell_type、cell_value 等等...)例如，假设我有以下内容:

I have been reading the Python-Excel tutorial in hopes of finding some functions in xlwt that emulate xlrd ones (such as sheet_names(), cellname(row, col), cell_type, cell_value, and so on...) For example, suppose I have the following:

from xlwt import Workbook    
wb = Workbook()
sh1 = wb.add_sheet('sheet1' , cell_overwrite_ok = True)    
sh2 = wb.get_sheet(0)

wb.get_sheet(0) 类似于 xlrd 中提供的 rb.sheet_by_index(0) 函数，除了前者允许您修改内容(提供用户已设置 cell_overwrite_ok = True)

wb.get_sheet(0) is similar to the rb.sheet_by_index(0) function offered in xlrd, except that the former allows you to modify the contents (provided the user has set cell_overwrite_ok = True)

假设 xlwt 确实提供了我正在寻找的功能，我正计划再次浏览每个工作表，但这一次跟踪占用特定列空间最多的内容，并根据该内容设置列宽.当然，我也可以在写入工作表时跟踪特定列的最大宽度，但我觉得在所有数据都已写入后设置宽度会更清晰.

Assuming xlwt DOES offer the functions I am looking for, I was planning on going through every worksheet again, but this time keeping track of the content that takes the most space for a particular column, and set the column width based on that. Of course, I can also keep track of the max width for a specific column as I write to the sheet, but I feel like it would be cleaner to set the widths after all the data has been already written.

有谁知道我可以这样做吗?如果没有，您建议如何调整列宽?

Does anyone know if I can do this? If not, what do you recommend doing in order to adjust the column widths?

推荐答案

我刚刚实现了一个包装类，用于在您输入项目时跟踪项目的宽度.它似乎工作得很好.

I just implemented a wrapper class that tracks the widths of items as you enter them. It seems to work pretty well.

import arial10

class FitSheetWrapper(object):
    """Try to fit columns to max size of any entry.
    To use, wrap this around a worksheet returned from the 
    workbook's add_sheet method, like follows:

        sheet = FitSheetWrapper(book.add_sheet(sheet_name))

    The worksheet interface remains the same: this is a drop-in wrapper
    for auto-sizing columns.
    """
    def __init__(self, sheet):
        self.sheet = sheet
        self.widths = dict()

    def write(self, r, c, label='', *args, **kwargs):
        self.sheet.write(r, c, label, *args, **kwargs)
        width = arial10.fitwidth(label)
        if width > self.widths.get(c, 0):
            self.widths[c] = width
            self.sheet.col(c).width = width

    def __getattr__(self, attr):
        return getattr(self.sheet, attr)

所有的魔法都在 John Yeung 的 arial10模块.这对于 Arial 10 有很好的宽度，这是默认的 Excel 字体.如果您想使用其他字体编写工作表，则需要更改 fitwidth 函数，最好考虑传递给 FitSheetWrapper.write 的 style 参数.

All the magic is in John Yeung's arial10 module. This has good widths for Arial 10, which is the default Excel font. If you want to write worksheets using other fonts, you'll need to change the fitwidth function, ideally taking into account the style argument passed to FitSheetWrapper.write.

这篇关于Python xlwt - 访问现有单元格内容，自动调整列宽的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！