1

I have a 2d list in a Python utility that writes to a table using PyQt. I also want to copy all of the data from that list to the clipboard, with the caveat that it must be formatted for insertion into an excel document or a table in Word (as excel-formatted data generally is).

The question, then, is two-fold: 1) How do I go about formatting the data, stringwise, to match Excel's standards, and 2) how do I copy that to the clipboard?

From what I understand, Excel-formatted data, when viewed directly as plaintext, is XML-formatted in some way, but I don't know how to find out exactly what that XML looks like. I have not found a text editor that copies the formatting information upon pasting; they all just drop the formatting and paste the cell contents as plaintext. Copying to the clipboard once I have that should be pretty straightforward, I think.

Danny_ds
  • 11,201
  • 1
  • 24
  • 46
Clinton J
  • 1,975
  • 3
  • 19
  • 31
  • For copying to the clipboard see https://stackoverflow.com/q/579687/5987. I'm partial to my own answer https://stackoverflow.com/a/25678113/5987. – Mark Ransom Jun 26 '18 at 21:07

2 Answers2

2

The easiest way is probably to use TAB seperated values, for example:

15.117  0.681   39.871      533.47
14.771  0.755   47.559      652.65
11.849  0.682   47.561      660.76
10.92   0.63    46.908      658.26
10.087  0.613   47.649      676.23

This pastes well in Word and Excel (however, tabs are replaced with spaces in the example above - choose 'edit' to copy tha sample data).

For a real table in Word, you could format your data as a html table.

It is also possible to put multiple formats on the clipboard, but I'm not sure how this would be done in Python.

Mark Ransom's answer uses the Windows API to copy to the clipboard. This allows you to set the clipboard format.

See this for the standard formats.

For the html format you'll need to add some header data.

Danny_ds
  • 11,201
  • 1
  • 24
  • 46
  • so, this works fine for excel. however if I try to paste to a text editor (with the exception of MSWord), it ignores the \n for newline, and goes from the last character of the last column of a row straight into the first char of the first element of the next row, all on one line. Why is that? – Clinton J Jun 26 '18 at 22:31
  • 1
    @ClintonJ Try using `\r\n` instead (`0x0d` `0x0a`). – Danny_ds Jun 26 '18 at 22:56
0

You're actually asking two questions, so I'll limit myself to the first one about the clipboard. You should probably ask another question about the behavior of Excell since it's completely unrelated to Python.

The best way to send text to the clipboard seems to be with Tk since it's usually shipped with Python so it's way more portable:

from Tkinter import Tk
r = Tk()
r.withdraw()
r.clipboard_clear()
r.clipboard_append('i can has clipboardz?')
r.update() # now it stays on the clipboard after the window is closed
r.destroy()

This code was written by atomizer here

I also suggest you searching a bit more before asking. I had no idea on how to do this until now.

Good luck with the Excell thing!

rbertoche
  • 31
  • 2
  • So I can just write a string that includes the formatting Excel needs and send that to the clipboard? I know how to copy a string to the clipboard already, I just wasn't sure if the same method would work with the specific thing I'm trying to do. – Clinton J Jun 26 '18 at 21:28
  • I'll try it out and tell you my results as soon as I get back to Windows. It's probably just a matter of putting the right whitespace By the way, won't a CSV or something alike solve your problem? https://en.wikipedia.org/wiki/Comma-separated_values – rbertoche Jun 29 '18 at 18:20