Search code examples
pythonexcelpywin32

Pywin32 Excel Formatting

I want to write to an Excel sheet via pywin32. I can do it actually without problem. But I couldnt format a range of cells in sheet. I want to align the values centerly inside cells. And also i need to fill the cells with color. How can I do it?

Thanks in advance.

Solution

  • I've not specifically done this using python before, but I'm assuming you're using the COM automation interface to excel.

    This page has an example that seems to cover both alignment and filling cells with colour in C#, so it should be fairly easy to adapt to python. Assuming you have a Worksheet object called sheet, and the Excel automation object is called Excel, I'm guessing it might look a bit like this:

    //Format A1:D1 as center alignment, 
sheet.Range("A1", "D1").VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
sheet.Range("A1", "D1").HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
sheet.Range("A1", "D1").Interior.ColorIndex = Excel.XlColorIndex.Red

    If you don't have access to the Excel.XlAlign and XlColorIndex constants from python then you can just replace them with the specific integers they represent, though I'm not entirey sure where you could get them from. Probably from a VBA Reference Site or similar. (Though that link I provided doesn't seem to allow you to expand each of the entries in the list, so you may need to look elsewhere)

    EDIT: Just had a play about with excel automation via the python console, and it seems to work alright:

    >>> from win32com.client import Dispatch
>>> xlApp = Dispatch("Excel.Application")
>>> xlWb = xlApp.Workbooks.Add()
>>> xlSht = xlWb.WorkSheets(1)
>>> xlSht.Range("A1", "D1").VerticalAlignment = 1
>>> xlSht.Range("A1", "D1").Interior.ColorIndex = 6
>>> # The background color of A1-D1 should now be yellow
>>> xlSht.Cells(1, 1).VerticalAlignment = 1

    If you can't find any good reference on what the various alignment/colour constants are, then I'd just play about with python on the console like this, then open the resulting worksheet in excel and have a look at the results to figure things out.

    You can find the official reference for the office 2003 automation API here Specifically, you'll probably find the range documentation most usefull.