CODEWITHSUNDEEP
pythonpython-3.6xlsxwriter
Prashant Dabral
Prashant Dabral

Reputation: 41

Python xlsxwriter is converting date to number while writing xlsx

I've to export the database table to Excel (xlsx) format.

I came to know that Python is very fast in file processing.

I've installed python 3.6.1, XlsxWriter-1.0.2 and cx_Oracle-5.3-12c

But I saw the date field from the database was getting converted to the number. To resolve this, I made a check if a list value \ (cell) value is date time, if yes then formatted the value to date type which resolves my problem

But, I am now running into performance issue with this extra check any suggestion how to gain performance

Below python function is used to generate excel xlsx from the database cursor

#Function to write excel from Oracle Cursor
def writeToExcel(cur_sor, targetDir, export_file_name):
    Actual_Path = os.path.join(targetDir, export_file_name)

    #Array to capture Date type columns
    DateTimeColumns = []

    print('\t\t\t  writing: '+export_file_name+'\t\t\t\t'+str(datetime.datetime.now()))
    workbook = xlsxwriter.Workbook(Actual_Path)  # Create Excel Object for new workbook
    worksheet = workbook.add_worksheet(sourceSYS)  # Add a New Worksheet Name - scott_schema
    row = 0
    col = 0

    for i in range(len(cur_sor.description)):
        desc = cur_sor.description[i]

        #Only Data Type column will be capture
        if format(desc[1])== "<class 'cx_Oracle.TIMESTAMP'>":
            DateTimeColumns.append(i)

        bold = workbook.add_format({'bold': True})
        date_format = workbook.add_format({'num_format': 'dd/mm/yy'})
        worksheet.write(row, (col + i), format(desc[0]), bold)  # Iterate for column headers

    date_format = workbook.add_format({'num_format': 'dd/mm/yy'})
    color_format = workbook.add_format()
    color_format.set_font_color('red')
    row = row + 1

    #Loop for each row, return by database cursor
    for tupple_row in cur_sor:
        col = 0
        #Loop for each column, for particular row open in level-up cursor
        for list_item in tupple_row:

            #If column position matches with datetype column position
            if col in DateTimeColumns:
                #Check if the cell value is date type, additional check to handle "None" (blank date cell)
                if isinstance(list_item, datetime.date) or isinstance(list_item, datetime.datetime) \
                    or isinstance(list_item, datetime.time) or isinstance(list_item, datetime.timedelta):
                    #Format the date, inorder to save as date rather number
                    worksheet.write(row, col, list_item.strftime("%Y-%b-%d %H:%M:%S.%f"))
            else:
                worksheet.write(row, col, list_item)

            col = col + 1
        row = row + 1

    workbook.close()

Upvotes: 4

Views: 1672

Answers (1)

Fran
Fran

Reputation: 81

You should read the docs http://xlsxwriter.readthedocs.io/working_with_dates_and_time.html

Working with Dates and Time Dates and times in Excel are represented by real numbers, for example “Jan 1 2013 12:00 PM” is represented by the number 41275.5.

The integer part of the number stores the number of days since the epoch and the fractional part stores the percentage of the day.

A date or time in Excel is just like any other number. To display the number as a date you must apply an Excel number format to it

Can you share some code so I can be of better help with your performance problem?

Upvotes: 2

Related Questions