How to use exceljs - 10 common examples

public static async startup(inputPath?: string, outputPath?: string): Promise {
    if (undefined !== inputPath)
      PerformanceReportWriter.input = inputPath;
    if (undefined !== inputPath || undefined !== outputPath)
      PerformanceReportWriter.output = undefined !== outputPath ? outputPath : inputPath!;

    // if file doesn't exist, do setup a new file by writing column headers
    const doSetupNewFile = !fs.existsSync(PerformanceReportWriter.input);

    PerformanceReportWriter.dataArray = [];

    if (doSetupNewFile) {
      const wb = new Excel.Workbook();
      const ws = wb.addWorksheet("Performance Results");

      ws.columns = [
        { header: "" },
        { header: "IModel" },
        { header: "View" },
        { header: "Flags" },
        { header: "TileLoadingTime" },
        { header: "Scene" },
        { header: "GarbageExecute" },
        { header: "InitCommands" },
        { header: "BackgroundDraw" },
        { header: "Skybox" },
        { header: "Terrain" },
        { header: "SetClips" },
        { header: "OpaqueDraw" },

import * as Excel from 'exceljs';

// most examples taken and adapted from README.md

const workbook = new Excel.Workbook();

workbook.creator = 'Me';
workbook.lastModifiedBy = 'Her';
workbook.created = new Date(1985, 8, 30);
workbook.modified = new Date();
workbook.lastPrinted = new Date(2016, 9, 27);
workbook.properties.date1904 = true;

workbook.views = [
  {
    x: 0, y: 0, width: 10000, height: 20000,
    firstSheet: 0, activeTab: 1, visibility: 'visible'
  }
];

const worksheet: Excel.Worksheet = workbook.addWorksheet('My Sheet');

static async ImportClassification(filelocation)
    {
        var workbook    = new Excel.Workbook()

        workbook = await workbook.xlsx.readFile(filelocation)

        //console.log(filelocation)
        let explanation = workbook.getWorksheet('Sheet 1') // get sheet explanation (sheet name)
        //console.log('Worksheet', explanation)
        let colComment  = explanation.getColumn('C')//column name
        //console.log(colComment)

            colComment.eachCell(async (cell, rowNumber) => {
                if(rowNumber >= 11){
                    let sekolah = explanation.getCell('B' + rowNumber).value //get cell and the row
                    let kode    = explanation.getCell('C' + rowNumber).value
                    let nama    = explanation.getCell('D' + rowNumber).value
                    let nip     = explanation.getCell('E' + rowNumber).value

public async format(messages: Problem[], options: FormatterOptions = {}) {
        if (messages.length === 0) {
            return;
        }

        const language = options.language!;
        const target = options.target || '';
        const resources: _.Dictionary = _.groupBy(messages, 'resource');
        const workbook = new Excel.Workbook();
        const sortedResources = _.sortBy(Object.keys(resources));

        // Styles for the cells
        const bold = { font: { bold: true } };
        const mediumBorder = { style: 'medium' };
        const border = {
            border: {
                bottom: mediumBorder,
                left: mediumBorder,
                right: mediumBorder,
                top: mediumBorder
            }
        };
        const rightAlign = { align: { horizontal: 'right' } };
        const tableHeader = {
            fill: {

data.forEach((items) => {
        Object.keys(items.technical).forEach(key => {
            if (possible_technical.indexOf(key) === -1) {
                possible_technical.push(key);
            }
        });

        Object.keys(items.features).forEach(key => {
            if (possible_features.indexOf(key) === -1) {
                possible_features.push(key);
            }
        });
    });

    const workbook = new Excel.Workbook();
    const worksheet = workbook.addWorksheet('My Sheet', {
        views: [
            { xSplit: 1, ySplit: 1, zoomScale: 80 }
        ]
    });

    const headers = [];

    // head
    Object.keys(data[0]).forEach(key => {
        switch (key) {
            case 'technical': return possible_technical.forEach(key => headers.push(key));
            case 'features':  return possible_features.forEach(key  => headers.push(key));
            default:          return headers.push(key);
        }
    });

export async function writeXLSX(
  axios: AxiosInstance,
  token: string,
  res: ServerResponse,
  base: string,
): Promise {
  try {
    const options = {
      stream: res,
      useStyles: true,
      useSharedStrings: true,
    };
    const { userId } = jwtDecode(token);
    const workbook = new Excel.stream.xlsx.WorkbookWriter(options);
    workbook.creator = userId ? userId : "Unknown TruBudget User";
    workbook.created = new Date();

    // Prepare sheets
    const projectSheet = workbook.addWorksheet("Projects");
    const subprojectSheet = workbook.addWorksheet("Subprojects");
    const workflowitemSheet = workbook.addWorksheet("Workflowitems");
    const projectProjectedBudgetsSheet = workbook.addWorksheet("Project Projected Budgets");
    const subprojectProjectedBudgetsSheet = workbook.addWorksheet("Subproject Projected Budgets");
    const documentSheet = workbook.addWorksheet("Documents");

    projectSheet.columns = [
      { header: "Project ID", key: "id", width: mediumWidth },
      { header: "Project Name", key: "displayName", width: mediumWidth },
      { header: "Created", key: "creationUnixTs", width: mediumWidth },
      { header: "Status", key: "status", width: smallWidth },

filename = filename.join(".");
  }
  return filename;
}

let filename;

if (process.argv[3]) {
  filename = getName(process.argv[3]);
} else {
  filename = getName(filePath);
}

filename = `${filename}.xlsx`.replace("[time]", new Date().toLocaleString());

const workbook = new Excel.stream.xlsx.WorkbookWriter({
  filename
});
const worksheet = workbook.addWorksheet("Sheet");

worksheet.columns = [
  { header: "key", key: "header" },
  { header: "value", key: "value" }
];

const dataArray = [];

getTiledData(data, dataArray);

const length = dataArray.length;

// 当前进度

toExcel () {
    const { tables, options } = this
    const workbook = new ExcelJS.Workbook() // create workbook

    Object.assign(workbook, options)

    // workbookCreated plugins
    this._invokePlugin('workbookCreated', { workbook, tables })

    tables.forEach((table, index) => {
      const worksheet = workbook.addWorksheet(`Sheet ${index + 1}`)

      // worksheetCreated plugins
      this._invokePlugin('worksheetCreated', { worksheet, table })

      this.toSheet(table, worksheet)

      // worksheetCompleted plugins
      this._invokePlugin('worksheetCompleted', { worksheet, table })

TableExport.prototype._writeTable = function (data) {
    var wb = new Excel.Workbook();
    var ws = wb.addWorksheet('Table');
    
    var s = '';
    for (var i = 0; i < data.length; i++) {
        var row = data[i];
        for (var j = 0; j < row.length; j++) {
            var cell = row[j];

            var cc = ws.getRow(i+1).getCell(j+1);
            if (cell.type == 'fact') {
                cc.value = Number(cell.fact.value());
                cc.numFmt = '#,##0';
                ws.getColumn(j+1).width = 18;
                /* Make this an option - apply presentation signs */
                if (cell.negative) {
                    cc.value = Math.abs(cc.value) * -1;

public static async applyToWorksheet(fnc: (worksheet: Excel.Worksheet) => void): Promise {
    const workbook = new Excel.Workbook();
    return workbook.xlsx.readFile(PerformanceReportWriter.input)
      .then(async () => {
        fnc(workbook.getWorksheet(1));
        return workbook.xlsx.writeFile(PerformanceReportWriter.output);
      });
  }