Python script to write and append a single xls document by every functions inside the script. Currently its overwriting the sheet

Question

Python script to write and append a single xls document by every functions inside the script. Currently its overwriting the sheet.

Here's the code:

import os
import os.path
import MySQLdb
import configuration
import hashlib
import xlsxwriter
from lxml import etree


def Check():
    try:
        PATH='config.txt'
        workbook = xlsxwriter.Workbook('demo.xlsx')
                worksheet = workbook.add_worksheet()
                worksheet.set_column('A:A', 20)
                bold = workbook.add_format({'bold': True})
                worksheet.write('A1', 'File Name',bold)
                worksheet.write('B1', 'is Present', bold)
                worksheet.write(2, 1, 'yes')
                worksheet.write('D1', 'Verification', bold)
                worksheet.write(2, 0, 'config.txt')


        if os.path.isfile(PATH) and os.access(PATH, os.R_OK):

                print "config.txt file exists and is readable"
            global cur1
                    var = configuration.client['client_id']
                    host = configuration.mysql_host['host']
                    username = configuration.mysql_username['username']
                    password = configuration.mysql_password['password']
                    connection=MySQLdb.connect( user=username, passwd=password, db="db")
                    cur1=connection.cursor()
                    cur1.execute("select md5sum from **** where file_name = 'config.txt' and sd = '%s'" % var)
                    row=cur1.fetchone()
                    ele = 'ncconfig.txt'
                    for md5 in row:
                            file_name = 'config.txt'
                            with open(file_name) as file_to_check:
                                    data = file_to_check.read()
                                    md5_returned = hashlib.md5(data).hexdigest()
                            if md5 == md5_returned:
                                    print('File %s md5 has been verified' % ele)
                                worksheet.write(2, 3, 'verified')
                                workbook.close()

                            else:
                                    print('File %s md5 is not matching....So syncing failed ' % ele)
                                worksheet.write(2, 3, 'Failed')
                                workbook.close()

            cur1.close()
                    connection.close()

        else:
                print "Either config.txt file is missing or is not readable"
                        worksheet.write(2, 3, 'Failed')
                        worksheet.write('G1','Reason', bold)
            worksheet.write(2,5,'May be file is not present')
                        workbook.close()    
    except MySQLdb.Error, e:
                print "Error %d: %s" % (e.args[0], e.args[1])





def Check_md5_for_rconfig():
        try:
                PATH='rconfig.txt'
                if os.path.isfile(PATH) and os.access(PATH, os.R_OK):
                        print "rconfig.txt file exists and is readable"
                        global cur1
                        var = configuration.client['client_id']
                        host = configuration.mysql_host['host']
                        username = configuration.mysql_username['username']
                        password = configuration.mysql_password['password']
                        connection=MySQLdb.connect( user=username, passwd=password, db="db")
                        cur1=connection.cursor()
                        cur1.execute("select md5sum from **** where file_name = 'rconfig.txt' and sd = '%s'" % var)
                        row=cur1.fetchone()
                        ele = 'rconfig.txt'
                        for md5 in row:
                                file_name = 'rconfig.txt'
                                with open(file_name) as file_to_check:
                                        data = file_to_check.read()
                                        md5_returned = hashlib.md5(data).hexdigest()
                                if md5 == md5_returned:
                                        print('File %s md5 has been verified' % ele)



                                else:
                                        print('File %s md5 is not matching....So syncing failed ' % ele)


                        cur1.close()
                        connection.close()

                else:
                        print "Either rconfig.txt file is missing or is not readable"

        except MySQLdb.Error, e:
                print "Error %d: %s" % (e.args[0], e.args[1])

score 1 · Answer 1 · 2017-09-12T06:42:37.773

1

update This should answer your question: Overflow Answer Here! I just read that xlmswriter will not modify existing excel files, but there's a module for that in the link

with open(file_name) as file_to_check:

Should be

with open(file_name, 'rb' ) as file_to_check:

For both read instances

Are you trying to append to the worksheet? From what I see everytime check() is preformed it writes to the same filename as before and each worksheet.write inserts data into previous defined spots, which would overwrite the existing data in the file, I haven't worked with xlswriter so maybe it'll append but that's what I see.

edited Sep 12 '17 at 06:42

answered Sep 12 '17 at 05:52

Thanks for your reply nknous but I was not asking this , I was asking how can I make 3 functions writing in a single xlsx file using python script. – Puneet Kumar Sep 12 '17 at 21:36
Open the workbook outside of the functions and call the functions, close workbook after functions. Perhaps make worksheet a global var. If you open the workbook in a function then the other functions need to be called inside of that function. Otherwise you'll just keep overwriting. Per the docs nothing is written to the sheet until the workbook.close() method is called. – Sep 12 '17 at 21:45
I read somewhere in some thread and it was saying xlsxwriter cannot append any existing document. – Puneet Kumar Sep 12 '17 at 21:49
Correct, that's in my answer link, there is another module that will allow it to append, which will probably be cleaner – Sep 12 '17 at 21:50
Np let me know how it goes, still problems and I'll write a script too and we'll tackle it – Sep 12 '17 at 21:54
Yeah sure nknous – Puneet Kumar Sep 13 '17 at 17:36

Python script to write and append a single xls document by every functions inside the script. Currently its overwriting the sheet

1 Answers1