openpyxl从.xlsx读取列宽并将其写入新的.xlsx:新文件中的宽度不同 [英] openpyxl reading column width from a .xlsx and writing it to a new .xlsx: width is different in a new file

问题描述

我正在使用openpyxl处理Python中的.xlsx文件.

我有一个sample.xlsx文件，我想从中获取标题字段和列宽的列表，以便在脚本生成的文件中使用.

首先，我创建一个包含列名及其相应宽度的列表. 所以我用:

filter_list = {"name": [], "width": []} 
row_count = sheet.max_row 
   for i in range(row_count): 
      cell = sheet.cell(row, i + 1) 
      if cell.value: 
        filter_list["name"].append(cell.value)
        filter_list["width"].append(sheet.column_dimensions
             [cell.column].width)  

值以获取列宽，然后设置相同的值:

if filter_list["width"][i] is not None: 
    sheet.column_dimensions[cell.column].width = filter_list["width"][i]

如果特定列具有非默认值(即非None)，则可以正常工作.当我打开输出文件(在Mac和Windows上)时，我看到两个文件的此特定列的宽度相同.

但是如果其中一列在输入文件中具有... width = None(因此输出文件的column width参数具有相同的None值)，则输出文件中的同一列看起来会有所不同. /p>

关于它为什么会发生的任何想法吗?

在.xlsx中的某个地方似乎有一个参数，该参数以某种方式定义了默认宽度(= None值)设置为什么.

有什么办法可以使两个文件的列宽看起来都一样?

更新:我添加了宽度值None的检查.我只为没有None值的列设置列宽参数.结果仍然是相同的-文件的默认宽度有所不同.

更新2:我已经更新了代码，以准确显示我如何读取和设置列宽.

解决方案

第一句话的答案:不，不知道为什么会发生

但是在回答第二个问题时，在我看来却很简单:

if mywidth is not None:
    output_sheet.column_dimensions['A'].width = mywidth

即遍历所有列的循环只是检查列宽是否已实际定义.如果是这样，则将其应用于新工作表；否则，将其忽略.

注意，它是column_dimensions(复数)而不是单数版本

编辑问题更新后:

Excel处理默认列宽的方法是在工作表级别.使用 OpenXML工具<，然后与 openpyxl docs 进行交叉引用看来您可能遵循以下条件:

wb1 = load_workbook("my/workbook/name.xlsx")
ws1 = wb1.active
default_col_width = ws1.sheet_format.defaultColWidth

或者，打开新工作簿后，转到Home选项卡的Cells部分，单击Format按钮，然后选择Default width...并根据需要进行设置.

I am using openpyxl to work with .xlsx files in Python.

I have a sample.xlsx file from which I want to get a list of title fields and column width to use in the file generated by my script.

At first I create a list with column names and their corresponding widths. So I use:

filter_list = {"name": [], "width": []} 
row_count = sheet.max_row 
   for i in range(row_count): 
      cell = sheet.cell(row, i + 1) 
      if cell.value: 
        filter_list["name"].append(cell.value)
        filter_list["width"].append(sheet.column_dimensions
             [cell.column].width)  

value to get column widths and then I set the same value:

if filter_list["width"][i] is not None: 
    sheet.column_dimensions[cell.column].width = filter_list["width"][i]

It works fine if a particular column has a non-default value (i.e. not None). When I open the output file (on both Mac and Windows) I see that the width of this particular column is the same for both files.

But if one of the columns has ...width = None in the input file (so the output file have the same None value for column width parameter) the same column in the output file looks different.

Any ideas on why it happens?

It looks like somewhere in the .xlsx there is a parameter, which somehow defines what the default width (= None value) is set to.

Is there any way to make both files look the same in terms of column width?

Update: I added a check for width value of None. I only set the column width parameter for columns that have not None value. The result is still the same - files are different in terms for default width.

Update 2: I have updated the code to show exactly how I read and set column width.

解决方案

In answer to the first bit: No, no idea why it happens

But in answer to the second, it seems to me to be as straightforward as:

if mywidth is not None:
    output_sheet.column_dimensions['A'].width = mywidth

ie in a loop through all the columns just check that the column width is actually defined. If it is then apply it to the new sheet, if not then just ignore it.

NB it's column_dimensions (plural) not the singular version

EDIT After question updates:

The way that Excel deals with default column widths is on a worksheet level. Having looked at a couple of basic Excel workbooks using the OpenXML tools then cross referencing with the openpyxl docs it would seem that you might be after the following:

wb1 = load_workbook("my/workbook/name.xlsx")
ws1 = wb1.active
default_col_width = ws1.sheet_format.defaultColWidth

Alternatively, once you've opened the new workbook go to the Home tab, Cells section, click the Format button and choose Default width... and set it as you want.

这篇关于openpyxl从.xlsx读取列宽并将其写入新的.xlsx:新文件中的宽度不同的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！