Export to Excel without a table

  • 2
  • Idea
  • Updated 2 years ago
  • Implemented
I found myself wanting to create a blank hidden table today just to use the export to excel function.

Is there a way to create a button to export all the fields from a model using the same functionality as the table export? (just without the table)
Photo of Greg Huete

Greg Huete

  • 2,340 Points 2k badge 2x thumb
  • exporting

Posted 6 years ago

  • 2
Photo of Zach McElrath

Zach McElrath, Employee

  • 54,004 Points 50k badge 2x thumb
Official Response
Actually, yes, you can!

At the simplest possible level, you can just call the "exportData() method on any Skuid Model once you get a reference to it:

var model = skuid.model.getModel('LeadData');
model.exportData();


and this will do what you're looking for.

However, the exportData method takes a LOT of possible optional parameters. We haven't exposed all these to our API yet, but here's some you can rely on that you might want to use:

fileName: the name of the file that will be generated
rows: an array of the particular data rows in the model that you want to export
fields: an array of the particular Model Fields that you want to have included in the export (as the Columns)
useAPINamesForHeaders: boolean. Defaults to false, but if true, the API Names for fields will be used as Column Headers instead of the Labels. Can be useful for doing imports/exports between systems.
additionalConditions: an array of JavaScript Condition objects to limit the data in your Model that actually gets exported. These conditions will be applied to rows, if rows is specified, or to model.data, if rows is not provided.

These can be used like this:

var model = skuid.model.getModel('LeadData');
model.exportData({
fileName: 'MyFile',
additionalConditions: [
{ field: "IsConverted", value: false, operator: '=' }
]
});

See the docs for more information.
(Edited)
Photo of Rajendra Rathore

Rajendra Rathore

  • 1,490 Points 1k badge 2x thumb
Hi Zach,

I also used :

var model = skuid.model.getModel('LeadData');
model.exportData({
fileName: 'MyFile',
additionalConditions: [
{ field: "IsConverted", value: false, operator: '=' }
]
}); That code for export. That export work perectly when i am doing testing inside salesforce. But once i use that same page in Force.com Site then its redirecting me on /apex/ExportData page. Can you please let me know how can we do export in force.com site using a custom button. Thanks, Rajendra
Photo of Rajendra Rathore

Rajendra Rathore

  • 1,490 Points 1k badge 2x thumb
Hi Zach,


I got it answer here:

Before you can reliably use the <skuid:page> Visualforce (VF) component approach, there is a post-install configuration step you will need to take to guarantee that your Skuid Pages will behave exactly the same as they would if you were taking the Redirect approach:

You must find the following 3 VF Pages that come in the Skuid app / managed package, and create a local clone of each of them with the exact same name:

  1. ExportData
  2. UploadImage
  3. Include
http://help.skuidify.com/m/page-assignments-and-overrides/l/201793-using-the-skuid-page-visualforce-component-to-run-skuid-pages

Thanks,
Rajendra
Photo of Greg Huete

Greg Huete

  • 2,340 Points 2k badge 2x thumb
Woo! Thanks Zach!
Photo of Peter Herzog

Peter Herzog

  • 1,704 Points 1k badge 2x thumb
Hey Zach, Can you clarify how to use 'fields:'?  Struggling to get this right.  If we don't use 'fields:' we get everything.  However we want to select the fields to export and when we specify them in an array it breaks.  
Photo of Zach McElrath

Zach McElrath, Employee

  • 54,004 Points 50k badge 2x thumb
Sure Peter, fields is an Array of Model Field objects, which we recommend that you retrieve via model.getField(fieldAPIName), like this:

var model = skuid.model.getModel('MyModel');
model.exportData({
    fileName: 'MyFile',
    fields: [
        model.getField('Name'),
        model.getField('BillingCountry'),
        model.getField('Industry'),
        model.getField('Owner.Name')
    ]
});
Photo of David Ross

David Ross

  • 562 Points 500 badge 2x thumb
Zach,

I have a customer who wants to export data and when using the Excel export option she can not pull all data without having to use the More button and with thousands of records it takes a long time.  What she is trying to do is export data to excel based on criteria to create a custom report in excel.  Would the above example work for this use case or is there a better way for her to do this?  She can only set the model to show about 500 records at a time before she throws an error using the table.  
Photo of Zach McElrath

Zach McElrath, Employee

  • 54,004 Points 50k badge 2x thumb
David, we are working on adding some options to Export to Excel in our upcoming release or the release after that, but ultimately we will only be able to let you export a theoretical max of 10,000 records using the Skuid-based export approach, due to limitations in how many "offset" records can be retrieved and limitations in Apex JSON Heap Size. Current state today, it would take some custom JavaScript that uses the Skuid Model API to achieve a "Load All" functionality.
Photo of James Charles

James Charles

  • 84 Points 75 badge 2x thumb
Hello Zach,

I have another question related to the exportData method.  This functionality has been very beneficial in allowing us to export data that has not yet been saved in the model.

Is there a condition that can be set when calling the method so leading zeros in text fields are not truncated?  We have department numbers like "000220" that are converted to "220" when exported.  

Thanks for your help.
Photo of Rob Hatch

Rob Hatch, Official Rep

  • 44,968 Points 20k badge 2x thumb
I'm pretty sure that it is Excel,  or whatever tool you are using to open the file that is stripping out the leading zeros.  You can test this by openining your downloaded file in a text editor and validating whether the leading zeros are there.  You can configure how Excel does the data import from the CSV.  See this document:  http://superuser.com/questions/431637/why-is-excel-removing-leading-leading-zeros-when-displaying-cs...
Photo of James Charles

James Charles

  • 84 Points 75 badge 2x thumb
