2

I have a html table, which I'm passing to an Excel file with javascript. I'm using excellentexport library which I downloaded from https://github.com/jmaister/excellentexport . The data in the table transferring well to the excel file, except the numbers column, which some rows starting with zero and when the data is transferring to excel the numbers treated as octal. Numbers that not starting with zero are transferring well. so for example 0001 will be 1 in the excel file, and I do need it to be output as 0001 as original. attached my live sample code: http://jsfiddle.net/mv4ooc7m/1/ and I need that the excel output will be same as the HTML table. Anybody can help me how to fix the javascript code and to send the data as string? I'll be very glad to find a solution, thank you so much for your help!

HTML:

    <div id="itemRowsList2">
<table class="table table-hover" name="ItemsList" border="2">
<tbody>
<tr class="active" id="rowNum1"><td class="active">0001</td></tr>
<tr class="active" id="rowNum2"><td class="active">0002 </td></tr>
<tr class="active" id="rowNum3"><td class="active">1001 </td></tr>
<tr class="active" id="rowNum4"><td class="active">1002 </td></tr>
</tbody></table>
</div>

Javascript:

/**
 * ExcellentExport.
 * A client side Javascript export to Excel.
 *
 * @author: Jordi Burgos ([email protected])
 *
 * Based on:
 * https://gist.github.com/insin/1031969
 * http://jsfiddle.net/insin/cmewv/
 *
 * CSV: http://en.wikipedia.org/wiki/Comma-separated_values
 */

/*
 * Base64 encoder/decoder from: http://jsperf.com/base64-optimized
 */

/*jslint browser: true, bitwise: true, plusplus: true, vars: true, white: true */

var characters = 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/=';
var fromCharCode = String.fromCharCode;
var INVALID_CHARACTER_ERR = (function () {
        "use strict";
        // fabricate a suitable error object
        try {
            document.createElement('$');
        } catch (error) {
            return error;
        }
    }());

// encoder
if (!window.btoa) {
    window.btoa = function (string) {
        "use strict";
        var a, b, b1, b2, b3, b4, c, i = 0, len = string.length, max = Math.max, result = '';

        while (i < len) {
            a = string.charCodeAt(i++) || 0;
            b = string.charCodeAt(i++) || 0;
            c = string.charCodeAt(i++) || 0;

            if (max(a, b, c) > 0xFF) {
                throw INVALID_CHARACTER_ERR;
            }

            b1 = (a >> 2) & 0x3F;
            b2 = ((a & 0x3) << 4) | ((b >> 4) & 0xF);
            b3 = ((b & 0xF) << 2) | ((c >> 6) & 0x3);
            b4 = c & 0x3F;

            if (!b) {
                b3 = b4 = 64;
            } else if (!c) {
                b4 = 64;
            }
            result += characters.charAt(b1) + characters.charAt(b2) + characters.charAt(b3) + characters.charAt(b4);
        }
        return result;
    };
}

// decoder
if (!window.atob) {
    window.atob = function(string) {
        "use strict";
        string = string.replace(new RegExp("=+$"), '');
        var a, b, b1, b2, b3, b4, c, i = 0, len = string.length, chars = [];

        if (len % 4 === 1) {
            throw INVALID_CHARACTER_ERR;
        }

        while (i < len) {
            b1 = characters.indexOf(string.charAt(i++));
            b2 = characters.indexOf(string.charAt(i++));
            b3 = characters.indexOf(string.charAt(i++));
            b4 = characters.indexOf(string.charAt(i++));

            a = ((b1 & 0x3F) << 2) | ((b2 >> 4) & 0x3);
            b = ((b2 & 0xF) << 4) | ((b3 >> 2) & 0xF);
            c = ((b3 & 0x3) << 6) | (b4 & 0x3F);

            chars.push(fromCharCode(a));
            b && chars.push(fromCharCode(b));
            c && chars.push(fromCharCode(c));
        }
        return chars.join('');
    };
}


