使用XLSXWRITER，有问题的格式输出以脱颖而出
我有一系列SQL数据库查询，这些查询是我使用XLSWRITER/PANDAS编写的。 我正在使用简单的全局格式，用于字体类型和大小。 每个表都不一样，所以我唯一想做的是标准字体和尺寸。
format = workbook.add_format()
format.set_font_size(9)
format.set_font_name='Calibri'
for col_name in df1:
column_width=max(df1[col_name].astype(str).map(len).max(),len(col_name))
col_idx=df1.columns.get_loc(col_name)
if col_idx < 4:
column_width=column_width + 1
worksheet1.set_column(col_idx,col_idx,column_width,format)
writer.save()
这一切都很好，直到我遇到日期。
每个Excel表中可能有多个日期字段，或每个Excel表中没有日期字段
除了日期字段外， 。所有日期字段都显示为11，我不知道如何解决该问题：
另外， 日期本身，以excel作为日期时间出现，而不是日期，即使它们在数据库中定义为日期字段。转换它们也是一个问题。我似乎无法摆脱时间部分。
任何帮助将不胜感激。我已经花了很多时间了。
I have a series of SQL database queries, that I am writing to Excel, using Xlswriter/Pandas.
I am using a simple global format, for font type and size.
Each table is different, so the only thing I want to do, is present a standard font and size.
format = workbook.add_format()
format.set_font_size(9)
format.set_font_name='Calibri'
for col_name in df1:
column_width=max(df1[col_name].astype(str).map(len).max(),len(col_name))
col_idx=df1.columns.get_loc(col_name)
if col_idx < 4:
column_width=column_width + 1
worksheet1.set_column(col_idx,col_idx,column_width,format)
writer.save()
This all work well, until I encounter a DATE.
There may be multiple date fields, or no date field in each Excel table
All the fonts in the output are 9, except the date field. All the date fields are showing up as 11 and I don't know how to resolve the issue:
Also,
the dates themselves, show up in Excel as Date-time, not Date, even though they are defined in the Database as a Date field. Converting them is also an issue. I cant seem to get rid of the Time portion.
Any help would be greatly appreciated. I have spent waay to much time on this.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
对我来说，听起来像 this 问题。
如果您有现有文件，请使用正确的格式创建模板Excel文件，然后使用Python填充单元格。 （这种情况是帖子中公认的答案）。您也可以在Excel中定义某种样式，并将其应用于列
显然，您的场景更复杂。第二个答案建议在将熊猫的数据写入Excel之前调整数据。
但是，我个人的猜测是它是格式化的Excel问题，因此您的方法似乎是合理的。如何明确指定格式：
格式= workbook.add_format（{'num_format'：'yyyy-mm-dd'}）？ （您宁愿与 /a>帖子。作为单位）Sounds to me like this problem.
If you have existing files, create a template excel file with in the correct format and use python to just fill the cells. (This scenario is the accepted answer in the post). You can also define a certain style in excel once and apply it to columns
Apparently, you have a somehow more complicated scenario. The second answer proposes to adjust the data in pandas before writing it to excel.
However, my personal guess is that it is rather formatting problem of excel so your approach seems reasonable. How about specifying the format explicitly:
format = workbook.add_format({'num_format': 'yyyy-mm-dd'})? (Which youl rather align with this post. Try specifying the font height if setting the global font size does not work:'height': 9*20(note that you need to scale the height by 20 to use "points" as unit)