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


Messages In This Thread
overwrite data using openpyxl - by BNB - Jun-21-2017, 11:31 AM

Possibly Related Threads…
Thread Author Replies Views Last Post
  Openpyxl overwrite Linechart SamLiu 0 840 Nov-26-2022, 05:44 AM
Last Post: SamLiu
  openpyxl convert data to float jacklee26 13 5,719 Nov-19-2022, 11:59 AM
Last Post: deanhystad
  Overwrite previous live data. Makada 2 2,303 Nov-07-2020, 07:40 PM
Last Post: Makada
  Formatting Data/Time with Pyodbc and openpyxl bearcats6001 0 2,251 Aug-17-2020, 03:44 PM
Last Post: bearcats6001
  openpyxl Pasting data to different cells Kristenl2784 3 7,562 Jun-15-2020, 08:50 PM
Last Post: Yoriz
  How to overwrite file SriRajesh 3 6,257 Apr-14-2020, 12:57 PM
Last Post: steve_shambles
  python openpyxl writing data Bikram 2 2,415 Oct-17-2019, 03:33 PM
Last Post: Bikram
  Dict overwrite previous list vincentspm 1 2,185 Mar-20-2019, 12:31 PM
Last Post: scidam
  Openpyxl -Coming up with a single column from a multi column data set Givan007 1 2,699 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