Automating Excel with Ruby: One Cell, Multiple Fonts

As a loyal reader mentions in the comments to my article about Formatting Worksheets, he wanted to apply different formatting to separate words in a single cell. Specifically, he had cells containing a surname, a comma, and then first name, such as:

Jeter, Derek
Matsui, Hideki
Teixeira, Mark

His goal was to format the last name in bold, while leaving the first name in normal weight font:

Jeter, Derek
Matsui, Hideki
Teixeira, Mark

(BTW, the sample names are mine. How many days until Opening Day? Or at least until pitchers and catchers report? But, alas, I digress...)

My suggested solution was to use the Characters object to get a reference to a specific subset of characters within the cell, rather than to all of the cell's text.

Call the Characters(start, length) method on a Range object (usually a cell) to return a reference to the specific characters. Note that the Characters object's index is 1-based.

Once you have your subset of characters you can apply Font formatting much like you would with a Cell object, setting the Font object's Name, Size, Bold, ColorIndex, and other properties.

So, assuming you have a worksheet object, ws, and you want to format the values in cells A1 through A3 as described above, try the following code:

ws.Range('A1:A3').Cells.each do |cell|
    # find the index of the first comma:
    comma = cell.Value.to_s.index(',')
    # get the range of characters before the comma
    lastname = cell.Characters(1, comma)
    # get the range of characters after the comma
    firstname = cell.Characters(comma + 2, cell.Value.to_s.size)
    # apply formatting:
    lastname.Font.Bold = true
    firstname.Font.Bold = false
end

There you have it. Let me know if you have questions or comments.

Thanks for stopping by!

Amen, Brother

In an article entitled "The Best Environment for Rails on Windows", Fabio Akita writes:

The same goes for Windows: some Railers would say that you need to give up on Windows completely and simply go to Ubuntu or Mac OS X. I know for a fact that there are lots of people simply unable to do just that. And “give up their jobs” – as some kindly suggest – is not an option. Some of the people that are starting Rails in closed-minded companies are exactly the seeds those companies need to start to change their minds and every time we, Railers, tell them to just move to another job, we are killing a precious opportunity to introduce Rails to those companies.

In a followup comment to a post on Rails Inside, Fabio adds:

Please keep this in mind: there are several circumstances that keep people locked to Windows. Specially if this person works for a closed minded company that won't allow him to use Ubuntu. The instant you tell this person "give up, Ruby is unable to be used under Windows", you lose the single person that could actually bootstrap Ruby adoption inside this company. People have to start somewhere and sometimes Windows is this way. So let's help them get started and one of three things will happen: 1) he will be super happy and will evangelize Ruby to his peers, leading to mass adoption on this company; 2) he will lose hope on his employer and will try to find another job; 3) he will simply give up and none of us lose nothing.

You should read Fabio's article and the comments at both links above; even if, like me, you don't use Rails as much as you use Ruby itself.

It's very easy (and very fashionable -- all the cool kids do it!) for the Mac OS and Linux crowds to take shots at the Windows developers. And I tip my hat to those that actually changed jobs just so that they could avoid working with Windows. Good for you.

But some of us actually like our jobs and our companies, despite having to work with -- Gasp! Cover the children's ears, Mother! -- Windows. Besides, as Marcos Ricardo commented on Fabio's site, "We can never throw way 90% of market share."