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!