Sheetjs: Unable to fill background color

[ZoeLeee]

Why does this code have no effect?It can not fill the background color?Who can help me？

`
json.map((v, i) => {
if(maxLen maxLen=Object.keys(v).length;
}
return Object.keys(v).map((k, j) => { //取出键对应的值

        return Object.assign({}, { //拼接输出的sheet
          v: v[k],
          position: (j > 25 ? this.getCharCol(j) : String.fromCharCode(65 + j)) + (i + 1),

        })}
      )
    }).reduce((prev, next) =>  prev.concat(next)).forEach(function (v) {
      var cell={ //转换输出json
        v: v.v,
        s:{ 
          fill : {
            fgColor : {
                theme : 8,
                tint : 0.3999755851924192,
                rgb : '08CB26'
            }
          },
          font : {
              color : {
                  rgb : "FFFFFF"
              },
              bold : true
          },
          border : {
              bottom : {
                  style : "thin",
                  color : {
                      theme : 5,
                      tint : "-0.3",
                      rgb: "E8E5E4"
                  }
              }
          }  
        }
      }
      if ( typeof cell.v === 'number')  
           cell.t = 'n';  
        else if ( typeof cell.v === 'boolean')  
            cell.t = 'b';  
        else if (cell.v instanceof Date) {  
            cell.t = 'n';  
            cell.z = XLSX.SSF._table[14];  
            cell.v = datenum(cell.v);  
        } else  
            cell.t = 's';
      tmpdata[v.position] = cell;

    })

`

[JesusFregoso]

i think is because of the bgColor and the patternType look this is my implementation for the header of cells

const COLORS = {
    'good': { patternType : 'solid', bgColor    : { rgb: 'C6EFCE' }, fgColor : { rgb: 'C6EFCE' }, fontColor : { rgb :'006100'} },
if (headers[C].style) {
   if (headers[C].style.color) {
        headers[C].style.fill = COLORS[headers[C].style.color];
        if (headers[C].style.font) {
           headers[C].style.font.color = COLORS[headers[C].style.color].fontColor;
        }
  }
  if (headers[C].style.repeat) {
    defaultStyle = headers[C].style;
  }
  if (defaultStyle) {
    ws[address].s = defaultStyle;       
  }else{
    ws[address].s = headers[C].style
  }
}else{
  ws[address].s = defaultStyle;
}

[JesusFregoso]

Sorry my bad im using this fork https://github.com/xSirrioNx/js-xlsx
i think is better the should merge to master

[SheetJSDev]

We offer this in the Pro compendium. Since companies have paid for the features already, it would be unfair to them if we turned around and made it available as open source. We have a longer comment in a gist.

[msillz]

@JesusFregoso Using xSirrioNx's fork, I have a very simple code snippet:

var worksheet_name = "SheetJS";
var workbook = XLSX.utils.book_new();

// complete Report Data is a 2D array of floats
var ws = XLSX.utils.aoa_to_sheet(completeReportData);

ws['A2'].s = {
      fill: {
        patternType: "none", // none / solid
        fgColor: {rgb: "FF000000"},
        bgColor: {rgb: "FFFFFFFF"}
        },
        font: {
        name: 'Times New Roman',
        sz: 16,
        color: {rgb: "#FF000000"},
        bold: true,
        italic: false,
        underline: false
        },
        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}}
        }
    };
XLSX.utils.book_append_sheet(workbook, ws, worksheet_name);
var filename = "REPORTJSONNAME.xlsx";
XLSX.writeFile(workbook, filename);

The 2D array gets written and exported perfectly to the excel sheet that I download, but A2 has no formatting. Any ideas?

[ajitqait]

var workbook = this.XLSX.readFile(fileName);
var first_sheet_name = workbook.SheetNames[0];
var address_of_cell = cellNumber
var worksheet = workbook.Sheets[first_sheet_name];
worksheet[address_of_cell].v = cellValue;
worksheet[address_of_cell].s = {
fill: {
bgColor: {rgb: "#FF0000"}
}
};
this.XLSX.writeFile(workbook, fileName,);
}

Unable to fill background color in given cell
is there any way?

[msillz]

@ajitqait I dont use the "#" in mine, try removing that. Also, I found that sometimes I needed to set the fill to solid or it wouldn't display. Try this:

{
type: 'pattern',
pattern:'solid',
fgColor:{argb:'FF1c4587'},
bgColor:{argb:'FF1c4587'}
}

[ajitqait]

@msillz still not working

var workbook = this.XLSX.readFile(fileName);
var first_sheet_name = workbook.SheetNames[0];
var address_of_cell = cellNumber
var worksheet = workbook.Sheets[first_sheet_name];
worksheet[address_of_cell].s = {
fill: {
type:'pattern',
pattern: "solid", // none / solid
fgColor: { argb: "FF1c4587" },
bgColor: { argb: "FF1c4587" }
}
}
worksheet[address_of_cell].v = cellValue;
this.XLSX.writeFile(workbook, fileName);

[vishnusbabu]

@msillz Showing me such a error

ERROR TypeError: Cannot set property 's' of undefined

[tahasadraoui]

You are using worksheet[address_of_cell].s, So your worksheet[address_of_cell] is undefined, maybe you are accessing a non existing cell