I don't think there's a way to do this directly out of an ArcGIS REST web service. I utilized a separate libraries to support doing this, namely:
<!--Libraries for importing and exporting Excel data-->
<script type="text/javascript" src="//unpkg.com/xlsx/dist/shim.min.js"></script>
<script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.10.6/xlsx.full.min.js"></script>
<script type="text/javascript" src="//unpkg.com/blob.js@1.0.1/Blob.js"></script>
<script type="text/javascript" src="//unpkg.com/file-saver@1.3.3/FileSaver.js"></script>
Following the logic of this page from 'Ferry Ren' https://www.cnblogs.com/zhenggaowei/p/11732170.html, I built a procedure to pull the data from the REST endpoint into a JSON string, and then exported it into an Excel file.
The main thing to keep in mind is that if you have more than 2000 records to be exported, you need to keep pulling them until you get them all. I 'borrowed' the concept of using a recursive function from Gavin Rehkemper (https://gavinr.com/arcgis-javascript-query-all-features/ ). It looks something like this:
// Recursive function - Handles calling the service multiple times if necessary.
const _getAllRecsRecursive = (layerURL, featuresSoFar, sWhere, relID) => {
// Have to use queryTask instead of queryFeatures as the related tables can't be turned into feature layers.
if (relID < 0) {
let aQuery = new Query();
aQuery.start = featuresSoFar.length;
aQuery.num = 2000;
aQuery.where = sWhere;
aQuery.outFields = ["*"];
return query.executeQueryJSON(layerURL, aQuery).then(function (results) {
// If "exceededTransferLimit" is true, then make another request (call this same function) with a new "start" position.
// If not, we're at the end and we should just concatenate the results and return what we have.
if (
results.exceededTransferLimit &&
results.exceededTransferLimit === true
) {
return _getAllRecsRecursive(
layerURL,
[...featuresSoFar, ...results.features],
sWhere,
-1
);
} else {
return Promise.resolve([...featuresSoFar, ...results.features]);
}
});
}
else {
let aRelQuery = new Query(); //RelationshipQuery();
aRelQuery.start = featuresSoFar.length;
aRelQuery.num = 2000;
aRelQuery.where = sWhere;
aRelQuery.outFields = ['*'];
aRelQuery.relationshipId = relID;
return query.executeRelationshipQuery(layerURL, aRelQuery).then(function (results) {
// If "exceededTransferLimit" is true, then make another request (call this same function) with a new "start" position.
// If not, we're at the end and we should just concatenate the results and return what we have.
let curFeatures = [];
for (let aRecIdx of Object.keys(results)) {
let aRelSet = results[aRecIdx];
for (let aFeature of aRelSet.features) {
curFeatures.push(aFeature);
}
}
if (results.exceededTransferLimit &&
results.exceededTransferLimit === true) {
return _getAllRecsRecursive(
layerURL,
[...featuresSoFar, ...curFeatures],
sWhere,
relID
);
} else {
return Promise.resolve([...featuresSoFar, ...curFeatures]);
}
});
}
};
function getAllRecs(layerURL, sWhere, relId) {
return _getAllRecsRecursive(layerURL, [], sWhere, relId);
}
// Export to excel
let btnExport = document.getElementById("btnExport");
let dateNow = Date.now();
btnExport.onclick = function () {
// Create a new Excel Workbook
var workBook = {
SheetNames: ["Testing"],
Sheets: {},
Props: {
Title: "Title for Testing",
Subject: "Testing",
Author: "Your name, leave blank, or ?",
CreatedDate: dateNow
}
// Pull data until there is no more, storing data until needed later
let outResults = [];
let outWhere = "1=1"; // modify this to support subset of total recs
getAllRecs(restURL, outWhere, -1).then((results) => {
for (let aRec of results) {
let curAtts = aRec.attributes;
// Make any necessary attribute modifications here...
// I have routines here to fix dates from Epoch dates, remove edit fields, etc.
// Push results to results list
outResults.push(curAtts);
//console.log(curStopAtts)
stopObjectIds.push(curStopAtts[fldBusStopOID]); //"OBJECTID"]);
}
})
// Set up Excel workbook
var wopts = {
bookType: 'ods',
bookSST: false,
type: 'binary'
};
// Pull in workbook sheet.
try {
workBook.Sheets["Testing"] = XLSX.utils.json_to_sheet(outResults, { dateNF: 'mm/dd/yyyy;@', cellDates: true });
// Export spreadsheet using unique name built from current date/time.
let today = new Date(Date.now());
let cDate = (today.getMonth() + 1) + '_' + today.getDate() + '_' + today.getFullYear();
let cTime = today.getHours() + "_" + today.getMinutes() + "_" + today.getSeconds();
let sToday = cDate + '_' + cTime;
let exportFileName = 'BSR_' + sToday + '.xls'; //'BSR_' + sToday + '.xls'; //'BSR_' + sToday + '.ods'; //'BSR_' + sToday + '.xlsx';
let wbOut = XLSX.write(workBook, wopts);
saveAs(new Blob([changeData(wbOut)], { type: "" }), exportFileName);
} catch (error) {
console.log(error);
}
};
Hopefully that all makes sense. I took out a lot that I have in there as I'm cleaning data as it gets pulled, and I use it to export out related data as well (hence the 'relID' variable in the getAllRecs and _getAllRecsRecursive procedures). Also, it pushes out an ODS file (Open Document Spreadsheet). I was able to push it out to a CSV file, but then Excel always complained that it wasn't in the correct format, so I changed it to ODS, which is obviously not the 'correct' format, but when it is opened in Excel (no problems there) the user understands why the error pops up that it's not in the correct format.
Would love to know if there's an easier way to do this, but this has been operational for over a year now.
