CODEWITHSUNDEEP
pythonexcelrangewin32com
James
James

Reputation: 21

Creating array and writing to excel column using Range function using Python

I would like to create a data container on the fly in my python script (based on calculations), and then write this to a column within excel using win32com client and the range() function. I can successfully do this for a row, but cannot do for a column. The hard coded code I basically want to do is like following:

import win32com.client as win32
from win32com.client import Dispatch

Excel=Dispatch('Excel.Application')
wb = Excel.ActiveWorkbook
ws = wb.ActiveSheet
data_container = [5],[6],[7],[8],[9]
ws.Range(ws.Cells(11,9),ws.Cells(15,9)).Value = (data_container)

The tricky part for me, is creating a data_container that I can create on the fly. I know there is a lot on stackoverflow about tuples, data dictionaries and lists, but I cannot work it out and am confused (if I see the warning "tuple is immutable" one more time!!!!!!). Whenever I create something like the following, it just outputs the first item in all of the cells (i.e below fills all cells with 5).

data_container = []
data_container.append(5)
data_container.append(6)
data_container.append(7)
data_container.append(8)
data_container.append(9)
ws.Range(ws.Cells(11,9),ws.Cells(15,9)).Value = (data_container)

I could loop through and write to each cell, however don't want to do this due to time limitation. I know there are probably other python addons you can use, but I want this to be usable on many computers that do not necessarily have all these addons that can prevent people using my script.........I like the simplicity of using the win32com setup and have most items working except for this. Ideally, I would like to have a number of columns stored in 1 dictionary/list/matrix/array......and be able to write to one range referencing a certain part of the data container. For example:

ws.Range(ws.Cells(11,9),ws.Cells(15,9)).Value = (data_container[0])

Where data_container[0] contains [5],[6],[7],[8],[9]......

The ultimate of what I want to do, is to read a number of columns (not necessarily in order) into a data container, make modifications, and then write back to a number of columns (again not necessarily in order).

I appreciate all the building blocks for this are probably on stack overflow at present, i just havn't been able to pull them together over the few days I've been trying to nut this out. Any help is appreciated.

Upvotes: 0

Views: 2681

Answers (1)

James
James

Reputation: 21

I was fortunate to get someone from work to spend some time helping me, but might be useful for anyone else as reference. Below seems to work. Issue was creating the array/list structure. I think fundamentally below I have created a 3D array/list? Below code creates this structure all with 5, then modifies only two cells and writes back to two separate columns. This might be obvious to others, but wasn't to me.

import win32api
import os
import sys
import win32com.client as win32
from win32com.client import Dispatch


if __name__ == "__main__":
Excel=Dispatch('Excel.Application')
wb = Excel.ActiveWorkbook
ws = wb.ActiveSheet

data_work = [[[5] for i in range(10)] for j in range(10)]
data_work[0][2] = [500]
data_work[1][4] = [45]
ws.Range(ws.Cells(1,9),ws.Cells(9,9)).Value = (data_work[0])
ws.Range(ws.Cells(1,12),ws.Cells(9,12)).Value = (data_work[1])

Upvotes: 2

Related Questions