Using openpyxl to get column width from Excel

Question

I need to get column widths from Excel and write them to a .txt file. I believe openpyxl is the way to do it, but I cannot get the openpyxl.worksheet.dimensions.ColumnDimension.width to return a value. Is this the right approach?

try `print(ws.column_dimensions['A'].width)` – LuckyZakary Sep 10 '19 at 00:23 — LuckyZakary, Sep 10 '19 at 00:23
What have you actually tried to do? – Charlie Clark Sep 13 '19 at 10:16 — Charlie Clark, Sep 13 '19 at 10:16

Trenton McKinney · Answer 1 · 2019-09-10T06:03:43.807

1

Here is an alternate method, which instantiates Excel as an object.
This only works on Windows, and if Excel is installed.

Given:

Code:

import win32com.client as win32

filename = 'D:/PythonProjects/stack_overflow/test.xlsx'

excel = win32.gencache.EnsureDispatch('Excel.Application')
excel.Visible = False  # can be True if you like

wb = excel.Workbooks.Open(filename)
ws1 = wb.Sheets('Sheet1')

for x in range(1, ws1.UsedRange.Columns.Count +1):
    print(ws1.Columns(x).ColumnWidth)

wb.Close(True)

Output:

ws1.UsedRange.Columns.Count returns an int of the number of used columns, in the Excel screenshot A:F == 1:6
ws1.Columns(x).ColumnWidth returns the column width
Incidentally, using this method to interact with Excel exposes all Excel functions, allowing for interaction with new or existing files, graphing, formatting, etc.
- If excel.Visible = True, operations can be performed on the live file while you watch; just don't interact with Excel with python is working on it.

edited Sep 10 '19 at 06:03

answered Sep 10 '19 at 00:51

Trenton McKinney

56,955
33
144
158

what's the difference between win32.gencache.EnsureDispatch vs win32.client.Dispatch ? – hackwithharsha Sep 12 '19 at 18:32
1

Lucky for the both of us, that can be found at [win32.Dispatch vs win32.gencache in Python. What are the pros and cons?](https://stackoverflow.com/questions/50127959/win32-dispatch-vs-win32-gencache-in-python-what-are-the-pros-and-cons) – Trenton McKinney Sep 12 '19 at 18:37
1

Thank You !! In short difference would be, early binding vs late binding. – hackwithharsha Sep 12 '19 at 18:41
If you're interested, I have [Excel_Automation_with_Python](https://github.com/trenton3983/Excel_Automation_with_Python) with an example generating pivot tables. – Trenton McKinney Sep 12 '19 at 18:44
Thank You Trenton, I will check... – hackwithharsha Sep 12 '19 at 18:45

Using openpyxl to get column width from Excel

1 Answers1

Given:

Code:

Output: