Clearing excel formula cache when using jruby-poi - making Formulas work right

Last week I've been working with jruby-poi to make an script that would automatically update an MS Excel file.
The part that cost me the most time, believe it or not, was formula updating, you see, I would add rows that should change the value of pre-set  formulae, but the results would not update on Excel until you clicked on the formula.
After diving in the nearly nonexistent documentation of the gem (they give you base-usage on git, some test case you can check out also on git, everything else is default generated rdocs without a single improvement) I've came up with the following work around:


       %w[jobs defects].each do |sheet_name|
            sheet    = self.workbook.worksheets[ sheet_name ]
            rows     = sheet.rows
            from, to = 2, 70
            rows.each_with_index do |row, i|
                next if i <= from
                #order is important!
                %w[L K D E F N O P Q U V X Y Z W AA AB AC AD AE AF AG AH AI AJ].each do |letter|
                    column = letter.to_number - 1
                    puts sprintf( 'updating formula on C: %s row %d', letter, i )
                    self.workbook.on_formula_update rows[ i ][ column ]
                end
                break if i >= to
            end
end

I hope it helps anyone, also I've opened an issue on their git, let's see how they respond.

Codebits Talk Approved

After being accepted earlier to Codebits 2012, my talk was also accepted.
Now I gotta pretend I'm an expert in some stuff.

Turning letters into numbers in Ruby (Excel style)

Today I was writing some code to update an Excel file with more recent data from various sources. I was using JRuby with jruby-poi, it provides a nice API and so far it's the only gem I found that can open and edit an xlsx file without breaking it.

