How to specified cell style in a export xlsx file?

[johnscliang]

I am sorry.It's difficult for me to understand this part Cell Style. Could you please show me some complete code ?

[johnscliang]

I want to generate a xlsx file with some custom style, like a gray background.

[pietersv]

see the file example-style.js for a working example. The following code below will create a cell with a red background. The colors are hex ARGB, so "FFFF0000" is FF-FF-00-00 meaning fully opaque saturated red with no green or blue. Light grey would be "FFEEEEEE"

Excel uses fgColor to represent what we think of as 'background color'. Background colors can have textures/patterns so that's what the bgColor attribute is for.

...
"B2": {
        "v": "Red",
        "s": {
          "fill": {
            "fgColor": {
              "rgb": "FFFF0000"
            }
          }
        },
        "t": "s"
      },

[johnscliang]

Thank you for the reply.I have a try, But only the 'v' attribute work well, I can change the value of cell in export file, the 's' attribute doesn't work.

function export_table_to_excel(id) {
        var theTable = document.getElementById(id);
        var oo = generateArray(theTable);
        var ranges = oo[1];

        /* original data */
        var data = oo[0];
        var ws_name = "SheetJS";

        var wb = new Workbook(), ws = sheet_from_array_of_arrays(data);

        /* add ranges to worksheet */
        // ws['!cols'] = ['apple', 'banan'];
        ws['!merges'] = ranges;
        console.log(ranges)

        /* add worksheet to workbook */
        wb.SheetNames.push(ws_name);
        wb.Sheets[ws_name] = ws;
        console.log('wb.Sheets[ws_name]', wb.Sheets[ws_name])
        wb.Sheets[ws_name]['B1'] = {
            "v": "Red",
            "s": {
                "fill": {
                    "bgColor": {
                        "rgb": "#FFFAFA"
                    }
                }
            },
            "t": "s"
        }

        var wbout = XLSX.write(wb, {bookType: 'xlsx', bookSST: false, type: 'binary'});

        saveAs(new Blob([s2ab(wbout)], {type: "application/octet-stream"}), "test.xlsx")
    }

By calling this function, I can change the cell value only of the exported excel file .

[johnscliang]

Could you please add the example about "Export to XLSX with custom style" to your demo ? Thanks a lot!

[pietersv]

Are you working with the version on GitHub? The npm may be older, but I don't want to update that and potentially generate support requests.

[johnscliang]

OK, thanks again.I will try it out later.

[linrl3]

Same question. The style is not working. Could you give a workable example on how to format a cell?

[pietersv]

How are you including the library? For examples, clone the github repo, move to that directory:

Node.js

• node example-style.js
• open /tmp/example-style.xlsx

Browser

• open browser_example-simple.html
• press the export button

In either case you should see styled Excel:

All that said, if you're just exporting Excel and not reading it, most of this library may be unnecessary, and is here mainly for legacy reference. You might prefer https://github.com/protobi/msexcel-builder which is far smaller and easier to understand/extend:

[linrl3]

Thank you for your reply. After I include the library correctly, the problem was solved.

On Mon, Apr 1, 2019 at 4:39 PM Pieter Sheth-Voss notifications@github.com wrote:

How are you including the library? WhatThe entire purpose of this branch is to read and export styled cells. For examples, clone the github repo, move to that directory: Node.js

• node example-style.js
• open /tmp/example-style.xlsx

Browser

• open browser_example-simple.html
• press the export button

In either case you should see styled Excel: [image: image] https://user-images.githubusercontent.com/10464727/55361322-e3849f00-54a4-11e9-8d4f-77cc0d5f6874.png

All that said, if you're just exporting Excel and not reading it, most of this library may be unnecessary, and is here mainly for legacy reference. You might prefer https://github.com/protobi/msexcel-builder which is far smaller and easier to understand/extend:

— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/protobi/js-xlsx/issues/126#issuecomment-478758108, or mute the thread https://github.com/notifications/unsubscribe-auth/AP08F2lH5AflzsOyJ3Sbnz94rmE99gY9ks5vcnymgaJpZM4ZpZzR .

[pietersv]

Thanks for reporting back. For reference, can you report back what method worked and what didn't? I suspect the npm may be outdated vs github.