AGW Logo

agw_title 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).


description Description

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

XLSGrid requires the xlrd package from:

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

Minimum version requirement for xlrd is 0.7.1. If you wish to have support for hyperlinks inside cells and rich text content, you need the SVN version of xlrd.

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 strikethrough, 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 rich text (new in version 0.2): support for strings containing partial bold, italic and underlined text, change of font inside a string etc... Cells with rich text content can not be multi-line and they will not honour the shrink-to-fit and wrapping settings.
  • 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).

And a lot more. Check the demo for an almost complete review of the functionalities.


usage Usage

Sample usage:

import wx
import xlrd
import os

import xlsgrid as XG

class MyFrame(wx.Frame):

    def __init__(self):

        wx.Frame.__init__(self, parent, -1, "XLSGrid Demo", size=(1000, 800))

        filename = os.path.join(os.getcwd(), "Excel", "Example_1.xls")
        sheetname = "Example_1"

        book = xlrd.open_workbook(filename, formatting_info=1)

        sheet = book.sheet_by_name(sheetname)
        rows, cols = sheet.nrows, sheet.ncols

        comments, texts = XG.ReadExcelCOM(filename, sheetname, rows, cols)

        xls_grid = XG.XLSGrid(self)
        xls_grid.PopulateGrid(book, sheet, texts, comments)


# our normal wxApp-derived class, as usual

app = wx.PySimpleApp()

frame = MyFrame(None)
app.SetTopWindow(frame)
frame.Show()

app.MainLoop()

This code snippet can be downloaded, see this example script.

Note

Some of the AGW snippets of code in the documentation use images and external files (to create bitmaps or access external data). As these files are not provided in these snippets, you should make the approriate modifications to the code to actually run it.

Note

Please note that you have to pass the keyword formatting_info to the method xlrd.open_workbook to obtain the cell formatting.


todos TODOs

  1. XLSGrid is sufficiently efficient and fast for reasonably small Excel files. There might be some improvement to be made in the code to make it work with bigger files and in a faster way;
  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 seems to be fixed in wxPython 2.9.2.1);
  3. There is currently no support for strikethrough fonts, although xlrd correctly reports this format. The issue is a bug in wxWidgets itself which doesn’t allow the creation of strikethrough fonts (http://trac.wxwidgets.org/ticket/9907).

platforms Supported Platforms

XLSGrid has been tested on the following platforms:
  • Windows (Windows Vista and 7);

styles Window Styles

No particular window styles are available for this class.


events Events Processing

No custom events are available for this class.


license License And Version

XLSGrid is distributed under the wxPython license.

Latest Revision: Andrea Gavana @ 17 Aug 2011, 15.00 GMT

Version 0.3

Module author: Andrea Gavana <andrea.gavana@gmail.com>


hierarchy Inheritance Diagram

Inheritance diagram for module: xlsgrid

Inheritance diagram of xlsgrid.Excel, xlsgrid.TransientPopup, xlsgrid.XLSBackground, xlsgrid.XLSBorder, xlsgrid.XLSBorderFactory, xlsgrid.XLSCell, xlsgrid.XLSComment, xlsgrid.XLSGrid, xlsgrid.XLSRenderer, xlsgrid.XLSRichText, xlsgrid.XLSTable, xlsgrid.XLSText


svn_main SVN Revisions

A graphical representation of the SVN commits in the last year.

Click on any date in the picture to jump to that particular revision page, containing information about committers, log messages and SVN diffs.

xlsgrid

Revision Graph For xlsgrid


2to3 Python 3 Issues (via 2to3)

No issues have been detected by 2to3.py.