Python Xlwings - Copy and Paste Ranges
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...
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.
2 Answers
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
For anyone interested in doing a copy-paste that includes the source formatting you can use the api as shown:
import xlwings as xw
wb = xw.Book('mybook.xlxs')
sht = wb.sheets['Sheet1']
sht2 = wb.sheets['Sheet2']
sht.range('A1:A6').api.copy
sht2.range("A1").api.select
sht2.api.paste
wb.app.api.CutCopyMode=False
Marcus Vance is a cybersecurity auditor and technology writer dedicated to educating the public about online safety, data privacy regulations, enterprise security, and emerging cyber threats.
