Sheetjs: Change header´s title when using json_to_sheet

[martinnov92]

Hello,

I would like to know, if there is any way how to change header´s title when using json_to_sheet function. Now I am gettings object´s keys and I would like to change it to something more readable.

Thank you very much.

[SheetJSDev]

There's no json_to_xlsx function, you probably mean json_to_sheet.

That being said, the easiest way is to change the worksheet after the fact. We'll start from a simple example:

> var data = [{name:"Sheet", age: 12}, {name:"JS", age: 24}]
> var ws = XLSX.utils.json_to_sheet();
> ws
{ A2: { t: 's', v: 'Sheet' },
  B2: { t: 'n', v: 12 },
  A3: { t: 's', v: 'JS' },
  B3: { t: 'n', v: 24 },
  A1: { t: 's', v: 'name' },
  B1: { t: 's', v: 'age' },
  '!ref': 'A1:B3' }

The headers are automatically inserted in the first row (the A1/B1 from above). You can directly change the values. Let's say "name" -> "Name":

ws.A1.v = "Name";

If you want to loop through every header and make them uppercase:

var range = XLSX.utils.decode_range(ws['!ref']);
for(var C = range.s.c; C <= range.e.c; ++C) {
  var address = XLSX.utils.encode_col(C) + "1"; // <-- first row, column number C
  if(!ws[address]) continue;
  ws[address].v = ws[address].v.toUpperCase();
}

[samuelkavin]

Hi @martinnov92,

I'm new to js-xlsx. Can u share some sample code how did you generate the json to xlsx format?
This will be really helpful for me.

This is sample data from me
var data = [ { "agentNo":"324234", "subName":"30, Jul 2013 09:24 AM" }, { "agentNo":"444443", "subName":"30, Jul 2013 09:24 AM" } ];
var wb = XLSX.utils.json_to_sheet(ge);

[martinnov92]

Hello @samuelkavin ,

I am new to js-xlsx as well :D But I will try my best and help you.

The first thing you have to do is set writing options for xlsx:
const wopts = { bookType: 'xlsx', bookSST: false, type: 'binary' };

Then you create the worksheet like this:
const ws = xlsx.utils.json_to_sheet(data);

Then you create the workbook:
const wb = { SheetNames: ['Export'], Sheets: {}, Props: {} };

Next you put data in the sheet:
wb.Sheets['Export'] = ws;

And the final step is from documentation:

    // make a file
    var wbout = xlsx.write(wb, wopts);

    function sheetToArrayBuffer(s) {
        var buf = new ArrayBuffer(s.length);
        var view = new Uint8Array(buf);
        for (var i = 0; i != s.length; ++i) view[i] = s.charCodeAt(i) & 0xFF;
        return buf;
    }

    // the saveAs call downloads a file on the local machine
    saveAs(new Blob([sheetToArrayBuffer(wbout)], { type: "application/octet-stream" }), "Export.xlsx");

I hope this will be somehow helpful.

[samuelkavin]

Hi @martinnov92, thank you for quick respone.
I got Uncaught ReferenceError: xlsx is not defined

Here is my js
`function export_json_to_excel() {
var wopts = {
bookType: 'xlsx',
bookSST: false,
type: 'binary'
};

var ws = xlsx.utils.json_to_sheet(data);

var wb = {
SheetNames: ['Export'],
Sheets: {},
Props: {}
};

wb.Sheets['Export'] = ws;

// make a file
var wbout = xlsx.write(wb, wopts);

function sheetToArrayBuffer(s) {
var buf = new ArrayBuffer(s.length);
var view = new Uint8Array(buf);
for (var i = 0; i != s.length; ++i) view[i] = s.charCodeAt(i) & 0xFF;
return buf;
}

// the saveAs call downloads a file on the local machine
saveAs(new Blob([sheetToArrayBuffer(wbout)], {
type: "application/octet-stream"
}), "Export.xlsx");

return wbout;

}
`

HTML:
<p id="xportxlsx" class="xport"> <input type="submit" value="Export to XLSX!" onclick="export_table_to_excel();"> </p>

Json:
var data = [ { "agentNo":"324234", "subName":"30, Jul 2013 09:24 AM" }, { "agentNo":"444443", "subName":"30, Jul 2013 09:24 AM" } ];

[artaniszeratul]

No worries @martinnov92 .

Hi @SheetJSDev ,

Please allow me to ask you a question.

I wanted to make the headers of my excel data to be in bold fonts and with filters.
I am currently stuck on looking for possible ways to do this.

I would appreciate it so much if you can point me into something.
Or better yet, if you can show me an example.

Cheers,
Artanis

[jdhines]

I don't think that kind of formatting is possible, but to address your earlier question, the way I did it, since I also needed to change the order of the columns sometimes (although I know you can use the headers prop to do it), was to just map the keys of the JSON object to new keys:

newJson = oldJson.map(rec => {
  return {
    'Last Name': rec.lastName,
    'First Name': rec.firstName,
    ...
  }
}

[cmfc31]

ws.A1.s = "Name";

Shouldn't be .v?

ws.A1.v = "Name"

[SheetJSDev]

Thanks @cmfc31 corrected the snippet

[soanvig]

@SheetJSDev and it should be

range.e.c

and

range.s.c

if we want to iterate columns

[christiaanwesterbeek]

I followed the same strategy as @jdhines . I think it's the simplest way to go since it doesn't require any knowledge of the SheetJS api. Just plain modern javascript altering the data before you pass it json_to_sheet.

There's a complete example here: https://github.com/mbrn/material-table/issues/1534#issuecomment-601603029

[thomascayne]

I don't think that kind of formatting is possible, but to address your earlier question, the way I did it, since I also needed to change the order of the columns sometimes (although I know you can use the headers prop to do it), was to just map the keys of the JSON object to new keys:

newJson = oldJson.map(rec => {
  return {
    'Last Name': rec.lastName,
    'First Name': rec.firstName,
    ...
  }
}

This is fabulous because it automatically omits unwanted data and can be used application wide. Most useful. Thank you @jdhines

[SheetJSDev]

Thanks @soanvig fixed the snippet

@jdhines love the simplicity 👍 For complete control, we normally recommend an array of arrays, but your approach is definitely more straightforward

@artaniszeratul bold and other style formatting is supported in our Pro builds