ExcellentExport = (function() {
    "use strict";
    var version = "1.3";
    var csvSeparator = ',';
    var uri = {excel: 'data:application/vnd.ms-excel;base64,', csv: 'data:application/csv;base64,'};
    var template = {excel: '<html xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns="http://www.w3.org/TR/REC-html40"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8"><!--[if gte mso 9]><xml><x:ExcelWorkbook><x:ExcelWorksheets><x:ExcelWorksheet><x:Name>{worksheet}</x:Name><x:WorksheetOptions><x:DisplayGridlines/></x:WorksheetOptions></x:ExcelWorksheet></x:ExcelWorksheets></x:ExcelWorkbook></xml><![endif]--></head><body><table>{table}</table></body></html>'};
    var csvDelimiter = ",";
    var csvNewLine = "\r\n";
    var base64 = function(s) {
        return window.btoa(window.unescape(encodeURIComponent(s)));
    };
    var format = function(s, c) {
        return s.replace(new RegExp("{(\\w+)}", "g"), function(m, p) {
            return c[p];
        });
    };

    var get = function(element) {
        if (!element.nodeType) {
            return document.getElementById(element);
        }
        return element;
    };

    var fixCSVField = function(value) {
        var fixedValue = value;
        var addQuotes = (value.indexOf(csvDelimiter) !== -1) || (value.indexOf('\r') !== -1) || (value.indexOf('\n') !== -1);
        var replaceDoubleQuotes = (value.indexOf('"') !== -1);

        if (replaceDoubleQuotes) {
            fixedValue = fixedValue.replace(/"/g, '""');
        }
        if (addQuotes || replaceDoubleQuotes) {
            fixedValue = '"' + fixedValue + '"';
        }
        return fixedValue;
    };

    var tableToCSV = function(table) {
        var data = "";
        var i, j, row, col;
        for (i = 0; i < table.rows.length; i++) {
            row = table.rows[i];
            for (j = 0; j < row.cells.length; j++) {
                col = row.cells[j];
                data = data + (j ? csvDelimiter : '') + fixCSVField(col.textContent.trim());
            }
            data = data + csvNewLine;
        }
        return data;
    };

    var ee = {
        /** @expose */
        excel: function(anchor, table, name) {
            table = get(table);
            var ctx = {worksheet: name || 'Worksheet', table: table.innerHTML};
            var hrefvalue = uri.excel + base64(format(template.excel, ctx));
            anchor.href = hrefvalue;
            // Return true to allow the link to work
            return true;
        },
        /** @expose */
        csv: function(anchor, table, delimiter, newLine) {
            if (delimiter !== undefined && delimiter) {
                csvDelimiter = delimiter;
            }
            if (newLine !== undefined && newLine) {
                csvNewLine = newLine;
            }
            table = get(table);
            var csvData = tableToCSV(table);
            var hrefvalue = uri.csv + base64(csvData);
            anchor.href = hrefvalue;
            return true;
        }
    };

    return ee;
}());

3 Answers 3

2

The x:str works fine for me!

<div id="itemRowsList2">
<table class="table table-hover" name="ItemsList" border="2">
<tbody>
<tr class="active" id="rowNum1"><td class="active" x:str="0001">0001</td></tr>
<tr class="active" id="rowNum2"><td class="active" x:str="0002">0002</td></tr>
<tr class="active" id="rowNum3"><td class="active" x:str="1001">1001</td></tr>
<tr class="active" id="rowNum4"><td class="active" x:str="1002">1002</td></tr>
</tbody></table>
</div>

Sign up to request clarification or add additional context in comments.

Comments

1

OK, so I figure it out, the answer is not related to javascript at all. Need to add x:str to the table/th/td tag and that's it!

So in the table tag level it should be like

<table class="table table-hover" id="ItemsList" border=2 x:str>

Simple as that!

Comments

0

x:str didn't work for me, but prefixing the number with &nbsp; did for exmaple <td>&nbsp;09876</td>.

Source: http://www.tek-tips.com/viewthread.cfm?qid=821266

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.