Jun-21-2017, 11:31 AM
I have an excel sheet where I need to mainupulate a certain work sheet. I am trying to clear the contents in the worksheet and overwrite contents from a text file. My code is as follows
#!/usr/bin/python import os from openpyxl.reader.excel import load_workbook import csv from openpyxl.drawing.image import Image import PIL xl_directory = r'/home/test' txt_directory = r'/home/test' for xl_root, xl_dirs, xl_files in os.walk(xl_directory): for xl_file in xl_files: if xl_file.endswith('.xlsx'): xl_abs_file = os.path.join(xl_root, xl_file) wb = load_workbook(xl_abs_file, data_only=True) ws = wb.get_sheet_by_name('Unannotated') ##clear the contents of the file for row in ws['A4:U1000']: for cell in row: cell.value = None image = Image('/home/logo3.jpg') ws.add_image(image, 'A1') ## go through text file and write data on worksheet for txt_root, txt_dirs, txt_files in os.walk(txt_directory): for txt_file in txt_files: if txt_file == xl_file.replace('xlsx', 'txt'): with open(os.path.join(txt_root, txt_file)) as fh: reader = csv.reader(fh, delimiter='\t') [next(reader) for skip in range(1)] for row in reader: ws.append(row) wb.save(xl_abs_file)But this generates a blank worksheet for me (except for the image and header). ANy help is appreciated. thank you.