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, Dec 24 '19 at 02:58
then you need to read the excel file with data ...after add more data to file excel?..sheet — GiovaniSalazar, Dec 24 '19 at 03:17

score 1 · Answer 1 · answered Dec 24 '19 at 03:24

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 '19 at 03:24

Ali Atiia

139
7

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 – Retsied Dec 24 '19 at 08:35
@Retsied That extra 0 is the index; you can [disable it](https://docs.xlwings.org/en/stable/converters.html#pandas-series-converter) by setting it to False to prevent it from being written out to the sheet. Alternatively, you can [set one of your own columns](https://github.com/quantopian/qgrid/issues/122#issuecomment-363347274) to be the index. – Ali Atiia Dec 24 '19 at 08:58

score 0 · Answer 2 · edited Dec 24 '19 at 09:26

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 '19 at 09:26

Charlie Clark

18,477
4
49
55

answered Dec 24 '19 at 03:24

Nithin Varghese

893
1
6
28

1

Where does load_workbook come from? And the array I have is of varying size, so would prefer to only specify the starting cell. – Retsied Dec 24 '19 at 08:09

score 0 · Answer 3 · answered Dec 24 '19 at 09:41

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.

Copy Python list variable into existing xlsx excel file

3 Answers3