8

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.

CodeNoob
  • 85
  • 1
  • 1
  • 6

2 Answers2

19

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
Felix Zumstein
  • 6,737
  • 1
  • 30
  • 62
  • @ Felix. Thank you! That worked perfectly and also thanks for the explanation as to why it returns horizontal. That makes sense. I read over the docs prior to posting the question, but I guess I didn't catch that. I am also a very new to learning Python. I was going to start learning VBA, but I ran across your xlwings and was very intrigued by it. Very good work and thanks for your contribution to the Python community. – CodeNoob Jun 22 '15 at 13:22
  • 1
    BTW...I tried to upvote your answer, but looks like I don't have enough reputation points to do so. Thank you though. – CodeNoob Jun 22 '15 at 13:22
0

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
West
  • 2,350
  • 5
  • 31
  • 67
  • Does this work in retaining the source formatting (for example in excel when we use paste with source formatting) ,I found that `.value` leaves out the single quote and format of the souce column copied from – Scope Nov 10 '20 at 13:19
  • @Scope For me it retained the source format, I didnt test with single quotes. You can also retain source format using pastespecial. So the above becomes `sht2.range("A1").api.PasteSpecial("-4122")` -4122 represents the paste type and I get that from the microsoft docs for pastespecial – West Nov 11 '20 at 00:37
  • Ok -4122 is the paste special ( the obe with a square icon when right click on excel) – Scope Nov 11 '20 at 04:12
  • This didn't work for me , when `.api` was used the code ran non stop and if .api was not used gave an error , and also `PasteSpecial` is not s valid attribute to `Range` it said , could you pls recheck the code you mentioned / the one which worked for you personally if you happen to remember – Scope Nov 12 '20 at 13:52
  • @Scope yes the code works for me i just checked. Oh actually -4122 only copies the source format only and not values. To copy everything use -4104. And yes you'll get that attribute error if you dont use .api. The code can seem to hang sometimes if you were doing something in excel like if you were in the middle of typing something then you ran the code while typing cursor is still active, other than that no issues. – West Nov 13 '20 at 02:46