ANN: XLSGrid For wxPython

Andrea_Gavana · August 8, 2011, 9:06pm

Hi All,

it's been a while since my last "announcement"... Anyway, I am happy to announce the birth of my latest child, XLSGrid.

Source code:

http://xoomer.virgilio.it/infinity77/Zipped/XLSGrid.zip

Screenshots:

(XLSGrid standalone)

http://xoomer.virgilio.it/infinity77/Softwares/XLSGrid_1.png

(XLSGrid against Excel, same file)

http://xoomer.virgilio.it/infinity77/Softwares/XLSGrid_3.png

@Robin and other wxGrid gurus (which group I am not part of, unfortunately), a couple of questions at the end of the message. Any suggestion is more than welcome.

@Warning: XLSGrid requires the xlrd package from:

http://pypi.python.org/pypi/xlrd

@Warning: XLSGrid has been tested on Windows Vista/Windows 7 with wxPython 2.8.11 unicode and Python 2.5, and wxPython 2.9.2.1 unicode and Python 2.7.

So, what is XLSGrid?

XLSGrid is a class based on wx.grid.Grid that can be used to faithfully
reproduce the appearance of a Microsoft Excel spreadsheet (one worksheet

per every instance of XLSGrid).

XLSGrid is a completely owner-drawn control, and it relies on the power of
wx.grid.PyGridTableBase and wx.grid.PyGridCellRenderer to draw the cell
content. For this reasons (and for some others, see the TODOs section),

it will work efficiently only for relatively small Excel files.

NOTE: On Windows, it is strongly recommended to install Mark

Hammonds’ pywin32 package:

http://sourceforge.net/projects/pywin32/

This will allow you to perfectly reproduce the appearance of the Excel

worksheet in your instance of XLSGrid.

WARNING: If Mark Hammonds’ pywin32 package is not available, the

formatting capabilities of XLSGrid are severely limited; for instance,

you won’t probably get the exact WYSIWYG between the Excel spreadsheet

and XLSGrid.

WARNING: XLSGrid can only read Excel .xls files, not the newer versions

.xlsx generated by Office 2007/2010. If you have a .xlsx file, you will
need to save it in 1997-2003 Office compatibility mode.

Currently this class provides a read-only subclass of wx.grid.Grid, with

the following formatting features already implemented:

Cell background: support for any cell background colour and fill pattern
(hatching) in the Excel default set. There currently is no support for

gradient shading inside a cell as xlrd doesn’t report this information.
Cell borders: support for all the border types and colours exposed by

Excel (left, top, bottom, right and diagonal borders, thin, double,

thick, ect… line styles).
Cell text: support for all kind of fonts (except strike-through, but this

is a bug in wxWidgets), and font colours. As a subset of text/font

capabilities, XLSGrid supports the following features found in Excel:
- Horizontal alignment: left, right, centered, left-indented;
- Vertical alignment: left, right, centered;
- Text direction: left-to-right or right-to-left;
- Text-wrapping: wrapping long texts inside a grid cell;
- Shrink-to-fit: text font is reduced until the text can fit in a one-line
  
  inside the grid cell;
- Text rotation: text can be rotated from +90 to -90 degrees.
Cell text appearance: if you are using Mark Hammonds’ pywin32 package,

the text displayed in the XLSGrid cells has exactly the same appearance

as in the Excel spreadsheet.
Cell comments (notes): if you are using Mark Hammonds’ pywin32 package,
cell comments (notes) are extracted and you will see a small red triangle

at the top-right corner of any cell containing a comment. Hovering with

the mouse on that cell will pop-up a “comment-window” displaying the

comment text (the comment window is based on wx.lib.agw.supertooltip).
Cell hyperlinks: starting from version 0.7.2 (SVN), xlrd is capable of

extracting hyperlinks from Excel cells. This will be appropriately displayed
in XLSGrid with a cursor changing and a tooltip on that cell.
Cell merging: merged cells in the Excel spreadsheet will be correctly

handled by XLSGrid.
Columns and rows sizes: XLSGrid calculates the correct rows and columns

sizes based on the Excel reported values in characters. The calculations

are based on the default width of the text in 1/256 of the width of the

zero character, using default Excel font (first FONT record in the Excel

file).

Questions:

I have this piece of code in the XLSGrid implementation (which is currently commented out):
```
 # Commented-out section
```

if self.attr is not None:

self.attr.IncRef()

return self.attr

attr = gridlib.GridCellAttr()

attr.SetRenderer(XLSRenderer(self))

attr.SetSize(*self.size)

attr.SetOverflow(True)

self.attr = attr

return self.attr

