#!/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)