Apex Export To Excel

0 views

Skip to first unread message

Gladys Anick

unread,

Aug 3, 2024, 2:02:30 PM8/3/24

to fanloralo

The download to excel is always in CSV format. The file extension is not .xlsxbut .CSV. So, i'd say no.
It's tough too. Even if you were to create a custom export to excel you'd have to extract the current query of the report (which is something that has finally been made easier in 4.2, but is possible in 4.0/1 with 3rd party packages). Then you'd also have to account for the control break(s) you applied, since those are not reflected in the IR query (even with APEX_IR).
I've dabbled with generating an xlsx file and made a blogpost/sample application on that if you'd like to see what it encompasses. Be aware that this is taking 'custom solution' to the extreme though (at least, in my opinion).
=10063

In many of the requirements, there is one most common requirement is the exporting orgs data in the form of .csv or .xls. (excel formate). Most of the Salesforce developer may have come across such requirement. Previously using VF pages this was very straight forward to fulfill. Let see how to Export to CSV/XLS using Lightning Web Component.

Here we will be discussing the yet another way to export the information in tabular form either as .csv or .xls file using the Lightning Web component (LWC). This is very simple technique without using any external Java Script library we can export any kind of data. Following example illustrate the way we can easily export the information as Steps.

Using LWC it becomes very easy to export the data in tabular for even you can export the large data. Stay connect we will be coming with another blog where we will be exporting the data by applying some formatting like text background, foreground colors etc.

Hello Mahesh
Code works as expected but only problem is once we click to open the downloaded file I am getting below error:excel cannot open the file because the file format or file extension is not valid. verify that the format is correct. Can you please help.

Exporting data to an Excel sheet or CSV is a common requirement in Salesforce Applications. We can export data to XLS/CSV format in Lightning Web Component using MIME type but this will not work for XLSX format. The file will be saved but it will not open. To export data in XLSX format we can use an external JS library write-excel-file or SheetJs. This post will provide step by step guide to export data from Lightning Web Component to Excel Sheet using the JS library write-excel-file.

XLS is an older format from Excel 97 to Excel 2003 while XLSX is the default file format for Excel 2007 and later. XLSX is designed for not only Windows but also MacOS, iOS, and Android. It contains calculation, pivot tables, graphing tools, as well as a macro programming language known as Visual Basic for apps.

The above code will not work for the XLSX format. Although there will be no error while saving the file but file not be opened. We will get an unsupported file extension error. You can try with MIME type application/vnd.openxmlformats-officedocument.spreadsheetml.sheet for saving files as XSLX extension.

We will use an external JS library write-excel-file for saving data with the XSLS extension. By default, this JS library can not be used in Salesforce as it is blocked by the locker service. You can download the JS library from GitHub and upload it in the static resource with the name writeexcelfile.

Let us add the apex class code to retrieve data from the Account object. This is required to make a dynamic Excel sheet based on our requirements. This is a very simple apex code to retrieve data, you can create an apex class based on your requirement.

This LWC code is using an external JS library WriteExcelFile using Static Resource. As mentioned put static resource name as writeexcelfile. If you want to give a different name, then change in LWC code as well in the import section.

I have around 14 years of Experience in Web Based Application. In this experience, I have worked with various technology like SalesForce, .NET, .NET Core, MS Dynamic CRM, Azure, Oracle, SQl Server, WCF, Ionic, Angular.I am more focused on Technology instead of Management. I love to know and research about new technology.

Hello Veer,
You can check the documentation at -excel-file#readme for your request. Header color can be done. About Star symbol not sure,but you can try adding with encoded format for star. If not working, please connect with thier support team.

Hello, is it possible to export only the filtered rows to excel? When doing the export, it will hide the rows that were filtered, but they will still be in the excel so if I try to copy and paste or create a mailmerge, the excel will also pick up the hidden rows.

@Mark Salamon If you create the report and just leave it there, adjusting the report criteria is pretty similar to adjusting sheet filter criteria. Just keep the report, and you can do a quick adjust on that, and you don't have to worry about the sheet itself being saved with the wrong filter or anything.

I opened a support ticket, but I can't close this pop-up no matter what I click or what browser I use and I'm wondering if anyone has any insight. I'm LOCKED OUT of my work because of some stupid UI refresh alert. ?

I am using Salesforce data and when I export the data to excel it has first rows showing which filters were used and some basic information (who generated the report, time and name of the person). Data headers are only on 12 row and data is starting from row 13 of excel file. I would like to read this data and perform calculations in Alteryx, but I would like to skip first rows and read data starting from row 12. Alteryx flow should also recognize that header are on raw 12. Do you have any suggestions how to solve it?

Then with Alteryx I want to read those reports together. I build a flow for these. However the problem is now that I have a long list of these values and these filter rows are appearing in the middle of the list. I wonder how I could remove fist these filter rows and then combine the data?

Maybe I'm not understanding. If you're reading a series of Excel files, then you have the same options at the Input Tool for the Input Data Source Template. Did you mean you are directly querying the database?

That option is only available in xlsx and not xls. You can use the "first row contains data" to push your headers into the first row. Then use a combination of a Record ID tool and a Sample or Filter tool to remove the rows you don't want. Then use a Dynamic Rename tool to take the data from the first row to create headers.

Extra I performed a different, second test.
Now in the second database I first drop the entire REST module and then again I run the export script from the first database against the second database.
Upon completion I export the rest definitions from the second database and make a file compare against the script I ran.
Again these are different in order.

Is there a way to import back this file into the same schema using sqlplus? We put our code in git and we take the source from there when deploying to prod and we actually need to open apex web administrator to export/import the rest services and doing via sqlplus would save us time.

Searching a little more in the web, i found that by running the following would allow me to execute an ords script through sqlplus and it did compile fine, however, when i go to our apex web manager -> sql workshop -> restful services -> ords restful services -> click the module i made changes, the changes i made are not present. Would you mind to help me to make this script work please?

it seems like autorest enabled objects are not exported using ORDS_METADATA.ords_export.export_schema(). The asscociated roles and privileges are part of the export, but not the ords.enable_object() call.