Inconsistency (?) between xlrd and Excel borders

[Andrea Gavana]

Hi All,

    I am attaching a insanely simple Excel file (with 2 values in it) and a Python script that you may use to reproduce what I see.

Basically, the 2 cells contain the values "Hello" and "World", respectively, and they are one on top of the other ("Hello"=B2, "World"=B3). In Excel, I right-clicked the "World" cell and set the TOP border as thick, automatic. If now I right-click on the "Hello" cell above and look at the cell formatting, Excel tells me that the BOTTOM border of this cell has the same formatting as the TOP one for the cell below. I would say this is obvious as the two cells share the same horizontal border.

However, when I run the Python script attached, this is the output I get (row, column, text, line style property, line style):

E:\MyProjects\temp>sample.py

1 1 Hello left_line_style      0

1 1 Hello right_line_style     0

1 1 Hello top_line_style       0

1 1 Hello bottom_line_style    0  <=== ????

2 1 World left_line_style      0

2 1 World right_line_style     0

2 1 World top_line_style       5  <=== OK

2 1 World bottom_line_style    0

What could be the reason of this difference? Doesn't xlrd recognize that the two cells have the same line style in common or is there a more profound (i.e., related to Excel...) reason?

Thank you in advance for your help.

Andrea.

"Imagination Is The Only Weapon In The War Against Reality."
http://xoomer.alice.it/infinity77/

[John Machin]

xlrd is showing exactly what Excel put into the file. Excel is showing you the EFFECT of having B3 top set to Thick and B2 bottom set to None.

See if you can find some documentation on the web about how the winner is determined when two non-None sides conflict. If that fails, write an xlwt script to create a file with the nC2 combinations, eyeball the result, and fill in the answers. I'll add it to one or both of the xlrd / xlwt distributions.

[Ken Starks]

Hi Andrea,

AS John has said, this is an Excel thing not a python one. Excel indeed does not
realise that there is a common borderl it thinks there are two borders with one on
top.

I enclose another Excel file for you to play with. I have added a __named__ style
called RedBorder for you to play with.

--
You received this message because you are subscribed to the Google Groups "python-excel" group.
To post to this group, send an email to python...@googlegroups.com.
To unsubscribe from this group, send email to python-excel...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/python-excel?hl=en-GB.

[Andrea Gavana]

Hi John,

On 7 August 2011 15:41, John Machin wrote:

xlrd is showing exactly what Excel put into the file. 

I don't have any doubt about that, I just wanted to understand why there was that difference.

 

See if you can find some documentation on the web about how the winner is determined when two non-None sides conflict. If that fails, write an xlwt script to create a file with the nC2 combinations, eyeball the result, and fill in the answers. I'll add it to one or both of the xlrd / xlwt distributions.

The winner is determined by the last user's choice, no question about that. If I decide to make B3 top thick then B2 bottom will just adapt itself. This is the very meaning of "shared" borders. 

I meant no disrespect or criticism, my questions were just for my own understanding, I'll dig down into the xlrd source code to see if anything can be done about it. However, the general atmosphere in the Python-Excel group just makes me think that whatever I will come up with will just be not good enough.

Andrea.

"Imagination Is The Only Weapon In The War Against Reality."
http://xoomer.alice.it/infinity77/

==> Never *EVER* use RemovalGroup for your house removal. You'll regret it forever.
http://thedoomedcity.blogspot.com/2010/03/removal-group-nightmare.html <==

[John Yeung]

On Mon, Aug 8, 2011 at 6:11 PM, Andrea Gavana <andrea...@gmail.com> wrote:
>
> On 7 August 2011 15:41, John Machin wrote:
>>
>> xlrd is showing exactly what Excel put into the file.
>
> I don't have any doubt about that, I just wanted to
> understand why there was that difference.

What difference are you referring to? Excel wrote some border values
into the file; xlrd read them out exactly. You acknowledge this, so
where's the difference?

>> See if you can find some documentation on the web
>> about how the winner is determined when two non-None
>> sides conflict. If that fails, write an xlwt script to create a
>> file with the nC2 combinations, eyeball the result, and
>> fill in the answers. I'll add it to one or both of the xlrd / xlwt
>> distributions.
>
> The winner is determined by the last user's choice, no
> question about that.

Well, that doesn't really have much meaning in terms of the file.
Excel, upon loading a file, cannot tell what the "last user's choice"
was. What it can do is just read what's in the file. So what John is
asking you is: If the *file* contains conflicting border values, what
does *Excel* show?

> I meant no disrespect or criticism, my questions were just for
> my own understanding, I'll dig down into the xlrd source code
> to see if anything can be done about it. However, the general
> atmosphere in the Python-Excel group just makes me think
> that whatever I will come up with will just be not good enough.

