A node.js server module generating xlsx file using elxml.

journal.js

var _ = require('lodash');
var path = require('path');
var moment = require('moment');
var request = require('superagent');
var excel = require('elxml');
var conf = require('../../configure');
var upstream = conf.upstream;

var baseuri = 'http://' + upstream.host + ':' + upstream.port;

module.exports = {

  ///
  // Query Journal Metadata in Range of Date
  ///
  metadata: function (req, res, next) {

    var data = req.query;
    var drng = _.sortBy(data.date);

    request
    .get(baseuri + '/api/export/brief') // TODO Re-design Upstream URI
    .set(req.headers)
    .query({
      begin : +moment(drng[0]),
      end   : +moment(drng[1]).add('days', 1)
    })
    .end(function (err, _res) {
      if (err) return next(err);

      if (_res.statusCode == 403) {
        return res.send(403, 'Authentication Failed');
      }

      var data = _res.body;
      // [{
      //  user_id: <id>,
      //  profile: {
      //    stuff_name: <string>,
      //    stuff_work_id: <id>,
      //    stuff_title: <string>,
      //    stuff_region: <string>
      //  }
      //  project: [<string>, ...],
      //  count: <number>,
      // }, ...]

      // Data Transformation
      var result = _.map(data, function (card) {
        return {
          uid     : card.user_id,
          work_id : card.profile.stuff_work_id,
          name    : card.profile.stuff_name,
          title   : card.profile.stuff_title,
          tag     : card.profile.stuff_region,
          project : card.project.join(' '),
          count   : card.count
        };
      });

      return res.send(200, result);
    });

  },

  ///
  // Generate Journal Report in Range of Date with Selected
  ///
  download: function (req, res, next) {

    var data = req.query;
    var drng = _.sortBy(data.date);

    request
    .get(baseuri + '/api/export/journal') // TODO Re-design Upstream URI
    .set(req.headers)
    .query({
      begin : +moment(drng[0]),
      end   : +moment(drng[1]).add('days', 1),
      pick  : data.pick
    })
    .end(function (err, _res) {
      if (err) return next(err);

      if (_res.statusCode == 403) {
        return res.send(403, 'Authentication Failed');
      }

      var data = _res.body;
      // [{
      //  user_id: <id>,
      //  stuff_name: <string>,
      //  stuff_title: <string>,
      //  stuff_work_id: <id>,
      //  stuff_region: <string>,
      //  project_name: <string>,
      //  project_customer: <string>,
      //  checkpoint_name: <string>,
      //  checkpoint_timestamp: <epoch>,
      //  journal_content: <string>,
      //  journal_submit_address: <string>
      //  journal_submit_location: <string>
      //  journal_timestamp: <epoch>
      //  }, ...]

      var book  = excel.createWorkbook();

      // Style Definition {{{

      var border = {
        thead: book.addBorder({
          top:    book.createBorderPr(excel.BORDER_STYLE_MEDIUM),
          bottom: book.createBorderPr(excel.BORDER_STYLE_MEDIUM)
        }),
        tbody: book.addBorder({
          bottom: book.createBorderPr(excel.BORDER_STYLE_THIN)
        }),
      };

      var font = {
        title: book.addFont({
          bold: true,
          size: 18
        }),
        thead: book.addFont({
          bold: true,
          size: 12
        }),
        tbody: book.addFont({
          bold: false,
          size: 12
        }),
      };

      var style = {
        stdrd: book.createStyle('stdrd'),
        title: book.createStyle('title')
        .setFont(font.title)
        .setAlignment(excel.CELL_ALIGNMENT_H_CENTER, excel.CELL_ALIGNMENT_V_CENTER),
        thead: book.createStyle('header')
        .setFont(font.thead)
        .setBorder(border.thead)
        .setAlignment(excel.CELL_ALIGNMENT_H_CENTER, excel.CELL_ALIGNMENT_V_CENTER),
        tbody: book.createStyle('body')
        .setFont(font.tbody)
        .setBorder(border.tbody)
        .setAlignment(excel.CELL_ALIGNMENT_H_CENTER, excel.CELL_ALIGNMENT_V_CENTER),
        reset: book.createStyle('reset'),
      };

      // }}}

      var sheet = book.addSheet('日报');

      // Row/Column Counter
      var ir = 1;
      var ic = 1;

      var resetic = function () { ic = 1; };

      // Title {{{

      sheet
      .addRow(ir++, { height: 40 })
      .addCell(book.coords(ic++), excel.CELL_TYPE_STRING)
      .setStyle(style.title)
      .setValue('工作日报汇总(' + drng.join('至') + ')');

      sheet.mergeCell('A1:G1');

      // }}}

      // Column Width {{{

      sheet.setColumn(1, 1, 18);
      sheet.setColumn(2, 3, 10);
      sheet.setColumn(4, 6, 20);
      sheet.setColumn(7, 7, 31, true);

      // }}}

      // Table Header {{{

      resetic();

      sheet.addRow(ir++, { height: 16 })
      .addCell(book.coords(ic++), excel.CELL_TYPE_STRING).setStyle(style.thead)
      .setValue('时间')
      .row
      .addCell(book.coords(ic++), excel.CELL_TYPE_STRING).setStyle(style.thead)
      .setValue('工号')
      .row
      .addCell(book.coords(ic++), excel.CELL_TYPE_STRING).setStyle(style.thead)
      .setValue('姓名')
      .row
      .addCell(book.coords(ic++), excel.CELL_TYPE_STRING).setStyle(style.thead)
      .setValue('职位/区域')
      .row
      .addCell(book.coords(ic++), excel.CELL_TYPE_STRING).setStyle(style.thead)
      .setValue('项目/进展')
      .row
      .addCell(book.coords(ic++), excel.CELL_TYPE_STRING).setStyle(style.thead)
      .setValue('地点')
      .row
      .addCell(book.coords(ic++), excel.CELL_TYPE_STRING).setStyle(style.thead)
      .setValue('日报内容')
      ;

      // }}}

      // Table Content {{{

      _.each(data, function (row) {

        resetic();

        // Fill row data

        sheet.addRow(ir++, { height: 16 })
        .addCell(book.coords(ic++), excel.CELL_TYPE_STRING).setStyle(style.tbody)
        .setValue(moment(row.journal_timestamp).format('YYYY-MM-DD HH:mm:ss'))
        .row
        .addCell(book.coords(ic++), excel.CELL_TYPE_STRING).setStyle(style.tbody)
        .setValue(row.stuff_work_id)
        .row
        .addCell(book.coords(ic++), excel.CELL_TYPE_STRING).setStyle(style.tbody)
        .setValue(row.stuff_name)
        .row
        .addCell(book.coords(ic++), excel.CELL_TYPE_STRING).setStyle(style.tbody)
        .setValue(row.stuff_region + row.stuff_title)
        .row
        .addCell(book.coords(ic++), excel.CELL_TYPE_STRING).setStyle(style.tbody)
        .setValue(row.project_name + row.checkpoint_name)
        .row
        .addCell(book.coords(ic++), excel.CELL_TYPE_STRING).setStyle(style.tbody)
        .setValue(row.journal_submit_address)
        .row
        .addCell(book.coords(ic++), excel.CELL_TYPE_STRING).setStyle(style.tbody)
        .setValue(row.journal_content)
        ;

      });

      // }}}

      // Save to File

      var filename = 'journal-' + drng.join('-') + '.xlsx';
      var filepath = path.join(conf.cacheDir, filename);
      book.save(filepath);

      return res.download(filepath, filename);
    });

  }

};

Reference

• elxml https://github.com/dunkelrot/elxml
• Standard ECMA-376 OOXML http://www.ecma-international.org/publications/standards/Ecma-376.htm