在spyder中从excel到python代码进行手动复制粘贴的想法 [英] Ideas for manual copy-paste from excel to python code in spyder

问题描述

我经常使用excel工作表，我只想将其中一列特定的数字复制到我的python脚本中以进行绘图.这必须手动完成，因为它始终是不同的文件，列和行.

I often work with excel sheets of which I want to copy just one specific column of numbers into my python script for plotting purposes. This has to be done manually since it is always a different file, columns and rows.

要使用numpy数组，我需要以逗号结尾的数据以这种方式创建python数组(我必须添加空格，否则stackexchange会将其张贴在一行中):

To use numpy arrays, I need the data with a trailing comma to create a python array in this way (I had to add spaces, otherwise stackexchange would post it in a line):

myArray=np.array([
    1,
    2,
    3,
    4,
    6
)]

因此，复制带有excel中数字的列后，我必须手动添加逗号.如果我有大量数据，我将在Excel中添加一列，在其中添加所有逗号，并将带有逗号的数字一起复制.但是我觉得应该有一个更好的方法.

So after copying the column with the numbers from excel, I have to add the commas manually. If I have a lot of data, I'll add a column in Excel where I add alle the commas and copy the numbers with the commas together. However I feel like there should be a better way to do this.

你们知道python可能吞没我的数据而没有逗号的方法吗?例如

Do you guys know a way that python might swallow my data without commas? E.g.

myData = someFunction(
   1
   2
   3
   4
   6
)

还是您有另一个想法，如何添加更优雅的逗号?我正在将Spyder与Python 3配合使用.

Or do you have another Idea how to add the commas more elegant? I'm using Spyder with Python 3.

谢谢！

推荐答案

编辑:如果要求是复制一列或一行而不是网格.下面的 in _ 可与replace一起使用以产生逗号分隔的字符串.

Edit: If the requirement is to copy one column or row and not a grid. in_ below can be used with replace to produce a comma separated string.

# First copy a column from a spread sheet to the clipboard.
# in_() will return the values separated by newlines '\n'. 
# replace these by comma
in_()
# Out[21]: '3\n13\n23\n33\n43\n53\n63\n73\n83\n93\n103\n113\n123\n'
in_().replace('\n', ',')
# Out: '3,13,23,33,43,53,63,73,83,93,103,113,123,'

# If copying a row the copied separator is tab '\t'
# Copy a row in a spreadsheet
in_()
# Out: '60\t61\t62\t63\t64\t65\t66\t67\t68\n'    
in_().replace('\t', ',')[:-1] # [-1] removes the newline character.
# Out[25]: '60,61,62,63,64,65,66,67,6'

我编写了 in _()和 from_grid()来将电子表格中的数据复制到我想在python终端中使用的变量中.他们让用户将电子表格中的区域复制到剪贴板. in _()将片段作为字符串返回. from_grid()会将其转换为字符串列表.

I wrote the in_()and from_grid() to copy data from spreadsheets into variables I wanted to use in my python terminal. They let a user copy a region in a spreadsheet to the clipboard. in_() will return the clip as a string. from_grid() will return this converted to a list of lists of strings.

import tkinter as tk
import numpy as np

def in_():
    """
    Returns the contents of the clipboard as text.
    Usage:  txt = in_()
    """
    clip=tk.Tk()
    clip.withdraw()
    temp=clip.selection_get(selection="CLIPBOARD")
    clip.destroy()
    return temp

def from_grid(delimit_row="\n", delimit_cell="\t"):
    """
    Returns a list of lists copied from the clipboard.
    Usage:  grid=from_grid(delimit_row="\t", delimit_cell="\n")
        grid is a list of lists 
        [  [ r00, r01, r02, ... ],
           [ r10, r11, r12, ... ],
            ....
        ]
    by defaut:  the row delimiter is a newline, "\n"
                the cell delimiter is a tab character, "\t"

    This will paste a copied region of a spreadsheet into a list of lists.
    """
    txt=in_()
    rows=txt.split(delimit_row)
    ret=[]
    for row in rows:
        temp=row.split(delimit_cell)
        ret.append(temp)
    return ret[:-1]  # A final empty last row is appended.
                     # This loses it.

import numpy as np

def to_floats( data ):
    result= []
    for row in data:
        temp = []
        for item in row:
            temp.append(float(item))
        result.append(temp)
    return np.array(result)

arr = to_floats( from_grid() )

这并不能完全满足您的要求，但是提供了一种将电子表格数据导入python进行处理的方法.

This doesn't do exactly what you've asked for but gives a way of getting the spreadsheet data into python where it can be processed.

在python控制台中运行此命令将使用户打印结果，然后将其复制到脚本中.

Running this in a python console will let the user print the results, which can then be copied into a script.

可能有更整齐的方法来做到这一点.周围有一些图书馆，Pyperclip是其中之一，但我从未使用过.

There may be neater ways to do it. There are some Libraries around, Pyperclip is one but I've never used it.

这篇关于在spyder中从excel到python代码进行手动复制粘贴的想法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！