CODEWITHSUNDEEP
pythonexcelpython-3.xopenpyxl
macaroni
macaroni

Reputation: 151

Creating new sheet in excel and writing data with openpyxl

I have already existing excel file and at the beginning in my code i import data from first sheet.

Now i cant write points from two variables (xw, yw) into new sheet in excel to two colmuns (A1-A[Q] and B1-B[Q]).

xw and yw are arrays consisting of float numbers (example: 1.223, 2.434 etc.).

Q = len(xw)
wb.create_sheet('Points')
sheet2 = wb.get_sheet_by_name('Points')
for q in range(1,Q):
    sheet2["A[q]"]=xw[q]
    sheet2["B[q]"]=yw[q]
wb.save('PARAMS.xlsx')

EDIT:

I want to fill third column with zeros (C1-CQ). My code is below, but its start from C2, not C1. I did sheet2[f"C{1}"]=0 but it looks bad. What is the solution?

Q = len(xw)
zw= np.zeros(Q)
sheet2 = wb.create_sheet('Points')
sheet2[f"A{1}"]=0
sheet2[f"B{1}"]=T_r
sheet2[f"C{1}"]=0
for i, row in enumerate(sheet2.iter_rows(min_row=2, max_col=3, max_row=Q+1)):
    row[0].value = xw[i]
    row[1].value = yw[i]
    row[2].value = zw[i]
wb.save('PARAMS.xlsx')

Upvotes: 1

Views: 3405

Answers (2)

Charlie Clark
Charlie Clark

Reputation: 19537

Where possible try and and avoid your own counters. openpyxl uses 1-based indexing for rows and columns so it can be confusing to mix this with Python's standard 0-based indexing. You can usually avoid this by looping directly over iterables like lists and arrays.

for idx, a, b in enumerate(zip(xw, yw), 1):
   ws.cell(row=idx, column=1, value=a)
   ws.cell(row_idx, column=2, value=b)

Upvotes: 0

Tomerikoo
Tomerikoo

Reputation: 19431

You are trying to acces literally the cell "A[q]" which of course does not exist. Change it to f"A{q}" (The same to B of course).

Also, openpyxl uses 1-based indexing so it means you will skip your first elements from the lists. Therefore you should do:

for q in range(Q):
    sheet2[f"A{q+1}"] = xw[q]
    sheet2[f"B{q+1}"] = yw[q]

Alternatively, you could use the API of openpyxl to access the cells using iter_rows and enumerate:

for i, row in enumerate(sheet2.iter_rows(min_row=1, max_col=2, max_row=Q)):
    row[0].value = xw[i]
    row[1].value = yw[i]

Notice that create_sheet also returns the sheet created so you can simply do: 

sheet2 = wb.create_sheet('Points')

Upvotes: 1

Related Questions