0

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.

enter image description here

C0ppert0p
  • 634
  • 2
  • 7
  • 23

1 Answers1

0

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)

max
  • 3,915
  • 2
  • 9
  • 25