Write to a worksheet within an Excel (.xslx) spreadsheet

[alundavid]

I have a requirement to write derived data to specified worksheets within a .xslx file.

There is a DSS Plugin that can output multiple datasets to a spreadsheet. It seems to create a new, single spreadsheet, where each dataset gets written to a worksheet within the .xslx. 

That doesn't solve my requirement, where, for example, I have an existing spreadsheet containing worksheets named A, B, and C, and I need to write my dataset to B.

I'm sure it would be possible to create a code recipe to do what I want, but I'd prefer a visual approach. By way of comparison, Alteryx's Output Data tool has the functionality I'm looking for. 

I'd welcome any suggestions on a DSS solution. Thanks in advance.

[tgb417]

@alundavid 

Welcome to the Dataiku Community.

You might find this multi-sheet excel plugin to be of interest.

https://www.dataiku.com/product/plugins/multisheet-excel-export/

Plugins are additional code that can be added to Dataiku DSS to do a variety of nifty additional features.

[alundavid]

Thanks Tom. That is the plugin I mentioned in my question, which doesn't do what I need (whereas the Alteryx tool does).

It is interesting to look at the code of the plugin, and I can see where perhaps it might be enhanced to support my requirement, i.e. by adapting this function:

def dataframes_to_xlsx(input_dataframes_names, xlsx_abs_path, dataframe_provider):
    """
    Write the input datasets into same excel into the folder
    :param input_datasets_names:
    :param writer:
    :return:
    """
    logger.info("Writing output xlsx file ...")
    writer = pd.ExcelWriter(xlsx_abs_path, engine='openpyxl')
    for name in input_dataframes_names:
        df = dataframe_provider(name)
        logger.info("Writing dataset into excel sheet...")
        df.to_excel(writer, sheet_name=name, index=False, encoding='utf-8')
        logger.info("Finished writing dataset {} into excel sheet.".format(name))
    writer.save()
    logger.info("Done writing output xlsx file")

It may be that I have to write a code recipe, but it seems a shame that I need to do that when Alteryx makes this process step effortless.

[Ignacio_Toledo · Accepted solution]

Hi @alundavid. I think you might have two options here:

• Request the feature to be added by proposing an idea in the Community Feedback section, where any user can suggest new features and get votes from other members
• Adapt the current plugin to your needs so it can works as you expect, then be reused by others in your team, and you could also share it with the rest of the dataiku Community as explained at the public github repo

"Adapt" means that from the plugin section in DSS you can convert the installed plugin to a "dev plugin" that can be edited to match your needs.

Cheers.