Python xlsxwriter 在编写 xlsx 时将日期转换为数字

Question

我必须将数据库表导出为 Excel (xlsx) 格式。

我开始知道 Python 在文件处理方面非常快。

我已经安装了 python 3.6.1、XlsxWriter-1.0.2 和 cx_Oracle-5.3-12c

但我看到数据库中的日期字段正在转换为数字。为了解决这个问题，我检查了列表值\(单元格)值是否是日期时间，如果是，则将值格式化为日期类型，这解决了我的问题

但是，我现在遇到了这个额外检查的性能问题，关于如何获得性能的任何建议

下面的python函数用于从数据库游标生成excel xlsx

#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()

Answer 1

您应该阅读文档 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

能否分享一些代码，以便我更好地帮助您解决性能问题？