Perfect!  Thanks so much Rob.
Photo of Ross Freeman

Ross Freeman

  • 154 Points 100 badge 2x thumb
Hello Zach, 

We have implemented a custom export button that calls the exportData() method on our model, the problem is our customer requested for the record ID to not be included in the exported file. I have tried including the fields parameter with the hopes it would only export the fields I specified, but the record Id is still appended to each row in the generated export file. I know you can remove the option to "append record id column" for the standard export button on a table, but is there a way to turn off this option via a parameter in the exportData() method?

Thank you.
Photo of Zach McElrath

Zach McElrath, Employee

  • 53,974 Points 50k badge 2x thumb
Yes Ross this is possible, just set the parameter doNotAppendRowIdColumn: true when calling exportData(), see the docs on exportData() for more information.
Photo of Ross Freeman

Ross Freeman

  • 154 Points 100 badge 2x thumb
Thank you, this is exactly what I was looking for!
Photo of Ryan Denis

Ryan Denis

  • 478 Points 250 badge 2x thumb
What is the best way to get record names rather than ids? For example I have a custom object table that has lookups to a user and an account. It is exporting the account id and user id rather than their names. 

Do I need to use the solution above rather than the standard export to excel functionality?

Thanks!
(Edited)
Photo of Zach McElrath

Zach McElrath, Employee

  • 54,004 Points 50k badge 2x thumb
Ryan, the export process by default exports BOTH the lookup record's Id and Name. You can circumvent this by only requesting the Name field in your export, e.g. using the example above / in the docs, request model.getField('User__r.Name') and model.getField('Account__r.Name') rather than model.getField('User__c'):

var model = skuid.model.getModel('MyModel');
model.exportData({
    fileName: 'MyFile',
    fields: [
        model.getField('Name'),
        model.getField('User__r.Name'),
        model.getField('Account__r.Name')
    ]
});
Photo of Ryan Denis

Ryan Denis

  • 478 Points 250 badge 2x thumb
Thanks Zach. For some reason mine is just exporting the lookup ids though. I will try the other method. 
Photo of Jarrod Hinson

Jarrod Hinson

  • 2,330 Points 2k badge 2x thumb
Has anyone made any custom export field "Picker" that would allow the end user to choose what fields from the model to export?

My users are asking for a way to export the table data but choose different fields depending on the scenario. Any ideas I can run with let me know.

This post is extremely helpful! Thanks all...
Photo of Jack Sanford

Jack Sanford, Champion

  • 10,090 Points 10k badge 2x thumb
Just thinking out loud here, but maybe you could do a a whole slew of UI-only fields that are checkboxes, that have the same API name of the fields you want. I think you'd want to have a model that has no fields except these UI-Only fields. 

Then in the JavaScript, if there's some way to "get all the fields in this model", you could iterate through the fields. I'm not at all a JS developer, but here's the kind of code I would try, after getting all the fields in a given model in an array called modelFields (not even sure THAT is possible)

 fields: [
$.each(modelFields, function(){	

var currentFieldName= this.Name;
model.getField('currentFieldName')
}
]
(Edited)
Photo of Ryan Denis

Ryan Denis

  • 542 Points 500 badge 2x thumb
When did "Export Columns" become an option!? Or have I been overlooking it this whole time? That's a great feature!

Photo of Rob Hatch

Rob Hatch, Official Rep

  • 44,968 Points 20k badge 2x thumb
So many features,  so little time!   Glad you like them.   I believe export columns was a Superbank thing. 
Photo of Matt Hoffman

Matt Hoffman

  • 172 Points 100 badge 2x thumb
is there a way to implement the "Attempt to Load in All Records Before Export" check box while using exportData()?
Photo of Bill McCullough

Bill McCullough, Champion

  • 13,542 Points 10k badge 2x thumb
Matt,

Check out->  loadAllRemainingRecords

https://docs.skuid.com/latest/en/skuid/api/skuid_model_model.html

Thanks,

Bill
Photo of Matt Hoffman

Matt Hoffman

  • 172 Points 100 badge 2x thumb
Thank you! I don't know how I missed that in the documentation.
Photo of Jack Sanford

Jack Sanford, Champion

  • 10,090 Points 10k badge 2x thumb
Is there a way to export a list of the conditions on a model and indicate which are activated and deactivated? 
Photo of Jack Sanford

Jack Sanford, Champion

  • 10,090 Points 10k badge 2x thumb
Separate question, is there a way in the javascript exportData to give an alternate name to the fields you are exporting, the way there is in the export options on the table?
Photo of Brayden Smith

Brayden Smith

  • 1,270 Points 1k badge 2x thumb
+1 - This would be very helpful.
Photo of Jack Sanford

Jack Sanford, Champion

  • 10,090 Points 10k badge 2x thumb
Brayden, you can do this by override field metadata on the model and giving your fields new labels. I use this code to get at the field labels, but I'm not sure it's necessary any more, they've fixed some things in the export code. 


var $ = skuid.$;
var today = new Date();
var model = skuid.model.getModel('YourModelName');
var fields = [ //use the api names of your fields here
    'Index',
    'Id',
    'Name', 
    'CustomField__c'    
];
var fieldsWithCorrectLabels = $.map(fields, function(v){
    var actualField = model.getField(v);
     console.log(actualField.id);
     
     return {
        id: actualField.id,
        name: actualField.name,
        label: actualField.label
    };    
});
model.exportData({
    fileName: 'Custom Export File Name '+today,
    fields: fieldsWithCorrectLabels,
    doNotAppendRowIdColumn: true,
    useAPINamesForHeaders: false
    });


H/T to skuid support for unofficially helping with this

Note: "Index" is a UI-Only field I made with the formula {{index}}, which gives you the row number in a column.