Format Excel Column header for better visibility and Color

2024/11/6 3:12:32

I have gone through many posts but did not found the exact way to do the below. Sorry for attaching screenshot(Just for better visibility) as well , I will write it also. Basically it looks like -

Name_of_the_Man Address_of_Man  City
Jordan           NC             LMN

Input csv looks like

Available csv

Output Needed Need this

I have this code with me that picks the csv and attach as sheet in excel.

writer = pd.ExcelWriter('final.xlsx'), engine='xlsxwriter')
for f in glob.glob(os.path.join(Path, "*.csv")):df = pd.read_csv(f)df.to_excel(writer, sheet_name=os.path.basename(f))
writer.save()

I want my csv file - having good space in between and color for the column header.I have went through this link Python - change header color of dataframe and save it to excel file but it's not serving the purpose - It is coloring the sheet itself apart from column.

Update: Got the answer below . Also wondering if that can be possible just a thought enter image description here

Answer

You can use Pandas Excel output with user defined header format with solution for change width by content:

writer = pd.ExcelWriter("file.xlsx", engine='xlsxwriter')# Convert the dataframe to an XlsxWriter Excel object. Note that we turn off
# the default header and skip one row to allow us to insert a user defined
# header. Also remove index values by index=False
df.to_excel(writer, sheet_name='Sheet1', startrow=1, header=False, index=False)workbook  = writer.book
worksheet = writer.sheets['Sheet1']
# Add a header format.
header_format = workbook.add_format({'bold': True,'fg_color': '#ffcccc','border': 1})
for col_num, value in enumerate(df.columns.values):worksheet.write(0, col_num, value, header_format)column_len = df[value].astype(str).str.len().max()# Setting the length if the column header is larger# than the max column value lengthcolumn_len = max(column_len, len(value)) + 3print(column_len)# set the column lengthworksheet.set_column(col_num, col_num, column_len)# Close the Pandas Excel writer and output the Excel file.
writer.save()

pic

Changed your solution:

writer = pd.ExcelWriter('final.xlsx'), engine='xlsxwriter')
for f in glob.glob(os.path.join(Path, "*.csv")):df = pd.read_csv(f)df.to_excel(writer, sheet_name=os.path.basename(f))workbook  = writer.bookworksheet = writer.sheets[os.path.basename(f)]# Add a header format.header_format = workbook.add_format({'bold': True,'fg_color': '#ffcccc','border': 1})for col_num, value in enumerate(df.columns.values):worksheet.write(0, col_num, value, header_format)column_len = df[value].astype(str).str.len().max()# Setting the length if the column header is larger# than the max column value lengthcolumn_len = max(column_len, len(value)) + 3print(column_len)# set the column lengthworksheet.set_column(col_num, col_num, column_len)writer.save()

EDIT:

writer = pd.ExcelWriter("file.xlsx", engine='xlsxwriter')#skip 2 rows
df.to_excel(writer, sheet_name='Sheet1', startrow=2, header=False, index=False)workbook  = writer.book
worksheet = writer.sheets['Sheet1']
# Add a header format.
header_format = workbook.add_format({'bold': True,'fg_color': '#ffcccc','border': 1})#create dictionary for map length of columns 
d = dict(zip(range(25), list(string.ascii_uppercase)))
#print (d)max_len = d[len(df.columns) - 1]
print (max_len)
#C
#dynamically set merged columns in first row
worksheet.merge_range('A1:' + max_len + '1', 'This Sheet is for Personal Details')for col_num, value in enumerate(df.columns.values):#write to second rowworksheet.write(1, col_num, value, header_format)column_len = df[value].astype(str).str.len().max()column_len = max(column_len, len(value)) + 3worksheet.set_column(col_num, col_num, column_len)# Close the Pandas Excel writer and output the Excel file.
writer.save()
https://en.xdnf.cn/q/70971.html

Related Q&A

Using multiple keywords in xattr via _kMDItemUserTags or kMDItemOMUserTags

While reorganizing my images, in anticipation of OSX Mavericks I am writing a script to insert tags into the xattr fields of my image files, so I can search them with Spotlight. (I am also editing the …

JAX Apply function only on slice of array under jit

I am using JAX, and I want to perform an operation like @jax.jit def fun(x, index):x[:index] = other_fun(x[:index])return xThis cannot be performed under jit. Is there a way of doing this with jax.ops …

Using my own corpus for category classification in Python NLTK

Im a NTLK/Python beginner and managed to load my own corpus using CategorizedPlaintextCorpusReader but how do I actually train and use the data for classification of text?>>> from nltk.corpus…

Python ImportError for strptime in spyder for windows 7

I cant for the life of me figure out what is causing this very odd error.I am running a script in python 2.7 in the spyder IDE for windows 7. It uses datetime.datetime.strptime at one point. I can run …

How to show diff of two string sequences in colors?

Im trying to find a Python way to diff strings. I know about difflib but I havent been able to find an inline mode that does something similar to what this JS library does (insertions in green, deletio…

Regex for timestamp

Im terrible at regex apparently, it makes no sense to me...Id like an expression for matching a time, like 01:23:45 within a string. I tried this (r(([0-9]*2)[:])*2([0-9]*2)but its not working. I need …

os.read(0,) vs sys.stdin.buffer.read() in python

I encountered the picotui library, and was curious to know a bit how it works. I saw here (line 147) that it uses: os.read(0,32)According to Google 0 represents stdin, but also that the accepted answer…

python - Pandas: groupby ffill for multiple columns

I have the following DataFrame with some missing values. I want to use ffill() to fill missing values in both var1 and var2 grouped by date and building. I can do that for one variable at a time, but w…

Gtk-Message: Failed to load module canberra-gtk-module

My pygtk program writes this warning to stderr:Gtk-Message: Failed to load module "canberra-gtk-module"libcanberra seems to be a library for sound.My program does not use any sound. Is there …

Why does installation of some Python packages require Visual Studio?

Say, you are installing a Python package for pyEnchant or crfsuite, etc. It fails to install and in the error trace it says some .bat (or .dll) file is missing.A few forums suggest you install Visual S…