formatting cells in excel using python

Question

Hi I'm using python to parse through an excel file and copy only the contents I want to another excel file however, I want the new cells formatted in a different way. Currently I'm using the xlrd and xlwt libraries. I looked at the documentation on their website and it doesn't seem to address it, only font formatting such as bolding and underlining. Specifically I want my new copied cells centered and some of the other cells merged. Any ideas?

Take a look at openpyxl if xlsx format files are ok for your use. I have found it to be much more friendly than xlrd/xlwt. pythonhosted.org/openpyxl and openpyxl.readthedocs.org/en/latest — Dave_750
– Dave_750, Commented Nov 14, 2013 at 22:19

joshua · Accepted Answer · 2013-11-14 21:28:21Z

1

In the comments, Joe points out that formatting is supported by xlrd and xlwt. However, you also can use win32com to drive excel directly.

See: Using Automation Objects from Python

Once you have this working you can do pretty much anything excel can do. Unfortunately there isn't complete documentation. The best bet is to read the excel VB documentation (for the version you have) and guess at the python binding.

If you know the general format, it's easiest to start with an excel file that has all the formatting and formulas predefined.

edited Nov 14, 2013 at 21:28

answered Nov 14, 2013 at 19:55

joshua

2,51717 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

joshua Over a year ago

Oh, good. I'll edit the answer. Do they support formulas and charting as well? If so I may migrate. It would be way better than fighting win32com/excel versions/etc.

jmcnamara Over a year ago

XlsxWriter supports charts.

Shankar Kumar Over a year ago

@joshua: Is there a method in win32com that allows me to set the orientation of an Excel sheet to landscape?

joshua Over a year ago

I haven't tried it, but if you can do it in a macro then you can do it with win32com. Record a macro then look at the generated VB source. Use that as a basis to guess the python.

user2989027 · Accepted Answer · 2013-11-21 20:49:22Z

1

update: I have found the solution by importing easyxf from xlwt

I set the styles by the statement:

style = easyxf('align:horizontal center;')

so when i write to the sheet i can use:

sheet.write(row, col, item_to_write, style)

Documentation from: http://www.simplistix.co.uk/presentations/python-excel.pdf

answered Nov 21, 2013 at 20:49

user2989027

1,4653 gold badges11 silver badges13 bronze badges

Collectives™ on Stack Overflow

formatting cells in excel using python

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related