3

I am trying to format certain columns in a date format.

I am able to successfully change an individual cell's format using:

date_style = Style(number_format="M/D/YYYY")
ws['E7'].style = date_style

but is there an easier way to apply a blanket formatting to a column aside from the header?

I noticed some code on the openpyxl website that is supposed to do this, but it didn't seem to work. It is below:

col = ws.column_dimensions['E']
col.number_format = "M/D/YYYY"

and I assume if it did work correctly, it would apply to the header as well.

EDIT: I tried the following code to format the cells: 

ws.cell(row=1,column=5).style=date_style

Which works the same as the ws['E7'] statement above but is in a format that should allow me to run a loop on it. But when I execute the following:

for i in ws.rows[1:]:
    ws.cell(row=i,column=5).style = date_style

It returns the error: unorderable types: tuple() < int()

Dennis
  • 111
  • 2
  • 2
  • 8
  • 1
    You must format all cells individually. The documentation explains that formatting column and row dimension objects has no effect on individual cells. – Charlie Clark May 12 '16 at 17:46
  • The passage Charlie is referring to is this: "Styles can also applied to columns and rows but note that this applies only to cells created (in Excel) after the file is closed. If you want to apply styles to entire rows and columns then you must apply the style to each cell yourself. This is a restriction of the file format" (found in the section on [applying styles](https://openpyxl.readthedocs.io/en/default/styles.html#applying-styles)). However, the last bit isn't quite correct. It's not a limitation of the file format so much as a limitation of openpyxl. XlsxWriter can do it. – John Y May 13 '16 at 15:04
  • @CharlieClark I did not find a link to applying styles in doc – FabioSpaghetti Mar 06 '19 at 07:29

1 Answers1

5

I figured it out, although probably not the most elegant way:

date_style = Style(number_format="M/D/YYYY")
for col in range(5,8,2):
    for row in range(2,100,1):
        ws.cell(row=row,column=col).style = date_style

I wanted columns 5 and 7 to have the date formatting. This did the trick, but of course if I have more than 100 rows, I'll have to raise that number.

Dennis
  • 111
  • 2
  • 2
  • 8
  • 1
    If you specifically want columns 5 and 7, it is clearer to say `for col in (5, 7):` or `for col in {5, 7}:` rather than using a range. Also, it's customary to omit the "step" parameter if it's going to be 1. – John Y May 13 '16 at 14:54
  • Note that XlsxWriter (different package to write Excel files) can do what you are describing. It lets you set the default styling for a whole column, and then as long as you don't *override* that format when you write your individual cells, the column default styling holds. (It also lets you set the styling for a whole row at a time, so you can do your headings that way. Row default styling trumps column default styling, so this is perfect for your situation.) – John Y May 13 '16 at 15:14
  • Thank you for this Dennis - I know this is old but if someone else sees this...use max_row - for row in range(2, ws.max_row + 1) for the 2nd part...again ty - this was helpful :) – beginAgain Dec 18 '18 at 20:52