A complete example using excel-builder.js

Question

I need to use excel-builder.js to export excel file with stylings(header background color blue, freezing top row, column filtering), but the website with all documentation is not accessible anymore, I even contacted the author but without response.

If anyone can help create a small example with all these formattings, I will appreciate it a lot!

I know the following code will build an excel file:

var jsonData = [
['sample', 'data', 'for'],
['generating', 'excel', 'in'],
['java', 'script', ' ']
];

require(['excel-builder.js/excel-builder', 'download'], function (EB, downloader) {
    var JSworkBook = EB.createWorkbook();
    var JSworkSheet = JSworkBook.createWorksheet({name: 'Sheet'});

    JSworkSheet.setData(jsonData); 

    JSworkBook.addWorksheet(JSworkSheet);

    var data = EB.createFile(JSworkBook);
    downloader('Artist WB.xlsx', data);
});

Answer 1

UPDATE 2024-09-20:
Project is now maintained here: https://github.com/ghiscoding/excel-builder-vanilla
Thanks go to @ghiscoding for his excellent work upgrading it as well.

Original answer below:

I'm only adding this because the project is now declared dead (https://github.com/stephenliberty/excel-builder.js), and the website documentation is offline(http://excelbuilderjs.com/). So best we can do is find someone willing to take control of it with a fork, or share our code.

The server-side script..

<cfscript>

path="/app/uploads/temp/";
full_path=ExpandPath(path) & FORM.filename;
if (!DirectoryExists(ExpandPath(path))) DirectoryCreate(ExpandPath(path));
FileWrite(full_path,(BinaryDecode(FORM.contents,"Base64")));

json=StructNew();
json.file=path & FORM.filename;
WriteJSON(json);

</cfscript>

you could use PHP..

<?php
header("Content-type: ".$_POST['contentType']);
header("Content-disposition: attachment; filename=\"{$_POST['fileName']}\"");
echo base64_decode($_POST['contents']);
?>

or asp..

[Authorize]
[System.Web.Mvc.SessionState(System.Web.SessionState.SessionStateBehavior.ReadOnly)]
public class SystemController : ApiController {
    private ILog log = LogManager.GetLogger(typeof(SystemController));

    public class ExcelResponse {
        public String FILE = "";
    }
    public class ExcelRequest {
        public String filename;
        public String contentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
        public String contents;
    }

    [HttpPost]
    public ExcelResponse Excel(ExcelRequest request) {
        ExcelResponse response = new ExcelResponse();
        String the_path = "/_assets/temp/";
        String path=System.Web.HttpContext.Current.Server.MapPath("~"+the_path);
        log.Info("Generating excel file from data: "+path+request.filename);
        if (!Directory.Exists(path)) Directory.CreateDirectory(path);
        byte[] file_data=(Convert.FromBase64String(request.contents));
        File.WriteAllBytes(path + request.filename, file_data);
        response.FILE = the_path+request.filename;
        return response;
    }
}

And the include script...

require.config({
    baseUrl: '/app/assets/',
    paths: {
            underscore: '//cdnjs.cloudflare.com/ajax/libs/underscore.js/1.3.3/underscore-min',
            JSZip: './excel-builder/jszip',
            EB: './excel-builder.dist.min',
            spin: '//cdnjs.cloudflare.com/ajax/libs/spin.js/1.2.7/spin.min',
            image: '/3rdparty/requirejs/image',
            text: '/3rdparty/requirejs/text',
            util: '/excel-builder/Excel/util'
    },
    shim: {
            'underscore': {
                    exports: '_'
            },
            'JSZip': {
                    exports: 'JSZip'
            },
            'swfobject': {
                    exports: 'swfObject'
            }
    }
});

goes into ./excel-builder/download.js

