How to paste a Numpy array to Excel

Question

I have multiple files which I process using Numpy and SciPy, but I am required to deliver an Excel file. How can I efficiently copy/paste a huge numpy array to Excel?

I have tried to convert to Pandas' DataFrame object, which has the very usefull function to_clipboard(excel=True), but I spend most of my time converting the array into a DataFrame.

I cannot simply write the array to a CSV file then open it in excel, because I have to add the array to an existing file; something very hard to achieve with xlrd/xlwt and other Excel tools.

Answer 1

My best solution here would be to turn the array into a string, then use win32clipboard to sent it to the clipboard. This is not a cross-platform solution, but then again, Excel is not avalable on every platform anyway.

Excel uses tabs (\t) to mark column change, and \r\n to indicate a line change.

The relevant code would be:

import win32clipboard as clipboard

def toClipboardForExcel(array):
    """
    Copies an array into a string format acceptable by Excel.
    Columns separated by \t, rows separated by \n
    """
    # Create string from array
    line_strings = []
    for line in array:
        line_strings.append("\t".join(line.astype(str)).replace("\n",""))
    array_string = "\r\n".join(line_strings)

    # Put string into clipboard (open, clear, set, close)
    clipboard.OpenClipboard()
    clipboard.EmptyClipboard()
    clipboard.SetClipboardText(array_string)
    clipboard.CloseClipboard()

I have tested this code with random arrays of shape (1000,10000) and the biggest bottleneck seems to be passing the data to the function. (When I add a print statement at the beginning of the function, I still have to wait a bit before it prints anything.)

EDIT: The previous paragraph related my experience in Python Tools for Visual Studio. In this environment, it seens like the print statement is delayed. In direct command line interface, the bottleneck is in the loop, like expected.

Answer 2

    import pandas as pd
    pd.DataFrame(arr).to_clipboard()

I think it's one of the easiest way with pandas package.