Excel Export with custom style
Answers
-
Sv3n-Sk4 Dataiku DSS Core Designer, Dataiku DSS ML Practitioner, Dataiku DSS Adv Designer, Registered Posts: 32 ✭✭✭✭
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 Dataiku DSS Core Designer, Dataiku DSS & SQL, Dataiku DSS ML Practitioner, Dataiku DSS Core Concepts, Neuron 2020, Neuron, Registered, Dataiku Frontrunner Awards 2021 Finalist, Neuron 2021, Neuron 2022, Frontrunner 2022 Finalist, Frontrunner 2022 Winner, Dataiku Frontrunner Awards 2021 Participant, Frontrunner 2022 Participant, Neuron 2023 Posts: 1,598 Neuron
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-916e00e33302To 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.
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.
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/19848Finally 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.