top of page
Writer's pictureYash Agarwal

Export to Excel from Canvas Apps in Power Apps using PCF

In this #PowerShot, I will show you how to export data from a gallery control to an excel sheet from canvas apps using the ExportToExcel PCF. Currently the way to move data from a canvas app and to an excel worksheet involves a Flow from Power Automate. Let's cut down this process by introducing a Custom Component that enables the "Export to Excel" functionality on the canvas app itself.


Let's Get Started!


Step 1: In this step, we will look at creating the component. Follow the steps here to create a generic component structure of type field. Once the setup is complete, certain parts of the index.ts and ControlManifest.Input.xml files need to be modified. The same have been updated and present on the GitHub repository as mentioned below.

The source code for this component is on this GitHub repository here.


Process followed in the component: String retrieval from the canvas app -> Convert string to JSON -> Populate the excel sheet.


The next task is to configure components for canvas apps. This can be done in three easy steps and those have been listed out here.


After following the steps 2, 3, 4 follow the step below to implement the Marquee element component on the canvas app.

Step 5: Update the "dataToExport" custom property to point to the Gallery control for the data that you want to print. (this could be any data control element on the canvas app). The catch here is that the data should be passed in a valid JSON format to eventually populate the excel sheet appropriately. (Example implementation shown in the next section)


You can provide the following custom properties for this component:

1. ButtonText: Text to display on the button.

2. FileName: Filename to save the excel file with.

3. ButtonHeight: Height of the button control. (width is set automatically based on component)

4. TextColor: Text color of the text on the button.

5. TextSize: Text size of the text on button.

6. Font: Font of the text on the button.

7. BackgroundColor: Background color of the button.

 

Implementation


In this section let's implement the above created component on the canvas app. The app has a Gallery control that displays data of 20 rows from a SharePoint list. We will use the custom "dataToExport" property to pass the data from the gallery as a JSON and then use the "Export to Excel" button to download the excel sheet.


Expression used on the "dataToExport" property:

Substitute(Concatenate("[",Concat(Gallery1.AllItems,"{"&Char(34)&"Order"&Char(34)&":"&Char(34)&Title&Char(34)&","&Char(34)&"Customer"&Char(34)&":"&Char(34)&Customer&Char(34)&","&Char(34)&"Billing Address"&Char(34)&":"&Char(34)&BillingAddress&Char(34)&","&Char(34)&"Shipping Address"&Char(34)&":"&Char(34)&ShippingAddress&Char(34)&"},"),"]"),",]","]")

Explanation: "Gallery1.AllItems" fetches the values from the gallery control and this can be replaced with any of the supported controls/ data sources. The "Concat" function is being used to produce a string in the form of JSON (which will be used to populate the excel sheet in the component). The keys for the respective column data needs to be provided manually as per the data source schema. In the current example I have passed "Order", "Customer", "Shipping Address" and "Billing Address" matching to my data from the "OrderDets" SharePoint list.

 

Setup in Action


 

In this article we saw how to create a component that enables users to export data from a gallery control in canvas apps to an excel sheet. This component addresses a current limitation of the export functionality for canvas apps and we have found a way to address that using the amazing PCF.


This reference was helpful in putting all this together.


I hope you found this interesting and it helped you!

Recent Posts

See All

4 Comments


Ganesh Lathi
Ganesh Lathi
Sep 25

Hi Yash, this is very impressive, thank you, i just had one question, can this work in offline mode ?

Like

Karen Kemppainen
Karen Kemppainen
Jul 29

I can get an export to work, which is fast and super simple, but I also have the need to update specific records after export. How can I call a button click or do a patch after the data has been exported? This component has no OnSelect to be able to do something like that. Thanks.

Like

Martin White
Martin White
Jul 05

I've got this working - and it's great, thank you. I've tried sending an image column by including JSON(Image1, JSONFormat.IncludeBinaryData) as one of the values. The binary date doesn't display in the spreadsheet at all.

Like

Pitsi Ngoetjana
Pitsi Ngoetjana
May 06

when i click on Export to excel button it does not export:


Here is my code:


Substitute(

    Concatenate(

        "[",

        Concat(

            galMain.AllItems,

            "{" & Char(34) & "ChangeOrder#" & Char(34) & ":" & Char(34) & 'Change Order #' & Char(34) & "," & Char(34) & "Summary" & Char(34) & ":" & Char(34) & Summary & Char(34) & "," & Char(34) & "Category" & Char(34) & ":" & Char(34) & Category & Char(34) & "," & Char(34) & "Priority" & Char(34) & ":" & Char(34) & Priority & Char(34) & "},"

        ),

        "]"

    ),

    ",]",

    "]"

)

Edited
Like
bottom of page