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
Output Needed
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
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()
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()