Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

Excel Spreadsheet Replication with XLSGrid for wxPython

DZone's Guide to

Excel Spreadsheet Replication with XLSGrid for wxPython

· Web Dev Zone ·
Free Resource

Learn how error monitoring with Sentry closes the gap between the product team and your customers. With Sentry, you can focus on what you do best: building and scaling software that makes your users’ lives better.

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/

What’s the best way to boost the efficiency of your product team and ship with confidence? Check out this ebook to learn how Sentry's real-time error monitoring helps developers stay in their workflow to fix bugs before the user even knows there’s a problem.

Topics:

Opinions expressed by DZone contributors are their own.

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

{{ parent.tldr }}

{{ parent.urlSource.name }}