[SheetJS] xslx import/export functions improvments

[ThibautSF]

Introduction

Hi,

As I'm working on an x-spreadsheet integration, and would like to also allow xlsx import/export I started to work also with SheetJS.
Using suggesting codes from SheetJS demo shows that some informations are lost during the import&export.

I observed the lost of :

• Merged cells
• Formulas (result being interpreted directly)
• Lost of types (export) (Note: basic fix, might need upgrade -> string/number/empty cells correctly exported)
• Lost of all empty left columns (import only ?)
• Styles ¹

Thus I started to work on improving import/export functions.
Feel free to use those functions, make more tests and repost any bug-less versions, or optimize (either for browser support or better performances).
I will try to keep track of updated versions here.

Import functions

Known bugs:

• If spreadsheet doesn't start at column 1, merged cells will be incorrect (due to XLSX.utils.sheet_to_json 'trimming' empty columns)

Version: 0.2 (basic semantic to keep track)

/**
 * Converts data from SheetJS to x-spreadsheet
 * 
 * @param  {Object} wb SheetJS workbook object
 * @param  {Boolean} [keepMerges=false] Does the conversion keep merged cells? (default: false)
 * @param  {Boolean} [keepFormulas=false] Does the conversion keep formulas (=true) or result value (=false)? (default: false)
 * 
 * @returns {Object[]} An x-spreadsheet data
 */
function stox(wb, keepMerges, keepFormulas) {
    keepMerges = keepMerges === undefined ? false : keepMerges;
    keepFormulas = keepFormulas === undefined ? false : keepFormulas;

    var out = [];
    wb.SheetNames.forEach(function (name) {
        var o = { name: name, rows: {} };
        var ws = wb.Sheets[name];
        var range = XLSX.utils.decode_range(ws['!ref']);
        // sheet_to_json will lost empty row and col at begin as default
        range.s = { r: 0, c: 0 }
        var aoa = XLSX.utils.sheet_to_json(ws, {
            raw: false,
            header: 1,
            range: range,
        });

        aoa.forEach(function (r, i) {
            var cells = {};
            r.forEach(function (c, j) {
                cells[j] = { text: c };

                if (keepFormulas) {
                    var cellRef = XLSX.utils.encode_cell({ r: i, c: j })

                    if (
                        ws[cellRef] != undefined &&
                        ws[cellRef].f != undefined
                    ) {
                        cells[j].text = "=" + ws[cellRef].f;
                    }
                }
            });
            o.rows[i] = { cells: cells };
        });

        if (keepMerges) {
            o.merges = [];
            ws["!merges"].forEach(function (merge, i) {
                //Needed to support merged cells with empty content
                if (o.rows[merge.s.r] == undefined) {
                    o.rows[merge.s.r] = { cells: {} };
                }
                if (o.rows[merge.s.r].cells[merge.s.c] == undefined) {
                    o.rows[merge.s.r].cells[merge.s.c] = {};
                }

                o.rows[merge.s.r].cells[merge.s.c].merge = [
                    merge.e.r - merge.s.r,
                    merge.e.c - merge.s.c,
                ];

                o.merges[i] =
                    XLSX.utils.encode_cell(merge.s) +
                    ":" +
                    XLSX.utils.encode_cell(merge.e);
            });
        }

        out.push(o);
    });

    return out;
}

Note:

• Call stox(wb) or stox(wb, false, false) will give the same result as original function.
• If keepMerges=true all merged cells in spreadsheet file will be transferred to x-spreadsheet instance.
• If keepFormulas=true all formulas in spreadsheet file will be written as is in their cells (instead of their interpreted value)

Export functions

Known bugs:

• Cells with date type might encounter some problems on the exported file (type converted as string by code)
• Same for booleans (code not tested)

Version: 0.2 (basic semantic to keep track)

/**
 * Converts data from x-spreadsheet to SheetJS
 *
 * @param  {Object[]} sdata An x-spreadsheet data object
 * @param  {Boolean} [keepMerges=false] Does the conversion keep merged cells? (default: false)
 * @param  {Boolean} [keepFormulas=false] Does the conversion keep formulas (=true) or result value (=false)? (default: false)
 *
 * @returns {Object} A SheetJS workbook object
 */
