python 3 open excel files in a directory

Question

I'm new to programming and I'm trying to work on a script that will open an excel file in a folder. update the workbook with openpyxl. Save the file. Open the next file in the directory and do the same.

I'm trying this without success:

import os.glob
folder_path = 'G:\\My Drive\\Reports\\'
for filename in glob.glob(os,path.join(folder_path, '*.xlsm')):
    with open(filename, 'r' as f:
        text = f.read()
        print (filename)
        print (len(text))
from openpyxl import Workbook
from openpyxl.styles import NamedStyle, Font, Border, Side, Alignment
wb=Workbook()
ws=wb.active
Update cells

wb.save(filename)

you have wrong indentations - you have to do it inside for-loop but you do it after leaving for-loop. BTW: put all imports at the beginning to make it more readable. — furas
– furas, Commented May 28, 2020 at 20:18
I don't understand why you use open(), read() if you want to work with excel files. open(), read() is useful for text files (or similar) but Excel uses more complex file and it needs specilized function to read file - and you should use openpyxl for this. — furas
– furas, Commented May 28, 2020 at 20:22

furas · Accepted Answer · 2020-05-28 20:40:14Z

1

You have wrong indentations. You have to do it inside for-loop.

And you have to use function from module openpyxl to open and read Excel file.

Functions open(), read() are useful to read text files (or similar) or to read raw data but Excel uses more complex files (ie. it can use .zip file with many .xml files) and you need module like openpyxl to read it.

import os
import glob
import openpyxl

folder_path = 'G:\\My Drive\\Reports\\'

for filename in glob.glob(os.path.join(folder_path, '*.xlsm')):

    print(filename)

    wb = openpyxl.open(filename)
    ws = wb.active

    #Update cells
    print('old value:', ws['A1'].value)
    ws['A1'] = 'Hello World'
    print('new value:', ws['A1'].value)

    wb.save(filename)

BTW: always put all imports at the beginning to make code more readable.

See also: PEP 8 -- Style Guide for Python Code

edited May 28, 2020 at 20:40

answered May 28, 2020 at 20:26

furas

149k12 gold badges121 silver badges171 bronze badges

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

1 Comment

Bill Nash Over a year ago

Thank you. The PEP 8 -- Style guide for Python code is very helpful.

Collectives™ on Stack Overflow

python 3 open excel files in a directory

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