Over a million developers have joined DZone.

Excel Spreadsheet Replication with XLSGrid for wxPython

· Web Dev Zone

Last year, Andrea Gavana, developer of the agw library in the wxPython code base, released this cool widget: XLSGrid. It’s purpose is to faithfully reproduce the appearance of a Microsoft Excel spreadsheet (one worksheet per every instance of XLSGrid). This widget is based on wx.grid.PyGridTableBase and wx.grid.PyGridCellRenderer and requires xlrd. Andrea also recommends using Mark Hammond’s PyWin32 module or the widget’s formatting abilities will be very limited. If you’d like to read the full announcement, just go here.

If you grab the download from the wxPython group, you’ll get three files:

  • Example_1.xls
  • xlsgrid.py
  • XLSGridDemo.py


The first is an example Microsoft Excel file, the second is the widget file itself and the third is a handy demo. If you run the demo and notice the following error in your command window, then you need to download the latest agw stuff from the wxPython SVN repository:

Traceback (most recent call last):
  File "C:\Users\Mike\Desktop\xls\xlsgrid.py", line 1657, in OnMouseMotion
    self.tip_window = TransientPopup(window, comment, wx.GetMousePosition())
  File "C:\Users\Mike\Desktop\xls\xlsgrid.py", line 1853, in __init__
    self.DoShowNow()
AttributeError: 'TransientPopup' object has no attribute 'DoShowNow'


Now we’ll take a moment and create a simple Excel file and our own little demo. Let’s get coding!

Note: The Excel file is available for download at the end of this post.

import wx
import xlrd
import xlsgrid as XG
 
########################################################################
class MyForm(wx.Frame):
 
    #----------------------------------------------------------------------
    def __init__(self):
        wx.Frame.__init__(self, None, wx.ID_ANY, "Tutorial")
 
        panel = wx.Panel(self, wx.ID_ANY)
 
        filename = "demo.xls"
        book = xlrd.open_workbook(filename, formatting_info=1)
        sheetname = "Sheet1"
        sheet = book.sheet_by_name(sheetname)
        rows, cols = sheet.nrows, sheet.ncols
        comments, texts = XG.ReadExcelCOM(filename, sheetname, rows, cols)
 
        xlsGrid = XG.XLSGrid(panel)
        xlsGrid.PopulateGrid(book, sheet, texts, comments)
 
        sizer = wx.BoxSizer(wx.VERTICAL)
        sizer.Add(xlsGrid, 1, wx.EXPAND, 5)
        panel.SetSizer(sizer)
 
#----------------------------------------------------------------------
# Run the program
if __name__ == "__main__":
    app = wx.App(False)
    frame = MyForm().Show()
    app.MainLoop()


If you run the code above, you should see something like this:


The only problem I had when I ran this was that if the Excel file had no comments whatsoever, I’d get the following traceback:

pywintypes.com_error: (-2147352567, 'Exception occurred.', (0, 'Microsoft Excel', 'No cells were found.', 'C:\\Program Files\\Microsoft Office\\Office10\\1033\\xlmain10.chm', 0, -2146827284), None)
File "E:\My Documents\My Dropbox\Scripts\wx tutorials\XLSGrid-tut\mvp_xlsDemo.py", line 32, in <module>
  frame = MyForm().Show()
File "E:\My Documents\My Dropbox\Scripts\wx tutorials\XLSGrid-tut\mvp_xlsDemo.py", line 19, in __init__
  comments, texts = XG.ReadExcelCOM(filename, sheetname, rows, cols)
File "E:\My Documents\My Dropbox\Scripts\wx tutorials\XLSGrid-tut\xlsgrid.py", line 475, in ReadExcelCOM
  comm_range = workbook.GetCommentsRange()
File "E:\My Documents\My Dropbox\Scripts\wx tutorials\XLSGrid-tut\xlsgrid.py", line 535, in GetCommentsRange
  return self.sheet.Cells.SpecialCells(-4144)
File "L:\Python25\Lib\site-packages\win32com\client\dynamic.py", line 3, in SpecialCells


Thus, this widget currently seems to require at least one comment to work as of version 0.2. Other than that, it works great. If you have a need to read and display Microsoft Excel files in your code (or you just want to learn some neat wx.grid tricks), you should go download this cool new widget!

Downloads



Source: http://www.blog.pythonlibrary.org/2011/08/20/wxpython-new-widget-announced-xlsgrid/

Topics:

The best of DZone straight to your inbox.

SEE AN EXAMPLE
Please provide a valid email address.

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.
Subscribe

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}