How to print json array in excel4node

Question

I have to print an excel with 10000 rows. There is any way to bulk print a json array using excel4node?.

e.g.: Mongodb insertMany operation

Answer 1

I've found a gist from the npm author. It seems is not possible already to do it

https://gist.github.com/natergj/46bc984855c4478f50d890353727514f

This is the example:

const xl = require('excel4node');

// Create workbook and add worksheet
const wb = new xl.Workbook();
const ws = wb.addWorksheet('Data', {
  disableRowSpansOptimization: true,
});

// Generate some fake data for testing
const data = [];
const rowCount = 50000;
let row = 1;
while (row <= rowCount) {
  data.push({
    zone_name: `zone_name_${row}`,
    circle_name: `circle_name_${row}`,
    div_town_name: `div_town_name_${row}`,
    tender_name: `tender_name_${row}`,
    tkc_name: `tkc_name_${row}`,
    work_name: `work_name_${row}`,
    material_name: `material_name_${row}`,
    insert_dttm: new Date(),
    app_fname: `app_fname_${row}`,
    ins_fname: `ins_fname_${row}`,
  });
  row += 1;
}

const startRow = 4;
// Add data to worksheet
if (data.length) {
  data.forEach((item, i) => {
    const currentRow = i + startRow;
    const newtarget = 'newtarget';
    const newprogress = 'newprogress';

    ws.cell(currentRow, 1).number(i).style({
      alignment: {
        vertical: ['center'],
        horizontal: ['left']
      },
      font: {
        color: '000000',
        size: 12
      },
      border: {
        bottom: {
          style: 'thin',
          color: '000000'
        },
        right: {
          style: 'thin',
          color: '000000'
        },
        left: {
          style: 'thin',
          color: '000000'
        },
        top: {
          style: 'thin',
          color: '000000'
        }
      }
    });
    ws.cell(currentRow, 2).string(item.zone_name).style({
      alignment: {
        vertical: ['center'],
        horizontal: ['left']
      },
      font: {
        color: '000000',
        size: 12
      },
      border: {
        bottom: {
          style: 'thin',
          color: '000000'
        },
        right: {
          style: 'thin',
          color: '000000'
        },
        left: {
          style: 'thin',
          color: '000000'
        },
        top: {
          style: 'thin',
          color: '000000'
        }
      }
    });
    ws.cell(currentRow, 3).string(item.circle_name).style({
      alignment: {
        vertical: ['center'],
        horizontal: ['left']
      },
      font: {
        color: '000000',
        size: 12
      },
      border: {
        bottom: {
          style: 'thin',
          color: '000000'
        },
        right: {
          style: 'thin',
          color: '000000'
        },
        left: {
          style: 'thin',
          color: '000000'
        },
        top: {
          style: 'thin',
          color: '000000'
        }
      }
    });
    ws.cell(currentRow, 4).string(item.div_town_name).style({
      alignment: {
        vertical: ['center'],
        horizontal: ['left']
      },
      font: {
        color: '000000',
        size: 12
      },
      border: {
        bottom: {
          style: 'thin',
          color: '000000'
        },
        right: {
          style: 'thin',
          color: '000000'
        },
        left: {
          style: 'thin',
          color: '000000'
        },
        top: {
          style: 'thin',
          color: '000000'
        }
      }
    });
    ws.cell(currentRow, 5).string(item.tender_name).style({
      alignment: {
        vertical: ['center'],
        horizontal: ['left']
      },
      font: {
        color: '000000',
        size: 12
      },
      border: {
        bottom: {
          style: 'thin',
          color: '000000'
        },
        right: {
          style: 'thin',
          color: '000000'
        },
        left: {
          style: 'thin',
          color: '000000'
        },
        top: {
          style: 'thin',
          color: '000000'
        }
      }
    });
    ws.cell(currentRow, 6).string(item.tkc_name).style({
      alignment: {
        vertical: ['center'],
        horizontal: ['left']
      },
      font: {
        color: '000000',
        size: 12
      },
      border: {
        bottom: {
          style: 'thin',
          color: '000000'
        },
        right: {
          style: 'thin',
          color: '000000'
        },
        left: {
          style: 'thin',
          color: '000000'
        },
        top: {
          style: 'thin',
          color: '000000'
        }
      }
    });
    ws.cell(currentRow, 7).string(item.work_name).style({
      alignment: {
        vertical: ['center'],
        horizontal: ['left']
      },
      font: {
        color: '000000',
        size: 12
      },
      border: {
        bottom: {
          style: 'thin',
          color: '000000'
        },
        right: {
          style: 'thin',
          color: '000000'
        },
        left: {
          style: 'thin',
          color: '000000'
        },
        top: {
          style: 'thin',
          color: '000000'
        }
      }
    });
    ws.cell(currentRow, 8).string(item.material_name).style({
      alignment: {
        vertical: ['center'],
        horizontal: ['left']
      },
      font: {
        color: '000000',
        size: 12
      },
      border: {
        bottom: {
          style: 'thin',
          color: '000000'
        },
        right: {
          style: 'thin',
          color: '000000'
        },
        left: {
          style: 'thin',
          color: '000000'
        },
        top: {
          style: 'thin',
          color: '000000'
        }
      }
    });
    ws.cell(currentRow, 9).string(newtarget).style({
      alignment: {
        vertical: ['center'],
        horizontal: ['left']
      },
      font: {
        color: '000000',
        size: 12
      },
      border: {
        bottom: {
          style: 'thin',
          color: '000000'
        },
        right: {
          style: 'thin',
          color: '000000'
        },
        left: {
          style: 'thin',
          color: '000000'
        },
        top: {
          style: 'thin',
          color: '000000'
        }
      }
    });
    ws.cell(currentRow, 10).string(newprogress).style({
      alignment: {
        vertical: ['center'],
        horizontal: ['left']
      },
      font: {
        color: '000000',
        size: 12
      },
      border: {
        bottom: {
          style: 'thin',
          color: '000000'
        },
        right: {
          style: 'thin',
          color: '000000'
        },
        left: {
          style: 'thin',
          color: '000000'
        },
        top: {
          style: 'thin',
          color: '000000'
        }
      }
    });
    ws.cell(currentRow, 11).date(item.insert_dttm).style({
      alignment: {
        vertical: ['center'],
        horizontal: ['left']
      },
      font: {
        color: '000000',
        size: 12
      },
      border: {
        bottom: {
          style: 'thin',
          color: '000000'
        },
        right: {
          style: 'thin',
          color: '000000'
        },
        left: {
          style: 'thin',
          color: '000000'
        },
        top: {
          style: 'thin',
          color: '000000'
        }
      }
    });
    ws.cell(currentRow, 12).string(item.app_fname).style({
      alignment: {
        vertical: ['center'],
        horizontal: ['left']
      },
      font: {
        color: '000000',
        size: 12
      },
      border: {
        bottom: {
          style: 'thin',
          color: '000000'
        },
        right: {
          style: 'thin',
          color: '000000'
        },
        left: {
          style: 'thin',
          color: '000000'
        },
        top: {
          style: 'thin',
          color: '000000'
        }
      }
    });
    ws.cell(currentRow, 13).string(item.ins_fname).style({
      alignment: {
        vertical: ['center'],
        horizontal: ['left']
      },
      font: {
        color: '000000',
        size: 12
      },
      border: {
        bottom: {
          style: 'thin',
          color: '000000'
        },
        right: {
          style: 'thin',
          color: '000000'
        },
        left: {
          style: 'thin',
          color: '000000'
        },
        top: {
          style: 'thin',
          color: '000000'
        }
      }
    });
  });
}

// Write workbook once all data has been added to worksheet
wb.write('Overall_Progress_Report.xlsx');

As you can see, he iterates json array.

Answer 2

For that you can make a function that takes json as input and iterate over it inside of the function. Because of that you will be able to see your operation as single one if that’s what you wanted.