Copy Python list variable into existing xlsx excel file

Question

I'm new to Python, so please excuse my ignorance. I have tried several different bits of code using openpyxl and pandas, however, can't get anything to work.

What I need is to copy the text of an existing list-variable in Python (which is an array of file paths), and paste this into an existing xlsx worksheet at a given cell.

For example, given the list-variable in Python ["apple", "orange", "grape"], I need cells A2, A3, and A4 of Sheet 1 to read the same. Any help is much appreciated!

import pandas as pd
import os

folder = "C:\\Users\\user\\Documents\\temp"
x = []
for path in os.listdir(folder):
if path.endswith(".png"):
    full_path = os.path.join(folder, path)
    x.append(full_path)

fn = r"C:\\Users\\user\\Documents\\test.xlsx"
df = pd.read_excel(fn, header = None)
df2 = pd.DataFrame(x)
writer = pd.ExcelWriter(fn)
df2.to_excel(writer, startcol=0, startrow=1, header=None, index=False)
writer.save()

What this gets me is the correct information but seemingly overwrites any existing data. All other cell contents and sheets are now gone and the newly added data is in an otherwise blank Sheet1. I need to maintain the existing spreadsheet as-is, and only add this info starting at a given cell on a given sheet.

Have also tried the following with xlwings, which imports the correct data into the existing sheet while maintaining all other data, but replaces the first cell with a 0. How do I get rid of this extra 0 from the dataframe?

filename = "C:\\Users\\user\\Documents\\test.xlsx"
df = pd.DataFrame(x)
wb = xw.Book(filename)
ws = wb.sheets('Sheet1')
ws.range('A2').options(index=False).value = df
wb = xw.Book(filename)

EDIT: The above xlwings code appears to work if I replace DataFrame with Series. SOLUTION:

import xlwings as xw
filename = "C:\\Users\\user\\Documents\\test.xlsx"
df = pd.Series(x)
wb = xw.Book(filename)
ws = wb.sheets('Sheet1')
ws.range('A2').options(index=False).value = df
wb = xw.Book(filename)

Could you please make it more clear by adding your desired output? — hemanta
– hemanta, Commented Dec 24, 2019 at 2:58
then you need to read the excel file with data ...after add more data to file excel?..sheet — GiovaniSalazar
– GiovaniSalazar, Commented Dec 24, 2019 at 3:17

Ali Atiia · Accepted Answer · 2019-12-24 03:24:10Z

1

I need to maintain the existing spreadsheet as-is

Open the excel file in append mode, take a look at this answer for example (other answers in the same thread are useful as well).

answered Dec 24, 2019 at 3:24

Ali Atiia

1397 bronze badges

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

2 Comments

Retsied Over a year ago

Hmm, after looking through that thread and using xlwings, I now have the array copied to my existing sheet while maintaining all other data but there exists an extra 0 in the dataframe? How do I get rid of this? See above code

Ali Atiia Over a year ago

@Retsied That extra 0 is the index; you can disable it by setting it to False to prevent it from being written out to the sheet. Alternatively, you can set one of your own columns to be the index.

Charlie Clark · Accepted Answer · 2019-12-24 09:26:11Z

0

If you want to reference excel sheet by its indexing eg("A1") you can use openpyxl

from openpyxl import load_workbook

workbook = load_workbook(filename="sample.xlsx")
ll = ["apple", "orange", "grape"]
sheet = workbook.active

sheet["A2"] = ll[0]
sheet["A3"] = ll[1] 
sheet["A4"] = ll[2]

workbook.save(filename="sample.xlsx")

Guess this solves your problem

edited Dec 24, 2019 at 9:26

Charlie Clark

19.7k4 gold badges56 silver badges64 bronze badges

answered Dec 24, 2019 at 3:24

Nithin Varghese

9321 gold badge7 silver badges31 bronze badges

1 Comment

Retsied Over a year ago

Where does load_workbook come from? And the array I have is of varying size, so would prefer to only specify the starting cell.

moo · Accepted Answer · 2019-12-24 09:41:49Z

0

Depending on how your data is formatted, this may work, if you are satisfied appending to the next empty column in the sheet. With this proviso, the pandas library is really good for handling tabular data and can work with Excel files (using the openpyxl library).

It looks like you've installed pandas already but, for the benefit of others reading, make sure you've installed pandas and openpyxl first:

pip install pandas openpyxl

Then try:

import pandas as pd
df = pd.read_excel('filename.xlsx')
df['new_column_name'] = listname
df.to_excel('output_spreadsheet.xlsx')

I personally find that more readable than using openpyxl directly.

answered Dec 24, 2019 at 9:41

moo

2,3462 gold badges23 silver badges37 bronze badges

Collectives™ on Stack Overflow

Copy Python list variable into existing xlsx excel file

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related