Excel Export with custom style

jasonsnare

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 Highlighted in Different color.

Sv3n-Sk4

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

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-env-of-the/td-p/3349

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/24816

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-with-python-where-folder/m-p/19848

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.