1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
|
.. wxPython Phoenix documentation
This file was generated by Phoenix's sphinx generator and associated
tools, do not edit by hand.
Copyright: (c) 2011-2018 by Total Control Software
License: wxWindows License
.. include:: headings.inc
.. module:: wx.lib.agw.xlsgrid
.. currentmodule:: wx.lib.agw.xlsgrid
.. highlight:: python
.. _wx.lib.agw.xlsgrid:
==========================================================================================================================================
|phoenix_title| **wx.lib.agw.xlsgrid**
==========================================================================================================================================
:class:`~wx.lib.agw.xlsgrid.XLSGrid` is a class based on :class:`grid.Grid` that can be used to faithfully
reproduce the appearance of a Microsoft Excel spreadsheet (one worksheet per
every instance of :class:`XLSGrid`).
Description
===========
:class:`XLSGrid` is a class based on :class:`grid.Grid` that can be used to faithfully
reproduce the appearance of a Microsoft Excel spreadsheet (one worksheet per
every instance of :class:`XLSGrid`).
:class:`XLSGrid` is a completely owner-drawn control, and it relies on the power of
:class:`grid.PyGridTableBase` and :class:`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:
:class:`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 :class:`XLSGrid`.
.. warning::
If Mark Hammonds' `pywin32` package is not available, the formatting
capabilities of :class:`XLSGrid` are severely limited; for instance, you won't
probably get the exact WYSIWYG between the Excel spreadsheet and :class:`XLSGrid`.
.. warning::
:class:`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 :class:`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,
:class:`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 :class:`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 :mod:`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 :class:`XLSGrid` with a cursor changing and a tooltip on that cell.
* Cell merging: merged cells in the Excel spreadsheet will be correctly handled
by :class:`XLSGrid`.
* Columns and rows sizes: :class:`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.App(0)
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. :class:`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. :class:`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
===================
:class:`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
===================
:class:`XLSGrid` is distributed under the wxPython license.
Latest Revision: Andrea Gavana @ 27 Dec 2012, 21.00 GMT
Version 0.4
|function_summary| Functions Summary
====================================
================================================================================ ================================================================================
:func:`~wx.lib.agw.xlsgrid.FontFromFont` Creates a copy of the input `font`.
:func:`~wx.lib.agw.xlsgrid.ReadExcelCOM` Reads and Excel spreadsheet (a single worksheet) using Mark Hammonds' `pywin32`
:func:`~wx.lib.agw.xlsgrid.SplitThousands` Splits a general float on thousands. GIGO on general input.
================================================================================ ================================================================================
|
|class_summary| Classes Summary
===============================
================================================================================ ================================================================================
:ref:`~wx.lib.agw.xlsgrid.Excel` A simple class that holds a COM interface to Excel.
:ref:`~wx.lib.agw.xlsgrid.TransientPopup` This is a sublass of :class:`SuperToolTip` and it is used to display a
:ref:`~wx.lib.agw.xlsgrid.XLSBackground` This is a class which holds information about the cell background, in terms
:ref:`~wx.lib.agw.xlsgrid.XLSBorder` This is a class which holds information about a single cell border, in terms
:ref:`~wx.lib.agw.xlsgrid.XLSBorderFactory` This is a factory class which holds information about all the borders in a
:ref:`~wx.lib.agw.xlsgrid.XLSCell` This is a class which holds information about a single cell in :class:`XLSGrid`.
:ref:`~wx.lib.agw.xlsgrid.XLSComment` This is a class which holds information about the content of the "comment
:ref:`~wx.lib.agw.xlsgrid.XLSGrid` :class:`XLSGrid` is a class based on :class:`grid.Grid` that can be used to faithfully
:ref:`~wx.lib.agw.xlsgrid.XLSRenderer` This class is responsible for actually drawing the cell in the grid.
:ref:`~wx.lib.agw.xlsgrid.XLSRichText` This is a class which holds information about the cell content, in terms
:ref:`~wx.lib.agw.xlsgrid.XLSTable` The almost abstract base class for grid tables.
:ref:`~wx.lib.agw.xlsgrid.XLSText` This is a class which holds information about the cell content, in terms
================================================================================ ================================================================================
|
.. toctree::
:maxdepth: 1
:hidden:
wx.lib.agw.xlsgrid.Excel
wx.lib.agw.xlsgrid.TransientPopup
wx.lib.agw.xlsgrid.XLSBackground
wx.lib.agw.xlsgrid.XLSBorder
wx.lib.agw.xlsgrid.XLSBorderFactory
wx.lib.agw.xlsgrid.XLSCell
wx.lib.agw.xlsgrid.XLSComment
wx.lib.agw.xlsgrid.XLSGrid
wx.lib.agw.xlsgrid.XLSRenderer
wx.lib.agw.xlsgrid.XLSRichText
wx.lib.agw.xlsgrid.XLSTable
wx.lib.agw.xlsgrid.XLSText
Functions
------------
.. function:: FontFromFont(font)
Creates a copy of the input `font`.
:param `font`: an instance of :class:`wx.Font`.
.. function:: 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.
.. function:: 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
|
