Use of borders causes problems in Excel

[Uraki]

I'm working on a Ruby script to embed formatting commands in a .csv file so it can be turned into a new formatted .xls file.

I've accomplished this and it works well....except for cell formatting with borders.

 

I can do any custom formatting of cells that I want and have no issues/errors with Windows 7 & XP & Excel 2003/2007/2010 (and Linux & LibreOffice Calc 3.5.3.2) , but if I add borders to a Spreadsheet::Format, you can no longer bring up the right-click "Format Cells" menu (nothing happens) for ANY cell that I created with 'spreadsheet' (borders or not) in any version of Excel! 

Additionally, any spreadsheet I create with borders on even 1 cell will eventually cause Excel to crash if you start trying to bring up the "Format Cells" menu and/or copy or otherwise start using the spreadsheet!

LibreOffice does NOT suffer from this problem and appears to have no issues with "Format Cells" menu using the generated .xls, borders or no.

I'm using the 0.7.2 version and I've been using 1.8.7-p358 & 1.9.3-p194 on both Linux (Ubuntu 12.04 x64) and Windows 7 x64. The issue persists no matter the combination used to create or open the .xls.

Everything appears to be fine until a border is turned on one or more cells and then Excel will have issues and quickly crash if you persist in trying to use the spreadsheet. I'm getting no useful info from Excel or Windows.

I'm putting this out here to ask if anyone else has tried to use borders in cell formatting and whether or not they have had issues.  Note that the sheet will open and is viewable in Excel so if you just created a sheet with cell borders and opened it to look at it, it would be fine and you'd never know you had a problem unless you tried to use the spreadsheet

I'm creating a Spreadsheet::Format and then updating it with parameters supplied and then using a statement like 'sheet1.row(cnt).set_format 0,format' so I don't believe I'm doing anything incorrect and when I use pry to examine the objects, I see things being updated as I expect. 

I suspect there is something buggy with the border in a Format, but it's getting a bit beyond me to chase it down.

Any ideas or experiences with using borders in a Format?

Thanks for the great library, we use it all them.....but the boss wanted me to go 'fancy' and you see where it lead me....

Robert

[Zeno Davatz]

Dear Robert

;) try to be less fancy.

If I understand correctly, then all works fine with LibreOffice, correct?

Think of the 80:20 rule...

Best

Zeno

[Uraki]

Yep, "fancy" will get you killed sometimes.

LibreOffice is fine with it. In fact, if you open the generated .xls in LibreOffice (with cell borders) make a trivial change (put a space in a cell) and re-save it, it "heals" the .xls and now Excel can open it and use "Format Cell" right-click menu and move/add/copy without crashing Excel.

I'm pretty sure I can talk the boss out of borders. I'm just mentioning this so we can determine if it is a real problem with the gem and if so, find someone smart enough that can dig into it and get it fixed.

Or just remove the 'borders' from Format :)

Robert

On Thursday, June 21, 2012 3:36:53 PM UTC-5, Zeno R.R. Davatz wrote:

[Uraki]

Here's another tidbit:  My co-workers uses a PHP Pear library Spreadsheet_Excel_Writer http://pear.php.net/package/Spreadsheet_Excel_Writer  and we just ran a test where he created a spreadsheet with cells with borders and it works in Excel with no problem.

I think it's based on the same original underlying work. 

One of the PHP bugs & comments about border/setborder causing a corrupted file mention a change of the PHP OLE library. 

The 'ruby-ole' I'm using is this one https://code.google.com/p/ruby-ole/  1.2.11.3

Hmmmm...pretty sure I'm going to remove my 'borders' code & formatting for now. 

[Zeno Davatz]

Dear Robert

On Thu, Jun 21, 2012 at 10:55 PM, Uraki <ura...@gmail.com> wrote:
> Yep, "fancy" will get you killed sometimes.
>
> LibreOffice is fine with it. In fact, if you open the generated .xls in
> LibreOffice (with cell borders) make a trivial change (put a space in a
> cell) and re-save it, it "heals" the .xls and now Excel can open it and use
> "Format Cell" right-click menu and move/add/copy without crashing Excel.

Ok, this is the expected behavior! ;) LibreOffice can heal a lot!
LibreOffice still is know more about the MSOffice binary format then
spreadsheet does.

> I'm pretty sure I can talk the boss out of borders. I'm just mentioning this
> so we can determine if it is a real problem with the gem and if so, find
> someone smart enough that can dig into it and get it fixed.

It something in the binary format of XLS, that seems to be very special.

> Or just remove the 'borders' from Format :)

Or keep the format and work with LibreOffice that is free and OpenSource. ;)

Best
Zeno

[Zeno Davatz]

Dear Robert

Thanks for mentioning this! Yes spreadsheet is based on ruby-ole it
seems that Ruby-Ole does not fully, correctly deal with MSOffice
binary format. ;( - so we need to fix Ruby-Ole ourselfs or we need to
file a bug with its maintainer.

Best
Zeno

[Uraki]

I posted a pull request that fixes this issue.

[Zeno Davatz]

Dear Uraki

On Tue, Jun 26, 2012 at 2:59 PM, Uraki <ura...@gmail.com> wrote:
> I posted a pull request that fixes this issue.

Thank you!

The new spreadsheet-0.7.3.gem is out.

Best
Zeno