zdavatz/spreadsheet — pull request #6 by uraki66 (2012-06-26 04:17:10+00:00)
Title: Fix Format borders

These commits correct the Format borders implementation and correct the issue with Excel choking on a .xls written by Spreadsheet when borders are specified.

You can now write an .xls with borders and/or border_colors and the resulting file works in both Excel and Open/LibreOffice.

Borders were previously implemented as booleans which is incorrect...the are line-sytles (bit values).

The full list of border styles are now available:

* :none, :thin, :medium, :dashed, :dotted, :thick,
* :double, :hair, :medium_dashed, :thin_dash_dotted,
* :medium_dash_dotted, :thin_dash_dot_dotted,
* :medium_dash_dot_dotted, :slanted_medium_dash_dotted

So something like:

x = Spreadsheet::Format.new
x.border = :thick
x.border_color = :green

Sets the cell border to a thick, green border

The issue with Excel crashing if you set a border wasn't related to the border style, but rather the border color....I'm not sure of the exact issue, but if the border_color was one of the :builtin_, then Excel had issues with the resulting .xls. Changing the default color to :black fixed this....I suspect it's a result of builtin formats/indexes vs "user" formats when the binary .xls is written (the 'xf' records), but it's beyond my feeble skills to figure out what is happening. I did save and dump a few Excel workbooks in .xml format (to get an idea of what was being saved in the real .xls) and I really think the default/builtin formats aren't being written correctly and/or indexed for formats that have :builtin_ colors in them.

The reader was also fixed to properly pull in the border styles & border colors...however, if the reader pulls in a border color of :builtin_, it will end up saving it with that color and most likely create an .xls that will crash Excel and not allow right-click "Format Cells".

Further work of dumping & tracing the layout of Excel sheets needs to be done to ultimately correct the issue/mystery of :builtin_black, red, green, etc and how the system xf records relate to them.

I added some basic tests for the Format.border & Format.border_colors.