SheetJS xlsx-cell styling

Question

I am referring this example to export a worksheet https://github.com/SheetJS/js-xlsx/issues/817. How to do cell styling like background coloring,font size and increasing the width of the cells to make the data fit exactly.I have gone through the documentation but couldn't find any proper examples to use fill etc.Is there a way to do the formatting?

Below is the code snippet:
    /* make the worksheet */
var ws = XLSX.utils.json_to_sheet(data);

/* add to workbook */
var wb = XLSX.utils.book_new();
XLSX.utils.book_append_sheet(wb, ws, "People");

/* write workbook (use type 'binary') */
var wbout = XLSX.write(wb, {bookType:'xlsx', type:'binary'});

/* generate a download */
function s2ab(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;
}
saveAs(new Blob([s2ab(wbout)],{type:"application/octet- 
stream"}),"sheetjs.xlsx");

Blowup

i also faced this same problem, but cannot solved. use this link it may help you. – Bui

Answer 1

Styling is only available in Pro Version of SheetJS. But I think you are using community version(free version). In Community version styling is not available.

You can check here official information:

We also offer a pro version with performance enhancements, additional features like styling, and dedicated support.

Blowzed

You can use github.com/protobi/js-xlsx. This fork support styling in excel. – Demott

@AfshinAlizadeh can we apply styling on worksheet level? I know it specifically states cell-styling but is there any other resource you might know. That will reduce the size of bigger file downloads as blob. Thank you – Granddaughter

This doesn't seem to me a fair answer to OP, since it does not offer any of the many FOSS solutions. A lot better is Jesper's answer. Personally I use xslx-js-style which completely substitutes sheetjs, while some other solutions still require you to keep in the package.json sheetjs and its clumsy imports (when you work in ES6). – Amalamalbena

I am not using Pro Version but still able to add styles using xlsx-js-Style package. – Phagocyte

Answer 2

There are a bunch of community forks that allow styling. My personal favorite is xlsx-js-style. It is up to date and works well compared to other libraries.

sheetjs-style is also up to date, but i had some problems with it. See: Styles not working

xlsx-style is not up to date. Currently 397 commits behind SheetJS:master. I would not use it if possible.

All of these libraries share the same styling options. Here is a bunch of examples:

for (i in ws) {
    if (typeof(ws[i]) != "object") continue;
    let cell = XLSX.utils.decode_cell(i);

    ws[i].s = { // styling for all cells
        font: {
            name: "arial"
        },
        alignment: {
            vertical: "center",
            horizontal: "center",
            wrapText: '1', // any truthy value here
        },
        border: {
            right: {
                style: "thin",
                color: "000000"
            },
            left: {
                style: "thin",
                color: "000000"
            },
        }
    };

    if (cell.c == 0) { // first column
        ws[i].s.numFmt = "DD/MM/YYYY HH:MM"; // for dates
        ws[i].z = "DD/MM/YYYY HH:MM";
    } else { 
        ws[i].s.numFmt = "00.00"; // other numbers
    }

    if (cell.r == 0 ) { // first row
        ws[i].s.border.bottom = { // bottom border
            style: "thin",
            color: "000000"
        };
    }

    if (cell.r % 2) { // every other row
        ws[i].s.fill = { // background color
            patternType: "solid",
            fgColor: { rgb: "b2b2b2" },
            bgColor: { rgb: "b2b2b2" } 
        };
    }
}

Goodnight

xlsx-js-style worked well just had to link the bundle.min.js – Vazquez

Answer 3

I used sheetjs-style (which is a fork of sheetjs) to add formatting to cells in excel file.

ws["A1"].s =        // set the style for target cell
  font: {
    name: '宋体',
    sz: 24,
    bold: true,
    color: { rgb: "FFAA00" }
  },
};

It's very easy. However, you have to add style to each individual cell. It's not convenient to add style to a range of cells.

UPDATE: The official example use color "FFFFAA00". But I removed the first "FF" and it still works as before. The removed part is used for transparency (see COLOR_SPEC in Cell Styles), but somehow it has no effect when I change it or remove it.

Nedanedda

How color works in rgb? For example, this color in hex '#7da7d8' how is transformed to rgb? – Alive

@aral-roca You can use hex color (without the prefix '#'). Please see my updated answer for detail. – Nedanedda

The problem was that I was using bgColor instead of fgColor. – Alive

@aral-roca Does it work now with fgColor? – Nedanedda

Answer 4

After testing all the above options. For ReactJS I finally found a package that worked perfectly.

https://github.com/ShanaMaid/sheetjs-style

import XLSX from 'sheetjs-style'; 

var workbook = XLSX.utils.book_new();

var ws = XLSX.utils.aoa_to_sheet([
    ["A1", "B1", "C1"],
    ["A2", "B2", "C2"],
    ["A3", "B3", "C3"]
])
ws['A1'].s = {
    font: {
        name: 'arial',
        sz: 24,
        bold: true,
        color: "#F2F2F2"
    },
}

XLSX.utils.book_append_sheet(workbook, ws, "SheetName");
XLSX.writeFile(workbook, 'FileName.xlsx');

Cooe

Answer 5

this is the only way that worked for me with cell background color (with sheetjs-style)

see "Cell Style Properties" in the documentation:

https://www.npmjs.com/package/xlsx-js-style?activeTab=readme

ws[cell].s = {
    font: { 
      bold: true,
      color: "000000",
      sz: '11'
    },
    fill: {
        type: 'pattern',
        patternType: 'solid',
        fgColor: { rgb: "e8f0f8" }
    }
}

for column widths: (the data is an array of objects)

fitToColumn(data) {
  const widths = []
  for (const field in data[0]) {
    widths.push({
        wch: Math.max(
            field.length,
            ...data.map(item => item[field]?.toString()?.length ?? 0)
        )
    })
  }
  return widths
}

ws['!cols'] = this.fitToColumn(data)

Spindle

Answer 6

Note following points while adding styling:-

1. Cell should not be empty
2. First add data into the cell, then add styling to that cell.

For 2 days I was struck and did not got any styling appearing on my excel file since I was just adding styling before adding the data.Don't do that it won't appear.

I used xlsx-js-style Package and added the styles to my excel in the following way :-

XLSX.utils.sheet_add_aoa(worksheet, [["Firstname"]], { origin: "A1"
 });
      const LightBlue = {
         fgColor: { rgb: "BDD7EE" }
       }; 
 const alignmentCenter = { horizontal: "center", vertical: "center", wrapText: true };   

      const ThinBorder = {
       top: { style: "thin" },
        bottom: { style: "thin" },
        left: { style: "thin" },
        right: { style: "thin" }
      };

     const fillAlignmentBorder = {
        fill: LightBlue,
        alignment: alignmentCenter,
         border: ThinBorder
      };
     worksheet["A1"].s = fillAlignmentBorder;

  

Hope this helps.....Happy Coding :-)

Phagocyte

i have give a try but doesnt applying styles – Squalor