how to fill values to one single column efficiently in excel file using pywin32?

Question

I know that I could fill rows using a source in an array using Pywin32 like

import win32com.client as win32
excel = win32.gencache.EnsureDispatch('Excel.Application')
excel.Visible = True
wb = excel.Workbooks.Add()
ws = wb.Worksheets('Sheet1')
ws.Range("A4:D4").Value = [i for i in range(1,5)]

But in single column case, When I try this like

ws.Range("A1:A4").Value = [i for i in range(1,5)]

I got four 1 in column 'A', but what I wanted is 1, 2, 3, 4 in column 'A'

Is there similar way to deal with column?

If no, what's the most efficient way to fill multiple cells once?

Answer 1

What you need to do is

import win32com.client as win32
excel = win32.gencache.EnsureDispatch('Excel.Application')
excel.Visible = True
wb = excel.Workbooks.Add()
ws = wb.Worksheets('Sheet1')
ws.Range("A1:A4").Value = [[i] for i in range(1,5)]

What you did before was try to fit [1, 2, 3, 4] into a column. However, when it gets converted to the excel formatting an array is always converted into a row. So when you create a 2D array you create an array of rows which is how excel views a column. So what you want is [[1],[2],[3],[4]]. Which is what the code above generates.

EDIT: I realize this may not be the best wording in the world. If you don't get it just ask and I will try to come up with a better explanation.