I though I could store a wx.grid.GridCellAttr inside a class member, but every time I try to un-comment that section I get this from wxPython 2.8.11:

Traceback (most recent call last):

File “E:\MyProjects\temp\XLSGrid.py”, line 1553, in GetAttr

return cell.GetAttr()

File “E:\MyProjects\temp\XLSGrid.py”, line 1356, in GetAttr

self.attr.IncRef()

File “C:\Python25\lib\site-packages\wx-2.8-msw-unicode\wx\grid.py”, line 529, in IncRef

return _grid.GridCellAttr_IncRef(*args, **kwargs)

TypeError: in method ‘GridCellAttr_IncRef’, expected argument 1 of type ‘wxGridCellAttr *’

Or this one, in wxPython 2.9.2.1:

Traceback (most recent call last):

File “E:\MyProjects\XLSGrid\xlsgrid.py”, line 1553, in GetAttr

return cell.GetAttr()

File “E:\MyProjects\XLSGrid\xlsgrid.py”, line 1356, in GetAttr

self.attr.IncRef()

File “c:\Python27\lib\site-packages\wx-2.9.2-msw\wx_core.py”, line 797, in IncRef

return core.RefCounter_IncRef(*args, **kwargs)

TypeError: in method ‘RefCounter_IncRef’, expected argument 1 of type ‘wxRefCounter *’

Isn’t self.attr an instance of GridCellAttr?!?? I explicitly create it in the code above!!

wx.grid.Grid seems to completely redraw itself at every resize event, even if the cell content has not changed and it has not been damaged. This happens also in the “MegaGrid” sample in the wxPython demo. Why is that? I thought that the undamaged/unchanged cells should just be skipped from refreshing. I have tried to add wx.CLIP_CHILDREN and wx.NO_FULL_REPAINT_ON_RESIZE on the parent window, to no avail.

Enjoy, wxPython rules

Andrea.

“Imagination Is The Only Weapon In The War Against Reality.”

http://xoomer.alice.it/infinity77/

Mike_Driscoll0 · August 8, 2011, 9:22pm

Congrats Andrea! I can’t wait to check it out!

Mike

Tobias_Weber · August 8, 2011, 9:28pm

I already love it-

I am working on a project and had just started fiddling around doing

sthg like this… only more way more clumsy.
I will intensely play w/ it in the next days and am more than
willing to provide feedback on whatever I am running into.

Thanks a million

:-)

Cheers,

S.

···

wxPython-users+unsubscribe@googlegroups.com
http://groups.google.com/group/wxPython-users?hl=en


-- --------------------------------------------------
Tobias Weber
CEO
The ROG Corporation GmbH
Donaustaufer Str. 200
93059 Regensburg
Tel: +49 941 4610 57 55
Fax: +49 941 4610 57 56
Geschäftsführer: Tobias Weber
Registergericht: Amtsgericht Regensburg - HRB 8954
UStID DE225905250 - Steuer-Nr.184/59359
--------------------------------------------------

www.roglink.com

Tobias_Weber · August 8, 2011, 9:30pm

I already love it-

I am working on a project and had just started fiddling around doing

sthg like this… only more way more clumsy.
I will intensely play w/ it in the next days and am more than
willing to provide feedback on whatever I am running into.

Thanks a million

:-)

Cheers,

S.

···

wxPython-users+unsubscribe@googlegroups.com
http://groups.google.com/group/wxPython-users?hl=en


-- --------------------------------------------------
Tobias Weber
CEO
The ROG Corporation GmbH
Donaustaufer Str. 200
93059 Regensburg
Tel: +49 941 4610 57 55
Fax: +49 941 4610 57 56
Geschäftsführer: Tobias Weber
Registergericht: Amtsgericht Regensburg - HRB 8954
UStID DE225905250 - Steuer-Nr.184/59359
--------------------------------------------------

www.roglink.com

Malcolm_Greene · August 8, 2011, 10:51pm

Screenshots:

(XLSGrid standalone)

http://xoomer.virgilio.it/infinity77/Softwares/XLSGrid_1.png

(XLSGrid against Excel, same file)

http://xoomer.virgilio.it/infinity77/Softwares/XLSGrid_3.png

Looks gorgeous Andrea!!!

Malcolm

Robin · August 9, 2011, 1:53am

Questions:

1) I have this piece of code in the XLSGrid implementation (which is
currently commented out):

         # Commented-out section
         # if self.attr is not None:
         # self.attr.IncRef()
         # return self.attr
         attr = gridlib.GridCellAttr()

         attr.SetRenderer(XLSRenderer(self))

         attr.SetSize(*self.size)
         attr.SetOverflow(True)
         self.attr = attr
         return self.attr

