I am trying to use a snippet and a custom button to export model data to excel but I have a couple things I am stuck on. I am mainly doing this because I want to export fields that are outside my table but do not want to append the record id. It seems I can’t do both point and click.
1. When using a snippet, how do I add custom labels to the fields? Is this possible?
2. When using this method it seems I lose the functionality of selecting which rows I want to export. Is this true? Can you only export all rows?
Here’s my sample snippet:
var model = skuid.model.getModel(‘Estimates’);
model.exportData({
fileName: ‘All_Estimates’,
usetablecolumns:“false”,
fields: [
model.getField(‘Estimate__r.Name’),
model.getField(‘Estimate__r.Description__c’),
model.getField(‘Estimate__r.Location__r.Name’),
model.getField(‘Status__c’),
model.getField(‘Estimate__r.Status__c’)
],
doNotAppendRowIdColumn: true,
});
Question
Using a Snippet to Export to Excel
+5Translate
This topic has been closed for comments
+3Hi Ryan
Try this:
var $ = skuid.$;<br /><br />var model = skuid.model.getModel('Estimates'),<br /> TABLE_UNIQUE_ID = 'yourTableId',<br /> list = skuid.$('#'+TABLE_UNIQUE_ID).data('object').list,<br /> selectedRows = skuid.$.map(list.getSelectedItems(),function(item){ <br /> return item.row; <br /> });<br /><br />var estName = model.getField('Estimate__r.Name'),<br /> estDesc = model.getField('Estimate__r.Description__c');<br />estName.label = 'yourlabel';<br />estDesc.label = 'yourlabel';<br /><br />model.exportData({<br /><br /> fileName: 'All_Estimates',<br /><br /> fields: [<br /> estName,<br /> estDesc<br /> ],<br /><br /> rows: selectedRows,<br /> <br /> doNotAppendRowIdColumn: true,<br /><br />});
Add the rest of your fields to the code.
This will export all rows, which you selected in the table (Like a mass action).
Hope that helps…
Translate
+10I’ve built a custom export routine (tested in V1) using SheetJS that exports data from a Model or a Table to an Excel file instead of a CSV and allows you to get pretty granular with your export. It works for all fields except Templates and Child Relationships (I can’t find where this is in the javascript). I tend to use UI formula fields on the model instead of templates to achieve similar results while keeping this usable.
You’d need to add the SheetJS library ( https://sheetjs.com/ ) as a static resource ( https://cdn.sheetjs.com/xlsx-0.19.3/package/dist/xlsx.full.min.js – the file to turn into a static resource ) on the page. Then you can use the following functions:
// skuid.custom.sheetJSFromTable(c)
// takes a table component or a table component's ID
// returns array of arrays to use for XLSX.utils.aoa_to_sheet
// table MUST have "Show Export Button" selected
// can add the CSS class "hideExport" to the table to force hide the export button
// c: table's component or component's ID
// options:
// {
// showHidden: true //defaults to true
// }
skuid.custom.sheetJSFromTable = function(c, options){
if(c === undefined){
return undefined;
}
//If c is a string it is the component id, get the component from the component id
if(typeof c === 'string'){
c = skuid.$C(c);
}
if(c===undefined || c.element===undefined || c.element.fieldsMetadataForExport===undefined || c.element.model===undefined){
return undefined;
}
//Get table settings to determine if columns are set as userHidden
let set = c._personalizationService.getSettings();
if (!set.columnSettingsByUID) {
set.columnSettingsByUID = c.element._columnSettingsByUID;
}
function getKey(object,value) {
return Object.keys(object).find(key => object[key]['fieldId'] === value);
}
let showHidden = true;
if(options !== undefined && options.showHidden !== undefined){
showHidden = options.showHidden;
}
let sheetJSDataOptions = {fields: []};
let meta = c.element.fieldsMetadataForExport;
let model = c.element.model;
for(let o of meta){
if(o.type === 'childRelationship'){
//Handle child relationship column
}
else if(o.id === undefined && o.template !== undefined){
//Handle template field
}
else if(o.id === undefined){
//Handle Button or Image field (do nothing)
}
else{
//Handle standard model field
if(showHidden){
sheetJSDataOptions.fields.push({id: o.id, name: o.label});
}
else{
if(!set.columnSettingsByUID[getKey(set.columnSettingsByUID,o.id)]?.userHidden){
sheetJSDataOptions.fields.push({id: o.id, name: o.label});
}
}
}
}
return skuid.custom.sheetJSData(model.data,sheetJSDataOptions);
}
// skuid.custom.sheetJSData(d, options)
// takes model.data and options object
// returns array of arrays to use for XLSX.utils.aoa_to_sheet
// d: model.data array
// options:
// {
// //fields to include in export
// fields: [
// {id: 'FIELDID', name: 'RENAMEFIELDCOLUMNHEADERTOTHIS'}
// ]
// includeId: false //defaults to false
// }
skuid.custom.sheetJSData = function (d, options) {
if(d===undefined || d.length === 0){
return undefined;
}
let retArr = [];
let usingFields = false;
if(options !== undefined && options.fields !== undefined){
usingFields=true;
}
let fieldsToSkip = {};
fieldsToSkip['__skuid_record__'] = true;
fieldsToSkip['Id'] = true;
if(options !== undefined && options.includeId === true){
fieldsToSkip['Id'] = false;
}
//construct first row
let firstRow = [];
function pushSubObj(row,obj,preObjStr){
if(preObjStr === undefined){
preObjStr = '';
}
if(typeof obj !== 'object'){
if(fieldsToSkip[obj] === false){
row.push(obj);
}
return row;
}
for (const [key, value] of Object.entries(obj)) {
if(fieldsToSkip[preObjStr+'.'+key] === true || fieldsToSkip[key] === true){
continue;
}
else if(typeof value === 'object' && value !== null){
row = pushSubObj(row,value,preObjStr+'.'+key);
}
else{
row.push(value);
}
}
return row;
}
if(!usingFields){
for (const [key, value] of Object.entries(d[0])) {
if(fieldsToSkip[key] === true){
continue;
}
firstRow = pushSubObj(firstRow,key);
}
}
else{
for (let i=0; i < options.fields.length; i++){
let field = options.fields[i];
let fieldName = field.id;
if(field.name !== undefined){
fieldName = field.name;
}
firstRow.push(fieldName);
}
}
retArr.push(firstRow);
for(let i=0; i < d.length; i++){
let o = d[i];
let thisRow = [];
if(!usingFields){
for (const [key, value] of Object.entries(o)) {
if(fieldsToSkip[key] === true){
continue;
}
thisRow = pushSubObj(thisRow,value,key);
}
}
else{
for (let j=0; j < options.fields.length; j++){
let fieldId = options.fields[j].id;
thisRow.push(Object.byString(o,fieldId));
}
}
retArr.push(thisRow);
}
return retArr;
};
Here are some examples of using it:
//Make sure to embed the Static Resource: “SheetJS” into your SKUID Page
//----Basic Export Example (Component/Table Export)----
//Use a global action on a table to get the component from params.component
//Table must have “Show Export Button” selected to use this function
//If you want to hide the export button, apply the hideExport css class to the table
//Put the snippet in an action sequence that has an Input of “fileSubname” to get the fileSubname
var params = arguments[0];
var $ = skuid.$;
let component = params.component;
let fileSubname = params['$Input'].fileSubname;
//Get the current date in yyyy-mm-dd format
var todayDate = new Date();
todayDate.setMinutes(todayDate.getMinutes() - todayDate.getTimezoneOffset());
todayDate = todayDate.toISOString().slice(0,10);
let fn = `${todayDate}_${fileSubname}`;
// make sure this is a valid file name
fn = fn.replace(/[ &\/\\#,+()$~%.'":*?<>{}\[\]]/g, "");
//create a workbook
var wb = XLSX.utils.book_new();
//create a worksheet
//use our skuid.sheetJSData to translate model.data to a proper export using specific fields
var ws = XLSX.utils.aoa_to_sheet(skuid.custom.sheetJSFromTable(component));
//use skuid.custom.sheetJSFromTable(component, {showHidden: false})
//to not export hidden items
//append the worksheet
//third parameter is the worksheet name, max chars = 31
//invalid worksheet name characters: \ / * [ ] : ?
XLSX.utils.book_append_sheet(wb, ws, fn.substring(0,30).replace(/[ &\/\\#,+()$~%.'":*?<>{}\[\]]/g, ""));
//create and append additional worksheets if desired
//export
XLSX.writeFile(wb, fn+'.xlsx');
//----Basic Export Example (Model Export)----
var params = arguments[0];
var $ = skuid.$;
// use specific fields on the model for export
let fieldsToUse = [];
fieldsToUse.push({id:'Debit'});
fieldsToUse.push({id:'Credit'});
// will use field ID as the column header by default, override this using “name”
fieldsToUse.push({id:'amount',name:'Amount'});
//Get the current date in yyyy-mm-dd format
var todayDate = new Date();
todayDate.setMinutes(todayDate.getMinutes() - todayDate.getTimezoneOffset());
todayDate = todayDate.toISOString().slice(0,10);
let fn = 'FileNameToUse_'+todayDate;
// make sure this is a valid file name
fn = fn.replace(/[ &\/\\#,+()$~%.'":*?<>{}\[\]]/g, "");
//create a workbook
var wb = XLSX.utils.book_new();
//create a worksheet
//use our skuid.sheetJSData to translate model.data to a proper export using specific fields
var ws = XLSX.utils.aoa_to_sheet(skuid.custom.sheetJSData(skuid.$M('OurModel').data,{fields: fieldsToUse}));
//append the worksheet
//third parameter is the worksheet name, max chars = 31
//invalid worksheet name characters: \ / * [ ] : ?
XLSX.utils.book_append_sheet(wb, ws, fn.substring(0,30).replace(/[ &\/\\#,+()$~%.'":*?<>{}\[\]]/g, ""));
//create and append additional worksheets if desired
//export
XLSX.writeFile(wb, fn+'.xlsx');
//-----Advanced Export Example (Model Export)-----
var params = arguments[0], $ = skuid.$;
let bankRecModel = skuid.$M('BankRecInclude__BankRec');
let bankRecRow = bankRecModel.getFirstRow();
let startDate = bankRecModel.getFieldValue(bankRecRow,'Start_Date__c',true);
let endDate = bankRecModel.getFieldValue(bankRecRow,'End_Date__c',true);
let bankAccount = bankRecModel.getFieldValue(bankRecRow,'Bank_Account__r.Name',true);
let fn = 'BankRec_'+bankAccount+'_'+startDate+'_-_'+endDate;
// make sure this is a valid file name
fn = fn.replace(/[ &\/\\#,+()$~%.'":*?<>{}\[\]]/g, "");
//create a workbook
var wb = XLSX.utils.book_new();
//create a worksheet
//use our skuid.sheetJSData to translate model.data to a proper export using specific fields
//append the worksheet
XLSX.utils.book_append_sheet(wb, XLSX.utils.aoa_to_sheet(skuid.custom.sheetJSData(skuid.$M('BankRecInclude__BankRec').data,
{fields: [
{id:'Name',name:'Bank Rec Name'},
{id:'Bank_Account__r.Name',name:'Bank GL Account'},
{id:'Start_Date__c',name:'Start Date'},
{id:'End_Date__c',name:'End Date'},
{id:'Ending_Balance__c',name:'Bank Balance'},
{id:'ReconcilingCashDisbursements__c',name:'Outstanding Checks/Vouchers'},
{id:'ReconcilingBankDeposits__c',name:'Deposits in Transit'},
{id:'ReconcilingUndepositedCashReceipts__c',name:'Undeposited Cash Receipts'},
{id:'ReconcilingVoidCashReceipts__c',name:'Voided Receipts'},
{id:'ReconcilingJournalEntryLines__c',name:'Other Cash Items'},
{id:'ReconcilingSuspenseItems__c',name:'Suspense Items'},
{id:'ReconciledBankBalance__c',name:'Reconciled Bank Balance'},
{id:'GLBalance__c',name:'Balance Per Books'},
{id:'UnreconciledAmount__c',name:'Unreconciled Difference'},
{id:'Cleared_Disbursements__c',name:'Cleared Checks/Vouchers'},
{id:'Cleared_Deposits__c',name:'Cleared Deposits'},
{id:'ClearedVoidedCashReceipts__c',name:'Cleared Voided Receipts'},
{id:'ClearedJournalEntries__c',name:'Cleared Other Cash Items'},
{id:'ClearedTotal__c',name:'Cleared Total'}
]})), 'Overview');
if(skuid.$M('BankRecInclude__ReconcilingItemsCDs').data.length > 0){
XLSX.utils.book_append_sheet(wb, XLSX.utils.aoa_to_sheet(skuid.custom.sheetJSData(skuid.$M('BankRecInclude__ReconcilingItemsCDs').data,
{fields: [
{id:'CashDisbursement__r.Name',name:'CD #'},
{id:'CashDisbursement__r.CheckReference__c',name:'Check/Ref #'},
{id:'CashDisbursement__r.TE_Disbursement_Date__c',name:'Disbursement Date'},
{id:'CashDisbursement__r.Batch__r.Name',name:'Batch'},
{id:'CashDisbursement__r.TE_Vendor__r.Name',name:'Vendor Name'},
{id:'CashDisbursement__r.TE_Vendor__r.UnifiedID__c',name:'Vendor ID'},
{id:'Amount__c',name:'Amount'}
]})), 'Outstanding Checks_Vouchers');
}
if(skuid.$M('BankRecInclude__ReconcilingItemsDeposits').data.length > 0){
XLSX.utils.book_append_sheet(wb, XLSX.utils.aoa_to_sheet(skuid.custom.sheetJSData(skuid.$M('BankRecInclude__ReconcilingItemsDeposits').data,
{fields: [
{id:'BankDeposit__r.Name',name:'Deposit #'},
{id:'BankDeposit__r.Batch__r.Name',name:'Batch'},
{id:'Amount__c',name:'Amount'}
]})), 'Deposits In Transit');
}
if(skuid.$M('BankRecInclude__ReconcilingItemsSuspense').data.length > 0){
XLSX.utils.book_append_sheet(wb, XLSX.utils.aoa_to_sheet(skuid.custom.sheetJSData(skuid.$M('BankRecInclude__ReconcilingItemsSuspense').data,
{fields: [
{id:'Name',name:'Item #'},
{id:'Notes__c',name:'Notes'},
{id:'Amount__c',name:'Amount'}
]})), 'Suspense Items');
}
if(skuid.$M('BankRecInclude__ReconcilingItemsUndepCRs').data.length > 0){
XLSX.utils.book_append_sheet(wb, XLSX.utils.aoa_to_sheet(skuid.custom.sheetJSData(skuid.$M('BankRecInclude__ReconcilingItemsUndepCRs').data,
{fields: [
{id:'UndepositedCashReceipt__r.Name',name:'CR #'},
{id:'UndepositedCashReceipt__r.TE_Payment_Reference__c',name:'Payment Reference'},
{id:'UndepositedCashReceipt__r.Batch__r.Name',name:'Batch'},
{id:'UndepositedCashReceipt__r.NameonCheck__c',name:'Name on Check'},
{id:'UndepositedCashReceipt__r.TE_Account__r.Name',name:'Account Name'},
{id:'UndepositedCashReceipt__r.TE_Account__r.UnifiedID__c',name:'Account Number'},
{id:'UndepositedCashReceipt__r.TE_Receipt_Date__c',name:'Receipt Date'},
{id:'Amount__c',name:'Amount'}
]})), 'Undeposited Cash Receipts');
}
if(skuid.$M('BankRecInclude__ReconcilingItemsVoidCRs').data.length > 0){
XLSX.utils.book_append_sheet(wb, XLSX.utils.aoa_to_sheet(skuid.custom.sheetJSData(skuid.$M('BankRecInclude__ReconcilingItemsVoidCRs').data,
{fields: [
{id:'VoidedCashReceipt__r.Name',name:'CR #'},
{id:'VoidedCashReceipt__r.Batch__r.Name',name:'CR Batch'},
{id:'VoidedCashReceipt__r.Void_Cash_Receipt__r.Batch__r.Name',name:'Adjustment Batch'},
{id:'VoidedCashReceipt__r.TE_Receipt_Date__c',name:'Receipt Date'},
{id:'VoidedCashReceipt__r.Void_Cash_Receipt__r.TE_Receipt_Date__c',name:'Void Date'},
{id:'VoidedCashReceipt__r.TE_Account__r.Name',name:'Account Name'},
{id:'VoidedCashReceipt__r.TE_Account__r.UnifiedID__c',name:'Account Number'},
{id:'Amount__c',name:'Amount'}
]})), 'Voided Receipts');
}
if(skuid.$M('BankRecInclude__ReconcilingItemsJELs').data.length > 0){
XLSX.utils.book_append_sheet(wb, XLSX.utils.aoa_to_sheet(skuid.custom.sheetJSData(skuid.$M('BankRecInclude__ReconcilingItemsJELs').data,
{fields: [
{id:'JournalEntryLine__r.TE_Journal_Entry__r.Name',name:'Journal Entry'},
{id:'JournalEntryLine__r.TE_Journal_Entry__r.Description__c',name:'Description'},
{id:'JournalEntryLine__r.Name',name:'Journal Entry Line'},
{id:'JournalEntryLine__r.Description__c',name:'Line Description'},
{id:'JournalEntryLine__r.TE_Journal_Entry__r.Batch__r.Name',name:'Batch'},
{id:'JournalEntryLine__r.TE_Journal_Entry__r.TE_Journal_Date__c',name:'Date'},
{id:'Amount__c',name:'Amount'}
]})), 'Other Cash Items');
}
if(skuid.$M('BankRecInclude__CDs').data.length > 0){
XLSX.utils.book_append_sheet(wb, XLSX.utils.aoa_to_sheet(skuid.custom.sheetJSData(skuid.$M('BankRecInclude__CDs').data,
{fields: [
{id:'Name',name:'CD #'},
{id:'CheckReference__c',name:'Check/Ref #'},
{id:'Batch__r.Name',name:'Batch'},
{id:'TE_Vendor__r.Name',name:'Vendor Name'},
{id:'TE_Vendor__r.UnifiedID__c',name:'Vendor ID'},
{id:'TE_Disbursement_Date__c',name:'Disbursement Date'},
{id:'AmountSignChange',name:'Amount'}
]})), 'Cleared Checks_Vouchers');
}
if(skuid.$M('BankRecInclude__Deposits').data.length > 0){
XLSX.utils.book_append_sheet(wb, XLSX.utils.aoa_to_sheet(skuid.custom.sheetJSData(skuid.$M('BankRecInclude__Deposits').data,
{fields: [
{id:'Name',name:'Number'},
{id:'Batch__r.Name',name:'Batch'},
{id:'Deposit_Date__c',name:'Deposit Date'},
{id:'Total_Deposit_Amount__c',name:'Amount'}
]})), 'Cleared Deposits');
}
if(skuid.$M('BankRecInclude__VoidCRs').data.length > 0){
XLSX.utils.book_append_sheet(wb, XLSX.utils.aoa_to_sheet(skuid.custom.sheetJSData(skuid.$M('BankRecInclude__VoidCRs').data,
{fields: [
{id:'Name',name:'Number'},
{id:'TE_Payment_Reference__c',name:'Payment Reference'},
{id:'Batch__r.Name',name:'Receipt Batch'},
{id:'Void_Cash_Receipt__r.Batch__r.Name',name:'Adjustment Batch'},
{id:'NameonCheck__c',name:'Name on Check'},
{id:'TE_Account__r.Name',name:'Account Name'},
{id:'TE_Account__r.UnifiedID__c',name:'Account Number'},
{id:'TE_Receipt_Date__c',name:'Receipt Date'},
{id:'Void_Cash_Receipt__r.TE_Receipt_Date__c',name:'Void Date'},
{id:'RecAmount',name:'Amount'}
]})), 'Cleared Voided Receipts');
}
if(skuid.$M('BankRecInclude__JELs').data.length > 0){
XLSX.utils.book_append_sheet(wb, XLSX.utils.aoa_to_sheet(skuid.custom.sheetJSData(skuid.$M('BankRecInclude__JELs').data,
{fields: [
{id:'TE_Journal_Entry__r.Name',name:'Journal Entry'},
{id:'TE_Journal_Entry__r.Description__c',name:'Description'},
{id:'Name',name:'Journal Entry Line'},
{id:'Description__c',name:'Line Description'},
{id:'TE_Journal_Entry__r.Batch__r.Name',name:'Batch'},
{id:'TE_Journal_Entry__r.TE_Journal_Date__c',name:'Date'},
{id:'SignedAmount__c',name:'Amount'}
]})), 'Cleared Other Cash Items');
}
//export
XLSX.writeFile(wb, fn+'.xlsx');
Translate
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.