Python Forum
overwrite data using openpyxl
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
overwrite data using openpyxl
#1
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.
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  Overwrite previous live data. Makada 2 648 Nov-07-2020, 07:40 PM
Last Post: Makada
  Formatting Data/Time with Pyodbc and openpyxl bearcats6001 0 696 Aug-17-2020, 03:44 PM
Last Post: bearcats6001
  openpyxl Pasting data to different cells Kristenl2784 3 1,549 Jun-15-2020, 08:50 PM
Last Post: Yoriz
  How to overwrite file SriRajesh 3 1,852 Apr-14-2020, 12:57 PM
Last Post: steve_shambles
  python openpyxl writing data Bikram 2 934 Oct-17-2019, 03:33 PM
Last Post: Bikram
  Dict overwrite previous list vincentspm 1 1,040 Mar-20-2019, 12:31 PM
Last Post: scidam
  Openpyxl -Coming up with a single column from a multi column data set Givan007 1 1,324 Sep-07-2018, 04:29 AM
Last Post: Prabakaran141

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020