Excel Writer for Azure Data Factory

An easy-to-use packager API for use with Azure Data Factory and other applications

ADF pipeline

FREE Download From Azure Marketplace

This container provides a single API to package excel files from csv files stored in BLOB storage.

Excel packager is a simple API to create excel files from datasets for use with Azure Data Factory and other apps in azure. Offered as an Azure container so you can manage your data compliance and privacy. No data leaves your environment for this conversion.

Azure data factory does not have Excel file as a data sink. This API is developed to provide a simple workaround to create excel files with ADF. Azure Data Factory developers can use this utility to create Excel files to be used downstream in pipelines. A single deployment can be used by all ADFs and other applications in the organization.

Using Excel packager API with Azure Data Factory

After deploying the container use Web or Webhook blocks to use this utility to create excel files

URL: https://app_service_instance.url/excel

Method: POST

Body: JSON – see below

Return: Created File Name

 

Step 1: Create one csv file in blob storage for each tab with path of /EXCELFILENAME/SheetName.csv

Step 2: Call the API with the above details using Web or Webhook blocks

Step 3: Read and/or move the excel file to intended destination

Step 4: Cleanup files

 

API Payload: Body of the webhook block should contain following information. You have to dynamically populate these fields as required, from ADF variables.

    {

        "sas":"SAS url",

        "folder":"ExcelFileName",

        "container":"container name",

        "escape": "\\"

    }

SAS Url – expecting storage account SAS with list and create permissions at the minimum and provide container name as a separate field.

Alternatively, you can use “containersas” field to provide SAS url for the container, and no need to provide container field.

Escape to specify the escape character used in the csv file. Use \ with the escape char to work with json payload.

Note: make sure you specify an escape character while producing csv files. Without an escape characters csv files cannot have columns aligned if any quotes present in the data.

use with adf

Using Excel Packager API with as a webbook

Please follow the same instructions above. Send Authorization header if the service requires AAD authentication.

Security

For simplicity of implementation, Excel Packager API does NOT include any option to secure the API. It neither has its own storage nor connects to any other systems. By definition, containers are inherently secure. However, as the API does NOT provide any security mechanisms and offers an openly callable API, there is a potential for DOS attacks and or use as a trampoline to attack other internal resources. Please enable ONE of the below security measures

  1. Enable authentication on the app service, AKS, or ACS’s gateway – if using with ADF’s explicitly permit specific ADF’s or any authenticated user within your directory only.
  2. IP address whitelisting
  3. Allow Specific subnets only

FREE Download From Azure Marketplace

Let's Discuss How!

Please provide your contact info and a short description of what you would like to discuss, and a preferred time in the message box and we will reach out to you at the earliest.

Interested in concepts?

Schedule a quick call with a solution engineer to discuss how modern development strategies can benefit you!

Catching up on Tech Debt?

It’s hard aligning your deliverables with business priorities.

Let’s discuss where Teams apps and Low code platforms can help you deliver better value for your existing application development projects.