Geek Answers Handbook

How to insert a Numpy array in Excel

I have several files that I process using Numpy and SciPy, but I need to provide an Excel file. How can I efficiently copy / paste a huge numpy array in Excel?

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

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

+6
source share
4 answers

My best solution here would be to turn the array into a string and then use win32clipboard to send it to the clipboard. This is not a cross-platform solution, but, again, Excel is not always available on any platform.

Excel uses tabs ( \t ) to indicate a column change, and \r\n to indicate a row 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 tested this code with random form arrays (1000 10000), and it seems the biggest bottleneck is passing data to a function. (When I add the print statement at the beginning of the function, I still have to wait a bit before it prints anything.)

EDIT: The previous paragraph dealt with my experience with Python Tools for Visual Studio. In this environment, it looks like the expression about printing is delayed. In the direct command line interface, the bottleneck is in a loop, as expected.

+12
source

Today you can also use xlwings . It is open source and fully compatible with Numpy and Pandas DataFrames arrays.

+2
source

If I need to process several files uploaded in python and then parse in excel, I would probably do some tools using xlwt

However, I can offer my recipe Insert python data into the mailing list for any changes, complaints or feedback. It does not use third-party libraries and should be a cross platform.

+1
source

You can also look into the pyxll project.

0
source

More articles:


All Articles