Anyway, we thought we'd share our little trick to get page headers using the spreadsheet gem.

So what you see here (above) goes into a config/initializer, something like RAILS_ROOT/config/initializer/enable_headers_in_spreadsheet_gem.rb. Here's what's going on:

1) We know that the write_from_scratch method is called when everything is said and done and the data is ready to be written to the spreadsheet file. So we make use of this and alias that method to write_from_scratch_without_header. Which opens us up to call our write_header method inside our write_from_scratch method which, of course, will call the original write_from_scratch method.

2) Our write_header method makes use of the already existing opcode for Header in the spreadsheet gem. It's not being used, so my guess is the developers intend on solving this issue at some point. We have to send the opcode, the length info and the string we want to write out. This was the trickiest part to figure out.

3) We expose an add_header method that simply takes a string and stores it in the header accessor. This means, to set the header, you simply say sheet.add_header("foo header").

To implement the footer, you'd just do the same thing, create a footer accessor, add a method to update it. Then build the writer_footer method with opcode(:footer) and append write_footer at the end of write_from_scratch.

Well, that took us some time to figure out, so enjoy and let me know if you have any questions.

File error: data may have been lost

Like most microsoft errors, it was useless and the spreadsheet came up just fine. But that error was just so annoying, other spreadsheet applications (open office, excel on mac) opened without any problems. So after quite a bit of hacking and digging around, I finally tried setting the encoding, which defaults to UTF-8. Well it just so happens that the spreadsheet being modified was encoded with UTF-16LE.

So part one of my solution became this:

1
2

Spreadsheet.client_encoding = 'UTF-16LE'

Then doing a little more digging I decided that this would be a better long-term solution:

1
2
3

book = Spreadsheet.open spreadsheet_file
Spreadsheet.client_encoding = book.encoding

Well, hopefully it wasn't just me and someone will be able to save a bit of time with this.