Unable to color cells dynamically ?

[sahilbhatt92]

Hi,

Scenario:
I have json below which i am converting it into excel.

const arr = [{"Name": "my Name", "age": "again my age", "sampleCol1": "", "sampleCol2": "", .... n}]

In Excel (the position of keys and values would be):-
Key: Position
Name: A1
age: B1
sampleCol1: C1
sampleCol2: D1
.....n

Value: Position
my Name: A2
again my age: B2
"": C2
"": D2
.....n

Problem:
Fill (property) only accepts cell positions such as AA, AB that i cannot get from array index.

arr.forEach((item, index) => {
// is there any solution that array index can be used as cell position. 
const cellPosition = index;
worksheet.getCell(cellPosition).fill = {
    type: 'pattern',
    pattern:'darkVertical',
    fgColor:{argb:'FFFF0000'}
}
});

[Siemienik]

AA is not Cell position, its Column name

did You try to use woksheet.getColumn(column).fill(value) ?

[vegarringdal]

Sorry if Ive miss understood to question.

But you can just loop the row then cells after the data have been added
Sample code from code I use:
PS! rows and cells looped are only the one within the data "area" of the sheet.

let rowValue = null;
worksheet.eachRow(function (row, _rowNumber) {

    // toggle depending on this value I want rows to alter color
    let rowValueTemp = row.values[3];
    if (rowValueTemp) {
        rowValueTemp = rowValueTemp;
    }
    if (rowValue !== rowValueTemp) {
        toggle = toggle ? false : true;
    }
    rowValue = rowValueTemp;


    row.eachCell({ includeEmpty: true }, function (cell, _colNumber) {
        cell.border = {
            top: { style: 'thin' },
            left: { style: 'thin' },
            bottom: { style: 'thin' },
            right: { style: 'thin' }
        };
        if (toggle) {
            cell.fill = <any>{
                type: 'pattern',
                pattern: 'solid',
                fgColor: { argb: 'FFA9A9A9' }
            };
        }
    });

});

[sahilbhatt92]

@Siemienik i have not used this woksheet.getColumn(column).fill(value) but what should the column variable holds.

[sahilbhatt92]

@vegarringdal Thanks buddy! i will try this and let you know.

[Siemienik]

@sahilbhatt92
https://github.com/exceljs/exceljs/blob/master/lib/doc/worksheet.js#L170
string (eg. A) or number (eg. 1 for column A)

@vegarringdal 👍

[vegarringdal]

@sahilbhatt92

For complete solution from extracting csv data -> sqllite -> excel have a look here
https://github.com/vegarringdal/checkFile

[sahilbhatt92]

@vegarringdal @Siemienik Thank you for your support. Issue finally resolved.