Sheetjs: Pre formatting column names

[chathuraa]

Hi,

Thanks for this great lib. Is it possible to pre format column names? ie. remove line breaks, spaces make camelcase. If not, where should i look in the source to implement this?

Thanks,
Chat.

[SheetJSDev]

The sheet_to_json function takes an options variable, and the header parameter controls the labels in the JSON.

For example, consider the sheet with range A1:B4:

Suppose ws is the worksheet you want to export. Then, the normal JSON output is:

> XLSX.utils.sheet_to_json(ws)
[ { 'foo bar': '1',
    'baz qux': '2' },
  { 'foo bar': '3',
    'baz qux': '4' },
  { 'foo bar': 'foo bar',
    'baz qux': 'baz qux' } ]

If I understand your question correctly, you want to change the column headings. sheet_to_json takes an options argument that controls the output. To change the headers, set the header field to an array and set the range to 1:

> XLSX.utils.sheet_to_json(ws, {header:["sheet","js"], range:1})
[ { sheet: '1', js: '2' },
  { sheet: '3', js: '4' },
  { sheet: 'foo bar', js: 'baz qux' } ]

If you had a function format_column_name, you could generate the header array by looping :

function format_column_name(name) { return name.replace(/\s/g, "_"); }
var range = XLSX.utils.decode_range(wb.Sheets.Sheet1['!ref']);
var headers = [];
for(var C = range.s.c; C <= range.s.e; ++C) {
    var addr = XLSX.utils.encode_cell({r:range.s.r, c:C});
    var cell = ws[addr];
    if(!cell) continue;
    headers.push( format_column_name(cell.v));
}

Now if you run with the headers variable, you should see the formatted column names:

> XLSX.utils.sheet_to_json(ws, {header:headers, range:1})
[ { foo_bar: '1', baz_qux: '2' },
  { foo_bar: '3', baz_qux: '4' },
  { foo_bar: 'foo bar', baz_qux: 'baz qux' } ]

You can edit that format_column_name function to make other tweaks. For example, if you want camelcase:

function format_column_name(name) { return name.replace(/\s(.)/g, function($$,$1) { return $1.toUpperCase()}); }

@notatestuser @arg20 @nathanathan @nukulb does it make sense to add this as a feature? This could be implemented by accepting a function in the header param. For example, camelCasing column names would look something like:

XLSX.utils.sheet_to_json(ws, {
    range:1,
    header: function(column_name, column_number, column_letter) { return column_name.replace(/\s(.)/g, function($$,$1) { return $1.toUpperCase()}); }
});

[chathuraa]

Hey,

Thank you so much for the detailed explanation. This is exactly what I was looking for. One question (Sorry for my ignorance) Whats the purpose of the parameter 'Range' and why do we set it to 1?

Thanks,
Chat.

[SheetJSDev]

If you explicitly specify headers, the exporter assumes that your data doesn't contain headers (reasonable assumption). In this case, the data set has headers, but they aren't properly formatted, so setting range to 1 basically skips the first row in the data output.

To continue the example above:

> XLSX.utils.sheet_to_json(ws, {header:headers})
[ { foo_bar: 'foo bar', baz_qux: 'baz qux' }, <-- this row would be added, which you probably don't want
  { foo_bar: '1', baz_qux: '2' },
  { foo_bar: '3', baz_qux: '4' },
  { foo_bar: 'foo bar', baz_qux: 'baz qux' } ]

[chathuraa]

Makes perfect sense. Thanks again.

[smatthews1999]

Thanks! This is also exactly what I need. In my case I am importing spreadsheets where the creators can name headers whatever they want, as long as they are positioned in the correct order. This lets me replace their headers with my own and deal with them in a uniform manner.

[markcellus]

I second this feature! It is slated to be included in an upcoming version?