How to apply a python code to multiple Excel files in a folder

Question

I am new in programming with Python. I need to apply a code to multiple Excel files saved in the same folder. How can I do? I tried with os.listdir but the program gives me an error for not finding the file in the directory. Here is the code that I wrote:

import openpyxl as xl

from openpyxl.chart import ScatterChart, Reference, Series

import os

folder = r"C:\\Users\\PycharmProjects\\Project\\output"

for file in os.listdir(folder):

    wb = xl.load_workbook(file)
    sheet = wb["Sheet"]
    for row in range(42, sheet.max_row + 1):
        cell = sheet.cell(row, 5)
        cell_num = float(cell.value)
        cell_num_sheet = sheet.cell(row, 5)
        cell_num_sheet.value = cell_num
        cell1 = sheet.cell(row, 8)
        cell1_num = float(cell1.value)
        cell1_num_sheet = sheet.cell(row, 8)
        cell1_num_sheet.value = cell1_num
        chart1 = ScatterChart()
        chart1.title = "Speed of sound [m/s] vs Temperature [°C]"
        chart1.y_axis.title = "Speed of sound [m/s]"
        chart1.x_axis.title = "Temperature [°C]"
        xvalues = Reference(sheet, min_col=8, min_row=42, max_row=sheet.max_row)
        yvalues = Reference(sheet, min_col=5, min_row=42, max_row=sheet.max_row)
        series = Series(values=yvalues, xvalues=xvalues, title="1.5%")
        chart1.series.append(series)
        sheet.add_chart(chart1, "t42")
    wb.save(file)

AKX · Accepted Answer · 2021-12-30 14:04:00Z

1

os.listdir() gives you just filenames, not full paths.

Instead of

for file in os.listdir(folder):
    wb = xl.load_workbook(file)

you'll need to join the folder back into the path:

for file in os.listdir(folder):
    wb = xl.load_workbook(os.path.join(folder, file))

answered Dec 30, 2021 at 14:04

AKX

171k16 gold badges146 silver badges229 bronze badges

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

1 Comment

Annachiara Over a year ago

Great, thanks a lot!

Collectives™ on Stack Overflow

How to apply a python code to multiple Excel files in a folder

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related