0

I've been working with a SharePoint list that has several columns with specific color-coded formatting. When I try to export the data, I'm losing these important visual cues that help me quickly interpret the information. enter image description here

  • How can I ensure color formatting preserved when I click the "Export to Excel" button?
  • Are there any known workarounds (i,e, via power apps or power automate)? If so, which would be best if I am exporting around ~1000 rows of data each time
Improve this question
8
  • These color UI is only for visual purpose it will not come in your excel when you do export to excel. You can go with some customizations using Power Platform or Azure to export these data in excel and place color background same as here. But it will be the pure customization. Commented Dec 4, 2024 at 9:38
  • @DikeshGandhi Can you elaborate on the Power Platform bit? I've tried looking for a solution using Power Automate where it would read the SP list data and apply conditional foromatting, but from what I've read so far, it can't do that. Power Automate can only create the table without applying specific colors to certain cells. Commented Dec 4, 2024 at 10:36
  • Yes, I think its not possible with power automate, few examples I saw and they are using power automate desktop. Commented Dec 4, 2024 at 11:33
  • Another approach is creating a commandset button with spfx that you can place in the commandbar of the list. Upon button click, you can write logic to get all data and prepare excel file with background cell colors and download it. You can use xlsx-js-style library with react for the same. Commented Dec 4, 2024 at 11:40
  • 1
    @DikeshGandhi My organization won't let us use SPFX unfortunately. And yeah, only the desktop version allows cell formatting, but that would require every user who downloads the file to have it available on their device I believe. I think the only semi-feasible solution is to have the exported excel file (.iqy) stored in a shared drive. Then I modify that file and add the conditional formatting. Since it's an .iqy file, it would be connected to the latest data and refreshed anytime someone opens it, and on top of that the conditional formatting would still be applied. Commented Dec 4, 2024 at 12:06

1 Answer 1

Reset to default
0

These color backgrounds provided in the choice columns are only for the viewing purpose. It will not come in exported excel/csv when user clicks on OOTB "Export to Excel" feature.

To achieve this, the only option left is thru customizations. There are several options available in the customizations as follows:

  • Power Automate Desktop: Using this, you can apply color backgrounds to the cell but it is a very tedious approach. First we have to fetch all data from the SharePoint list -> create excel file and add colors to the cells. It is very static because it requires one machine to do such operations. More details you can find here.

  • SPFx Listview CommandSet button: Using this, you can create one custom commandset button on your list view. You can write logic that upon the button click, it will fetch all data -> create excel file and add colors to the cell -> download it in the browser. You can use xlsx-js-style react library to create the excel file. You can use JavaScript/typescript code for the same.

  • Azure Web Job: You can write C# console application to fetch all list data and to prepare the excel file(along with colors) as per your need. You can save this excel file in one document library and can share its url to the urls. Since it is the Azure web job, you can schedule it to trigger once in a day, thrice in a day...anything as per your need.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.