python - 使用Python xlsxwriter创建excel时，双击单元格后仅格式化 "takes"

Question

我创建了一个脚本来生成一个数据框，我想将其放入 Excel 文档中。在下面的代码中，我设置了我认为设置每列格式的正确参数。

生成 Excel 后，每个单元格的格式都不正确。只有双击单元格后，格式才会正确。

检查 Excel 文档的格式显示，对于每一行，我的格式都是由代码设置的，但未显示。

提前感谢您提供任何可能解决此问题的建议。

# -*- coding: utf-8 -*-
"""
Created on Tue Apr 30 10:28:45 2019

@author: ny
"""
import logging
import sys
import pyodbc           # Database connector
import pandas as pd     # Data frames 
import datetime         # Dates and time 
from datetime import timedelta 

import numpy as np 



test =  {'col1':['USD'],
         'col2':['USD Fed'],
         'col3':['2019-01-04 15:15:00'],
         'col4':['2019-01-04 15:24:59.999'],
         'col5':['1.13564'],
         'col6':['1.13528'],
         'col7':['1.13845'],
         'col8':['1.137'],
         'col9':['0.002474375682434556'],
         'col10':['0.0015150447466703754'],
         'col11':['-28.10'],
         'col12':['-17.20']}
testdf = pd.DataFrame(data=test)

#results = results.sort_values(by=['TimeStart'], ascending=False)
#Excel

#results = results.applymap(str)

outputString = 'test' + "MarketMovement.xlsx"
writer = pd.ExcelWriter(outputString, engine = 'xlsxwriter')



testdf.to_excel(writer, sheet_name ='Events',index = False)
resultsLn = len(testdf)
Clength = 'A1:L'+str(resultsLn+1)
workbook = writer.book
num_fmt =  workbook.add_format({'num_format': '#,##0.00000'})
day_fmt     =  workbook.add_format({'num_format': 'yyyy-mm-dd hh:mm:ss'})                                 

worksheet = writer.sheets['Events']    


worksheet.add_table(Clength, {'columns': [
  {'header': 'titleCurrency'}#a
 ,{'header': 'Event'}#a
 ,{'header': 'TimeStart'}#a
 ,{'header': 'TimeEnd'}#b
 ,{'header': 't1HighRate'}#c
 ,{'header': 't1LowRate'}#d
 ,{'header': 't10HighRate'}#e
 ,{'header': 't10LowRate'}#f
 ,{'header': 'PercentHighChange'}#g
 ,{'header': 'PercentLowChange'}#h
 ,{'header': 'changeHighPips'}#i
 ,{'header': 'changeLowPips'}#j
 ],
                                   'autofilter': True,
                                   'total_row': False,
                                   'style': 'Table Style Medium 2'})   



worksheet.set_column('A:A', 20)  
worksheet.set_column('B:B', 20)     
worksheet.set_column('C:C', 20, day_fmt)    
worksheet.set_column('D:D',20, day_fmt)
worksheet.set_column('E:E',25, num_fmt)
worksheet.set_column('F:F',25, num_fmt)
worksheet.set_column('G:G',25, num_fmt)
worksheet.set_column('H:H',25, num_fmt)
worksheet.set_column('I:I',25, num_fmt)
worksheet.set_column('J:J',25, num_fmt)
worksheet.set_column('K:K',25, num_fmt)
worksheet.set_column('L:L',25, num_fmt)

writer.save()
writer.close()   

#Clear all kernel after finished 
# import IPython
# app = IPython.Application.instance()
# app.kernel.do_shutdown(True)   

Answer 1

解决方案是我需要确保数据类型完全匹配。需要为 python 使用正确的日期时间格式，为数字使用数字格式。解决方案由 jmcnamara 提供。