Writing A List To New Excel Xlsx With Dataframes

I am having some trouble finding the best way to write a list to a loaded excel sheet and then saving the result as an xlsx. What I want my code to do is to take the lists that I h

Solution 1:

That error means that Python could not find a parameter named row in the definition of cell. If I'm not mistaken this is the xlrd module. Let's take a look at the API documentation for Sheet.cell() which you are attempting to call here.

cell(rowx, colx)

Cell object in the given row and column.

It appears that you've simply misnamed the parameters. Changing the line to the following should fix this.

s.cell(rowx = r, colx = 1).value = test

It goes without saying that Python cannot make guesses as to what you meant to type, so whenever you get an error about something not existing when you're sure it does, like the parameter names here, make sure to read the documentation and check for typos. Also, in the future post all relevant info you can find, such as the function definition and the names of the modules you are using.

Solution 2:

Here is one version with no extra installs on top of anaconda. It is not keeping styling, but that you can fix with a copy/'paste values' back to original xlsx.

Most excel manipulators have issues with keeping the original file intact. There are ways around that as well, but if you want it to be waterproof, you basically end up with a specific solution for you, or more or less recode all libraries out there, so it's not worth the effort.

Pandas can be a bit tricky to get right when extending existing dataframes, but there are always several alternative ways to do it. Here it's done with assign, so then one only needs to make sure that the dataframe's rowcount is long enough for what one wants to add.

import pandas as pd

# read the exceldf = pd.read_excel('Geen titel 1.xlsx') # there are options to choose sheetprint('original df')
print(df)
# your data is not valid python syntax so let's assume it's strings
col_test = ['1L', '2L', '3L', '4L', '5L']
new_idx = range(max(len(col_test), len(df.index)))
df = df.reindex(new_idx) # now it will accommodate different lengthsprint('reindexed df')
print(df)
df = df.assign(new_col=col_test) # new column added at right sideprint('modified df')
print(df)
df.to_excel('the_new.xlsx')

The printouts:

original df
   a  b
01c12  d
23  e
reindexed df
     a    b
01.0c12.0    d
23.0    e
3NaNNaN4NaNNaN
modified df
     a    b new_col
01.0c1L12.0    d      2L23.0    e      3L3NaNNaN4L4NaNNaN5L