openpyxl reading column width from a .xlsx and wri

问题:

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.

回答1:

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.

回答2:

If a column dimension has a width of None, Excel defaults to 0 and not to the default column width for the worksheet. openpyxl tries to make it easy to access dimensions by creating them if they do not exist. So your code is creating some with width=None in both files, but the effect of this only becomes visible when you save the file.

In your case you should be able to avoid problems by working explicitly with the dimensions in the first file:

for k, cd in ws1.column_dimensions.items():
    ws2.column_dimensions[k].width = cd.width