function xtos(sdata, keepMerges, keepFormulas) {
    keepMerges = keepMerges === undefined ? false : keepMerges;
    keepFormulas = keepFormulas === undefined ? false : keepFormulas;

    let out = XLSX.utils.book_new();
    sdata.forEach(function (xws) {
        var ws = {};
        var rowobj = xws.rows;
        for (var ri = 0; ri < rowobj.len; ++ri) {
            var row = rowobj[ri];
            if (!row) continue;

            var minCoord, maxCoord;
            Object.keys(row.cells).forEach(function (k) {
                var idx = +k;
                if (isNaN(idx)) return;

                var lastRef = XLSX.utils.encode_cell({ r: ri, c: idx });
                if (minCoord == undefined) {
                    minCoord = {
                        r: ri,
                        c: idx,
                    };
                } else {
                    if (ri < minCoord.r) minCoord.r = ri;
                    if (idx < minCoord.c) minCoord.c = idx;
                }
                if (maxCoord == undefined) {
                    maxCoord = {
                        r: ri,
                        c: idx,
                    };
                } else {
                    if (ri > maxCoord.r) maxCoord.r = ri;
                    if (idx > maxCoord.c) maxCoord.c = idx;
                }

                var cellText = row.cells[k].text,
                    type = "s";
                if (!cellText) {
                    cellText = "";
                    type = "z";
                } else if (!isNaN(parseFloat(cellText))) {
                    cellText = parseFloat(cellText);
                    type = "n";
                } else if (cellText === "true" || cellText === "false") {
                    cellText = Boolean(cellText);
                    type = "b";
                }

                ws[lastRef] = {
                    v: cellText,
                    t: type,
                };

                if (keepFormulas && type == "s" && cellText[0] == "=") {
                    ws[lastRef].f = cellText.slice(1);
                }

                if (keepMerges && row.cells[k].merge != undefined) {
                    if (ws["!merges"] == undefined) ws["!merges"] = [];

                    ws["!merges"].push({
                        s: {
                            r: ri,
                            c: idx,
                        },
                        e: {
                            r: ri + row.cells[k].merge[0],
                            c: idx + row.cells[k].merge[1],
                        },
                    });
                }
            });

            ws["!ref"] =
                XLSX.utils.encode_cell({ r: minCoord.r, c: minCoord.c }) +
                ":" +
                XLSX.utils.encode_cell({ r: maxCoord.r, c: maxCoord.c });
        }

        XLSX.utils.book_append_sheet(out, ws, xws.name);
    });

    return out;
}

Note:

• Call xtos(sdata) or xtos(sdata, false, false) will give the same result as original function (Note: cell type are updated).
• If keepMerges=true all merged cells in x-spreadsheet instance will be transferred to spreadsheet file.
• If keepFormulas=true all formulas in x-spreadsheet instance will be written as is in their cells

Issues for reference

#8
#20
#55
#112
#157
SheetJS/sheetjs#2165

Miscellaneous

Old functions (not used anymore) but kept in case it might be interesting for someone:

• columnToLetter(column) calls can be replaced by XLSX.utils.encode_col(column - 1)
• coordinateToReference(row, col) calls can be replaced by XLSX.utils.encode_cell({r: row - 1, c: col - 1})

/**
 * Transform a sheet index to it's column letter.
 * Warning: Begin at 1 ! 1=>A, 2=>B...
 *
 * @param  {number} column
 * @param  {string}
 */
function columnToLetter(column) {
    var temp,
        letter = "";
    letter = letter.toUpperCase();
    while (column > 0) {
        temp = (column - 1) % 26;
        letter = String.fromCharCode(temp + 65) + letter;
        column = (column - temp - 1) / 26;
    }
    return letter;
}

/**
 * Transform coordinates to it's reference.
 * Warning: Begin at 1 ! row= 1 & col= 1 => A1
 *
 * @param  {string} row
 * @param  {string} col
 * @returns {string} a sheet like coordinate
 */
function coordinateToReference(row, col) {
    return columnToLetter(col) + String(row);
}

/**
 * Transforms a sheet column letter to it's index.
 * Warning: Begin at 1 ! A=>1, B=>2...
 *
 * @param  {string} letter column value (ie: A, B, AA...)
 * @returns {number}
 */
function letterToColumn(letter) {
    var column = 0,
        length = letter.length;
    for (var i = 0; i < length; i++) {
        column += (letter.charCodeAt(i) - 64) * Math.pow(26, length - i - 1);
    }
    return column;
}

