python xlwings - copy and paste ranges

python excel xlwings
CodeNoob picture CodeNoob · Jun 19, 2015 · Viewed 11.5k times · Source

My first question on this site.

I am using xlwings with python 2.7. I want to copy the values in range 'A1:A6' from Sheet1 to cells 'A1:A6' in Sheet2. My code is as follows:

> my_range = Range('Sheet1','A1:A6').value
> 
> Range('Sheet2','A1:A6').value = my_range

When I run this code, however, it puts the values of my_range into cells 'A1:F1' on Sheet 2 instead of cells 'A1:A6' in Sheet 2.

Answer

Felix Zumstein picture Felix Zumstein · Jun 19, 2015

Columns (as well as Rows) are returned as simple ("horizontal") lists, see the docs here. Why? First of all, it mimics the behavior of numpy 1d arrays. Second, it's usually what you'll want, e.g. you can directly iterate over a column or check if a value exists without having to unpack a nested list first.

Now, in your case, you'll obviously want to preserve the 2d shape which you can do (again similar to numpy) as follows:

my_values = Range('Sheet1','A1:A6', atleast_2d=True).value 
Range('Sheet2','A1:A6').value = my_values

Also note that what you call my_range should rather be called my_values as it holds a list, not an xlwings Range object.

Update:

Since v0.9.0, the syntax changed to:

import xlwings as xw
wb = xw.Book('mybook.xlxs')
my_values = wb.sheets['Sheet1'].range('A1:A6').options(ndim=2).value 
wb.sheets['Sheet2'].range('A1:A6').value = my_values

Related questions

Read data in Excel column into Python list

I am using python xlwings to read a column of data in Excel 2013. Column A is populated with numbers. To import this column into a python list py_list, I have the following code; import xlwings as xw wb = xw.…

Read More
How do I call an Excel macro from Python using xlwings?

I've read the API docs for xlwings, and played around with Workbook and Sheet objects in the interpreter, but I can't figure out how to call a macro from Python. How do I use xlwings to call an Excel macro …

Read More
Open a Workbook with XLWINGS without making it visible

I am starting to use XLWings (not that I like Excel, at all, but it is something I HAVE TO do). The thing is that I cannot find the way to make Python open a Workbook without showing it. It …

Read More