Excel Export with custom style

jasonsnare
Level 1
Excel Export with custom style
Hi, 
I have one dataset it contains multiple columns. I need to export it into Excel (.xlsx) format with below conditions.
1. The output excel Columns width should automatically adjusted(fit) based on column text length.
2. The output excel specific Text cells should be Highlig2023-02-10_19-57-43.jpghted in Different color.
0 Kudos
2 Replies
Sv3n-Sk4
Level 3

Hello @jasonsnare,

I hope this answer will help you even if you asked the questions several weeks ago.

I think you will be able to create what you expect by using a custom python recipe with the python library openpyxl to customize your data and export it.

Best regards,

Hadrien

0 Kudos
tgb417

All,

Does anyone have an example of code they have used to do something like this?

I did find a Toward Data Science post that covers some of this from a formatting point of view.

The author of that artical seem to be using Pandas and xlswriter.  They are using some code something like the below.  (This is sort of psudocode and has not been tested, there would be additional stuff needed to write to a Dataiku Connection.)

https://xlsxwriter.readthedocs.io/

import pandas as pd
file_name = "Cryptocurrency.xlsx"
sheet_name = "Summary"
# fill thedataframe with some content
master_df = pd.DataFrame()

master_df = master_df[['iso', 'name', 'ohlc_o', 'ohlc_h', 'ohlc_l', 'ohlc_c', 'change_percent']].reset_index(drop=True)
master_df.columns = ['Symbol', 'Name', 'Open', 'High', 'Low', 'Close', 'Pct_Change']
master_df.iloc[:, 2:] = master_df.iloc[:, 2:].apply(lambda x: round(x, 2))
master_df['Pct_Change'] = master_df['Pct_Change'] / 100
master_df = master_df.sort_values('Pct_Change', ascending=False).reset_index(drop=True)
master_df.to_csv('master_df.csv', index=False)

#check the contents of the dataframe
master_df.head()


#write the dataframe to a local (non DSS Managed File)
writer = pd.ExcelWriter(file_name, engine='xlsxwriter')
master_df.to_excel(writer, sheet_name=sheet_name
, startrow = 2, index = False)


Here is a link to the article I found
https://towardsdatascience.com/use-python-to-stylize-the-excel-formatting-916e00e33302

To do this with Dataiku DSS you will have to install the xlswriter code into a Dataiku code environment.  This will take administrative rights on your instance to create and manage a python code environment.

https://community.dataiku.com/t5/Using-Dataiku/Embed-openpyxl-and-xlsxwriter-in-the-default-Python-e...

Here is a bit of a tutorial on setting up code environments.

https://knowledge.dataiku.com/latest/code/work-environment/tutorial-code-environments.html

This shows an advanced example using a Dataiku connection and a stream.

https://community.dataiku.com/t5/Using-Dataiku/Creating-an-excel-output-with-multiple-sheet/m-p/2481...

This one also is using a Dataiku Connection and writes to SharePoint.
https://community.dataiku.com/t5/Plugins-Extending-Dataiku/How-to-write-an-excel-file-in-a-folder-wi...

Finally there are some more examples in this post, it this cased writing to an SFTP swerver.

https://community.dataiku.com/t5/Using-Dataiku/Export-file-into-SFTP-using-Python/m-p/22179

Good luck.  Hope this helps.  Let us know how you are getting along.

--Tom
0 Kudos