I though I could store a wx.grid.GridCellAttr inside a class member, but
every time I try to un-comment that section I get this from wxPython 2.8.11:

Traceback (most recent call last):
   File "E:\MyProjects\temp\XLSGrid.py", line 1553, in GetAttr
     return cell.GetAttr()
   File "E:\MyProjects\temp\XLSGrid.py", line 1356, in GetAttr
     self.attr.IncRef()
   File "C:\Python25\lib\site-packages\wx-2.8-msw-unicode\wx\grid.py",
line 529, in IncRef
     return _grid.GridCellAttr_IncRef(*args, **kwargs)
  TypeError: in method 'GridCellAttr_IncRef', expected argument 1 of
type 'wxGridCellAttr *'

Or this one, in wxPython 2.9.2.1 <http://2.9.2.1>:

Traceback (most recent call last):
   File "E:\MyProjects\XLSGrid\xlsgrid.py", line 1553, in GetAttr
     return cell.GetAttr()
   File "E:\MyProjects\XLSGrid\xlsgrid.py", line 1356, in GetAttr
     self.attr.IncRef()
   File "c:\Python27\lib\site-packages\wx-2.9.2-msw\wx\_core.py", line
797, in IncRef
     return _core_.RefCounter_IncRef(*args, **kwargs)
TypeError: in method 'RefCounter_IncRef', expected argument 1 of type
'wxRefCounter *'

Isn't self.attr an instance of GridCellAttr?!?? I explicitly create it
in the code above!!

Adding a "print self.attr" will give you a bunch of these:

<wx.grid.GridCellAttr; proxy of wxPython wrapper for DELETED GridCellAttr object! (The C++ object no longer exists.) >

So something is deleting the C++ object when you are not expecting it. In this case it is the attr object itself as its refcount will be reduced when the return value from GetAttr is used, so it will delete itself because it is reaching zero. If you increment it an extra time when you create it then that will let it know that you are keeping an extra reference so you can use it again.

     def GetAttr(self):
         """
         Returns the attribute to use for this specific cell.

         :returns: an instance of `wx.grid.GridCellAttr`.
         """
         if self.attr is not None:
             self.attr.IncRef()
             return self.attr
         else:
             attr = gridlib.GridCellAttr()
             attr.SetRenderer(XLSRenderer(self))

             attr.SetSize(*self.size)
             attr.SetOverflow(True)
             self.attr = attr
             self.attr.IncRef() # <--
         return self.attr

2) `wx.grid.Grid` seems to completely redraw itself at every resize
event, even if the cell content has not changed and it has not been
damaged. This happens also in the "MegaGrid" sample in the wxPython
demo. Why is that? I thought that the undamaged/unchanged cells should
just be skipped from refreshing. I have tried to add wx.CLIP_CHILDREN
and wx.NO_FULL_REPAINT_ON_RESIZE on the parent window, to no avail.

Yes it should, but I seem to recall something from way back about some cases where not enough is being refreshed and so changes were added to the grid to be more aggressive about refreshing. The grid window's paint does try to calculate which cells intersect the update region and then only call the renderer's Draw method for those cells, but if something else is marking the whole window as invalid then the whole thing will end up in the update region.

···

On 8/8/11 2:06 PM, Andrea Gavana wrote:

--
Robin Dunn
Software Craftsman

Andrea_Gavana · August 9, 2011, 5:20pm

Hi Robin,

Questions:

I have this piece of code in the XLSGrid implementation (which is

currently commented out):
     # Commented-out section

     # if self.attr is not None:

     #     self.attr.IncRef()

     #     return self.attr

     attr = gridlib.GridCellAttr()



     attr.SetRenderer(XLSRenderer(self))



     attr.SetSize(*self.size)

     attr.SetOverflow(True)

     self.attr = attr

     return self.attr
I though I could store a wx.grid.GridCellAttr inside a class member, but

every time I try to un-comment that section I get this from wxPython 2.8.11:

Traceback (most recent call last):

File “E:\MyProjects\temp\XLSGrid.py”, line 1553, in GetAttr
 return cell.GetAttr()
File “E:\MyProjects\temp\XLSGrid.py”, line 1356, in GetAttr
 self.attr.IncRef()
File “C:\Python25\lib\site-packages\wx-2.8-msw-unicode\wx\grid.py”,

line 529, in IncRef
 return _grid.GridCellAttr_IncRef(*args, **kwargs)
TypeError: in method ‘GridCellAttr_IncRef’, expected argument 1 of

type ‘wxGridCellAttr *’

Or this one, in wxPython 2.9.2.1 <http://2.9.2.1>:

Traceback (most recent call last):

File “E:\MyProjects\XLSGrid\xlsgrid.py”, line 1553, in GetAttr
 return cell.GetAttr()
