# --------------------------------------------------------------------------------- #
# XLSGRID wxPython IMPLEMENTATION
#
# Andrea Gavana @ 08 Aug 2011
# Latest Revision: 17 Aug 2011, 15.00 GMT
#
#
# TODO List
#
# Current todo list:
#
# 1. There is currently no support for rich text, i.e. strings containing partial
# bold, italic and underlined text, change of font inside a string, etc... xlrd
# supports those (from version 0.7.2 in SVN) but there is no easy way to handle
# changing fonts/colours/formatting in the same string in wxPython;
#
# 2. 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;
#
# 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).
#
# For all kind of problems, requests of enhancements and bug reports, please write
# to me at:
#
# andrea.gavana@gmail.com
# andrea.gavana@maerskoil.com
#
# Or, obviously, to the wxPython mailing list!!!
#
#
# End of comments
# --------------------------------------------------------------------------------- #
"""
L{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 L{XLSGrid}).
Description
===========
L{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 L{XLSGrid}).
L{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:
L{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 L{XLSGrid}.
:warning:
If Mark Hammonds' `pywin32` package is not available, the formatting
capabilities of L{XLSGrid} are severely limited; for instance, you won't
probably get the exact WYSIWYG between the Excel spreadsheet and L{XLSGrid}.
:warning:
L{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,
L{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 L{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 L{XLSGrid} with a cursor changing and a tooltip on that cell.
* Cell merging: merged cells in the Excel spreadsheet will be correctly handled
by L{XLSGrid}.
* Columns and rows sizes: L{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
=====
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()
:note: Please note that you **have to** pass the keyword `formatting_info` to
the method `xlrd.open_workbook` to obtain the cell formatting.
TODOs
=====
1. L{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).
Supported Platforms
===================
L{XLSGrid} has been tested on the following platforms:
* Windows (Windows Vista and 7);
Window Styles
=============
`No particular window styles are available for this class.`
Events Processing
=================
`No custom events are available for this class.`
License And Version
===================
L{XLSGrid} is distributed under the wxPython license.
Latest Revision: Andrea Gavana @ 17 Aug 2011, 15.00 GMT
Version 0.3
"""
# Version Info
__version__ = "0.3"
# Start the imports
import wx
import os
try:
import xlrd
except ImportError:
pass
import datetime
import string
import wx.grid as gridlib
from wx.lib.embeddedimage import PyEmbeddedImage
from wx.lib.wordwrap import wordwrap
import supertooltip as STT
from math import pi, sin, cos
from operator import attrgetter
_hasWin32 = False
if wx.Platform == "__WXMSW__":
try:
from win32com.client import Dispatch
import pywintypes
_hasWin32 = True
except ImportError:
pass
#----------------------------------------------------------------------
# Constants used to translate xlrd stuff into wxPython stuff
#----------------------------------------------------------------------
BOTTOM = wx.BOTTOM
TOP = wx.TOP
LEFT = wx.LEFT
RIGHT = wx.RIGHT
DIAGONAL = 2 << 7
XF_BORDER_STYLES = {"bottom": BOTTOM, "left": LEFT, "right": RIGHT,
"top": TOP, "diag": DIAGONAL}
HORIZONTAL_ALIGNMENTS = {0: 0, 1: 0, 2: wx.ALIGN_CENTER_HORIZONTAL, 3: wx.ALIGN_RIGHT}
VERTICAL_ALIGNMENTS = {0: 0, 1: wx.ALIGN_CENTER_VERTICAL, 2: wx.ALIGN_BOTTOM,
3: wx.ALIGN_CENTER_VERTICAL, 4:wx.ALIGN_CENTER_VERTICAL}
NO_LINE = 0x0
THIN = 0x1
MEDIUM = 0x2
DASHED = 0x3
DOTTED = 0x4
THICK = 0x5
DOUBLE = 0x6
HAIR = 0x7
MEDIUM_DASHED = 0x8
THIN_DASH_DOTTED = 0x9
MEDIUM_DASH_DOTTED = 0xA
THIN_DASH_DOT_DOTTED = 0xB
MEDIUM_DASH_DOT_DOTTED = 0xC
SLANTED_MEDIUM_DASH_DOTTED = 0xD
XF_PEN_STYLES = {NO_LINE: (0, None), THIN: (1, wx.SOLID), MEDIUM: (2, wx.SOLID),
DASHED: (1, wx.SHORT_DASH), DOTTED: (1, wx.DOT),
THICK: (3, wx.SOLID), DOUBLE: (1, wx.SOLID), HAIR: (1, wx.DOT),
MEDIUM_DASHED: (2, wx.LONG_DASH), THIN_DASH_DOTTED: (1, wx.DOT_DASH),
MEDIUM_DASH_DOTTED: (2, wx.DOT_DASH), THIN_DASH_DOT_DOTTED: (1, wx.DOT_DASH),
MEDIUM_DASH_DOT_DOTTED: (2, wx.DOT_DASH),
SLANTED_MEDIUM_DASH_DOTTED: (2, wx.DOT_DASH)
}
XF_FONT_FAMILY = {0: wx.SWISS, 1: wx.ROMAN, 2: wx.SWISS,
3: wx.MODERN, 4: wx.SCRIPT, 5: wx.DECORATIVE}
# Unicode ordinals for Hebrew, Arabic and Syriac
# I don't know if there are other RTL languages
RTL_UNICODE = range(1424, 1872)
# To guess text direction we exclude digits and punctuation
USELESS_CHARS = string.punctuation + string.digits + " "
#----------------------------------------------------------------------
# Images used to draw the hatching on Excel cells background
#----------------------------------------------------------------------
_xls_background_01 = PyEmbeddedImage(
"iVBORw0KGgoAAAANSUhEUgAAAAQAAAAECAIAAAAmkwkpAAAAA3NCSVQICAjb4U/gAAAADElE"
"QVQImWNgIB0AAAA0AAEjQ4N1AAAAAElFTkSuQmCC")
#----------------------------------------------------------------------
_xls_background_02 = PyEmbeddedImage(
"iVBORw0KGgoAAAANSUhEUgAAAAQAAAAECAIAAAAmkwkpAAAAA3NCSVQICAjb4U/gAAAAFklE"
"QVQImWNgYGD4//8/lESwIAC7DABt4hfpI2a12wAAAABJRU5ErkJggg==")
#----------------------------------------------------------------------
_xls_background_03 = PyEmbeddedImage(
"iVBORw0KGgoAAAANSUhEUgAAAAQAAAAECAIAAAAmkwkpAAAAA3NCSVQICAjb4U/gAAAAE0lE"
"QVQImWP4//8/AxqACuGUAQBI+Qv1NTPP3AAAAABJRU5ErkJggg==")
#----------------------------------------------------------------------
_xls_background_04 = PyEmbeddedImage(
"iVBORw0KGgoAAAANSUhEUgAAAAQAAAAECAIAAAAmkwkpAAAAA3NCSVQICAjb4U/gAAAAH0lE"
"QVQImXXJsQ0AIAAEIc79d34LazsSwjbgPFXoOxdcCQwBh7OgqgAAAABJRU5ErkJggg==")
#----------------------------------------------------------------------
_xls_background_05 = PyEmbeddedImage(
"iVBORw0KGgoAAAANSUhEUgAAAAQAAAAECAIAAAAmkwkpAAAAA3NCSVQICAjb4U/gAAAAFUlE"
"QVQImWNgwAUY////D+cwIcsAAEggAwHHgMubAAAAAElFTkSuQmCC")
#----------------------------------------------------------------------
_xls_background_06 = PyEmbeddedImage(
"iVBORw0KGgoAAAANSUhEUgAAAAQAAAAECAIAAAAmkwkpAAAAA3NCSVQICAjb4U/gAAAAG0lE"
"QVQImWNkYGBgYGD4//8/AwMDEwMSwM0BAISAAwUnufp7AAAAAElFTkSuQmCC")
#----------------------------------------------------------------------
_xls_background_07 = PyEmbeddedImage(
"iVBORw0KGgoAAAANSUhEUgAAAAQAAAAECAIAAAAmkwkpAAAAA3NCSVQICAjb4U/gAAAAHklE"
"QVQImWNkYGBgYGD4//8/EgVh/P//H86HikH4APOCFO3yiGicAAAAAElFTkSuQmCC")
#----------------------------------------------------------------------
_xls_background_08 = PyEmbeddedImage(
"iVBORw0KGgoAAAANSUhEUgAAAAQAAAAECAIAAAAmkwkpAAAAA3NCSVQICAjb4U/gAAAAHUlE"
"QVQImWP4////////GSAAzvr//z8jmhADXCUAQSkU7eggG3gAAAAASUVORK5CYII=")
#----------------------------------------------------------------------
_xls_background_09 = PyEmbeddedImage(
"iVBORw0KGgoAAAANSUhEUgAAAAQAAAAECAIAAAAmkwkpAAAAA3NCSVQICAjb4U/gAAAAIElE"
"QVQImWNkYGBgYGD4//8/AwMDEwMy+P//P0QYXQYACtQI/cTE6U0AAAAASUVORK5CYII=")
#----------------------------------------------------------------------
_xls_background_10 = PyEmbeddedImage(
"iVBORw0KGgoAAAANSUhEUgAAAAQAAAAECAIAAAAmkwkpAAAAA3NCSVQICAjb4U/gAAAAG0lE"
"QVQImW3IsQ0AAAjAIPz/6LobGRmgclVfswpsCPmQczsGAAAAAElFTkSuQmCC")
#----------------------------------------------------------------------
_xls_background_11 = PyEmbeddedImage(
"iVBORw0KGgoAAAANSUhEUgAAAAQAAAAECAIAAAAmkwkpAAAAA3NCSVQICAjb4U/gAAAAFklE"
"QVQImWNgQAKM////h3OYkGVQOABvOgMD4NUKkwAAAABJRU5ErkJggg==")
#----------------------------------------------------------------------
_xls_background_12 = PyEmbeddedImage(
"iVBORw0KGgoAAAANSUhEUgAAAAQAAAAECAIAAAAmkwkpAAAAA3NCSVQICAjb4U/gAAAAG0lE"
"QVQImWNkYGD4//8/AwMDAwMDEwMSwM0BAI13AwWY+Mx+AAAAAElFTkSuQmCC")
#----------------------------------------------------------------------
_xls_background_13 = PyEmbeddedImage(
"iVBORw0KGgoAAAANSUhEUgAAAAQAAAAECAIAAAAmkwkpAAAAA3NCSVQICAjb4U/gAAAAI0lE"
"QVQImT3KsQ0AMAyAMMj/P9MhUUcLBCoAmEo9bFn7H/UBXEwMBN75abEAAAAASUVORK5CYII=")
#----------------------------------------------------------------------
_xls_background_14 = PyEmbeddedImage(
"iVBORw0KGgoAAAANSUhEUgAAAAQAAAAECAIAAAAmkwkpAAAAA3NCSVQICAjb4U/gAAAAIElE"
"QVQImT3KoQEAAAwCINz/P7tmI5C2QJKb2t6EYPMBOOUMBIWcMEIAAAAASUVORK5CYII=")
#----------------------------------------------------------------------
_xls_background_15 = PyEmbeddedImage(
"iVBORw0KGgoAAAANSUhEUgAAAAQAAAAECAIAAAAmkwkpAAAAA3NCSVQICAjb4U/gAAAAGUlE"
"QVQImWNgQAKMDAwM////h3CYkGVQOABmQwMDJpgq9gAAAABJRU5ErkJggg==")
#----------------------------------------------------------------------
_xls_background_16 = PyEmbeddedImage(
"iVBORw0KGgoAAAANSUhEUgAAAAQAAAAECAIAAAAmkwkpAAAAA3NCSVQICAjb4U/gAAAAHklE"
"QVQImWNgYGD4//8/lISzIAwEnxEqwMDAyMgIALKwF+2ym+hoAAAAAElFTkSuQmCC")
#----------------------------------------------------------------------
_xls_background_17 = PyEmbeddedImage(
"iVBORw0KGgoAAAANSUhEUgAAAAQAAAAECAIAAAAmkwkpAAAAA3NCSVQICAjb4U/gAAAAIklE"
"QVQImWNkYGD4//8/AwMDAwMDI5zFwMDABBVjZESXAQAc+AkAQ4bzDAAAAABJRU5ErkJggg==")
#----------------------------------------------------------------------
_xls_background_18 = PyEmbeddedImage(
"iVBORw0KGgoAAAANSUhEUgAAAAgAAAAECAIAAAA8r+mnAAAAA3NCSVQICAjb4U/gAAAAH0lE"
"QVQImWNkYGD4//8/AwZgxCrKwMDAhKKKkZGwDgA83QkAy10JvwAAAABJRU5ErkJggg==")
#----------------------------------------------------------------------
[docs]def SplitThousands(s, tSep=',', dSep='.'):
"""
Splits a general float on thousands. GIGO on general input.
:param `s`: can be a float or a string, representing a number;
:param `tSep`: the character to be used as thousands separator;
:param `dSep`: the character to be used as decimal separator.
:returns: a string properly formatted with thousands and decimal
separators in it.
:note: This method is used only if Mark Hammonds' `pywin32` package is
not available to try and format a number in an intelligent way.
:note: This code has been obtained from the public domain:
http://code.activestate.com/recipes/498181-add-thousands-separator-commas-to-formatted-number/#c14
"""
if not isinstance(s, basestring):
s = unicode(s, "utf-8", "ignore")
cnt = 0
numChars = dSep + '0123456789'
ls = len(s)
while cnt < ls and s[cnt] not in numChars:
cnt += 1
lhs = s[0:cnt]
s = s[cnt:]
if dSep == '':
cnt = -1
else:
cnt = s.rfind(dSep)
if cnt > 0:
rhs = dSep + s[cnt+1:]
s = s[:cnt]
else:
rhs = ''
splt = ''
while s != '':
splt = s[-3:] + tSep + splt
s = s[:-3]
return lhs + splt[:-1] + rhs
[docs]def ReadExcelCOM(filename, sheetname, rows, cols):
"""
Reads and Excel spreadsheet (a single worksheet) using Mark Hammonds' `pywin32`
package. If this package is not available, it returns two empty nested lists.
:param `filename`: a valid Excel `.xls` filename;
:param `sheetname`: the worksheet name inside the Excel file (i.e., the label
on the workbook tab at the bottom of the workbook);
:param `rows`: the number of significant rows in the worksheet, as returned
by `xlrd`;
:param `cols`: the number of significant columns in the worksheet, as returned
by `xlrd`.
:returns: two nested lists representing the comments (notes) on every cell and
the WYSIWYG representation of the cell content.
:note: If Mark Hammonds' `pywin32` package is not available, this method
returns two empty nested lists.
"""
comments = [["" for i in range(cols)] for j in range(rows)]
texts = [[None for i in range(cols)] for j in range(rows)]
if not _hasWin32:
return comments, texts
workbook = Excel(filename, sheetname)
for i in xrange(1, rows+1):
for j in xrange(1, cols+1):
texts[i-1][j-1] = workbook.GetText(i, j)
comm_range = workbook.GetCommentsRange()
if comm_range is not None:
for comm in comm_range:
comments[comm.Row-1][comm.Column-1] = comm.Comment.Text()
workbook.Close()
return comments, texts
[docs]def FontFromFont(font):
"""
Creates a copy of the input `font`.
:param `font`: an instance of `wx.Font`.
"""
new_font = wx.Font(font.GetPointSize(), font.GetFamily(), font.GetStyle(),
font.GetWeight(), font.GetUnderlined(),
font.GetFaceName(), font.GetEncoding())
return new_font
[docs]class Excel(object):
"""
A simple class that holds a COM interface to Excel.
By using the `win32com` module from Mark Hammonds' `pywin32` package, we
can manipulate various workbook/worksheet methods inside this class.
"""
[docs] def __init__(self, filename, sheetname):
"""
Default class constructor.
:param `filename`: a valid Excel `.xls` filename;
:param `sheetname`: the worksheet name inside the Excel file (i.e., the label
on the workbook tab at the bottom of the workbook).
"""
self.xlApp = Dispatch('Excel.Application')
self.filename = filename
self.xlBook = self.xlApp.Workbooks.Open(filename)
self.sheet = self.xlBook.Worksheets(sheetname)
self.xlApp.Visible = 0
[docs] def Close(self, save=False):
"""
Closes the Excel workbook, interrupting the COM interface.
:param `save`: ``True`` to save the changes you made to the workbook,
``False`` otherwise.
"""
self.xlBook.Close(SaveChanges=save)
del self.xlApp
[docs] def GetCommentsRange(self):
"""
Returns a range of cells containing comments, using the VBA API.
"""
try:
return self.sheet.Cells.SpecialCells(-4144)
except pywintypes.com_error:
return None
[docs] def GetText(self, row, col):
"""
Returns the WYSIWYG text contained in a cell.
:param `row`: the row in which the cell lives;
:param `col`: the column in which the cell lives.
:note: The `row` and `col` parameters are not real Python index, as they
use the Excel indexing mode (i.e., first index is 1 and not 0).
"""
cell = self.sheet.Cells(row, col)
if cell:
return cell.Text
[docs]class XLSText(object):
"""
This is a class which holds information about the cell content, in terms
of actual cell value, font, text colour, alignment and formatting.
"""
[docs] def __init__(self, book, cell, xf_index, display_text=None, hyperlink=None, default_width=10):
"""
Default class constructor.
:param `book`: an instance of the `xlrd.Book` class;
:param `cell`: an instance of `xlrd.sheet.Cell` class;
:param `xf_index`: an index into `xlrd.Book.xf_list`, which holds a
reference to the `xlrd.sheet.Cell` class (the actual cell for `xlrd`);
:param `display_text`: if Mark Hammonds' `pywin32` package is available,
this is the WYSIWYG cell content;
:param `hyperlink`: if this cell contains a hyperlink, it will be displayed
accordingly;
:param `default_width`: this is 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).
:note: If you are using version 0.7.1 or lower for `xlrd`, the *hyperlink*
parameter will always be ``None`` as this feature is available only in
`xlrd` 0.7.2 (SVN).
"""
XFClass = book.xf_list[xf_index]
font = book.font_list[XFClass.font_index]
self.font = self.CreateFont(font)
text_colour = book.colour_map[font.colour_index]
self.text_colour = self.CreateTextColour(text_colour)
if display_text is not None:
self.value = display_text
else:
format = book.format_map[XFClass.format_key]
self.CreateFormat(format, cell, book.datemode)
alignment = XFClass.alignment
self.CreateAlignment(alignment, default_width)
if hyperlink is not None:
self.SetupHyperlink(hyperlink)
else:
self.tooltip = None
[docs] def CreateFont(self, font):
"""
Creates a suitable wxPython font starting from an Excel font.
:param `font`: an instance of `xlrd.formatting.Font` class.
:note: 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. See
(http://trac.wxwidgets.org/ticket/9907).
"""
style, bold, underline = wx.FONTSTYLE_NORMAL, wx.NORMAL, False
if font.italic:
style = wx.FONTSTYLE_ITALIC
if font.underline_type > 0:
underline = True
if font.weight > 600:
bold = wx.BOLD
family = XF_FONT_FAMILY[font.family]
name = font.name
size = int(font.height/20.0)
if font.escapement_type > 0:
# subscript/superscript
size = int(size*0.7)
# No support for strike-through fonts in wxWidgets
# if font.struck_out:
# style = wx.FONTFLAG_DEFAULT
# if bold:
# style += wx.FONTFLAG_BOLD
# if underline:
# style += wx.FONTFLAG_UNDERLINED
# if font.italic:
# style += wx.FONTFLAG_ITALIC
#
# style += wx.FONTFLAG_STRIKETHROUGH
# self.font = wx.FFont(size, family, style, name.encode())
# else:
return wx.Font(size, family, style, bold, underline, name.encode())
[docs] def CreateTextColour(self, text_colour):
"""
Creates a suitable wxPython colour for the text starting from a `xlrd`
tuple representing this colour.
:param `text_colour`: a tuple representing the RGB components of the
colour. If `text_colour` is ``None``, use the default ``wx.SYS_COLOUR_WINDOWTEXT``.
"""
if text_colour is not None:
text_colour = wx.Colour(*text_colour)
else:
text_colour = wx.SystemSettings.GetColour(wx.SYS_COLOUR_WINDOWTEXT)
return text_colour
[docs] def CreateAlignment(self, alignment, default_width):
"""
Creates a suitable wxPython alignment flag for the text starting from a
`xlrd` class representing this alignment.
:param `alignment`: an instance of `xlrd.formatting.XFAlignment` class;
:param `default_width`: this is 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).
"""
hor_align, vert_align = alignment.hor_align, alignment.vert_align
self.horizontal_alignment = HORIZONTAL_ALIGNMENTS[hor_align]
self.vertical_alignment = VERTICAL_ALIGNMENTS[vert_align]
self.indent_level = alignment.indent_level
self.shrink_to_fit = alignment.shrink_to_fit
self.text_wrapped = alignment.text_wrapped
text_direction = 1
if alignment.text_direction == 0:
for char in self.value:
if char not in USELESS_CHARS:
if ord(char) in RTL_UNICODE:
text_direction = 2
break
self.text_direction = text_direction
self.default_width = default_width
if alignment.rotation > 90:
self.rotation = 90 - alignment.rotation
else:
self.rotation = alignment.rotation
[docs] def CreateFormat(self, format, cell, datemode):
"""
This method tries to guess the best format to apply to the current text
value.
:param `format`: an instance of `xlrd.formatting.Format` class;
:param `cell`: an instance of `xlrd.sheet.Cell` class;
:param `datemode`: the datemode associated with this Excel workbook.
:note: This method is used only if Mark Hammonds' `pywin32` package is
not available to try and format the cell text in an intelligent way.
:warning: The formatting applied by this method is severely limited; for
instance, you won't probably get the exact WYSIWYG between the Excel
spreadsheet and L{XLSGrid}.
"""
ctype, value = cell.ctype, cell.value
self.value = "%s"%value
isDate = False
if ctype == xlrd.XL_CELL_DATE:
value = xlrd.xldate_as_tuple(value, datemode)
isDate = True
elif ctype in [xlrd.XL_CELL_EMPTY, xlrd.XL_CELL_BLANK]:
return
elif ctype == xlrd.XL_CELL_TEXT:
self.value = "%s"%value
return
elif ctype == xlrd.XL_CELL_ERROR:
value = xlrd.error_text_from_code(ctype)
self.value = "%s"%value
return
self.FormatString(value, isDate, format.format_str)
[docs] def FormatString(self, value, isDate, format_str):
"""
This method tries to guess the best format to apply to the current text
value.
:param `value`: the actual raw cell text value;
:param `isDate`: ``True`` if this value represents a `xlrd` date object,
``False`` otherwise;
:param `format_str`: the actual formatting string as extracted from Excel.
:note: This method is used only if Mark Hammonds' `pywin32` package is
not available to try and format the cell text in an intelligent way.
:warning: The formatting applied by this method is severely limited; for
instance, you won't probably get the exact WYSIWYG between the Excel
spreadsheet and L{XLSGrid}.
"""
if "General" in format_str:
self.value = "%s"%value
return
number_format = format_str
currency = percentage = ""
if not isDate:
symbol = ""
split = format_str.split()
if len(split) > 1:
# Accounting and currency shit
currency = split[0].split(".")[0] + "."
number_format = ".".join(split[1:])
if "%" in number_format:
percentage = "%"
value = 100*value
number_format = number_format[0:-1]
representation = "%d"
if "." in number_format:
split = number_format.split(".")
num_decimals = len(split[1])
representation = "%0." + str(num_decimals) + "f"
try:
value = representation%value
except ValueError:
# Fall back to string
value = unicode(value, "utf-8", "ignore")
if "#," in number_format:
value = SplitThousands(value)
value = currency + value + percentage
else:
number_format = format_str.replace("\\", "")
number_format = number_format.replace("-", "-%").replace("/", "/%")
value = datetime.datetime(*value)
try:
value = value.strftime(number_format)
except (ValueError, TypeError):
value = value.strftime("%d.%m.%Y")
self.value = value
[docs] def SetupHyperlink(self, hyperlink):
"""
Sets up the cell text value in case it represents a hyperlink.
:param `hyperlink`: an instance of `xlrd.sheet.hyperlink`.
:note: If you are using version 0.7.1 or lower for `xlrd`, the *hyperlink*
parameter will always be ``None`` as this feature is available only in
`xlrd` 0.7.2 (SVN).
"""
url = (hyperlink.url_or_path and [hyperlink.url_or_path] or [hyperlink.textmark])[0]
self.tooltip = url
[docs] def IsHyperLink(self):
"""
Returns whether the cell text is representing a hyperlink.
:returns: ``True`` if the cell text represents a hyperlink, ``False``
otherwise.
"""
return self.tooltip is not None
[docs] def CombineAttr(self, attr):
"""
Combines the input attribute `attr` with the features of the L{XLSText} class.
:param `attr`: an instance of `wx.grid.GridCellAttr`.
"""
attr.SetAlignment(self.horizontal_alignment, self.vertical_alignment)
attr.SetTextColour(self.text_colour)
attr.SetFont(self.font)
[docs] def GetValue(self):
""" Returns the string representation of the cell text value. """
return self.value
[docs] def Draw(self, dc, rect):
"""
Actually draws the text value on a grid cell.
:param `dc`: an instance of `wx.DC`;
:param `rect`: an instance of `wx.Rect`, representing the cell rectangle.
"""
new_rect = wx.Rect(*rect)
xshift = yshift = 0
if self.rotation:
xshift = cos(self.rotation*pi/180)
yshift = sin(self.rotation*pi/180)
dc.SetTextForeground(self.text_colour)
dc.SetFont(self.font)
value = self.value
text_width, text_height = dc.GetTextExtent(value)
default_width = int(round(float(self.default_width)*text_width/256.0))
indentation = int(256.0*default_width/float(self.default_width))
if xshift == 0 and self.indent_level:
new_rect.SetLeft(new_rect.x + indentation)
else:
if self.horizontal_alignment == wx.ALIGN_LEFT:
new_rect.SetLeft(new_rect.x + 3)
elif self.horizontal_alignment == wx.ALIGN_RIGHT:
new_rect.SetWidth(new_rect.width - 1)
new_width = rect.width
if xshift > 0:
new_width = new_width/xshift
if self.shrink_to_fit:
font = FontFromFont(self.font)
point_size = font.GetPointSize()
while 1:
value = wordwrap(self.value, new_width, dc)
if "\n" not in value or point_size < 2:
break
point_size -= 1
font.SetPointSize(point_size)
dc.SetFont(font)
elif self.text_wrapped:
value = wordwrap(self.value, new_width, dc)
text_width, text_height, dummy = dc.GetMultiLineTextExtent(value)
if self.rotation:
if self.shrink_to_fit:
text_width, text_height = dc.GetTextExtent(value)
xc, yc = (rect.x+rect.width/2, rect.y+rect.height/2)
xp = xc - (text_width/2)*xshift - (text_height/2)*yshift
yp = yc + (text_width/2)*yshift - (text_height/2)*xshift
dc.DrawRotatedText(value, xp, yp, self.rotation)
else:
dc.DrawLabel(value, new_rect, self.horizontal_alignment|self.vertical_alignment)
[docs]class XLSRichText(XLSText):
"""
This is a class which holds information about the cell content, in terms
of actual cell value, font, text colour, alignment and formatting. In addition
to what L{XLSText} does, this class attempts to handle cells with rich text
content.
"""
[docs] def __init__(self, book, cell, xf_index, display_text=None, hyperlink=None, rich_text=None, default_width=10):
"""
Default class constructor.
:param `book`: an instance of the `xlrd.Book` class;
:param `cell`: an instance of `xlrd.sheet.Cell` class;
:param `xf_index`: an index into `xlrd.Book.xf_list`, which holds a
reference to the `xlrd.sheet.Cell` class (the actual cell for `xlrd`);
:param `display_text`: if Mark Hammonds' `pywin32` package is available,
this is the WYSIWYG cell content;
:param `hyperlink`: if this cell contains a hyperlink, it will be displayed
accordingly;
:param `rich_text`: if this cell contains text in rich text format, L{XLSGrid}
will do its best to render the text as rich text;
:param `default_width`: this is 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).
:note: If you are using version 0.7.1 or lower for `xlrd`, the *hyperlink*
parameter will always be ``None`` as this feature is available only in
`xlrd` 0.7.2 (SVN).
:note: If you are using version 0.7.1 or lower for `xlrd`, this class will
note be used by L{XLSGrid}.
:warning: This class currently supports only single-line non-rotated text,
and it discards properties like `shrink-to-fit` and `wrapping`.
"""
XLSText.__init__(self, book, cell, xf_index, display_text, hyperlink, default_width)
self.BuildChunks(book, xf_index, rich_text)
[docs] def BuildChunks(self, book, xf_index, rich_text):
"""
Splits the cell content accordingly to their rich text format index.
:param `book`: an instance of the `xlrd.Book` class;
:param `xf_index`: an index into `xlrd.Book.xf_list`, which holds a
reference to the `xlrd.sheet.Cell` class (the actual cell for `xlrd`);
:param `rich_text`: if this cell contains text in rich text format, L{XLSGrid}
will do its best to render the text as rich text.
"""
XFClass = book.xf_list[xf_index]
offset, index = rich_text[0]
if offset != 0:
new_tuple = (0, XFClass.font_index)
rich_text.insert(0, new_tuple)
value = self.value
rich_text.append((len(value), rich_text[-1][1]))
attributes = []
for indx in xrange(len(rich_text)-1):
offset_start, index_start = rich_text[indx]
offset_end, index_end = rich_text[indx+1]
chunk = value[offset_start:offset_end]
font = book.font_list[index_start]
ffont = self.CreateFont(font)
text_colour = book.colour_map[font.colour_index]
colour = self.CreateTextColour(text_colour)
ffont.escapement = font.escapement_type
attributes.append([chunk, ffont, colour])
self.attributes = attributes
[docs] def Measure(self, dc):
"""
Convenience method to measure the maximum height and total width of all
the chunks of text composing our rich text string.
:param `dc`: an instance of `wx.DC`.
"""
maxH = -1
full_width = 0
for chunk, font, colour in self.attributes:
dc.SetFont(font)
width, height, descent, leading = dc.GetFullTextExtent(chunk, font)
maxH = max(maxH, height-leading)
full_width += width
return maxH, full_width
[docs] def Draw(self, dc, rect):
"""
Actually draws all the chunks of text on a grid cell, one by one.
:param `dc`: an instance of `wx.DC`;
:param `rect`: an instance of `wx.Rect`, representing the cell rectangle.
"""
new_rect = wx.Rect(*rect)
text_width, text_height = dc.GetTextExtent(self.value)
default_width = int(round(float(self.default_width)*text_width/256.0))
indentation = int(256.0*default_width/float(self.default_width))
maxH, full_width = self.Measure(dc)
if self.indent_level:
new_rect.SetLeft(new_rect.x + indentation)
else:
if self.horizontal_alignment == wx.ALIGN_LEFT:
new_rect.SetLeft(new_rect.x + 3)
elif self.horizontal_alignment == wx.ALIGN_RIGHT:
new_rect.SetLeft(new_rect.x + (new_rect.width - full_width) - 1)
else:
space = int((new_rect.width - full_width)/2.0)
new_rect.SetLeft(new_rect.x + space)
new_rect.SetWidth(full_width+space)
if self.vertical_alignment == wx.ALIGN_TOP:
vspace = 0
elif self.vertical_alignment == wx.ALIGN_BOTTOM:
vspace = (new_rect.height - maxH - 1)
else:
vspace = int((new_rect.height - maxH)/2.0)
start = new_rect.x
y = new_rect.y
for chunk, font, colour in self.attributes:
dc.SetTextForeground(colour)
dc.SetFont(font)
width, height, descent, leading = dc.GetFullTextExtent(chunk, font)
if font.escapement > 0:
height = height*0.7
ypos = y-height+maxH+vspace
if font.escapement == 1:
ypos = ypos - maxH + height
dc.DrawText(chunk, start, ypos)
start += width
[docs]class XLSBackground(object):
"""
This is a class which holds information about the cell background, in terms
of background colour and background pattern (hatching).
"""
[docs] def __init__(self, book, xf_index):
"""
Default class constructor.
:param `book`: an instance of the `xlrd.Book` class;
:param `xf_index`: an index into `xlrd.Book.xf_list`, which holds a
reference to the `xlrd.sheet.Cell` class (the actual cell for `xlrd`).
"""
XFClass = book.xf_list[xf_index]
background = XFClass.background
background_colour = book.colour_map[background.background_colour_index]
pattern_colour = book.colour_map[background.pattern_colour_index]
fill_pattern = background.fill_pattern
self.CreateBackgroundColour(background_colour, pattern_colour, fill_pattern)
[docs] def CreateBackgroundColour(self, background_colour, pattern_colour, fill_pattern):
"""
Creates a suitable wxPython colour for the cell background starting from
a `xlrd` tuple representing this colour.
:param `background_colour`: a tuple representing the RGB components of the
cell background colour. If `background_colour` is ``None``, use the
default ``wx.SYS_COLOUR_WINDOW``;
:param `pattern_colour`: a tuple representing the RGB components of the
cell pattern colour;
:param `fill_pattern`: the pattern to use to draw hatches on top of the
background.
"""
if background_colour is not None:
background_colour = wx.Colour(*background_colour)
else:
background_colour = wx.SystemSettings.GetColour(wx.SYS_COLOUR_WINDOW)
if pattern_colour is not None:
pattern_colour = wx.Colour(*pattern_colour)
self.background_brush = wx.Brush(background_colour)
self.background_colour = background_colour
self.fill_brush = None
if fill_pattern <= 0:
return
r, g, b = pattern_colour
fill_image = eval("_xls_background_%02d.GetImage()"%fill_pattern)
fill_image.Replace(0, 0, 0, r, g, b)
r, g, b = background_colour.Red(), background_colour.Green(), background_colour.Blue()
fill_image.Replace(255, 255, 255, r, g, b)
fill_bitmap = fill_image.ConvertToBitmap()
self.fill_brush = wx.BrushFromBitmap(fill_bitmap)
[docs] def CombineAttr(self, attr):
"""
Combines the input attribute `attr` with the features of the L{XLSBackground} class.
:param `attr`: an instance of `wx.grid.GridCellAttr`.
"""
attr.SetBackgroundColour(self.background_colour)
return attr
[docs] def Draw(self, dc, rect):
"""
Actually draws the cell background and pattern hatching on a grid cell.
:param `dc`: an instance of `wx.DC`;
:param `rect`: an instance of `wx.Rect`, representing the cell rectangle.
"""
dc.SetClippingRect(rect)
dc.SetBackgroundMode(wx.SOLID)
dc.SetBrush(self.background_brush)
dc.SetPen(wx.TRANSPARENT_PEN)
dc.DrawRectangleRect(rect)
if self.fill_brush:
dc.SetBrush(self.fill_brush)
dc.SetBackgroundMode(wx.TRANSPARENT)
dc.DrawRectangleRect(rect)
dc.DestroyClippingRegion()
[docs]class XLSBorder(object):
"""
This is a class which holds information about a single cell border, in terms
of its location (top, left, bottom, right, diagonal), its colour, width and
shape.
"""
[docs] def __init__(self, location, line_style, border_colour, default_colour, diagonals):
"""
Default class constructor.
:param `location`: the actual border location (top, left, bottom, right,
diagonal);
:param `line_style`: the line style used by Excel to draw this border;
:param `border_colour`: the colour used by Excel to draw this border;
:param `default_colour`: the "magic" colour used by Excel to draw non-custom
border lines;
:param `diagonals`: a tuple containing whether or not to draw the up and down
diagonal borders.
"""
self.draw_priority = 2
if line_style == NO_LINE:
if border_colour == (0, 0, 0):
self.draw_priority = 0
border_colour = wx.Colour(*default_colour)
else:
self.draw_priority = 1
border_colour = wx.BLACK
self.pen = wx.Pen(border_colour, 1, wx.SOLID)
pen_style = THIN
else:
if border_colour == (0, 0, 0):
border_colour = wx.Colour(*default_colour)
self.draw_priority = 2
elif border_colour is None:
border_colour = wx.BLACK
self.draw_priority = 2
else:
border_colour = wx.Colour(*border_colour)
pen_width, pen_style = XF_PEN_STYLES[line_style]
if pen_width > 2:
self.draw_priority = 4
elif pen_width > 1:
self.draw_priority = 3
self.pen = wx.Pen(border_colour, pen_width, pen_style)
self.diagonals = diagonals
self.location = location
self.pen_style = pen_style
self.line_style = line_style
[docs] def Draw(self, dc, rect):
"""
Actually draws the cell border.
:param `dc`: an instance of `wx.DC`;
:param `rect`: an instance of `wx.Rect`, representing the cell rectangle.
"""
dc.SetBackgroundMode(wx.TRANSPARENT)
dc.SetPen(self.pen)
if self.location == DIAGONAL:
self.DrawDiagonals(dc, rect)
else:
self.DrawBorder(dc, rect)
[docs] def DrawDiagonals(self, dc, rect):
"""
Actually draws the cell diagonal border.
:param `dc`: an instance of `wx.DC`;
:param `rect`: an instance of `wx.Rect`, representing the cell rectangle.
"""
diag_up, diag_down = self.diagonals
if diag_down:
xstart, ystart = rect.GetTopLeft()
xend, yend = rect.GetBottomRight()
if self.line_style == DOUBLE:
dc.DrawLine(xstart+2, ystart, xend, yend-2)
dc.DrawLine(xstart, ystart+2, xend-2, yend)
else:
dc.DrawLine(xstart, ystart, xend, yend)
if diag_up:
xstart, ystart = rect.GetBottomLeft()
xend, yend = rect.GetTopRight()
if self.line_style == DOUBLE:
dc.DrawLine(xstart, ystart-2, xend-2, yend)
dc.DrawLine(xstart+2, ystart, xend, yend+2)
else:
dc.DrawLine(xstart, ystart, xend, yend)
[docs] def DrawBorder(self, dc, rect):
"""
Actually draws the cell border (one of left, right, bottom, top).
:param `dc`: an instance of `wx.DC`;
:param `rect`: an instance of `wx.Rect`, representing the cell rectangle.
"""
pen_width = self.pen.GetWidth()
location = self.location
new_rect = wx.Rect(*rect)
x, y, w, h = new_rect
line_style = self.line_style
shift = 0
if pen_width == 2:
shift = pen_width - 1
if pen_width > 2:
shift = pen_width - 2
if location == BOTTOM:
if self.draw_priority < 2:
return
if line_style == DOUBLE:
dc.DrawLine(x, y+h-1, x+w, y+h-1)
dc.DrawLine(x, y+h+1, x+w, y+h+1)
else:
dc.DrawLine(x+1, y+h, x+w, y+h)
elif location == TOP:
if line_style == DOUBLE:
dc.DrawLine(x, y-1, x+w, y-1)
dc.DrawLine(x, y+1, x+w, y+1)
else:
dc.DrawLine(x+1, y+shift, x+w, y+shift)
elif location == LEFT:
if line_style == DOUBLE:
dc.DrawLine(x-1, y, x-1, y+h)
dc.DrawLine(x+1, y, x+1, y+h)
else:
dc.DrawLine(x+shift, y+1, x+shift, y+h)
elif location == RIGHT:
if self.draw_priority < 2:
return
if line_style == DOUBLE:
dc.DrawLine(x+w-1, y, x+w-1, y+h)
dc.DrawLine(x+w+1, y, x+w+1, y+h)
else:
dc.DrawLine(x+w+1, y+1, x+w+1, y+h)
[docs]class XLSBorderFactory(object):
"""
This is a factory class which holds information about all the borders in a
cell. Its implementation and use is merely to simplify the handling of the
different cell borders (left, top, bottom, right, diagonal).
"""
[docs] def __init__(self, book, border, default_colour):
"""
Default class constructor.
:param `book`: an instance of the `xlrd.Book` class;
:param `border`: an instance of `xlrd.formatting.XFBorder` class;
:param `default_colour`: the "magic" colour used by Excel to draw non-custom
border lines.
"""
borders = {}
diagonals = border.diag_up, border.diag_down
for label, location in XF_BORDER_STYLES.items():
line_style = getattr(border, "%s_line_style"%label)
colour_index = getattr(border, "%s_colour_index"%label)
border_colour = book.colour_map[colour_index]
border_class = XLSBorder(location, line_style, border_colour, default_colour, diagonals)
borders[location] = border_class
self.draw_priority = sorted(borders.values(), key=attrgetter('draw_priority'))
[docs] def Draw(self, dc, rect):
"""
Actually draws all the cell borders based on their drawing priority.
:param `dc`: an instance of `wx.DC`;
:param `rect`: an instance of `wx.Rect`, representing the cell rectangle.
:note: The drawing priority is assigned depending on if the border is a
custom one or not. Customized borders are drawn last.
"""
for border in self.draw_priority:
border.Draw(dc, rect)
[docs]class XLSComment(object):
"""
This is a class which holds information about the content of the "comment
window" (aka note) in Excel.
:note: If Mark Hammonds' `pywin32` package is not available, this class can
not be used.
"""
[docs] def __init__(self, comment):
"""
Default class constructor.
:param `comment`: the actual text contained in the Excel cell comment (note).
"""
self.comment = comment
[docs] def Draw(self, dc, rect):
"""
Actually draws a small red triangle in the top-right corder of the cell
to indicate that a comment is present.
:param `dc`: an instance of `wx.DC`;
:param `rect`: an instance of `wx.Rect`, representing the cell rectangle.
"""
right = rect.GetTopRight()
points = [wx.Point(right.x-5, right.y),
right,
wx.Point(right.x, right.y+5)]
dc.SetBrush(wx.RED_BRUSH)
dc.SetPen(wx.RED_PEN)
dc.DrawPolygon(points)
[docs]class XLSCell(object):
"""
This is a class which holds information about a single cell in L{XLSGrid}.
It stores (via auxiliary classes), all details about cell background, text,
font, colours and borders.
"""
[docs] def __init__(self, book, cell, xf_index, xls_text, xls_comment, hyperlink, rich_text, default_width, default_colour):
"""
Default class constructor.
:param `book`: an instance of the `xlrd.Book` class;
:param `cell`: an instance of `xlrd.sheet.Cell` class;
:param `xf_index`: an index into `xlrd.Book.xf_list`, which holds a
reference to the `xlrd.sheet.Cell` class (the actual cell for `xlrd`);
:param `xls_text`: the actual WYSIWYG cell text, if available;
:param `xls_comment`: the cell comment (note), if any;
:param `hyperlink`: an instance of `xlrd.sheet.hyperlink`;
:param `rich_text`: if this cell contains text in rich text format, L{XLSGrid}
will do its best to render the text as rich text;
:param `default_width`: this is 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);
:param `default_colour`: the "magic" colour used by Excel to draw non-custom
border lines.
:note: If you are using version 0.7.1 or lower for `xlrd`, the *hyperlink*
parameter will always be ``None`` as this feature is available only in
`xlrd` 0.7.2 (SVN).
:note: If you are using version 0.7.1 or lower for `xlrd`, the `rich_text`
parameter will always be ``None`` as this feature is available only in
`xlrd` 0.7.2 (SVN).
:note: if Mark Hammonds' `pywin32` package is not available, the `xls_text`
parameter will almost surely not be the WYSIWYG representation of the cell
text.
:note: If Mark Hammonds' `pywin32` package is not available, the `xls_comment`
parameter will always be ``None``.
"""
self.size = 1, 1
self.comment = None
self.hyperlink = None
self.SetupCell(book, cell, xf_index, xls_text, xls_comment, hyperlink, rich_text, default_width, default_colour)
[docs] def SetupCell(self, book, cell, xf_index, xls_text, xls_comment, hyperlink, rich_text, default_width, default_colour):
"""
Actually sets up the L{XLSCell} class. This is an auxiliary method to
avoid cluttering the L{__init__} method.
:param `book`: an instance of the `xlrd.Book` class;
:param `cell`: an instance of `xlrd.sheet.Cell` class;
:param `xf_index`: an index into `xlrd.Book.xf_list`, which holds a
reference to the `xlrd.sheet.Cell` class (the actual cell for `xlrd`);
:param `xls_text`: the actual WYSIWYG cell text, if available;
:param `xls_comment`: the cell comment (note), if any;
:param `hyperlink`: an instance of `xlrd.sheet.hyperlink`;
:param `rich_text`: if this cell contains text in rich text format, L{XLSGrid}
will do its best to render the text as rich text;
:param `default_width`: this is 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);
:param `default_colour`: the "magic" colour used by Excel to draw non-custom
border lines.
:note: If you are using version 0.7.1 or lower for `xlrd`, the *hyperlink*
parameter will always be ``None`` as this feature is available only in
`xlrd` 0.7.2 (SVN).
:note: If you are using version 0.7.1 or lower for `xlrd`, the `rich_text`
parameter will always be ``None`` as this feature is available only in
`xlrd` 0.7.2 (SVN).
:note: if Mark Hammonds' `pywin32` package is not available, the `xls_text`
parameter will almost surely not be the WYSIWYG representation of the cell
text.
:note: If Mark Hammonds' `pywin32` package is not available, the `xls_comment`
parameter will always be ``None``.
"""
cvalue = cell.value
self.raw_value = cvalue
if rich_text:
self.text = XLSRichText(book, cell, xf_index, xls_text, hyperlink, rich_text, default_width)
else:
self.text = XLSText(book, cell, xf_index, xls_text, hyperlink, default_width)
self.background = XLSBackground(book, xf_index)
XFClass = book.xf_list[xf_index]
border = XFClass.border
self.borders = XLSBorderFactory(book, border, default_colour)
if xls_comment:
self.comment = XLSComment(xls_comment)
self.attr = None
[docs] 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
attr = gridlib.GridCellAttr()
attr.SetRenderer(XLSRenderer(self))
attr.SetSize(*self.size)
attr.SetOverflow(True)
self.attr = attr
self.attr.IncRef()
return self.attr
[docs] def GetValue(self):
""" Returns the actual WYSIWYG representation of the cell value. """
return self.text.GetValue()
[docs] def SetValue(self, value):
"""
Sets the actual WYSIWYG representation of the cell value.
:param `value`: the current text value to insert in the cell.
:note: This method is currently unused as everything is handled inside the L{XLSText} class.
:see: L{GetValue}
"""
self.value = value
[docs] def SetCellSize(self, rows, cols):
"""
Sets the size of the cell.
Specifying a value of more than 1 in `rows` or `cols` will make the cell
at (`row`, `col`) span the block of the specified size, covering the other
cells which would be normally shown in it. Passing 1 for both arguments
resets the cell to normal appearance.
:param `rows`: number of rows to be occupied by this cell, must be >= 1;
:param `cols`: number of columns to be occupied by this cell, must be >= 1.
"""
self.size = (rows, cols)
[docs] def GetComment(self):
"""
Returns the cell comment, if any.
:returns: an instance of L{XLSComment}.
:note: If Mark Hammonds' `pywin32` package is not available, this method
always returns ``None``.
"""
return self.comment
[docs]class XLSRenderer(gridlib.PyGridCellRenderer):
"""
This class is responsible for actually drawing the cell in the grid.
"""
[docs] def __init__(self, cell):
"""
Default class constructor.
:param `cell`: an instance of L{XLSCell}.
"""
gridlib.PyGridCellRenderer.__init__(self)
self.cell = cell
[docs] def Draw(self, grid, attr, dc, rect, row, col, isSelected):
"""
Draw the given cell on the provided `dc` inside the given rectangle using
default or selected state corresponding to the `isSelected` value.
:param `grid`: an instance of `wx.grid.Grid`;
:param `attr`: an instance of `wx.grid.GridCellAttr`;
:param `dc`: an instance of `wx.DC`;
:param `rect`: an instance of `wx.Rect`, representing the cell rectangle;
:param `row`: the row in which this cell lives;
:param `col`: the column in which this cell lives;
:param `isSelected`: ``True`` if the cell is selected, ``False`` otherwise.
"""
# clear the background
dc.SetBackgroundMode(wx.SOLID)
cell = self.cell
cell.background.Draw(dc, rect)
if cell.borders:
cell.borders.Draw(dc, rect)
cell.text.Draw(dc, rect)
if cell.comment:
cell.comment.Draw(dc, rect)
if isSelected:
gdc = wx.GCDC(dc)
sys_colour = wx.SystemSettings.GetColour(wx.SYS_COLOUR_HIGHLIGHT)
brush_colour = wx.Colour(sys_colour.Red(), sys_colour.Green(), sys_colour.Blue(), 90)
gdc.SetBrush(wx.Brush(brush_colour))
gdc.SetPen(wx.TRANSPARENT_PEN)
gdc.DrawRectangleRect(rect)
[docs]class XLSTable(gridlib.PyGridTableBase):
"""
The almost abstract base class for grid tables.
A grid table is responsible for storing the grid data and, indirectly, grid
cell attributes. The data can be stored in the way most convenient for the
application but has to be provided in string form to `wx.grid.Grid`.
"""
[docs] def __init__(self, grid, cells, rows, cols):
"""
Default class constructor.
:param `grid`: an instance of `wx.grid.Grid`;
:param `cells`: a Python dictionary. For every key `(row, col)`, the
corresponding value is an instance of L{XLSCell};
:param `rows`: the number of rows in the table;
:param `cols`: the number of columns in the table.
"""
# The base class must be initialized *first*
gridlib.PyGridTableBase.__init__(self)
self.cells = cells
self.dimens = (rows, cols)
[docs] def GetNumberCols(self):
""" Returns the number of columns in the table. """
return self.dimens[1]
[docs] def GetNumberRows(self):
""" Returns the number of rows in the table. """
return self.dimens[0]
[docs] def GetValue(self, row, col):
"""
Returns the cell content for the specified row and column.
:param `row`: the row in which this cell lives;
:param `col`: the column in which this cell lives.
"""
cell = self.cells[(row, col)]
return cell.GetValue()
[docs] def SetValue(self, row, col, value):
"""
sets the cell content for the specified row and column.
:param `row`: the row in which this cell lives;
:param `col`: the column in which this cell lives;
:param `value`: the new value to assign to the specified cell.
"""
cell = self.cells[(row, col)]
cell.SetValue(value)
[docs] def GetAttr(self, row, col, kind):
"""
Return the attribute for the given cell.
:param `row`: the row in which this cell lives;
:param `col`: the column in which this cell lives;
:param `kind`: the kind of the attribute to return.
"""
cell = self.cells[(row, col)]
return cell.GetAttr()
[docs] def GetRawValue(self, row, col):
"""
Returns the "raw" value for the cell content.
:param `row`: the row in which this cell lives;
:param `col`: the column in which this cell lives.
"""
cell = self.cells[(row, col)]
return cell.raw_value
[docs]class XLSGrid(gridlib.Grid):
"""
L{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 L{XLSGrid}).
L{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.
"""
[docs] def __init__(self, parent):
"""
Default class constructor.
:param `parent`: the grid parent window. Must not be ``None``.
"""
gridlib.Grid.__init__(self, parent)
self.SetMargins(0, 0)
self.SetDefaultCellBackgroundColour(parent.GetBackgroundColour())
self.SetDefaultCellOverflow(True)
self.tip_window = None
self.tip_shown = False
[docs] def DestroyTip(self):
"""
If a comment window or a tooltip over a hyperlink have been created, this
method destroys them.
"""
if self.tip_window:
try:
self.tip_window.GetTipWindow().Destroy()
except wx.PyDeadObjectError:
pass
del self.tip_window
self.tip_window = None
if self.tip_shown:
self.GetGridWindow().SetToolTipString("")
self.GetGridWindow().SetCursor(wx.NullCursor)
self.tip_shown = False
[docs] def InstallGridHint(self):
"""
Auxiliary method used to bind a ``wx.EVT_MOTION`` event to L{XLSGrid}.
"""
self.prev_rowcol = [None, None]
def OnMouseMotion(event):
"""
Handles a the ``wx.EVT_MOTION`` events for L{XLSGrid}.
:param `event`: a `wx.MouseEvent` event to be processed.
"""
# evt.GetRow() and evt.GetCol() would be nice to have here,
# but as this is a mouse event, not a grid event, they are not
# available and we need to compute them by hand.
position = event.GetPosition()
x, y = self.CalcUnscrolledPosition(position)
row = self.YToRow(y)
col = self.XToCol(x)
if [row, col] != self.prev_rowcol and row >= 0 and col >= 0:
self.prev_rowcol[:] = [row, col]
self.DestroyTip()
cell = self.cells[(row, col)]
rect = self.CellToRect(row, col)
comment = cell.GetComment()
window = self.GetGridWindow()
if cell.text.IsHyperLink():
window.SetCursor(wx.StockCursor(wx.CURSOR_HAND))
window.SetToolTipString(cell.text.tooltip)
self.tip_shown = True
if not comment:
return
if comment:
self.tip_window = TransientPopup(window, comment, wx.GetMousePosition())
event.Skip()
self.GetGridWindow().Bind(wx.EVT_MOTION, OnMouseMotion)
[docs] def PopulateGrid(self, book, sheet, display_texts, comments):
"""
This is the main method of this class, and it is used to actually create
the cells, size the columns and rows, merging cells, etc...
:param `book`: an instance of the `xlrd.Book` class;
:param `sheet`: an instance of the `xlrd.sheet` class;
:param `display_texts`: if Mark Hammonds' `pywin32` package is available,
this is the WYSIWYG cell content for all the cells in the Excel worksheet;
:param `comments`: if Mark Hammonds' `pywin32` package is available,
this is a nested list of cell comments (notes) for all the cells in the
Excel worksheet.
"""
self.BeginBatch()
nrows = sheet.nrows
ncols = sheet.ncols
default_width, default_height = self.GetDefaultFontData(book)
default_colour = self.GetGridLineColour()
hyperlinks, rich_text_list = {}, {}
if hasattr(sheet, "hyperlink_map"):
# New in xlrd version 0.7.2 from SVN
hyperlinks = sheet.hyperlink_map
if hasattr(sheet, "rich_text_runlist_map"):
# New in xlrd version 0.7.2 from SVN
rich_text_list = sheet.rich_text_runlist_map
self.cells = {}
for i in xrange(nrows):
for j in xrange(ncols):
hyperlink = rich_text = None
if (i, j) in hyperlinks:
hyperlink = hyperlinks[(i, j)]
if (i, j) in rich_text_list:
rich_text = rich_text_list[(i, j)]
self.FormatCell(book, sheet, i, j, display_texts, comments, hyperlink, rich_text, default_width, default_colour)
self.table = XLSTable(self, self.cells, nrows, ncols)
self.SetTable(self.table)
row_height = sheet.default_row_height
col_width = sheet.defcolwidth
for i in xrange(nrows):
if i in sheet.rowinfo_map:
current = sheet.rowinfo_map[i].height
else:
current = sheet.default_row_height
row_height = int(round(float(default_height)*current/256.0))
self.SetRowSize(i, row_height)
for j in xrange(ncols):
if j in sheet.colinfo_map:
current = sheet.colinfo_map[j].width
else:
current = sheet.defcolwidth
col_width = int(round(float(default_width)*current/256.0))
self.SetColSize(j, col_width)
for merged in sheet.merged_cells:
rlo, rhi, clo, chi = merged
if rlo >= 0 and rlo < len(self.cells) and clo >= 0:
self.cells[(rlo, clo)].SetCellSize(rhi-rlo, chi-clo)
self.EnableEditing(False)
self.EnableGridLines(False)
self.EndBatch()
self.ForceRefresh()
self.InstallGridHint()
[docs] def FormatCell(self, book, sheet, row, col, display_texts, comments, hyperlink, rich_text, default_width, default_colour):
"""
Processes the creation of a single cell (an instance of L{XLSCell}).
:param `book`: an instance of the `xlrd.Book` class;
:param `sheet`: an instance of the `xlrd.sheet` class;
:param `row`: the row in which this cell lives;
:param `col`: the column in which this cell lives;
:param `display_texts`: if Mark Hammonds' `pywin32` package is available,
this is the WYSIWYG cell content for all the cells in the Excel worksheet;
:param `comments`: if Mark Hammonds' `pywin32` package is available,
this is a nested list of cell comments (notes) for all the cells in the
Excel worksheet;
:param `hyperlink`: if this cell contains a hyperlink, it will be displayed
accordingly;
:param `rich_text`: if this cell contains text in rich text format, L{XLSGrid}
will do its best to render the text as rich text;
:param `default_width`: this is 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);
:param `default_colour`: the "magic" colour used by Excel to draw non-custom
border lines.
:note: If you are using version 0.7.1 or lower for `xlrd`, the *hyperlink*
parameter will always be ``None`` as this feature is available only in
`xlrd` 0.7.2 (SVN).
:note: If you are using version 0.7.1 or lower for `xlrd`, the `rich_text`
parameter will always be ``None`` as this feature is available only in
`xlrd` 0.7.2 (SVN).
:note: If Mark Hammonds' `pywin32` package is not available, the `display_texts`
and `comments` parameter will be two empty nested lists.
"""
cell = sheet.cell(row, col)
xf_index = sheet.cell_xf_index(row, col)
xls_text, xls_comment = display_texts[row][col], comments[row][col]
gridCell = XLSCell(book, cell, xf_index, xls_text, xls_comment, hyperlink, rich_text, default_width, default_colour)
self.cells[(row, col)] = gridCell
[docs] def GetDefaultFontData(self, book):
"""
Returns suitable width and height (in pixels) starting from Excel's own
measurements (in characters, whatever that means).
:param `book`: an instance of the `xlrd.Book` class.
:returns: a `default_width` and `default_height` in pixels, 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).
"""
font = book.font_list[0]
style, bold, underline = wx.FONTSTYLE_NORMAL, wx.NORMAL, False
if font.italic:
style = wx.FONTSTYLE_ITALIC
if font.underline_type > 0:
underline = True
if font.weight > 600:
bold = wx.BOLD
family = XF_FONT_FAMILY[font.family]
name = font.name
size = int(font.height/20.0)
dc = wx.ClientDC(self)
font = wx.Font(size, family, style, bold, underline, name.encode())
dc.SetFont(font)
width, height, descent, leading = dc.GetFullTextExtent("0", font)
return width, height + descent - leading
[docs]class TransientPopup(STT.SuperToolTip):
"""
This is a sublass of L{SuperToolTip} and it is used to display a
"comment-window" on the cells containing a comment (a note).
:note: If Mark Hammonds' `pywin32` package is not available, this class is
never invoked.
"""
[docs] def __init__(self, grid_window, comment, position):
"""
Default class constructor.
:param `grid_window`: the actual window representing the grid;
:param `comment`: an instance of L{XLSComment}, containing the
text for this comment;
:param `position`: the position at which we pop up the comment
window (currently unused).
"""
STT.SuperToolTip.__init__(self, grid_window)
xls_comment = comment.comment
split = xls_comment.split(":")
header, rest = split[0], split[1:]
rest = ":".join(rest)
dc = wx.ClientDC(grid_window)
rest = wordwrap(rest, 400, dc)
self.SetHeader(header)
self.SetMessage(rest)
self.SetTarget(grid_window)
self.SetDrawHeaderLine(True)
self.SetStartDelay(100000)
self.SetEndDelay(100000)
self.ApplyStyle("Office 2007 Blue")
self.SetDropShadow(True)
self.DoShowNow()
grid_window.SetFocus()
