sheet_to_json empty cell be skip, suport to option is better? #582

New Issue

2017-03-07T01:49:42Z

jimjin commented

2017-03-07 01:49:42 +00:00

(Migrated from github.com)

excel:
Col1, Col2, Col3, Col4
Val1, Val2, ,Val4

after call sheet_to_json():
[{Col1: 'Val1', Col2: 'Val2', Col4:'Val4'}] # the key 'Col3' was lost.

But, i need result is: [{Col1: 'Val1', Col2: 'Val2', Col3: null, Col4:'Val4'}]

If has option skipUndfendVale and defaultValue will better?
like:

sheet_to_json({skipUndfendVale: false, defaultValue:null});
sheet_to_json({skipUndfendVale: false, defaultValue: ''});
sheet_to_json({skipUndfendVale: true});

excel: Col1, Col2, Col3, Col4 Val1, Val2, ,Val4 after call sheet_to_json(): `[{Col1: 'Val1', Col2: 'Val2', Col4:'Val4'}] # the key 'Col3' was lost.` But, i need result is: [{Col1: 'Val1', Col2: 'Val2', Col3: null, Col4:'Val4'}] If has option skipUndfendVale and defaultValue will better? like: ``` sheet_to_json({skipUndfendVale: false, defaultValue:null}); sheet_to_json({skipUndfendVale: false, defaultValue: ''}); sheet_to_json({skipUndfendVale: true}); ```

👍 1 🎉 2

jimjin commented

2017-03-07 02:18:16 +00:00

(Migrated from github.com)

And changed the soure code:

function sheet_to_json(sheet, opts){
  var val, row, range, header = 0, offset = 1, r, hdr = [], isempty, R, C, v;
  var o = opts != null ? opts : {};
  var skipUndefined = o.skipUndefined === undefined ? true : o.skipUndefined;
  var raw = o.raw;
  if(sheet == null || sheet["!ref"] == null) return [];
  range = o.range !== undefined ? o.range : sheet["!ref"];
  if(o.header === 1) header = 1;
  else if(o.header === "A") header = 2;
  else if(Array.isArray(o.header)) header = 3;
  switch(typeof range) {
    case 'string': r = safe_decode_range(range); break;
    case 'number': r = safe_decode_range(sheet["!ref"]); r.s.r = range; break;
    default: r = range;
  }
  if(header > 0) offset = 0;
  var rr = encode_row(r.s.r);
  var cols = new Array(r.e.c-r.s.c+1);
  var out = new Array(r.e.r-r.s.r-offset+1);
  var outi = 0;
  for(C = r.s.c; C <= r.e.c; ++C) {
    cols[C] = encode_col(C);
    val = sheet[cols[C] + rr];
    switch(header) {
      case 1: hdr[C] = C; break;
      case 2: hdr[C] = cols[C]; break;
      case 3: hdr[C] = o.header[C - r.s.c]; break;
      default:
        if(skipUndefined && val === undefined) continue;
        hdr[C] = format_cell(val);
    }
  }

  for (R = r.s.r + offset; R <= r.e.r; ++R) {
    rr = encode_row(R);
    isempty = true;
    if(header === 1) row = [];
    else {
      row = {};
      if(Object.defineProperty) Object.defineProperty(row, '__rowNum__', {value:R, enumerable:false});
      else row.__rowNum__ = R;
    }
    for (C = r.s.c; C <= r.e.c; ++C) {
      val = sheet[cols[C] + rr];
      if(val === undefined || val.t === undefined) {
      	if (!skipUndefined) {
	        row[hdr[C]] = o.defaultValue || '';
	        isempty = false;
      	}
        continue;
      };
      v = val.v;
      switch(val.t){
        case 'e': continue;
        case 's': break;
        case 'b': case 'n': break;
        default: throw 'unrecognized type ' + val.t;
      }
      if(!skipUndefined || v !== undefined) {
        row[hdr[C]] = raw ? v : format_cell(val,v);
        isempty = false;
      }
    }
    if(isempty === false || header === 1) out[outi++] = row;
  }
  out.length = outi;
  return out;
}

And changed the soure code: ``` function sheet_to_json(sheet, opts){ var val, row, range, header = 0, offset = 1, r, hdr = [], isempty, R, C, v; var o = opts != null ? opts : {}; var skipUndefined = o.skipUndefined === undefined ? true : o.skipUndefined; var raw = o.raw; if(sheet == null || sheet["!ref"] == null) return []; range = o.range !== undefined ? o.range : sheet["!ref"]; if(o.header === 1) header = 1; else if(o.header === "A") header = 2; else if(Array.isArray(o.header)) header = 3; switch(typeof range) { case 'string': r = safe_decode_range(range); break; case 'number': r = safe_decode_range(sheet["!ref"]); r.s.r = range; break; default: r = range; } if(header > 0) offset = 0; var rr = encode_row(r.s.r); var cols = new Array(r.e.c-r.s.c+1); var out = new Array(r.e.r-r.s.r-offset+1); var outi = 0; for(C = r.s.c; C <= r.e.c; ++C) { cols[C] = encode_col(C); val = sheet[cols[C] + rr]; switch(header) { case 1: hdr[C] = C; break; case 2: hdr[C] = cols[C]; break; case 3: hdr[C] = o.header[C - r.s.c]; break; default: if(skipUndefined && val === undefined) continue; hdr[C] = format_cell(val); } } for (R = r.s.r + offset; R <= r.e.r; ++R) { rr = encode_row(R); isempty = true; if(header === 1) row = []; else { row = {}; if(Object.defineProperty) Object.defineProperty(row, '__rowNum__', {value:R, enumerable:false}); else row.__rowNum__ = R; } for (C = r.s.c; C <= r.e.c; ++C) { val = sheet[cols[C] + rr]; if(val === undefined || val.t === undefined) { if (!skipUndefined) { row[hdr[C]] = o.defaultValue || ''; isempty = false; } continue; }; v = val.v; switch(val.t){ case 'e': continue; case 's': break; case 'b': case 'n': break; default: throw 'unrecognized type ' + val.t; } if(!skipUndefined || v !== undefined) { row[hdr[C]] = raw ? v : format_cell(val,v); isempty = false; } } if(isempty === false || header === 1) out[outi++] = row; } out.length = outi; return out; } ```

reidblomquist commented

2017-03-13 17:58:41 +00:00

(Migrated from github.com)

Killer - thanks for this @jimjin using your fork till this gets merged into releases!

Reejesh-PK commented

2022-08-05 05:39:33 +00:00

(Migrated from github.com)

Make sure you are using the latest version of js from https://cdn.sheetjs.com/ (Read "how to use") and you can refer this https://github.com/SheetJS/sheetjs/issues/159#issuecomment-403228877

Sign in to join this conversation.