I've seen a few answers to this problem, but in VBA and C#---nothing in python. In python 3, I'm trying to do the following:
I'm using both tkinter and openpyxl for my project and through testing I've gotten some results, but it is not what I want. The closest that I've gotten is below.
# If I first go into excel,
# and copy a few columns within a single row with different data in them,
# then I run the following code:
import tkinter as tk
root = tk.Tk()
paste = root.clipboard_get()
print(paste)
# Then I manually copy the print output to the clipboard,
# and then go back to excel and paste,
# excel gives the following warning message:
# "The data you're pasting isn't the same size as your selection. do you want to paste anyways?"
# And after pressing OK, it seems as though the data is pasted properly.
This is OK, and the print(paste) string might help me create the appropriate format for the initial variable of strings that I want to generate, but I need a solution that will not cause Excel to make this warning sign pop up every time.
If anyone can provide some insight into this, I would greatly appreciate it. Also, the solution needs to be via python not through modifications to Excel. Secondly, the solution is not about appending/writing the data directly to Excel (via Openpyxl, etc) but about getting the data to the clipboard in the appropriate format (I'm using Excel 2016).
Thanks!!
Edit:
I have also tried using the "ctypes solution" presented here by user kichik.
To make this solution easier to work with, I downloaded an app called "InsideClipboard" which lets me easily see the format ID of each types of formats that the clipboard "copies" data in.
Using kichik's ctype solution, I checked to see if manually copying the print output of the different formats stored in the clipboard would let me manually CTRL+V to Excel in the original format, but this failed. The original "copy" was of multiple columns of strings in a single rows of strings from Excel, and the manual "paste" of the individual formats back into excel kept all the strings into a single cell (with the exception of HTML Format that created multiple rows of data--also wrong). The different formats included CF_LOCALE (16), CF_TEXT (1), CF_OEMTEXT (7), CF_UNICODETEXT (13), AND HTML Format (49384).
So, still: no solution.
Edit2:
I realized that I could create strings in python with actual tabs pressed in between the string, instead of using \t, and it worked to create a single string that would place the data into different columns of a single row when pasted into Excel.
Also, I realized that if I CTRL+V directly into Excel (not on the Row heading), on the actual row in which I want to paste the data, I no longer get the Excel "warning message". So, using this work around might work. If no one has any input, then this simple approach might be good enough.
However, I would like to be able to simply click on the row heading instead of the first relevant row cell to paste the data without the Excel warning popup. Ideas are still welcome, and it would be best to have it all done via python (without modifications to Excel as the app may be run on different Windows OS PCs).
So: possible simple solution, but not perfect.
Edit3:
So I've worked out a solution based on Edit2. The Excel "warning" popup still happens if Excel is opened as an app on the working computer; however, if the Excel file is opened and editable via an online processor, then the user can highlight the row heading and paste without generating the Excel "warning" popup. This works in my specific case, although the better solution would be to have the copied data not generate the Excel "warning" popup in any situation. Regardless, if no one knows how to prevent the Excel warning popup through python alone, then I can still work with this method.
Here's my solution (note that the spaces in the long string are actually 'tabs'):
import pyperclip
# t variables are defined prior to the below code
# note: the " " parts in the data string are actually tabs
data = t1 + " " + t2 + " " + " " + t3 + " " + t4 + " " + t5 + " " + t6 + " " + t7 + " " + " " + "t8" + " " + " " + " " + " " + " " + " " + t9 + " " + t10 + " " + t11 + " " + t12 + " " + " " + " " + " " + " " + " " + t13
pyperclip.copy(data)
print("clipboard:", data)
# now manually pressing CTRL+V while inside Excel (online processor) will
# paste the various t variables in the appropriate cells designated by
# the " " tabs in between. Of note, this works for columns of a single
# row as I didn't need multiple rows of data for my task.
Below is code to create a 5 row by 3 column grid of Entries in tkinter. Pressing the copy button copies the content of the Entries as a tab / newline separated string to the clipboard which can then be pasted into excel.
import tkinter as tk
from tkinter import ttk
ROWS = 5
COLS = 3
root = tk.Tk()
rows = [] # Container for Entry widgets.
# Create and grid the Entry widgets.
for row in range( ROWS ):
temp = []
for col in range( COLS ):
temp.append( ttk.Entry(root, width = 10 ))
temp[-1].grid( row = row, column = col )
rows.append( temp )
def entries_to_lists( rows ):
list_out = []
for row in rows:
temp = []
for col in row:
temp.append( col.get() )
list_out.append( temp )
return list_out
def string_out( rows ):
""" Prepares a '\t', '\n' separated string to send to the clipboard. """
out = []
for row in rows:
out.append( '\t'.join( row )) # Use '\t' (tab) as column seperator.
return '\n'.join(out) # Use '\n' (newline) as row seperator.
def do_copy():
data = entries_to_lists( rows )
root.clipboard_clear()
root.clipboard_append( string_out( data )) # Paste string to the clipboard
root.update() # The string stays on the clipboard after the window is closed
ttk.Button( text = " Copy ", command= do_copy ).grid( column = 1 )
# Button to trigger the copy action.
root.title("Copy to Excel Test")
root.geometry("400x200+10+10")
root.mainloop()
This copied into Excel 2013 in Windows 10 with no warning messages.
string_out will generate a suitable string from any list of lists of strings, a 2d list.