Unable to close worksheet in xlsxwriter

Question

I am trying to create an excel workbook using xlsxwriter, but when I try to do workbook.close() i get following exception :

Traceback (most recent call last):
  File "/usr/local/bin/fab", line 11, in <module>
    sys.exit(program.run())
  File "/usr/local/lib/python2.7/site-packages/invoke/program.py", line 363, in run
    self.execute()
  File "/usr/local/lib/python2.7/site-packages/invoke/program.py", line 532, in execute
    executor.execute(*self.tasks)
  File "/usr/local/lib/python2.7/site-packages/invoke/executor.py", line 129, in execute
    result = call.task(*args, **call.kwargs)
  File "/usr/local/lib/python2.7/site-packages/invoke/tasks.py", line 128, in __call__
    result = self.body(*args, **kwargs)
  File "/app/fabfile.py", line 168, in test2
    workbook.close()
  File "/usr/local/lib/python2.7/site-packages/xlsxwriter/workbook.py", line 304, in close
    self._store_workbook()
  File "/usr/local/lib/python2.7/site-packages/xlsxwriter/workbook.py", line 676, in _store_workbook
    xlsx_file.write(os_filename, xml_filename)
  File "/usr/lib64/python2.7/zipfile.py", line 1146, in write
    zinfo.header_offset = self.fp.tell()    # Start of header bytes
AttributeError: 'tuple' object has no attribute 'tell'
Exception AttributeError: "'tuple' object has no attribute 'tell'" in <bound method ZipFile.__del__ of <zipfile.ZipFile object at 0x7f87fcffa410>> ignored

I am querying a database, then creating 3 sheets and filling tables in them, this is the code:

def summary_main():
        start_time = time.time()

        print "starting job}"
        ##setup db and extract summaries
        files_last_date = input("date in yyyy-mm-dd")
        sql_h = SqlHelper()
        date = datetime.today().strftime("%Y-%m-%d")
        file_name=('/app/SUMMARY_',date,'.xlsx')
        print "file name created = ",file_name
        workbook = xlsxwriter.Workbook(file_name)
        get_uploaded_files(workbook,files_last_date,sql_h)

        # getting exception in the following line
        workbook.close()
        print "\n\nSummary has been saved, workbook closed"

The above code works perfectly fine in python3 on my local machine, however on server we are trying to run the same code on python2, is that the reason the code does not work? The exception is thrown at the last line, implying there is no compatibility issue up until then. In the get_uploaded_files function, i query the db and write data to worksheet as follows:

def get_uploaded_files(wb,dt,sql_h):
    sheet = wb.add_worksheet("Uploaded Files")
    data_set = sql_h.getData(dt,1)
    header = ["File ID", "First Name", "Last Name", "File Name", "Comments"]
    sql_h.parse_sql_data(sheet,header,data_set,wb)

Following 2 functions are defined in a separate file called SqlHelper_py2.py

def getData(self,dt,case):
        print(self.conn)
        cur=self.conn.cursor()
        data_set=""
        if case==1:
            cur.execute(self.sql_uploaded_file.replace("%s",str(dt)))
            data_set=cur.fetchall()
        cur.close()
        return data_set


def parse_sql_data(self,sheet,header,data_set,workbook):
        format_bold=workbook.add_format({'bold': True,'border':1})
        format_border=workbook.add_format({'border':1})
        col = 0
        count=0
        dict_max={0:0}
        for h in header:
            sheet.write(0,col,h,format_bold)
            col+=1
            dict_max[count]=len(str(h))
            count+=1
        row = 1
        for data in data_set:
            col=0
            for d in data:
                if(dict_max[col] is not None and len(str(d))>dict_max[col]):
                    dict_max[col]=len(str(d))
                if("datetime.datetime" not in str(type(d))): 
                    sheet.write(row,col,d,format_border)
                col+=1
            row+=1
        for key in dict_max: 
            sheet.set_column(key,key,dict_max[key]+5)

Fayina

Make sure xlsxwriter versions are the same. This may be a challenge if both need to support Python 2 and 3 – Broncobuster

@Broncobuster i created following demo code to test if it works: def test3(): workbook = xlsxwriter.Workbook('/app/cdpgrp/gp_brms/hello.xlsx') worksheet = workbook.add_worksheet() worksheet.write('A1', 'Hello..') worksheet.write('B1', 'Geeks') workbook.close() and this code is working fine, able to create the excel sheet, so i dont think the version of xlsxwriter is a problem. – Fayina

Just to note that the exception is happening in zipfile not xlsxwriter. Is it due to some permissions issue? Does the simple hello_world.py case work on both platforms without issues? – Coastal

@Coastal both the codes are running on same environment, if there were some permission error then the helloworld would also not work, right? i am new to python, so i am mostly guessing things, i come from java background – Fayina

@Fayina If the hello world example works in both environment then it isn't a permissions issue. I think you will need to debug this by removing features until you have a working example (where hello world in the minimal target). I'm the author/maintainer of XlsxWriter and I've never see an issue like this, and there aren't any Python 2/3 compatibility issues. – Coastal

@Coastal hey, i was able to solve this, created a dummy file with same name as required using touch command and then the code was able to execute properly. – Fayina

Answer 1

The above code works perfectly fine in python3 on my local machine

I find that hard to believe, given that you use print statements, which would cause a syntax error on Python 3. Anyway, back to your main problem:

AttributeError: 'tuple' object has no attribute 'tell'

tell is a method on file objects. Somehow xlswriter is holding a tuple where it expects a file object. Do we see any suspicious tuples in your code? Yes:

    file_name=('/app/SUMMARY_',date,'.xlsx')
    workbook = xlsxwriter.Workbook(file_name)

Your file_name is not a string but a tuple of strings. xlsxwriter.Workbook expects a string or a file object. When it sees you didn't pass it a string, it assumes it must be a file object.

To fix the problem, join the parts of the filename into an actual string:

    file_name = ''.join('/app/SUMMARY_',date,'.xlsx')

Reeve