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 script

1. 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();
    });
}