File “E:\MyProjects\XLSGrid\xlsgrid.py”, line 1356, in GetAttr
 self.attr.IncRef()
File “c:\Python27\lib\site-packages\wx-2.9.2-msw\wx_core.py”, line

797, in IncRef
 return _core_.RefCounter_IncRef(*args, **kwargs)
TypeError: in method ‘RefCounter_IncRef’, expected argument 1 of type

‘wxRefCounter *’

Isn’t self.attr an instance of GridCellAttr?!?? I explicitly create it

in the code above!!

Adding a “print self.attr” will give you a bunch of these:

<wx.grid.GridCellAttr; proxy of wxPython wrapper for DELETED GridCellAttr object! (The C++ object no longer exists.) >

So something is deleting the C++ object when you are not expecting it. In this case it is the attr object itself as its refcount will be reduced when the return value from GetAttr is used, so it will delete itself because it is reaching zero. If you increment it an extra time when you create it then that will let it know that you are keeping an extra reference so you can use it again.
def GetAttr(self):

    """

    Returns the attribute to use for this specific cell.



    :returns: an instance of `wx.grid.GridCellAttr`.


    """

    if self.attr is not None:

        self.attr.IncRef()

        return self.attr

else:

        attr = gridlib.GridCellAttr()

        attr.SetRenderer(XLSRenderer(self))



        attr.SetSize(*self.size)

        attr.SetOverflow(True)

        self.attr = attr

        self.attr.IncRef()   # <--

    return self.attr

Thank you for this. I thought that IncRef() should be called only when re-using an existing instance of an attr (such as one stored in as a class member). I was really put off by this example:

http://www.java2s.com/Tutorial/Python/0380__wxPython/subclassPyGridTableBase.htm

Where the GetAttr method simply did this:

def GetAttr(self, row, col, kind):
    attr = [self.even, self.odd][row % 2]
    attr.IncRef()
    **return **attr

wx.grid.Grid seems to completely redraw itself at every resize

event, even if the cell content has not changed and it has not been

damaged. This happens also in the “MegaGrid” sample in the wxPython

demo. Why is that? I thought that the undamaged/unchanged cells should

just be skipped from refreshing. I have tried to add wx.CLIP_CHILDREN

and wx.NO_FULL_REPAINT_ON_RESIZE on the parent window, to no avail.

Yes it should, but I seem to recall something from way back about some cases where not enough is being refreshed and so changes were added to the grid to be more aggressive about refreshing. The grid window’s paint does try to calculate which cells intersect the update region and then only call the renderer’s Draw method for those cells, but if something else is marking the whole window as invalid then the whole thing will end up in the update region.

I have discovered that wxPython 2.9.2.1 behaves much better than 2.8.11 on this issue. I have added this code in my XLSGrid class:

def __init__(self, parent):

    gridlib.Grid.__init__(self, parent)

    # Skipped some initialization code

    self.paint_iter = 1

    self.GetGridWindow().Bind(wx.EVT_PAINT, self.OnPaint)

def OnPaint(self, event):

    grid_window = event.GetEventObject()

    region = grid_window.GetUpdateRegion()

    rects = wx.RegionIterator(region)

    print "Paint event %d"%self.paint_iter

    print "Window size:", grid_window.GetSize()

    count = 0

    fmt_str = "Update rect %d: - width = %d , height = %d"

    while rects.HaveRects():

        print fmt_str%(count, rects.width, rects.height)

        rects.Next()

        count += 1

    print

    self.paint_iter += 1

    event.Skip()

And, on wxPython 2.8.11 (after the initial paint event which paints the whole window), every resize I make redraws the entire grid:

E:\MyProjects\XLSGrid>XLSGridDemo.py

Paint event 1

Window size: (819, 497)

Update rect 0: - width = 819 , height = 497

Paint event 2

Window size: (819, 498)

Update rect 0: - width = 819 , height = 498

Paint event 3

Window size: (819, 499)

Update rect 0: - width = 819 , height = 499

Paint event 4

Window size: (819, 500)

Update rect 0: - width = 819 , height = 500

Paint event 5

Window size: (819, 501)

Update rect 0: - width = 819 , height = 501

Paint event 6

Window size: (819, 502)

Update rect 0: - width = 819 , height = 502

Paint event 7

Window size: (819, 503)

Update rect 0: - width = 819 , height = 503

While on 2.9.2.1 it is much better:

E:\MyProjects\XLSGrid>c:\Python27\python.exe XLSGridDemo.py

Paint event 1

Window size: (819, 483)

Update rect 0: - width = 819 , height = 483

Paint event 2

Window size: (819, 484)