How to export data to excel in a grid with more than 50 columns?

Question

I am using a read-only grid to show data for reports. I need to export the data to excel but the grid has more than 50 columns. I have already tried default export to excel feature and a!exportDataStoreEntityToExcel() function but none of these worked. FYI: 
 
 Data is in SQL. 
 Appian version is 23.2. 
 Columns cannot be merged or it's count can't be reduced 
 
 Is there any way I can export the data to excel?

Shubham Aware · Answer

Hello yashikas77 , I would suggest you to use OOTB of appian to achieve this. We created xlsx file using below approach to create more than 50 columns to be exportable. 
 Create Web API: Parse request using a!fromJson( http!request.body) and retrieve information to query data for which identifier. Retrieves data using queryEntity from your database using request identifier. Encodes the generated Excel data in Base64 format using exportDataSubsetToBase64 function. Sends the Base64 encoded data as the response body in the HTTP response with appropriate headers. a!localVariables(
 /*Create Helper CDT of Request Type*/
 local!request: a!fromJson( http!request.body),/*Parse Request Here and cast to helper cdt*/
 local!entities: /*Retrive Data Here from DB using local!request parameters*/
 local!export:exportdatasubsettobase64(
 datasubset:todatasubset( local!entities),
 fieldLabels:{} /*The labels of header to set in Excel*/,
 fieldNames: {} /*The names of the datasubset fields to add to Excel*/,
 ),
 a!httpResponse(
 statusCode: 200,
 headers: 
 a!httpHeader(name: "Content-Type", value: "application/xlsx"),
 
 body: a!toJson(value: local!export)
 ),
 a!httpResponse(
 statusCode: 400,
 headers: 
 a!httpHeader(name: "Content-Type", value: "application/xlsx"),

 body: {}
 )
) 
 Create Integration: Calls the Web API using a POST method. Send request as for which identifier you want data(Create Request). Parses the JSON response from the Web API. Extracts the Base64 encoded data from the response body (likely from fv!result.body.base64Document). Decodes the Base64 data back into its original Excel format. Saves the decoded Excel data as an Appian document in the specified directory. Let me know if you have any questions.

Harsh Kumar Agarwal · Answer

Hi yashikas77 , 
 If you face heap memory issues as suggested in other replies, then as a workaround you can use following approach. 
 1. Using 'Export Data Store Entity to Excel' export first 50 columns. 
 2. Use the above document again in to export the next 50 columns. You can repeat this step until it won't covers all the columns. 
 3. In the step 2 make sure starting cell should be the (max column from previous output + 1) 
 4. Also, in step 2 you can export in a new tab each time. Keep a unique identifier among the tabs for business to create a VLOOKUP to navigate from one tab to another.

Kumar Agniwesh · Answer

a!httpResponse( /* * Set an HTTP header that tells the client that the body of the response * will be a CSV attachment. */ headers: { a!httpHeader( name: "Content-Disposition", value: "attachment; filename=" & char( 34 ) & "agniwesh" & now() & ".csv" & char( 34 ) ) }, /* * Create a CSV value of 'local!user' and place it in the response body. */ body: { "Id,name,age,comment" & char( 10 ) & joinarray( a!forEach( items: local!details, expression: { joinarray( merge( { char( 34 ) & fv!item.id & char( 34 ) }, { char( 34 ) & fv!item.name & char( 34 ) } ), "," ) } ), char( 10 ) ) } )