python add data to existing excel cell Win32com

Question

Assume I have A1 as the only cell in a workbook, and it's blank. I want my code to add "1" "2" and "3" to it so it says "1 2 3"

As of now I have:

NUMBERS = [1, 2, 3, 4, 5]
ThisSheet.Cells(1,1).Value = NUMBERS

this just writes the first value to the cell. I tried

ThisSheet.Cells(1,1).Value = Numbers[0-2]

but that just puts the LAST value in there. Is there a way for me to just add all of the data in there? This information will always be in String format, and I need to use Win32Com.

update: I did

stringVar = ', '.join(str(v) for v in LIST)

UPDATE:this .join works perfectly for the NUMBERS list. Now I tried attributing it to another list that looks like this

LIST=[Description Good\nBad, Description Valid\nInvalid]

If I print LIST[0] The outcome is

Description Good
Bad

Which is what I want. But if I use .join on this one, it prints

('Description Good\nBad, Description Valid\nInvalid')

so for this one I need it to print as though I did LIST[0] and LIST[1]

If you want all of the values from NUMBERS then you seem to need ThisSheet.Cells(1,1).Value = ' '.join([str(_) for _ in NUMBERS]). If you just want the first three values then use ThisSheet.Cells(1,1).Value = ' '.join([str(_) for _ in NUMBERS[:3]]). — Bill Bell
– Bill Bell, Commented Dec 16, 2016 at 13:56
This is essentially what I did with my stringVar, and it works, except it prints the special characters instead of just the items in the list. So it prints \n instead of writing it to a new line. It also prints (' at the beginning and ') at the end of each item. — nico
– nico, Commented Dec 16, 2016 at 14:13
Well it does that when I just print it to the command line, anyway. Thanks for your reply though! — nico
– nico, Commented Dec 16, 2016 at 14:17

otorrillas · Accepted Answer · 2016-12-16 15:03:39Z

2

So if you want to put each number in a different cell, you would do something like:

it = 1
for num in NUMBERS:
    ThisSheet.Cells(1,it).Value = num
    it += 1

Or if you want the first 3 numbers in the same cell:

ThisSheet.Cells(1,it).Value = ' '.join([str(num) for num in NUMBERS[:3]])

Or all of the elements in NUMBERS:

ThisSheet.Cells(1,1).Value = ' '.join([str(num) for num in NUMBERS])

EDIT

Based on your question edit, for string types containing \n and assuming every time you find a newline character, you want to jump to the next row:

# Split the LIST[0] by the \n character
splitted_lst0 = LIST[0].split('\n')
# Iterate through the LIST[0] splitted by newlines
it = 1
for line in splitted_lst0:
     ThisSheet.Cells(1,it).Value = line
     it += 1

If you want to do this for the whole LIST and not only for LIST[0], first merge it with the join method and split it just after it:

joined_list = (''.join(LIST)).split('\n')

And then, iterate through it the same way as we did before.

edited Dec 16, 2016 at 15:03

answered Dec 16, 2016 at 13:58

otorrillas

4,8351 gold badge24 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

nico Over a year ago

using this would work great if I didn't have new lines in my list. As of now it's printing \n instead of moving to a new line, and (' at the beginning of each item as well as ') at the end of each item. Any way of getting around that?

otorrillas Over a year ago

@nico do you mean that you have newlines in NUMBERS? Is NUMBERS of String type?

nico Over a year ago

apologies. At first I was trying to get the numbers working, but I tried doing the same thing to a second List that has new lines. Every list contains strings, and no ints. The second List is like List = [Description Good \n Bad, Description Valid \n Invalid] I can't provide the exact List due to restrictions, but when I print List[0] it prints Description Good and Bad on new lines. When I use .join, it prints ('Description Good\nBad'),('Description Valid\nInvalid')

otorrillas Over a year ago

Can you please update the question with this data, please? So using string arrays and with the expected behaviour when found a new line character.

Collectives™ on Stack Overflow

python add data to existing excel cell Win32com

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related