define(function () {
    return function (fileName, content) {
        var form = $("<form id='download'>").attr({
            target: '_BLANK',
            action: '/app/assets/excel-builder/excel.cfm',
            method: 'post'
        }).css({display: 'none'});
        form.append($("<input>").attr({name: 'fileName', value: fileName}));
        form.append($("<input>").attr({name: 'contentType', value: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'}));
        var ta=$("<textarea>").attr({name: 'contents', value: content});
        ta.val(content);
        form.append(ta);
        form.appendTo($("body"));
        form.submit();
        window.setTimeout(function () {form.remove();}, 30000);
    }
});

The bootstrap (for tablesorter)...

$(document).ready(function() {
    $("table.sort").on("filterEnd",ts_onTableFiltered);
});

The meat of it...

function ts_onTableFiltered(event,tblsorter) {
    $(tblsorter.table).data('ts_sorter',tblsorter);/*legacy :: */ window.ts_currentSorter=tblsorter;
    console.log(event.type);
    
    $(tblsorter.table).data('ts_isFiltered',false); /*legacy. remove me after verification :: */ window.ts_isFiltered=false;
    for (var i=0;i<tblsorter.lastSearch.length;i++) {
        var currentItemFilter=((tblsorter.lastSearch[i]!="")?true:false);
        if (currentItemFilter) {
            $(tblsorter.table).data('ts_isFiltered',true);/* legacy:: */ts_isFiltered=true;
            break;
        }
    }
    var rows=[];
    if ($.isEmptyObject( tblsorter.cache )) {
        rows=tblsorter.$tbodies.eq( 0 ).children( 'tr' );
    } else {
        for(i=0;i<tblsorter.cache[0].normalized.length;i++) {
            var row=tblsorter.cache[0].normalized[i][tblsorter.columns].$row[0];
            if (!ts_isFiltered) {rows.push($(row));continue;}
            
            var result=tblsorter.pager.regexRows.test(row.className);
            if (!result) { // skip 'filtered' className
                rows.push($(row));
            }
        }
    }
    
    $(tblsorter.table).data('ts_currentRowsInTable',rows); /*legacy :: */window.ts_currentRowsInTable=rows;
    console.log(rows.length);
    
}
function downloadExcelFromTable(tableObj,filename,props) {
    var button=$('a:contains("Excel")');
    if (button.length > 1) {
        var temp=0;
        button.each(function() {
            if ($(this).attr('onclick').indexOf(tableObj.attr('id'))>=0) {
                temp=$(this);
            }
        });
        //button=temp;
    }
    console.log("Generating document...");
    var orig_data = [];
    var headers=[];
    var headers_text_len_max=0;
    
    var excel_wait_html='<i class="fa fa-spinner fa-pulse"></i> Please wait...';
    button.data('orig-html',button.html());button.html(excel_wait_html);
    
    if (tableObj.data('ts_currentRowsInTable').length<1 || (tableObj.data('ts_currentRowsInTable').length==1 && tableObj.find('tr:contains("No known")').length>0)) {
        return BootstrapMessage("Cannot download Excel document","Sorry but there aren't any result rows to populate an excel document. Please refine your report/resultant listing.",function() {
            button.html(button.data('orig-html'));
        });
    }
    var doSetupProperties=false;
    if (typeof(props)==='undefined') {props={};}
    if (typeof(props.column_widths)==='undefined') {
        doSetupProperties=true;
        props.column_widths=[];
        props.column_widths_auto=[];
        props.maxWidthPerRow=[];
    }
    
    var filterChosen=false;
    if (tableObj.hasClass("selectable")) filterChosen=true;
    
    var ignoreCols=[];
    var i=0;
    tableObj.find('th').each(function() {
        if ($(this).text().length<1) ignoreCols.push(i);
        else {
            headers.push($(this).text());
            props.maxWidthPerRow.push(0);
            headers_text_len_max+=$(this).text().length;
        }
        i++;
    });
    orig_data.push(headers);
    console.log("Headers done.");
    
    
    
    var handleRow=function(row,ignoreCols) {
        var col_i=0;
        row.find('td').each(function() {
            if ($.inArray(col_i,ignoreCols)<0) {
                var text=$(this).text().split('\n').join('').trim();
                if ($(this).hasClass('sorter-select')) {
                    text=$(this).find('select:first').val();
                }
                var contents=text;
                if (tableObj.hasClass('attachTotalsRow')) {
                    if ((''+tableObj.data('totals_columns')).indexOf(col_i+1)!=-1) {
                        text=(text.replace('$',''));
                        contents=parseFloat(text);
                    }
                    
                    //row_data.push({value: text,metadata: {style: }});
                    //row_data.push(parseFloat(text));
                }
                row_data.push(contents);
                
                var href=$(this).find('a');
                if (href.length>0) {
                    var url=href.attr('href');
                    row_data[row_data.length-1]={value: 'HYPERLINK("'+url+'","'+text+'")', metadata: { type: 'formula' }};
                }
                if (text.length > props.maxWidthPerRow[col_i]) props.maxWidthPerRow[col_i]=text.length;
            }
            col_i++;
        });
        return (row_data);
    }
    
    if (tableObj.hasClass('tablesorter')) {
        /*its tablesorter, so we need to obtain all of the hidden filtered data too. */
        var rows=tableObj.data('ts_currentRowsInTable');
        for (i=0;i<rows.length;i++) {
            var row_data=[];
            var row = $(rows[i]);
            /*if (filterChosen && !isNaN(tableObj.data('chosen'))) {
                var target=row.find('td:nth-child('+tableObj.data('chosen')+' input[type=checkbox]');
                if (target.length==1) {
                    if (!target.prop('checked')) continue;
                }
            }*/
            orig_data.push(handleRow(row,ignoreCols));
        }
        
        
    } else {
        tableObj.find('tbody tr').each(function() {
            var row_data=[];
            var row=$(this);
            orig_data.push(handleRow(row,ignoreCols));
        });
    }
    if (tableObj.hasClass('attachTotalsRow')) {
        var list_cols=tableObj.data('totals_columns');
        if (isNaN(list_cols)) {
            var av_cols=list_cols.split(',');
        } else {
            var av_cols=[list_cols];
        }
        av_cols=av_cols.sort();
        var max=orig_data.length;
        var min=2; // skip header row
        
        var new_totals_row=[];
        for (var ii=0;ii<headers.length;ii++) {
            new_totals_row[ii]="";
        }
        for (var i=0;i<av_cols.length;i++) {
            var colIdent=String.fromCharCode(64+av_cols[i]);
            new_totals_row[0]="Totals:"
            new_totals_row[av_cols[i]-1]={value: 'SUM('+((colIdent+min)+':'+(colIdent+max))+')',metadata: {type: 'formula'}};
        }
        orig_data.push(new_totals_row);
    }
    
    // adjust column widths to fit their text.
    if (doSetupProperties) {
        var maxDigitWidth=8;
        var padding=22;
        var fn_truncate=function(num) {return Math.round(num*100)/100;};
        var fn_calcWidth=function(numchars,maxdigit,pad) {return fn_truncate(((numchars *  maxdigit + pad)/maxdigit*256)/256   );};
        var fn_calcPixels=function(p_width,maxdigit){return fn_truncate(((256 * p_width + fn_truncate(128/maxdigit))/256)*maxdigit);};
        for (var i=0;i<headers.length;i++) {
            var perc_size_of_whole=(100/headers_text_len_max)*headers[i].length;
            //props.column_widths.push({width: perc_size_of_whole*1.5});
            //props.column_widths_auto.push({bestFit: true,width: maxWidthPerRow[i]});
            props.column_widths.push({width: (fn_calcWidth(props.maxWidthPerRow[i],maxDigitWidth,padding)) });
        }
    }
    console.log(props);
    
    console.log("Row data done.");
     
    require(['excel-builder/excel-builder', 'excel-builder/Excel/Table','excel-builder/download'], function (EB, Table, downloader) {
        console.log("Beginning excel creation.");
        var workbook = EB.createWorkbook();
        var worksheet = workbook.createWorksheet({name: filename});
     
        
     
        var stylesheet = workbook.getStyleSheet();
        var currency = stylesheet.createFormat({
            format: '$#,##0.00'
        });
        var boldDXF = stylesheet.createDifferentialStyle({
            
            font: {
                italic: true,
                size: 12
            }
        });
     
        var sheetStyle=stylesheet.createTableStyle({
            name: 'SlightlyOffColorBlue',
            wholeTable: boldDXF.id,
            headerRow: stylesheet.createDifferentialStyle({
                alignment: {horizontal: 'center'},
                font: {
                    size: 13,
                    bold: true
                }
            }).id
        });
        console.log("Styles applied.");
     
        
        var table = new Table();
        table.styleInfo.themeStyle = "SlightlyOffColorBlue"; //"TableStyleDark2"; //This is a predefined table style
        table.setReferenceRange([1, 1], [headers.length, orig_data.length]); //X/Y position where the table starts and stops.
     
        //Table columns are required, even if headerRowCount is zero. The name of the column also must match the
        //data in the column cell that is the header - keep this in mind for localization
        table.setTableColumns(headers);
        console.log("headers configured.");
     
        worksheet.setData(orig_data);
        worksheet.setColumns(props.column_widths)
        //worksheet.setColumnFormats(props.column_widths_auto);
        workbook.addWorksheet(worksheet);
     
        worksheet.addTable(table);
        workbook.addTable(table);
        console.log("tables configured.");
        
        var dt=new Date();
        var file= replaceAll(filename," ","_") +"_"+ (dt.getFullYear()+"-"+dt.getMonth()+"-"+dt.getDate()+"_"+dt.getMilliseconds())+'.xlsx';
        file=replaceAll(file,"/","_"); file=replaceAll(file,"\\","_");
        var data = EB.createFile(workbook);
        console.log("workbook generated. File generation commensing: ");
        //downloader(file, data);  // this uses the downloader.js file. Disabled as I am customizing the ajax call completely... as follows..
        
        /*legacy attempt..     var contents=''+
            '<form method="POST" action="/app/assets/excel-builder/excel.cfm" enctype="application/x-www-form-urlencoded">'+
                '<input type="hidden" name="filename" value="'+file+'" />'+
                '<input type="hidden" name="contentType" value="'+('application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')+'" />'+
                '<input type="hidden" name="contents" value="'+data+'" />'+
                
                '<input type="submit" value="Download Now" class="button print" />'+
            '</form>';*/
        var dat={
            filename: file,
            contentType: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
            contents: data
        };
        $.ajax({
            url: '/app/assets/excel-builder/excel2.cfm',
            type: "POST",
            data: dat,
            success: function(result) {
                result=$.parseJSON(result);
                console.log("Response received. File is located here: "+result.FILE);
                if (typeof(onExcelDocGenerated)==='function') onExcelDocGenerated(); // optional callback (global)
                contents='<a style="color: lightgreen;" href="'+result.FILE+'" class="button print" >Download Now</a>';
                $.notice("Confirm Excel Download",contents,"Cancel",function() {
                    button.html(button.data('orig-html'));
                    if (typeof(excel_download_done)==='function') {
                        excel_download_done(); // optional callback (global)
                    }
                },function() {
                    button.html(button.data('orig-html'));
                    if (typeof(excel_download_done)==='function') {
                        excel_download_done();
                    }
                });
            },
            error: function(e) {
                console.log("Fatal networking error: "+e);
            }
        });
            
    });
}

And the implementation on the page..

        <a href="#" id="exceldownload" onclick="try {downloadExcelFromTable($('table'),'My Report');}catch(e){alert('failed due to error: '+e);};return false;" class="button print table-excel">
            <i class="fa fa-file-excel-o"></i> Excel
        </a>

So that was a tablesorter full implementation supporting links inserted into the excel sheet and even a row for summation (totals) by simply adding the class(.attachTotalsRow) to the table class attr. Even supports filtering, so it only grabs the rows that are filtered by tablesorter for the excel, and auto-column sizing based on text width. There's several pieces that you can see were being developed, and lots of sparse comments and console logs thrown about, but it works. The above is designed for the ajax call which provides a path to a file in JSON response. That way you can use the browser native click, which is supported on all devices. Opening a new window through javascript is unsafe, so that is the reason for that approach. Therefore the php script at the top would need updated to mimic the coldfusion one.

The part you wanted is at the bottom just before the ajax call. Good luck!

Answer 2

        var workbook = ExcelBuilder.Builder.createWorkbook();
        var stylesheet = workbook.getStyleSheet();
        var sheet1 = workbook.createWorksheet({
                    name: 'Data1'
                });
        var headings = stylesheet.createFormat({
                    "fill": {
                        "type": 'pattern',
                        "patternType": 'solid',
                        "fgColor": '4BACC6'
                    }
                });
        var jsonData = [
        [{value:'sample', metadata: {style: headings.id}}, {value:'data', metadata: {style: headings.id}}, {value:'for', metadata: {style: headings.id}}],
        ['generating', 'excel', 'in'],
        ['java', 'script', ' ']
        ];
        sheet1.setData(jsonData);
        workbook.addWorksheet(sheet1);
                ExcelBuilder.Builder.createFile(workbook, {
                    type: "blob"
                }).then(function(data) {
                    saveAs(new Blob([data], {
                        type: "base64"
                    }), "Demo.xlsx");
                });

Answer 3

You can access a cached version of the documentation via the web archive . Keep in mind though that you'll need to navigate between the pages of the documentation using the cached-url-syntax, instead of just clicking on the links.