What do you mean by "done about it"? It would be wrong for xlrd to
report values other than what's there (in files written by Excel
itself), so I am not sure what more can be done besides documenting
what happens in potentially contentious scenarios, which is what John
seems to be offering to do.

As far as the atmosphere here, I admit it can seem a bit on the tough
side, but try not to take anything personally. Most of the people
here are (1) busy and (2) have a matter-of-fact style of
communication. It's not meant to be unhelpful or uncooperative or
disparaging. It's just usually not very coddling.

John Y.

[Andrea Gavana]

Hi John,

On 9 August 2011 07:46, John Yeung wrote:

On Mon, Aug 8, 2011 at 6:11 PM, Andrea Gavana <andrea...@gmail.com> wrote:
>
> On 7 August 2011 15:41, John Machin wrote:
>>
>> xlrd is showing exactly what Excel put into the file.
>
> I don't have any doubt about that, I just wanted to
> understand why there was that difference.

What difference are you referring to?  Excel wrote some border values
into the file; xlrd read them out exactly.  You acknowledge this, so
where's the difference?

>> See if you can find some documentation on the web
>> about how the winner is determined when two non-None
>> sides conflict. If that fails, write an xlwt script to create a
>> file with the nC2 combinations, eyeball the result, and
>> fill in the answers. I'll add it to one or both of the xlrd / xlwt
>> distributions.
>
> The winner is determined by the last user's choice, no
> question about that.

Well, that doesn't really have much meaning in terms of the file.
Excel, upon loading a file, cannot tell what the "last user's choice"
was.  What it can do is just read what's in the file.  So what John is
asking you is:  If the *file* contains conflicting border values, what
does *Excel* show?

I guess I wasn't very clear in my explanation. Let's assume you are working with Excel and xlrd, and you do the following:

1) In Excel, set the top border of cell B3 to thick red. If you read the file with xlrd, B3 will have a top border thick red and B2 will have a bottom border = None;

2) In Excel, remove all the borders and now set the bottom border of B2 as thick red. If you read the file with xlrd, B3 will have a top border = None and B2 will have a bottom border thick red.

This is my (possibly mistaken) understanding of how xlrd reads the data in the file. Now, the original question was:

"See if you can find some documentation on the web about how the winner is determined when two non-None sides conflict. "

My answer to this question is that this situation is impossible whatever implementation you adopt in xlrd:

1) The *current* implementation reports B3 with a top border thick red and B2 with a bottom border = None;

2) An imaginary alternative implementation will simply look at the status of a shared border between two cells. If both borders are None, do nothing. If one of them is a custom border, set both cells to have the same style for this shared border (i.e., B3 with a top border thick red and B2 with a bottom border thick red).

There is no possible conflict between shared border styles, because either they are explicitly separated (as it is in the current implementation, the border is set for one cell and for the other is set to None), or they are forced to be exactly the same (as in this imaginary alternative implementation).

[John Yeung]

Dredging up this old thread because I'm now working a bit more
extensively with borders:

On Tue, Aug 9, 2011 at 3:37 AM, Andrea Gavana <andrea...@gmail.com> wrote:

> Now, the original question [from John Machin] was:

>
> "See if you can find some documentation on the web
> about how the winner is determined when two non-None
> sides conflict. "
>
> My answer to this question is that this situation is
> impossible whatever implementation you adopt in xlrd:
>

> There is no possible conflict between shared border
> styles, because either they are explicitly separated (as
> it is in the current implementation, the border is set for
> one cell and for the other is set to None), or they are
> forced to be exactly the same (as in this imaginary
> alternative implementation).

I guess we all gave up trying to make Andrea understand that Excel
files can be generated by a number of different programs, not limited
to Excel, and not even limited to platforms on which Excel can run;
and that there is a huge difference between what is actually stored in
an Excel file (which xlrd reads faithfully and without prejudice) and
what the Excel program presents to the user.

I was actually going to take a stab at another explanation, but then
decided it would be better if I simply tried to do some testing myself
with xlwt. And I've only now gotten around to it.

Attached is a simple exploratory program and the Excel file it
generates on my PC. It's by no means exhaustive, but what I gather
from it are

1. Border thickness is the first tiebreaker in conflicts. Thicker
wins. Dotted is thinner than thin. Whichever side has the thicker
border gets all its border attributes displayed by Excel (I'm using
2010 on Windows XP).

2. Colors seem to have a fixed pecking order. I only tried a few:
black > blue > red.

Again, far from exhaustive, but enough for my purposes. If I later
find cases that go against these behavior patterns, or if I do more
complete testing, I'll share my results then.

John Y.