0

I want to make a script that writes a certain text to the A1 cell of every excel file inside a folder. I'm not sure how to get Python to open every file one by one, make changes to A1, and then overwrite save the original file. 

import os
import openpyxl

os.chdir('C:/Users/jdal/Downloads/excelWorkdir')
folderList = os.listdir()
for file in in os.walk():
    for name in file:
        if name.endswith(".xlsx" and ".xls")
        wb = openpyxl.load_workbook()
        sheet = wb.get_sheet_by_name('Sheet1')

sheet['A1'] = 'input whatever here!'
sheet['A1'].value
wb.save()
Ersatzkaffe
  • 1
  • 1
    what is wrong with the code you currently have? are you getting an error or a different output than you are expecting? – db702 Feb 05 '19 at 17:01
  • Possible duplicate of [how to write to a new cell in python using openpyxl](https://stackoverflow.com/questions/31395058/how-to-write-to-a-new-cell-in-python-using-openpyxl) – dhilmathy Feb 05 '19 at 17:02
  • I was wondering how to get python to save to every file in the folder instead of opening one file by name, i.e wb = openpyxl.load_workbook() – Ersatzkaffe Feb 05 '19 at 18:41

2 Answers2

0

I see following errors in your code:

You have an error in .endswith, it should be

name.endswith((".xlsx",".xls"))

i.e. it needs to be feed with tuple of allowed endings.

Your if lacks : at end and your indentation seems to be broken.

You should deliver one argument to .load_workbook and one argument to .save, i.e. name of file to read/to write.

Daweo
  • 31,313
  • 3
  • 12
  • 25
0

I would iterate through the folder and use pandas to read the files as temporary data frames. From there, they are easily manipulable.

Assuming you are in the relevant directory: 

import pandas as pd
import os

files = os.listdir()

for i in range(len(files)):
    if files[i].endswith('.csv'):

        # Store file name for future replacement
        name = str(files[i])

        # Save file as dataframe and edit cell
        df = pd.read_csv(files[i])
        df.iloc[0,0] = 'changed value'

        # Replace file with df
        df.to_csv(name, index=False)
twerkules
  • 1
  • 2