I had an interface problem however, I was trying to provide the people that will use my code an easy interface, so I needed them to be able to pass me [excel's] columns as letters (A, B ,C...), but for editing with poi the interface is the object Rows, an Enumerable with numbered indexes like an Array, that is, I can't do rows['A'][1], I must do rows[0][0].

Googled around for a few ideas and I came up with this (it works up to column AZ as far as I know)

class String
    def to_number
        number = self.tr("A-Z", "1-9a-q").to_i(27)
        number -= 1 if number > 27
        return number
    end
end

It works fine with strings that are just an capital letter, I didn't do a sanity check because you might wanted convert a bunch of letters in the same string, also because your column might be something like 'AA' or 'AB'. If you were looking for the simple answer you can stop reading here.

I'm gonna math-nerdout here a bit to explain what is happening:

the method "tr" here will replace everything from "A" to "I" with the numbers from 1 to 9, everything else will be replaced with the letters from "a" to "q" which are the numbers and letters used in the base 27 numeric representation.

For those of you unfamiliar with numeric which aren't base 10, they are represented as such:

0 to 9 stays the same, but everything after that are letters from English alphabet.

So 10 in base 27 is 'a', 26 is 'q' (the last letter used by base 27) and 27 is '10'.

(Don't ask me how are numbers represented in any base greater than 36 (where 35 is 'z'), Ruby won't accept any greater base anyway.)

The argument 27 in the "to_i" call is to tell ruby that the string is a number in base 27 for when it does the conversion to Fixnum (you can also "to_s 27 "if you want to convert a number to base 27 ).

Let's say your string was 'AA', the tr method will turn it into '11', this is not "eleven", in base 27 this is 28*. But the result we wanted is 27, not 28 (AA is column number 27), thus the "-1 if number > 27".

I could explore this solution to come up with some better math to make sure this method worked correctly even if you're looking for column 'ZZZZZ', but I don't need it, as this was a very small problem from the big problem I was facing today.

*Trying to explain this in layman's terms (since I myself am a layman) 

Normally we represent numbers in base 10, this means that when we get to the number "ten" it kinda loops the representation and goes back to zero preceded by a 1, this one indicates how many "full loops" we've done to the base so we represent the number ten like so: "10".

However, if we increase the base , we need more symbols, so at base 20 when we reach "ten" we can't represent as "10" (that would be twenty) because we haven't looped it yet, so we just borrow letters till we have enough symbols to represent all numbers in one loop of the base.

In the case of base 20,  we represent one as "1"; nine as "9", ten as "a", nineteen as "j", twenty as "10".

Uploading attachments to Jira using Jira4r

Today I found myself needing to make a ruby script that would update a file and upload it to an issue as an attachment in Jira.

I found the SOAP API method to do this in Jira's documentation, but I didn't find a suitable mapping in the Jira4r docs.

I found this comment on the Jira4r page, the only  reference to the API's addBase64EncodedAttachmentsToIssue method. Also found some forum posts of people with the same issue and that comment was the only found solution.

So if you need to upload stuff to Jira, follow the instructions in that comment, you'll find the file to edit at "[GEM_PATH]/gems/jira4r-0.3.0/lib/jira4r/v2/jira_soap_service_driver.rb"

The end result I got in my file for it to work was this, worked for my task.

The usage is quite simple, the test example on the page linked shows well how to use it, I'm just going to paste here to keep it "in record".
The only difference between what I did and what's on the example is that I used the base64 standard lib for encoding while he uses Array#pack('m') which results on the same thing.

#!/usr/bin/env ruby

$LOAD_PATH << './lib'

gem('soap4r')

gem('jira4r')

require 'jira4r'

require 'base64'

require 'cgi'

jiraBaseUrl      = "http://localhost:8080"

jiraUsername     = "automated-user"

jiraPassword     = "Thbmahws,"

escapedPassword  = CGI::escape(jiraPassword)

jira = Jira4R::JiraTool.new(2, jiraBaseUrl)

jira.login(jiraUsername, jiraPassword)

newFilepath = "H:\\edisc\\Tst\\Printed\\Test.eml"
fileName = newFilepath.split('\').last

issue = Jira4R::V2::RemoteIssue.new

issue.project = "ED"

issue.type = "1"

issue.summary = "This is a test"

issue.description = newFilepath

returnedIssue = jira.createIssue(issue)



filedata = File.open(newFilename, "rb") { |f| f.read }

attachmentData = filedata.pack("m")

jira.addBase64EncodedAttachmentsToIssue(returnedIssue.key.to_s, fileName, attachmentData)

Codebits 2012

The most recent batch of acceptance to Sapo Codebits 2012 has been released, gladly I'm in that batch; unless something bad happens I'll be in Lisbon in November.

Now all they need to do is approve my talk...

Terminal Keyboard shortcut on Fedora 17

Recently I've installed Fedora 17 on a machine to do some learning Devel, easy enough.

Small problem though, what's the keyboard shortcut for opening the terminal window?

Well, there's isn't, because they thought it was fun to remove it since you can add it yourself you lazy bum, god forbid a user friendly Linux distribution that's not  Ubuntu.

Anyway, I looked around the keyboard settings and found where I can add the shortcut, but I had a hard time figuring out how to fill it for my intended purpose. Amazingly Google was no help on this.

So this is how you do it:

Settings -> Keyboard -> Shortcuts -> Custom Shortcuts -> +

On name just put whatever you want, on command type "gnome-terminal".

Here's what it should look like.

Edit: If you're using KDE you should type "konsole". To get there, go to System Settings -> Custom Shortcuts; click on the Edit -> New -> Global Shortcut -> URL/Command.

Also, to take screenshots on KDE you need KSnapShot.

Ruby - Spying on instance variables

This week I've been working on some code to be executed on cucumber's hooks, due to the lack of useful (or easy to read) documentation I found myself using a debugger to find out what info I could get from the object passed to the "After" block.

Deconstructing the object allowed me to see it had some instance variables with info I needed, but I could not retrieve it in any way, what a bummer, "it's right there!" I thought, if the debugger can get it so can I.

Poking the object a little I found "instance_variable_get" method (which I hadn't used before nor ever paid attention to it), since there's no real private variables in Ruby, you can get any instance variable despite if there's a method for it or not.

Translating this new acquired knowledge into simple examples:

Simple instance variable spying

irb(main):001:0> class A irb(main):002:1> def initialize var irb(main):003:2> @var = var irb(main):004:2> end irb(main):005:1> end => nil irb(main):006:0> a = A.new 20 => #<A:0x56ee20fe @var=20> irb(main):007:0> a.var NoMethodError: undefined method `var' for #<A:0x56ee20fe @var=20> [...] irb(main):008:0> irb(main):009:0* irb(main):010:0* irb(main):011:0* var.instance_variable_get(:@var)

=> 20

This could have easily been avoided using "attr :var" on class declaration, and it's the result of bad encapsulation.

If class A is yours, then go change it so you can easily access vars you need, if it's not (comes from a gem, maybe) you can still change it, because ruby classes are always open (which is awesome), so just add "class A; attr :var;end" before instancing it and you're good to go.

Now, when you don't have control over the class nor when it's instantiated (like on cucumber's hooks) you can still use what's described above, but have the option of using instance eval to define a new method to access the variable you want, although I hear it's slower I didn't bother with benchmarks for this case.

Spying with instance_eval

irb(main):001:0> class A irb(main):002:1> def initialize var irb(main):003:2> @var = var irb(main):004:2> end irb(main):005:1> end => nil irb(main):006:0> a = A.new 20 => #<A:0x63fb050c @var=20> irb(main):007:0> a.instance_eval do @var; end => 20 irb(main):008:0> #or, if you need to do it often.... irb(main):009:0* a.instance_eval do def var; @var;end; end => nil irb(main):010:0> a.var => 20

Instance eval evaluates your block in the context of the instance, so you can run code on it like if it were on the inside, you even have access to "self".

Rubygem try_again

Earlier this year when I started working at PT Inovação, my job then (and now) was to take in specification written in gherkin and make the corresponding Ruby code that Cucumber would execute.

The applications we were  testing are really big, they're spread over many machines and use several databases.

One part of the application will create an entry on a database, later this has to be synced to another database, until this sync happen another part of the program might not work as indented.

Similarly, some parts of the application would add actions to a queue, this queue would later be processed and an entry to a database would be added, but anytime from zero seconds to one minute  was acceptable in terms of delay.

So, one of the problems my team was facing was some random failures, some times it was due to this difference in synchronization or delays, other times times was something simpler like a hiccup on the network that caused an http request to hang.

Trying to minimize the damage it caused to the global status of our tests on Jenkins I've came up with a little piece of code I've called Try Again and since then it has been widely used.

Unfortunately I don't have testes written for it, as it was one of the first pieces Ruby I ever did, but you can get as a nice little gem here. It was fun to code it, I had to understand how to pass blocks into methods and how to begin, raise, rescue all work together. It made me see how much I'd like Ruby!

An example with output set to stdout so we can see what's happening:

awls99@artworking:~$ jirb
irb(main):001:0> require 'rubygems' #ruby 1.8
=> true
irb(main):002:0>
irb(main):003:0* require 'try_again'
=> true
irb(main):004:0>
irb(main):005:0* class TooLow < StandardError;end
=> nil
irb(main):006:0>
irb(main):007:0* i = 0
=> 0
irb(main):008:0> TryAgain.retry(:error => TooLow, :output => $stdout) do
irb(main):009:1* puts i
irb(main):010:1> i += 1
irb(main):011:1> raise TooLow if i < 2
irb(main):012:1> end
0
TooLow for the 1# attempt
1
TooLow took 2 attempts to pass
=> true

Returns true because it succeeded. Wasn't it fun?

Between the first line and second line of output there's a 3 seconds delay.

You probably noticed I'm passing some options to the method, I've made it a bit customizable to fit many uses, you can read all the details on the readme.

I honestly don't expect this gem to get much use, but I'm sure somewhere someone will have a similar need and will install it, hopefully that person will find this before recreating it's own.

Ruby 1.9 barewords - wat?

Recently I've seen this screencast by Destroy All Software entitled "Wat", really fun talk I'd recommend to watch,  despite the LONG loading time of the site.... maybe you should go there, click play, click pause for it to start loading, come back, read the rest of this post, grab yourself some coffee and watch the screencast.

Like any normal person geek I went to try this out myself

awls99@artworking:~$ jirb
irb(main):001:0> def method_missing *args; args.join ' '; end
=> nil
irb(main):002:0> Hello ruby bare words
SystemStackError: stack level too deep
from org/jruby/RubyKernel.java:1093:in `eval'
from org/jruby/RubyKernel.java:1419:in `loop'
from org/jruby/RubyKernel.java:1205:in `catch'
from org/jruby/RubyKernel.java:1205:in `catch'
from /home/awls99/jruby-1.6.4/bin/jirb:13:in `(root)'

It didn't work! How disappointing. But then I remembered that I was running jirb for Ruby 1.9 and that screencast has some time, so maybe it only works on Ruby 1.8, so after setting my jirb to 1.8 mode the above code sample simply worked.

That's neat and all, but why won't this work on 1.9? This sample, is one of those things that you should not use but the fact that it works simply shows how awesome Ruby is.

I've tried a few variations of the code, since this is a stack too deep issue, it seems that this method is calling itself ad infinitum, but I can't put my finger on why he's doing it.

I decided that I'd find a way to make this work on Ruby 1.9, it should be simple! Well, I found out how, it's a bit longer, so I've separated them:

awls99@artworking:~$ jirb
irb(main):001:0> def method_missing m, *args
irb(main):002:1> [m.to_s, args].flatten.join ' '
irb(main):003:1> end
=> nil
irb(main):004:0> ruby onenine has barewords too
=> "ruby onenine has barewords too"

Now this got me wondering, "why do I need to go over this extra loop for 1.9?"

For those of you unfamiliar with method_missing, it goes like this:

the first argument is the method (that is missing) as a symbol, the second an array with all arguments passed, the third a block, thus you usually see this:

def method_missing method, *args, &block

[code]

end

If you suppress the first argument it will be included in the args array as the first element, because that's the way the splat (*) operator works and it's awesome!

So it occurred to me that the reason it's going stack too deep is because when Ruby is trying to cast the method as a string (for the join) it's using a method missing and since we re-wrote the Object::method missing it ends up looping on itself. It makes sense.

To prove my theory I've fired up two jirbs, one with Ruby 1.8 the other 1.9

Ruby 1.8:

awls99@artworking:~$ jirb
irb(main):001:0> [:method].join ' '
=> "method"
irb(main):002:0> def method_missing m
irb(main):003:1> 1
irb(main):004:1> end
=> nil
irb(main):005:0> [:method].join ' '
=> "method"

As can be see clearly here line 005 didn't jump inside the method missing declared on line 002.

Ruby 1.9

irb(main):001:0> [:method].join ' '
=> "method"
irb(main):002:0> def method_missing m
irb(main):003:1> 1
irb(main):004:1> end
=> nil
irb(main):005:0> [:method].join ' '
TypeError: Symbol#to_str should return String
from org/jruby/RubyArray.java:1866:in `join'
from (irb):5:in `evaluate'
from org/jruby/RubyKernel.java:1093:in `eval'
from org/jruby/RubyKernel.java:1419:in `loop'
from org/jruby/RubyKernel.java:1205:in `catch'
from org/jruby/RubyKernel.java:1205:in `catch'
from /home/awls99/jruby-1.6.4/bin/jirb:13:in `(root)'

Here however, it did, as I predicted, Ruby 1.9 casts symbols to string using a method missing, that's why the original "wat barewords" method didn't work, however, I bet that if I enclose it inside a class it will work.

awls99@artworking:~$ jirb
irb(main):001:0> class A
irb(main):002:1> def method_missing *a;a.join ' ';end
irb(main):003:1> def bare; barewords inside a klass;end
irb(main):004:1> end
=> nil
irb(main):005:0> A.new.bare
=> "barewords inside a klass"

SCIENCE!