1

One of these two openpyxl, xlsxwriter changes automatically the font style to bold when exporting an csv file to excel. It happens only for the first column. Do you know why and how can i overcome this behavior ?

import pandas as pd
import openpyxl
import xlsxwriter
from pandas import DataFrame
import time
from glob import iglob

 data = pd.read_csv(next(iglob('*.csv')))
 data = data.sort_values(by=['A'], ascending=False)
 data.to_excel('out.xlsx',engine='xlsxwriter', index=False)
bogdann
  • 39
  • 1
  • 8
  • Just to note as a clarification that it is Pandas that is adding the bold format to the header via openpyxl or xlsxwriter. to specify your own header see this example: https://xlsxwriter.readthedocs.io/working_with_pandas.html#formatting-of-the-dataframe-headers – jmcnamara May 22 '19 at 09:01

1 Answers1

0

You can pass a custom pd.ExcelWriter instance to pd.DataFrame.to_excel() and explicitly disable any styles on the header column:

import pandas as pd

df = pd.DataFrame(data={'col_a': [1,2,3,4], 'col_b': [5,6,7,8]})

with pd.ExcelWriter('out.xlsx') as writer:
    df.to_excel(writer, index=False)

    workbook  = writer.book
    worksheet = writer.sheets['Sheet1']

    header_format = workbook.add_format({
        'bold': False,
        'border': False})

    for col_num, value in enumerate(df.columns.values):
        worksheet.write(0, col_num, value, header_format)

Edit [no longer supported in pandas 0.24]: alternatively, you can reset all pandas default header styling. Afterwards, just call df.to_excel() as usual.

import pandas.io.formats.excel
pandas.io.formats.excel.header_style = None
Jan Trienes
  • 2,501
  • 1
  • 16
  • 28
  • Pandas 0.24 doesn't support anymore pandas.io.formats.excel.header_style = None. Now, regarding to your code, my csv file could contain from 2 to 30 columns, for which i don't know the name. How to translate this into python ? – bogdann May 22 '19 at 08:55
  • 1
    @Jan Trienes That `header_style = None` workaround isn't recommended since the location of the format changed in different versions of pandas. Your `enumerate` approach in the sample code is preferable. See also: https://xlsxwriter.readthedocs.io/working_with_pandas.html#formatting-of-the-dataframe-headers – jmcnamara May 22 '19 at 08:57
  • @bogdann Thanks! The solution involving `workbook.add_format` should still work, though. What do you mean with _"my csv file could contain from 2 to 30 columns, for which i don't know the name?"_ – Jan Trienes May 22 '19 at 08:59
  • Sorry, i've got it! Runs perfect now. Thank you! – bogdann May 22 '19 at 09:13
  • @bogdann good to hear! Consider accepting the answer if it helped :) – Jan Trienes May 22 '19 at 09:15