Welcome, guest | Sign In | My Account | Store | Cart

A smooth way to paste data you are working with in python into a spreadsheet. Put into the system clipboard, select a cell and do ctrl-v (at least with ms office and libre-office).

Python, 99 lines
 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
# clipbrd.py

"""
# Examples:

>>> import clipbrd as cb
>>> D = [range(5) for i in range(3)]
>>> D
[[0, 1, 2, 3, 4], [0, 1, 2, 3, 4], [0, 1, 2, 3, 4]]
>>> print cb._buildstr(D)
0	1	2	3	4
0	1	2	3	4
0	1	2	3	4
>>> print cb._buildstr(D, transpose=True)
0	0	0
1	1	1
2	2	2
3	3	3
4	4	4
>>> cb.clipboard_put(D, transpose=True)
# Now paste into your favourite spread sheet, probably by ctrl-v.

>>> print cb._buildstr(458.78, replace=('.', ','))
458,78
>>> print cb._buildstr(range(5))
0	1	2	3	4
>>> print cb._buildstr(range(5), transpose=True)
0
1
2
3
4
>>> print cb._buildstr([('1A', '1B'), ('2A', '2B')])
1A	1B
2A	2B
>>> print cb._buildstr([('1A', '1B'), ('2A', '2B')], transpose=True)
1A	2A
1B	2B

"""

import Tkinter as tk

def _buildstr(D, transpose=False, replace=None):
    """Construct a string suitable for a spreadsheet.

    D: scalar, 1d or 2d sequence
        For example a list or a list of lists.

    transpose: Bool
        Transpose the data if True.

    replace: tuple or None
        If tuple, it is two strings to pass to the replace
        method. ('toreplace', 'replaceby')

    """

    try:
        D[0]
    except (TypeError, IndexError):
        D = [D]
    try:
        D[0][0]
    except (TypeError, IndexError):
        D = [D]

    if transpose:
        D = zip(*D)
        
    if not replace:
        rows = ['\t'.join([str(v) for v in row]) for row in D]
    else:
        rows = ['\t'.join([str(v).replace(*replace)
                           for v in row]) for row in D]
    S = '\n'.join(rows)
    return S

def clipboard_put(D, transpose=False, replace=None):
    """Construct a string suitable for a spreadsheet and put it into the
    clipboard.

    D: scalar, 1d or 2d sequence
        For example a list or a list of lists.

    transpose: Bool
        Transpose the data if True.

    replace: tuple or None
        If tuple, it is two strings to pass to the replace
        method. ('toreplace', 'replaceby')

    """
    s = _buildstr(D, transpose, replace)
    r = tk.Tk()
    r.withdraw()
    r.clipboard_clear()
    r.clipboard_append(s)
    r.destroy()

I do a lot of work in both python and spreadsheets. At times I feel I should be able to paste my data into the spreadsheet without the use of the excellent xlwt for example. So here goes something.

The recipe is limited to python 2.x as it is written. I didn't make the switch yet.

Created by Tomas Nordin on Sun, 14 Sep 2014 (GPL3)
Python recipes (4591)
Tomas Nordin's recipes (6)

Required Modules

Other Information and Tasks

  • Licensed under the GPL 3
  • Viewed 10059 times
  • Revision 3 (updated 9 years ago)