Quotation Excel Export
The quotation excel export script allows you to export quotation lines to an Excel file. The script fetches the quotation lines using the Data API and creates an Excel file with the lines. The file is then downloaded to the user's device when the link is clicked.
Install script1. Load the ExcelJS library​
We start by loading the ExcelJS library from a CDN. ExcelJS is a library that allows us to create and manipulate Excel files in JavaScript. We use the addEventListener
method to wait for the library to be loaded before we start using it.
When the library is loaded, we call the generateExcelSheet
function to create the Excel sheet.
For more information about ExcelJS, you can visit the official documentation.
2. Fetch quotation data​
From within our main generateExcelSheet
function, we start with loading the quotation data.
The fetchQuotation
and fetchQuotationLines
functions leverage the Data API to filter the quotation lines by the quotation ID. The Data API implements the OData specification, which allows us to filter data using the $filter
query parameter.
The fetchQuotationLines
function uses a helper function fetchAll
to fetch paginated results. By default the response is limited to 500 result items.
For more information about the Data API, you can visit the data integration page.
3. Create the Excel sheet​
We create a new Excel workbook and add a worksheet named 'Lines'. We then add a table to the worksheet with the columns 'Code', 'Description', and 'Quantity'.
The table data is populated with the quotation lines fetched in the previous step.
4. Download the Excel file​
Finally, we save the workbook as an Excel file. We use the writeBuffer
method to write the workbook to a buffer. We then create a Blob object from the buffer and create a download link.
When the link is clicked, the Excel file is downloaded to the user's device. We use the revokeObjectURL
method to release the object URL and remove the download link from the DOM.
Result​
The code above will generate an Excel file with the quotation lines. The file will be downloaded to the user's device when the link is clicked.
const scriptTag = document.createElement('script');
scriptTag.setAttribute('src', 'https://cdn.jsdelivr.net/npm/exceljs@4.3.0/dist/exceljs.min.js');
scriptTag.addEventListener('load', () => {
generateExcelSheet();
});
document.body.appendChild(scriptTag);
async function generateExcelSheet() {
const quotation = await fetchQuotation();
const quotationLines = await fetchQuotationLines();
const tableData = quotationLines
.map(l => [ l.articleCode, l.description, l.quantityAmount ]);
const workbook = new ExcelJS.Workbook();
const worksheet = workbook.addWorksheet('Lines');
worksheet.addTable({
name: 'Lines',
ref: 'A1',
headerRow: true,
style: {
},
columns: ['Code', 'Description', 'Quantity'].map(n => ({name: n, filterButton: true})),
rows: tableData,
});
const name = `${quotation.quotationNumber}v${quotation.versionNumber}.xlsx`;
saveWorkbook(workbook, name);
}
async function fetchQuotation(){
return (await api.fetch(`data/1/quotations/${parameters.quotationId}`)).body;
}
async function fetchQuotationLines() {
return await fetchAll(`data/1/quotationlines?\$filter=quotationId eq ${parameters.quotationId}`);
}
async function fetchAll(url) {
let nextUrl = url;
let result = [ ];
while(nextUrl) {
let response = await api.fetch(nextUrl);
result.push(...response.body.value);
nextUrl = response.body['@odata.nextLink'];
}
return result;
}
function saveWorkbook(workbook, name){
workbook.xlsx.writeBuffer().then(function (data) {
var blob = new Blob([data], {type: "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"});
var url = window.URL.createObjectURL(blob);
var a = document.createElement('a');
a.setAttribute("href", url);
a.setAttribute("download", name);
document.body.appendChild(a);
a.click();
window.URL.revokeObjectURL(url);
a.remove();
});
}