Forums Ignite UI for jQuery
Your Privacy Matters: We use our own and third-party cookies to improve your experience on our website. By continuing to use the website we understand that you accept their use. Cookie Policy
Close
Ben Hull
820
Error exporting grid into excel
posted

Hello,
We are using Ignite UI v19.1 and when attempting to call the excel export, we are receiving the below error.

Code:
var workbook = new $.ig.excel.Workbook($.ig.excel.WorkbookFormat.excel2007);
var sheet = workbook.worksheets().add('Sheet1');

Error:
The 'Excel2007' format requires a seralizer that is not loaded, in JavaScript this is contained in the infragistics.excel_serialization_openxml.js file

Attached is a screenshot of the Infragistic JS files loaded and the JS function used to generate the excel export.

function.txt 
function ExportResults(isPortfolio) {

    var workbook = new $.ig.excel.Workbook($.ig.excel.WorkbookFormat.excel2007);
    var sheet = workbook.worksheets().add('Sheet1');
    var _data = isPortfolio == true ? $('#grid').igGridSelection("selectedRows") : $('#grid').igGrid('option', 'dataSource');
    var columns = $("#grid").igGrid('option', "columns");
    var _columnWidth = 0;
    var iColumn = 1;
    var iRow = 7;//Start writing rows here
    var platform = $(rblPlatform_clientID + ' input:checked').val();
    exporting = true;

    //Add header text and bold
    sheet.getCell('R1C1', 0).value("Investment Search");
    sheet.getCell('R1C1', 0).cellFormat().font().bold(true);

    sheet.getCell('R1C1', 0).cellFormat().font().height(300);

    sheet.getCell('R3C1', 0).value("Last Updated: " + formatDate($('#grid').data('igGrid').dataSource.dataView()[0].DataSnapshotDate));
    sheet.getCell('R3C1', 0).cellFormat().font().bold(true);

    sheet.getCell('R4C1', 0).value("Average Annual Total Return as of: " + formatDate($('#grid').data('igGrid').dataSource.dataView()[0].PerformanceAsOfDate));
    sheet.getCell('R4C1', 0).cellFormat().font().bold(true);

    //Add column headers
    if (isPortfolio == true) {
        addColumnHeader(sheet, "Action", iColumn);
        iColumn++;
        addColumnHeader(sheet, "Select Broker Comm", iColumn);
        iColumn++;
    }

    addColumnHeader(sheet, "Investment Family", iColumn);
    iColumn++;
    addColumnHeader(sheet, "Investment Name", iColumn);
    iColumn++;
    addColumnHeader(sheet, "NASDAQ Symbol", iColumn);
    iColumn++;
    addColumnHeader(sheet, "NASDAQ CUSIP", iColumn);
    iColumn++;
    if (platform == "Trust") {
        //SRT - 42928 UI Changes > CSTB > Investment Search Changes
        addColumnHeader(sheet, "CSTB" + " Symbol", iColumn);
        iColumn++;
        addColumnHeader(sheet, "CSTB" + " CUSIP", iColumn);
        iColumn++;
        addColumnHeader(sheet, "CSTB" + " Availability", iColumn);
        iColumn++;
        addColumnHeader(sheet, "Category", iColumn);
        iColumn++;
        addColumnHeader(sheet, "MMF Attribute", iColumn);
        iColumn++;
        addColumnHeader(sheet, "SSP %", iColumn);
        iColumn++;
        addColumnHeader(sheet, "Net OER %", iColumn);
        iColumn++;
        addColumnHeader(sheet, "Gross OER %", iColumn);
        iColumn++;
        addColumnHeader(sheet, "SDE", iColumn);
        iColumn++;
        addColumnHeader(sheet, "LON", iColumn);
        iColumn++;
        addColumnHeader(sheet, "Mutual Fund OneSource / ETF OneSource", iColumn);
        iColumn++;
        addColumnHeader(sheet, "Broker Commission", iColumn);
        iColumn++;
        addColumnHeader(sheet, "Broker 12b-1", iColumn);
        iColumn++;
        addColumnHeader(sheet, "Broker's Finder's Fee", iColumn);
        iColumn++;
        addColumnHeader(sheet, "CDSC Fee", iColumn);
        iColumn++;
        addColumnHeader(sheet, "Sub T/A Payment", iColumn);
        iColumn++;
        addColumnHeader(sheet, "Additional Broker Notes", iColumn);
        iColumn++;
    }
    else {
        addColumnHeader(sheet, "Custody Symbol", iColumn);
        iColumn++;
        addColumnHeader(sheet, "Mutual Fund OneSource / ETF OneSource", iColumn);
        iColumn++;
        addColumnHeader(sheet, "Transaction Fee", iColumn);
        iColumn++;
        addColumnHeader(sheet, "PCRA Minimum", iColumn);
        iColumn++;
        addColumnHeader(sheet, "Load Rate", iColumn);
        iColumn++;
        addColumnHeader(sheet, "Settlement", iColumn);
        iColumn++;
        addColumnHeader(sheet, "Custody Availability", iColumn);
        iColumn++;
        addColumnHeader(sheet, "Category", iColumn);
        iColumn++;
        addColumnHeader(sheet, "MMF Attribute", iColumn);
        iColumn++;
        addColumnHeader(sheet, "Net OER %", iColumn);
        iColumn++;
        addColumnHeader(sheet, "Gross OER %", iColumn);
        iColumn++;
    }

    addColumnHeader(sheet, "Category", iColumn);
    iColumn++;
    addColumnHeader(sheet, "NAV", iColumn);
    iColumn++;
    addColumnHeader(sheet, "7 Day Yield w/ Waiver", iColumn);
    iColumn++;
    addColumnHeader(sheet, "7 Day Yield w/o Waiver", iColumn);
    iColumn++;
    addColumnHeader(sheet, "Gross Yield", iColumn);
    iColumn++;
    addColumnHeader(sheet, "YTD", iColumn);
    iColumn++;
    addColumnHeader(sheet, "1 Yr", iColumn);
    iColumn++;
    addColumnHeader(sheet, "3 Yr", iColumn);
    iColumn++;
    addColumnHeader(sheet, "5 Yr", iColumn);
    iColumn++;
    addColumnHeader(sheet, "10 Yr", iColumn);
    iColumn++;
    addColumnHeader(sheet, "Since Inception", iColumn);
    iColumn++;
    addColumnHeader(sheet, "Inception Date", iColumn);
    iColumn++;
    addColumnHeader(sheet, "RF Status", iColumn);
    iColumn++;
    addColumnHeader(sheet, "RF Rate (%)", iColumn);
    iColumn++;
    addColumnHeader(sheet, "RF Holding Period", iColumn);
    iColumn++;
    addColumnHeader(sheet, "RF Effective Date", iColumn);
    iColumn++;
    addColumnHeader(sheet, "Business/Calendar Days", iColumn);
    iColumn++;
    addColumnHeader(sheet, "Trade/Settlement Date", iColumn);
    iColumn++;
    addColumnHeader(sheet, "Waived for System Limitations", iColumn);
    iColumn++;
    addColumnHeader(sheet, "Waived For Systematic or Auto Rebalance", iColumn);
    iColumn++;
    addColumnHeader(sheet, "Waived for Employer Ret Plan Decision", iColumn);
    iColumn++;
    addColumnHeader(sheet, "Waived for Participant Directed Distributions", iColumn);
    iColumn++;
    addColumnHeader(sheet, "Waived for Re-Invested Divs/Cap Gains", iColumn);
    iColumn++;
    addColumnHeader(sheet, "Waived for Unitized Fund to Funds", iColumn);
    iColumn++;
    addColumnHeader(sheet, "Waived for Various Fees", iColumn);
    iColumn++;
    addColumnHeader(sheet, "Waived for Plan Participant Contributions", iColumn);
    iColumn++;
    addColumnHeader(sheet, "Waived for Transfers Between Ret Plans", iColumn);
    iColumn++;
    addColumnHeader(sheet, "Waived for De Minimis Amount", iColumn);
    iColumn++;
    addColumnHeader(sheet, "De Minimis Amount", iColumn);
    iColumn++;
    if (platform == "Trust") {
        sheet.columns(iColumn).setWidth(80, $.ig.excel.WorksheetColumnWidthUnit.pixel);
        sheet.getCell('R6C' + iColumn, 0).cellFormat().fill($.ig.excel.CellFill.createSolidFill('#D8D8D8'));
        sheet.getCell('R6C' + iColumn, 0).cellFormat().font().bold(true);
        sheet.getCell('R6C' + iColumn, 0).cellFormat().font().colorInfo(new $.ig.excel.WorkbookColorInfo($.ig.excel.WorkbookThemeColorType.dark1));
        sheet.getCell('R6C' + iColumn, 0).value("ETF T+1");
    }

    //Load data into columns
    for (var i = 0; i < _data.length; i++) {
        var $record = (isPortfolio == true ? $("#grid").igGrid("findRecordByKey", _data[i].id) : _data[i]);
        iColumn = 1;
        if (isPortfolio == true) {
            sheet.getCell('R' + iRow + ('C' + iColumn), 0).value($("#gridPortfolio").igGrid("findRecordByKey", $record.AmdMfSearchId).action);
            iColumn++;
            sheet.getCell('R' + iRow + ('C' + iColumn), 0).value($("#gridPortfolio").igGrid("findRecordByKey", $record.AmdMfSearchId).brokerCompSelected ? "YES" : "NO");
            iColumn++;
        }

        sheet.getCell('R' + iRow + ('C' + iColumn), 0).value($record.InvestmentFamily);
        iColumn++;
        sheet.getCell('R' + iRow + ('C' + iColumn), 0).value($record.InvestmentName);
        iColumn++;
        sheet.getCell('R' + iRow + ('C' + iColumn), 0).value($record.NasdaqSymbol);
        iColumn++;
        sheet.getCell('R' + iRow + ('C' + iColumn), 0).value($record.NasdaqCusip);
        iColumn++;
        if (platform == "Trust") {
            sheet.getCell('R' + iRow + ('C' + iColumn), 0).value($record.TrustSymbol);
            iColumn++;
            sheet.getCell('R' + iRow + ('C' + iColumn), 0).value($record.TrustCusip);
            iColumn++;
            sheet.getCell('R' + iRow + ('C' + iColumn), 0).value($record.PlanAdminServicesAssetAvailStatus);
            iColumn++;
            sheet.getCell('R' + iRow + ('C' + iColumn), 0).value($record.AssetTypeText);
            iColumn++;
            sheet.getCell('R' + iRow + ('C' + iColumn), 0).value($record.MoneymarketFundRestrictionText);
            iColumn++;
            sheet.getCell('R' + iRow + ('C' + iColumn), 0).value(parseInt($('#grid').igGrid("getCellValue", $record.AmdMfSearchId, "RevenueShareBps")) / 100);
            iColumn++;
            sheet.getCell('R' + iRow + ('C' + iColumn), 0).value($record.OperatingExpenseRatio);
            iColumn++;
            sheet.getCell('R' + iRow + ('C' + iColumn), 0).value($record.GrossOperatingExpenseRatio);
            iColumn++;
            sheet.getCell('R' + iRow + ('C' + iColumn), 0).value($record.SameDayExchangeCodeText);
            iColumn++;
            sheet.getCell('R' + iRow + ('C' + iColumn), 0).value($record.LargeOrderNotificationThresholdAmount);
            iColumn++;
            sheet.getCell('R' + iRow + ('C' + iColumn), 0).value($record.TrustOnesource);
            iColumn++;
            sheet.getCell('R' + iRow + ('C' + iColumn), 0).value($record.BrokerCompensation);
            iColumn++;
            sheet.getCell('R' + iRow + ('C' + iColumn), 0).value($record.Fee12b1);
            iColumn++;
            sheet.getCell('R' + iRow + ('C' + iColumn), 0).value($record.FeeFinder);
            iColumn++;
            sheet.getCell('R' + iRow + ('C' + iColumn), 0).value($record.ContingentDeferredSalesCharge);
            iColumn++;
            sheet.getCell('R' + iRow + ('C' + iColumn), 0).value(parseInt($('#grid').igGrid("getCellValue", $record.AmdMfSearchId, "SubTransferAgentFee")) / 100);
            iColumn++;
            sheet.getCell('R' + iRow + ('C' + iColumn), 0).value($record.SubTransferAgent);
            iColumn++;
        }
        else {
            sheet.getCell('R' + iRow + ('C' + iColumn), 0).value($record.CustodySymbol);
            iColumn++;
            sheet.getCell('R' + iRow + ('C' + iColumn), 0).value($record.CustodyOnesource);
            iColumn++;
            sheet.getCell('R' + iRow + ('C' + iColumn), 0).value($record.TransactionFee);
            iColumn++;
            sheet.getCell('R' + iRow + ('C' + iColumn), 0).value($record.MinimumInitialPurchase);
            iColumn++;
            sheet.getCell('R' + iRow + ('C' + iColumn), 0).value($record.LoadRate);
            iColumn++;
            sheet.getCell('R' + iRow + ('C' + iColumn), 0).value($record.SellSettleDayTypeText);
            iColumn++;
            sheet.getCell('R' + iRow + ('C' + iColumn), 0).value($record.CustodyAssetStatus);
            iColumn++;
            sheet.getCell('R' + iRow + ('C' + iColumn), 0).value($record.AssetTypeText);
            iColumn++;
            sheet.getCell('R' + iRow + ('C' + iColumn), 0).value($record.MoneymarketFundRestrictionText);
            iColumn++;
            sheet.getCell('R' + iRow + ('C' + iColumn), 0).value($record.OperatingExpenseRatio);
            iColumn++;
            sheet.getCell('R' + iRow + ('C' + iColumn), 0).value($record.GrossOperatingExpenseRatio);
            iColumn++;
        }
        sheet.getCell('R' + iRow + ('C' + iColumn), 0).value($record.CategoryDescription);
        iColumn++;
        sheet.getCell('R' + iRow + ('C' + iColumn), 0).value($record.NetAssetValue);
        iColumn++;
        sheet.getCell('R' + iRow + ('C' + iColumn), 0).value($record.Day7YieldWithWaiver);
        iColumn++;
        sheet.getCell('R' + iRow + ('C' + iColumn), 0).value($record.Day7YieldWithoutWaiver);
        iColumn++;
        sheet.getCell('R' + iRow + ('C' + iColumn), 0).value($record.GrossYield);
        iColumn++;
        sheet.getCell('R' + iRow + ('C' + iColumn), 0).value($record.TotalReturnYearToDate);
        iColumn++;
        sheet.getCell('R' + iRow + ('C' + iColumn), 0).value($record.TotalReturn1Year);
        iColumn++;
        sheet.getCell('R' + iRow + ('C' + iColumn), 0).value($record.TotalReturn3Year);
        iColumn++;
        sheet.getCell('R' + iRow + ('C' + iColumn), 0).value($record.TotalReturn5Year);
        iColumn++;
        sheet.getCell('R' + iRow + ('C' + iColumn), 0).value($record.TotalReturn10Year);
        iColumn++;
        sheet.getCell('R' + iRow + ('C' + iColumn), 0).value($record.ReturnSinceInception);
        iColumn++;
        sheet.getCell('R' + iRow + ('C' + iColumn), 0).value(moment($record.InceptionDate).isValid() == false || moment($record.InceptionDate).year() <= 1900 ? "N/A" : moment($record.InceptionDate).format('L'));
        iColumn++;
        sheet.getCell('R' + iRow + ('C' + iColumn), 0).value($record.ContingentRedemptionFeeStatusText);
        iColumn++;
        sheet.getCell('R' + iRow + ('C' + iColumn), 0).value($record.ContingentRedemptionFee);
        iColumn++;
        sheet.getCell('R' + iRow + ('C' + iColumn), 0).value($record.ContingentRedemptionFeeHoldPeriod);
        iColumn++;
        sheet.getCell('R' + iRow + ('C' + iColumn), 0).value(moment($record.ContingentRedemptionFeeEffectiveDate).isValid() == false || moment($record.ContingentRedemptionFeeEffectiveDate).year() <= 1900 ? "N/A" : moment($record.ContingentRedemptionFeeEffectiveDate).format('L'));
        iColumn++;
        sheet.getCell('R' + iRow + ('C' + iColumn), 0).value($record.RedemptionFeeDaysTypeText);
        iColumn++;
        sheet.getCell('R' + iRow + ('C' + iColumn), 0).value($record.RedemptionFeeDateTypeText);
        iColumn++;
        sheet.getCell('R' + iRow + ('C' + iColumn), 0).value($record.RedemptionFeeSystemWaived);
        iColumn++;
        sheet.getCell('R' + iRow + ('C' + iColumn), 0).value($record.RedemptionFeeAutoRebalanceWaived);
        iColumn++;
        sheet.getCell('R' + iRow + ('C' + iColumn), 0).value($record.RedemptionFeeEmployerRetentionWaived);
        iColumn++;
        sheet.getCell('R' + iRow + ('C' + iColumn), 0).value($record.RedemptionFeeDistPartWaived);
        iColumn++;
        sheet.getCell('R' + iRow + ('C' + iColumn), 0).value($record.RedemptionFeeReinvestmentWaived);
        iColumn++;
        sheet.getCell('R' + iRow + ('C' + iColumn), 0).value($record.RedemptionFeeUnitizedWaived);
        iColumn++;
        sheet.getCell('R' + iRow + ('C' + iColumn), 0).value($record.RedemptionFeeFeesWaived);
        iColumn++;
        sheet.getCell('R' + iRow + ('C' + iColumn), 0).value($record.RedemptionFeeContributePartWaived);
        iColumn++;
        sheet.getCell('R' + iRow + ('C' + iColumn), 0).value($record.RedemptionFeePlanTransferWaived);
        iColumn++;
        sheet.getCell('R' + iRow + ('C' + iColumn), 0).value($record.RedemptionFeeDeminimisWaived);
        iColumn++;
        sheet.getCell('R' + iRow + ('C' + iColumn), 0).value($record.RedemptionFeeDiminimusAmount);
        iColumn++;
        if (platform == "Trust") {
            sheet.getCell('R' + iRow + ('C' + iColumn), 0).value($record.ETF);
        }
        iRow++;
    }
    // Save the workbook
    saveWorkbook(workbook, "InvestmentSearchExport.xlsx");
    exporting = false;
}