/**
 * Transforms a reference (ie B2) to it's coordinate.
 * Warning: Begin at 1 ! A1 => {col: 1, row: 1}
 *
 * @param  {string} reference a sheet like coordinate
 * @returns {object} a number coordinates
 */
function referenceToCoordinate(reference) {
    l1 = reference.replace(/[^a-zA-Z]/g, "");
    n1 = reference.replace(/[^0-9]/g, "");

    return {
        col: letterToColumn(l1),
        row: n1,
    };
}

---

1 Styles are obtained only with SheetJS Pro, as I don't own a license yet, I won't be able to make a version with styles import/export. ↩

[Liyaa]

Import functions
Known bugs:

If spreadsheet doesn't start at column 1, merged cells will be incorrect (due to XLSX.utils.sheet_to_json 'trimming' empty columns)

XLSX.utils.sheet_to_json will trim empty rows and columns for it transform data for a specified range, default is safe_decode_range(sheet["!ref"]), for example, if sheet["!ref"] === 'B2:C3', the range is start at (1, 1), then the first row and columns was ignored:

So, we can specify range start at (0, 0) manually, such as:

……
const sheet: any = { name: name, rows: {} }
if (!ws || !ws['!ref']) {
  console.error('!ref not found for worksheet')
  return sheet
}

const range = utils.decode_range(ws['!ref'])
if (!range || !range.e) {
  console.error(
    'decode_range for !ref failed, worksheet !ref is: ',
    ws['!ref']
  )
  return sheet
}

// sheet_to_json will lost empty row and col at begin as default
range.s = { r: 0, c: 0 }

const aoa = XLSX.utils.sheet_to_json(ws, {
  raw: false,
  header: 1,
  range: range,
})
……

[reviewher]

@ThibautSF please raise an issue / send a PR to the actual demo. https://github.com/SheetJS/sheetjs/blob/master/demos/xspreadsheet/xlsxspread.js the import and export functions have been moved into their own script

[ThibautSF]

Ok, I will do that.
About functions in "Shared dependencies" should I add them in this same file too?

And I suppose that I need to update Readme too.

[reviewher]

columnToLetter(col) is XLSX.utils.encode_col(col - 1)

coordinateToReference(row, col) is either XLSX.utils.encode_col(col-1) + String(row) or XLSX.utils.encode_cell({r: row - 1, c: col - 1})

[ThibautSF]

Ok thanks

[reviewher]

(The code samples in the README can be removed. Instead, it should state that the xtos and stox are defined in xlsxspread.js)

[ThibautSF]

Issue and PR created on SheetJS/sheetjs#2410

Code updated here too (0.2) to incorporate suggestions by @Liyaa and @reviewher (and in order to be even with PR).

[saberjsd]

Is this any update for export excel with styles?

[adrijanb]

maybe now some updates ?

[lilianli]

I used 'xlsx-js-style', which extends 'xlsx' and provides to export styles. The data structure of x-spreadsheet is different from the 'xlsx-js-style' input and I made a transformation.

Known bug:

• border mistake of merged cells.

