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

Tips, tricks and tools for creating your own data-driven app, brought to you in partnership with Qlik.

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/

Explore data-driven apps with less coding and query writing, brought to you in partnership with Qlik.

Topics:

Opinions expressed by DZone contributors are their own.

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

{{ parent.tldr }}

{{ parent.urlSource.name }}