solution for exporting long number strings to Excel

mnbob70

I had the same need as this discussion and never found a final solution. After 1.5 days of searching the web and experimentation I came up with the following which correctly displays the text for the columns (E, L, R) that I'm trying to export.

customize: function (xlsx) {
    let sheet = xlsx.xl.worksheets['sheet1.xml'];
    ['E', 'L', 'R'].forEach(function (value, key) {
        let data = $('row:gt(0) c[r^="' + value + '"]', sheet).text();
        let inlineString = '<is><t>' + data + '</t></is>';
        $('row:gt(0) c[r^="' + value + '"]', sheet).find('v').remove();
        $('row:gt(0) c[r^="' + value + '"]', sheet).removeAttr('s').attr('t', 'inlineStr').append(inlineString);
    });
},

mnbob70

Need to do some more testing as I have some reports of files needing repair.

mnbob70

This update deals with a column where the data is already a string. I didn't realize that one of the columns of data I was working with didn't just consist of digits. This searches for a "v" element and only replaces the v element with an inline string if it exists. If no "v" element exists, then the data is already a string.

customize: function (xlsx) {
    let sheet = xlsx.xl.worksheets['sheet1.xml'];
    ['E', 'L', 'R'].forEach(function (value, key) {
        let data = $('row:gt(0) c[r^="' + value + '"]', sheet).text();
        let v = $('row:gt(0) c[r^="' + value + '"]', sheet).find('v');
        if (v.length > 0) {
            let inlineString = '<is><t>' + data + '</t></is>';
            v.remove();
            $('row:gt(0) c[r^="' + value + '"]', sheet).removeAttr('s').attr('t', 'inlineStr').append(inlineString);
        }
    });
},

colin

Nice, thanks for posting!

Colin

mnbob70

Doh! The following is needed to apply the changes per row:

customize: function (xlsx) {
    let sheet = xlsx.xl.worksheets['sheet1.xml'];
    ['E', 'L', 'R'].forEach(function (value, key) {
        $('row:gt(0) c[r^="' + value + '"]', sheet).each(function () {
            let v = $(this).find('v');
            if (v.length > 0) {
                let data = $(this).text();
                v.remove();
                let inlineString = '<is><t>' + data + '</t></is>';
                $(this).removeAttr('s').attr('t', 'inlineStr').append(inlineString);
            }
        });
    });
},