[python-win32] Formatting parts of an excel cell

[Mark Tolonen]

"Andrea Gavana" <andrea.gavana at gmail.com> wrote in message 
news:d5ff27200904250628g433ae546xb0b683b1f32f42f9 at mail.gmail.com...
> On Sat, Apr 25, 2009 at 2:00 PM, Mark Tolonen wrote:
> >
> > "Baehr, Manuel" <Manuel.Baehr at comsoft.aero> wrote in message
> > news:737A64DEB003704788E5CE515ED146054B6D5A at CSMAIL.comsoft.de...
> >>
> >> Hi John,
> >>
> >>> Check out the thread "Multiple formats in a cell" in this
> >>> group around 2009-03-06.
> >>
> >> thanks for the link! However, I can't use xlwt (and xlrd) because I 
> >> have
> >> to modify an existing excel sheet containing macros. Therefore I used
> >> the "win32com" module in python under Windows. This works pretty fine
> >> except for my original problem.
> >>
> >> Any idea how to do it using win32com? So far I only know how to set
> >> properties of an entire cell.
> >>
> >> cell.Font.ColorIndex = 6
> >>
> >> But as I said, I would like to do it for parts of the text of the cell
> >> content.
> >>
> >> Cheers,
> >> Manuel
> >
> > I recorded an Excel macro to color parts of a cell to find the correct
> > syntax. This should work, but I get an error:
> >
> >>>> xl.ActiveCell.Characters(1,5).ColorIndex
> >
> > Traceback (most recent call last):
> > File "<interactive input>", line 1, in <module>
> > AttributeError: Characters instance has no __call__ method
> >
> > The Excel documentation says Characters is a method that should support
> > Start and Length parameters.
> >
> > Anybody know how to call this correctly?
>
> You may try to use the FlagAsMethod method like this:
>
> from win32com.client.dynamic import DumbDispatch
>
> yourCell.Select()
>
> s = DumbDispatch(self.xlsapp.Selection)
> s._FlagAsMethod('Characters')
> s.Characters(1,5).ColorIndex = whatever

I got an error with that as well, but I figured out the correct way:

import win32com.client
xl = win32com.client.gencache.EnsureDispatch('Excel.Application')
xl.Visible = True
xl.Workbooks.Add()
c = xl.ActiveCell
c.FormulaR1C1 = 'Hello World'
c.GetCharacters(1,5).Font.ColorIndex = 3
c.GetCharacters(7,5).Font.ColorIndex = 4

Characters is a "property that takes parameters", not a method, so it is 
treated differently.

-Mark