Xsirrionx / JS xlsx set basic style to output excel file

Keywords: Excel less

;

problem

I've been using SheetJS Community Edition, which is its community version. I see that the document mentions the style control of each Cell layer( cell-object ), no output effect after one attempt. After looking up some data, we found that the community version of SheetJS could not set the style of the exported excel file, so we only need to find other ways to solve it.

process

After finding this (unable to set the output file style), start to look through all the questions about style in the Q & A area of SheetJS. In the process of viewing, find a demo example that can realize the basic style control: JSFiddle: https://jsfiddle.net/1g24vowu/1/

Solve

Use js-xlsx The improved version, this branch version of SheetJS, has added the output file color setting for the community version of SheetJS. Thank you very much xSirrioNx Contribution.

Creating style objects

excelCell

var excelCell = {
    v: "",
    t: "s",
    s: {
        fill: {
                patternType: "none",
                fgColor: {rgb: "FF000000"},
                bgColor: {rgb: "FFFFFFFF"}
        },
        font: {
          name: 'Times New Roman',
          sz: 16,
          color: {rgb: "#FF000000"},
          bold: false,
          italic: false,
          underline: false
        },
        alignment: {
            vertical: "center",
            horizontal: "center",
            indent:0,
            wrapText: true
        },
        border: {
          top: {style: "thin", color: {auto: 1}},
          right: {style: "thin", color: {auto: 1}},
          bottom: {style: "thin", color: {auto: 1}},
          left: {style: "thin", color: {auto: 1}}
        }
    }
};

headerCellStyle

var headerCellStyle =  {
    fill: {
        patternType: "solid",
        fgColor: {rgb: "FFdbdbdb"},
        bgColor: {rgb: "FFdbdbdb"}
    },
  alignment: {
    vertical: "center",
    horizontal: "center",
    indent:0,
    wrapText: true
  },
  border: {
    top: {style: "thin", color: {auto: 1}},
    right: {style: "thin", color: {auto: 1}},
    bottom: {style: "thin", color: {auto: 1}},
    left: {style: "thin", color: {auto: 1}}
  }
};

Set style object

The cell style of the first row is set here.

var headeRange = {s:{c:0, r:0}, e:{c:16, r:0}};
for (var R = headeRange.s.r; R <= headeRange.e.r; ++R) {
  for (var C = headeRange.s.c; C <= headeRange.e.c; ++C) {
    var cell_address = {
      c: C,
      r: R
    };
    /* if an A1-style address is needed, encode the address */
    var cell_ref = XLSX.utils.encode_cell(cell_address);
    var cell = ws[cell_ref];
    if(!$.isEmptyObject(cell)){
        cell.s = headerCellStyle;
    } else {
        ws[cell_ref] = excelCell;
    }
  }
}

summary

Export Excel library, a little less. Another one is called: handsontable , it is said to be very easy to use, but the excel function needs to be paid. If you encounter a local boss later, consider it again.

reference resources:
xSirrioNx/js-xlsx
SheetJS/js-xlsx
protobi/js-xlsx

Posted by raydar2000 on Tue, 05 May 2020 00:57:00 -0700