xtos(sdata: any[], keepMerges = true, keepFormulas = true) {
    function transformStyle(styleObj: Object):Object {
      const result = {};
      if (styleObj)
        Object.keys(styleObj).map(key => {
          switch (key) {
            case 'align':
              result['alignment'] = Object.assign(result['alignment'] || {}, { horizontal: styleObj[key] });
              break;
            case 'valign':
              result['alignment'] = Object.assign(result['alignment'] || {}, { vertical: styleObj[key] });
              break;
            case 'font':
              result['font'] = Object.assign(result['font'] || {}, styleObj[key]);
              if (result['font'] && result['font']['size'])
                result['font'] = Object.assign(result['font'] || {}, { sz: styleObj[key]['size'] });
              break;
            case 'underline':
              result['font'] = Object.assign(result['font'] || {}, { underline: styleObj[key] });
              break;
            case 'strike':
              result['font'] = Object.assign(result['font'] || {}, { strike: styleObj[key] });
              break;
            case 'color':
              result['font'] = Object.assign(result['font'] || {}, { color: { rgb: styleObj[key].slice(1) } });
              break;
            case 'bgcolor':
              // result["fill"] = Object.assign((result["fill"] || {}), { bgColor: { rgb: styleObj[key].slice(1) },patternType:"solid" });
              result['fill'] = Object.assign(result['fill'] || {}, { fgColor: { rgb: styleObj[key].slice(1) }, patternType: 'solid' });
              break;
            case 'border':
              result['border'] = Object.assign(result['border'] || {}, {
                top: styleObj[key]['top'] ? { style: styleObj[key]['top'][0], color: { rgb: styleObj[key]['top'][1].slice(1) } } : null,
                bottom: styleObj[key]['bottom']
                  ? { style: styleObj[key]['bottom'][0], color: { rgb: styleObj[key]['bottom'][1].slice(1) } }
                  : null,
                left: styleObj[key]['left'] ? { style: styleObj[key]['left'][0], color: { rgb: styleObj[key]['left'][1].slice(1) } } : null,
                right: styleObj[key]['right']
                  ? { style: styleObj[key]['right'][0], color: { rgb: styleObj[key]['right'][1].slice(1) } }
                  : null
              });
              break;
            case 'textwrap':
              result['alignment'] = Object.assign(result['alignment'] || {}, { wrapText: styleObj[key] });
              break;
            default:;
          }
        });
      return result;
    }
    function formatText(styleObj: Object) :string {
      let format = '';
      if (styleObj)
        Object.keys(styleObj).map(key => {
          if ('format' === key) {
            switch (styleObj[key]) {
              case 'scientific':
                format = '0.00E+0';
                break;
              case 'percent':
                format = '0.00%';
                break;
              case 'number':
                format = '0.00';
                break;
              default:
            }
          }          
        });
      return format;
    }
    const out = XLSX.utils.book_new();
    sdata.forEach((xws)=> {
      const ws = {};
      const rowobj = xws.rows;
      let minCoord: { r: number; c: number } | undefined = { r: 0, c: 0 },
        maxCoord: { r: number; c: number } | undefined = { r: 0, c: 0 };
      for (let ri = 0; ri < rowobj.len; ++ri) {
        const row = rowobj[ri];
        if (!row) continue;

        Object.keys(row.cells).forEach((k)=> {
          const idx = +k;
          if (isNaN(idx)) return;

          const lastRef = XLSX.utils.encode_cell({ r: ri, c: idx });
          if (minCoord === undefined) {
            minCoord = { r: ri, c: idx };
          } else {
            if (ri < minCoord.r) minCoord.r = ri;
            if (idx < minCoord.c) minCoord.c = idx;
          }
          if (maxCoord === undefined) {
            maxCoord = { r: ri, c: idx };
          } else {
            if (ri > maxCoord.r) maxCoord.r = ri;
            if (idx > maxCoord.c) maxCoord.c = idx;
          }

          let cellText = row.cells[k].text,
            type = 's';
          if (!cellText) {
            cellText = '';
            // keep style and format
            const styleIndex = row.cells[k].style
            if(undefined === styleIndex || null === styleIndex){
              type = 'z';  // type 'z' will not export style
            }
            if(undefined !== styleIndex && xws.styles[styleIndex]['format'] && 'scientific' === xws.styles[styleIndex]['format']){
              cellText = 0;
              type = 'n'; 
            }
          } else if (!isNaN(parseFloat(cellText))) {
            cellText = parseFloat(cellText);
            type = 'n';
          } else if (cellText === 'true' || cellText === 'false') {
            cellText = Boolean(cellText);
            type = 'b';
          }
          ws[lastRef] = {
            v: cellText,
            t: type,
            z: formatText(xws.styles[row.cells[k].style]),
            s: transformStyle(xws.styles[row.cells[k].style])
          };
         
          if (keepFormulas && type === 's' && cellText[0] === '=') {
            ws[lastRef].f = cellText.slice(1);
          }

          if (keepMerges && row.cells[k].merge !== undefined) {
            if (ws['!merges'] === undefined) ws['!merges'] = [];

            ws['!merges'].push({
              s: {
                r: ri,
                c: idx
              },
              e: {
                r: ri + row.cells[k].merge[0],
                c: idx + row.cells[k].merge[1]
              }
            });
          }
        });
        ws['!ref'] =
          XLSX.utils.encode_cell({ r: minCoord.r, c: minCoord.c }) + ':' + XLSX.utils.encode_cell({ r: maxCoord.r, c: maxCoord.c });
      }
      XLSX.utils.book_append_sheet(out, ws, xws.name);
    });
    return out;
  }

[onigetoc]

It miss a right click: insert row and column before|after