angular 上传excel导入 爬坑 angular-file-upload

一 前言

负责维护 公司很老的 angular前端web项目，最近有个导入excel的新需求，本来该项目原有已成功的导入excel功能，由于对该项目业务不是很熟,导致前期找 原有可成功导入execel 的业务项都找的挺费劲

二、需求实现 磕磕碰碰

页面 express写的servers 上传路由转发  controller

1.页面

<legend class='hyc-legend'>
    <span>运单明细导入</span>
</legend>


<div class="col-md-13" style="margin-bottom: 40px">
    <div style="font-size: 16px; text-align: left; position: relative;">
        第一步:下载价格导入模板，批量导入运单明细
        <!--<div class="btn btn-default btn-sm btn-warning" ng-click="loadData('query')">模板下载</div>&nbsp;&nbsp;&nbsp;-->
        <a class="btn btn-warning" href="../../downLoad/ils-sc-template-03.xlsx" target="_blank" rel="external nofollow" >模版下载</a>
    </div>
    <div style="font-size: 16px; text-align: left; position: relative;">
        第二步:上传填写好的运单明细
        <input class="btn btn-warning" type="file" name="file" id="file" nv-file-select="" uploader="uploader"/>
    </div>

    <!--<h3>Upload queue</h3>-->
    <!--<p>Queue length: {{ uploader.queue.length }}</p>-->

    <table class="table" style="display: none">
        <thead>
        <tr>
            <th width="50%">文件名</th>
            <th ng-show="uploader.isHTML5">文件大小</th>
            <th ng-show="uploader.isHTML5">导入进度条</th>
            <th>成功状态</th>
            <th>操作</th>
        </tr>
        </thead>
        <tbody>
        <tr ng-repeat="item in uploader.queue">
            <td><strong>{{ item.file.name }}</strong></td>
            <td ng-show="uploader.isHTML5" nowrap>{{ item.file.size/1024/1024|number:2 }} MB</td>
            <td ng-show="uploader.isHTML5">
                <div class="progress" style="margin-bottom: 0;">
                    <div class="progress-bar" role="progressbar" ng-style="{ 'width': item.progress + '%' }"></div>
                </div>
            </td>
            <td class="text-center">
                <span ng-show="item.isSuccess"><i class="glyphicon glyphicon-ok"></i></span>
                <span ng-show="item.isCancel"><i class="glyphicon glyphicon-ban-circle"></i></span>
                <span ng-show="item.isError"><i class="glyphicon glyphicon-remove"></i></span>
            </td>
            <td nowrap>
                <button type="button" class="btn btn-success btn-xs" ng-click="item.upload()"
                        ng-disabled="item.isReady || item.isUploading || item.isSuccess">
                    <span class="glyphicon glyphicon-upload"> 导入</span>
                </button>
                <button type="button" class="btn btn-warning btn-xs" ng-click="item.cancel()"
                        ng-disabled="!item.isUploading">
                    <span class="glyphicon glyphicon-ban-circle"> 取消</span>
                </button>
                <button type="button" class="btn btn-danger btn-xs" ng-click="item.remove()">
                    <span class="glyphicon glyphicon-trash"> 移除</span>
                </button>
            </td>
        </tr>
        </tbody>
    </table>

    <!--<div>-->
    <!--<div>-->
    <!--导入进度:-->
    <!--<div class="progress" style="">-->
    <!--<div class="progress-bar" role="progressbar" ng-style="{ 'width': uploader.progress + '%' }"></div>-->
    <!--</div>-->
    <!--</div>-->
    <!--<button type="button" class="btn btn-success btn-s" ng-click="uploader.uploadAll()" ng-disabled="!uploader.getNotUploadedItems().length">-->
    <!--<span class="glyphicon glyphicon-upload"></span> Upload all-->
    <!--</button>-->
    <!--<button type="button" class="btn btn-warning btn-s" ng-click="uploader.cancelAll()" ng-disabled="!uploader.isUploading">-->
    <!--<span class="glyphicon glyphicon-ban-circle"></span> Cancel all-->
    <!--</button>-->
    <!--<button type="button" class="btn btn-danger btn-s" ng-click="uploader.clearQueue()" ng-disabled="!uploader.queue.length">-->
    <!--<span class="glyphicon glyphicon-trash"></span> Remove all-->
    <!--</button>-->
    <!--</div>-->

</div>
<div class="pull-right" style="font-size: 16px; margin-bottom: 10px;">
    <div class="btn btn-warning" ng-click="deleteAll()">取消导入</div>
    <div class="btn btn-warning" ng-click="importData(item)">确认导入</div>
</div>
<table class="table table-bordered" style="margin-top: 10px;">
    <tr>
        <th class="text-center">指令号</th>
        <th class="text-center">订单号</th>
        <th class="text-center">车型分类名</th>
        <th class="text-center">目的地</th>
        <th class="text-center">备注</th>
        <th class="text-center">实付现金</th>
        <th class="text-center">实付油费</th>
        <th class="text-center">结账时间</th>
        <th class="text-center">结账期</th>
        <th class="text-center">更新时间</th>
        <th class="text-center">创建时间</th>
        <th class="text-center">操作</th>
    </tr>
    <tr ng-repeat="item in laborList track by $index" ng-if="laborList.length > 0"
        ng-class="{'hyc-color-ff8888':item.validateResultCodeString == '错误','hyc-color-66ff66':item.validateResultCodeString == '警告'}">
    <td class="text-center">{{item.routeName}}</td>
        <td class="text-center">{{item.orderCode}}</td>
        <td class="text-center">{{item.orderNo}}</td>
        <td class="text-center">{{item.vcstylename}}</td>
        <td class="text-center">{{item.desName}}</td>
        <td class="text-center">{{item.remark}}</td>
        <td class="text-center">{{item.cashCost}}</td>
        <td class="text-center">{{item.fuelCost}}</td>
        <td class="text-center">{{item.payDate}}</td>
        <td class="text-center">{{item.shippingSystem}}</td>
        <td class="text-center">{{item.updateTime}}</td>
        <td class="text-center">{{item.createTime}}</td>
        <td class="text-center">
            <!--<a href="javascript:;" target="_blank" rel="external nofollow"  target="_blank" rel="external nofollow"  ng-click="showUpdateDlg(item)" ng-hide="item.isLock">修改</a>&nbsp;&nbsp;-->
            <a href="javascript:;" target="_blank" rel="external nofollow"  target="_blank" rel="external nofollow"  ng-click="del(item)" ng-hide="item.isLock">删除</a>
        </td>
    </tr>
    <tr ng-if="laborList.length <= 0">
        <td class="text-center" colspan="13">暂无符合条件数据</td>
    </tr>
</table>
<div ng-include="pageIncludeUrl" ng-if="laborList.length > 0"></div>
           

2. express写的servers

2.1 upload.js

此处  router.post('/driverBillDetailsExcel'，....） 是 controller 内执行的 路由，然后调用  uploadService.uploadDriverBillExcel()方法

'use strict';

var express = require('express');
var router = express.Router();

var uploadService = require('../routes/service/upload-service');
var baseConfig = require('../routes/resource/base-config');

/**
 * 运费明细上传
 */
router.post('/driverBillDetailsExcel', function (req, res, next) {
  uploadService.uploadDriverBillExcel(req, res, function (result, message) {
    if (message) {
      res.setHeader('Content-Type', 'text/html');
      res.send({
        success: false,
        message: message,
        data: []
      });
    } else {
      uploadService.uploadDriverBillToExcel(req, result, function (data) {
        res.setHeader('Content-Type', 'text/html');
        res.send(data);
      });
    }
  });
});

module.exports = router;
           

2.2 uploadService.js

'use strict';

var xlsx = require('node-xlsx');
var formidable = require('formidable');
var fs = require('fs');
var _ = require('underscore');
var constant = require('../resource/base-config');
var baseConfig = require('../resource/base-config');
var adminHost = constant.adminHost;
var host = constant.tmsdriverHost;
var kashost=constant.kasHost;

var request = require('request');
var sessionUtils = require('../utils/session-utils');

/**
 * 实际请求体
 * post request
 * @param url
 * @param req
 * @param callback
 */
function post(url, req, callback) {
    request.post({
        url: host + url,
        headers: {
            'Authorization': parseUseSession(req)
        },
        form: req.body
    }, function(err, res, body) {
        if (!err && res.statusCode === 200) {
            var result = JSON.parse(body);
            if (body.indexOf('{') >= 0 || body.indexOf('[') >= 0) {
                result = JSON.parse(body)
            } else {
                result = {
                    data: body,
                    message: null,
                    messageCode: null,
                    success: true
                };
            }
            // 登录写入token
            if (result && result.data && result.data.Authorization) {
                sessionUtils.setToken(req, result.data.Authorization);
                delete result.data.Authorization;
            }
            callback(result);
        }
        else {
            callback(null);
        }
    });
};

/**
 * 传递数据 运单明细 到服务器
 * @param list 列表
 */
function uploadDriverBillExcelService(list, callback) {
  var sheet = null;
 // 列表 对应字段 
  var keys = ['orderCode', 'orderNo', 'vcstylename', 'desName', 'remark', 'cashCost', 'fuelCost',
    'payDate', 'shippingSystem', 'updateTime', 'createTime'];
  var requestList = [];
  var isXls = false;
  if (list && list.length > 0) {
    isXls = true;
    sheet = list[0];
    console.log('list', list)
    if (sheet.data.length > 1) {
      for (var j = 1; j < sheet.data.length; j++) {
        var row = sheet.data[j];
        var cellObj = {};
        for (var i = 0; i < row.length; i++) {
          if (i === 1 && !row[i]) {
            break;
          }
          if (i < keys.length) {
            cellObj[keys[i]] = row[i] || '';
          }
        }
       // 重点坑  if (cellObj.orderCode) 对应的查询字段 导入字段 才能成功返回导入体
        if (cellObj.orderCode) {
          if(cellObj.payDate){
            var date = new Date(1900, 0, cellObj.payDate-1).toLocaleString();
            cellObj.payDate=date;
          }
          if(cellObj.updateTime){
            var date = new Date(1900, 0, cellObj.updateTime-1).toLocaleString();
            cellObj.updateTime=date;
          }
          if(cellObj.createTime){
            var date = new Date(1900, 0, cellObj.createTime-1).toLocaleString();
            cellObj.createTime=date;
          }
          requestList.push(cellObj);
        }
      }
      _.map(requestList, function () {
        // item.isUrgent = item.isUrgent == baseConfig.available.enable ? true : false;
        // item.isSencondhand = item.isSencondhand == baseConfig.available.enable ? false : true;
        // item.isMobile = item.isMobile == baseConfig.available.enable ? true : false;
        // item.isPick = item.isPick == baseConfig.available.enable ? true : false;
        // item.isDeliv = item.isDeliv == baseConfig.available.enable ? true : false;
        // if (item.deliveryTime == baseConfig.deliveryTime.am) {
        //     item.deliveryTime = 0;
        // } else if (item.deliveryTime == baseConfig.deliveryTime.pm) {
        //     item.deliveryTime = 1;
        // }
      });
    }
  }
  if (!isXls) {
    if (callback) callback(null);
  } else {
    if (callback) callback(requestList);
  }
};


/**
 * 上传文件-运单明细
 * @param req
 * @param res
 * @param callback
 */
uploadService.uploadDriverBillExcel = function (req, res, callback) {
  // parse a file uploadService
  var form = new formidable.IncomingForm();
  form.encoding = 'utf-8';
  form.keepExtensions = true;
  form.parse(req, function(err, fields, files) {
    if (files) {
      var efile = files.file;
      //var fileName = efile.name;
      var path = efile.path;
      var list = null;
      try{
        list = xlsx.parse(path);
      } catch (e) {
        list = [];
      }
      fs.unlinkSync(path);
      uploadDriverBillExcelService(list, callback);
    }
  });
};


// 实际执行的 后端请求地址 /driver_bill_details/import
uploadService.uploadDriverBillToExcel = function (req, list, callback) {
  req.body.excelData = JSON.stringify(list);
  post('/driver_bill_details/import', req, callback);
};
           

2.3 controller

.controller('FreightDetailsImportCtrl', ['$rootScope', '$scope', '$modal', 'DriverBillDetailsImport', 'FileUploader', function ($rootScope, $scope, $modal, DriverBillDetailsImport, FileUploader) {
   // 创建 FileUploader
    var uploader = $scope.uploader = new FileUploader({
      url: '/upload/driverBillDetailsExcel', // 进入express 转发路径
      fileName : 'file'
    });
    // a sync filter
    uploader.filters.push({
      name: 'syncFilter',
      fn: function(item /*{File|FileLikeObject}*/, options) {
        return this.queue.length < 10;
      }
    });
    // CALLBACKS

    uploader.onWhenAddingFileFailed = function(item /*{File|FileLikeObject}*/, filter, options) {
      // console.info('onWhenAddingFileFailed', item, filter, options);
    };
    uploader.onAfterAddingFile = function(fileItem) {
      fileItem.upload();
      // console.info('onAfterAddingFile', fileItem);
    };
    uploader.onAfterAddingAll = function(addedFileItems) {
      // console.info('onAfterAddingAll', addedFileItems);
    };
    uploader.onBeforeUploadItem = function(item) {
      // console.info('onBeforeUploadItem', item);
    };
    uploader.onProgressItem = function(fileItem, progress) {
      // console.info('onProgressItem', fileItem, progress);
    };
    uploader.onProgressAll = function(progress) {
      // console.info('onProgressAll', progress);
    };
    uploader.onSuccessItem = function(fileItem, response, status, headers) {
      // console.info('onSuccessItem', fileItem, response, status, headers);
    };
    uploader.onErrorItem = function(fileItem, response, status, headers) {
      // console.info('onErrorItem', fileItem, response, status, headers);
    };
    uploader.onCancelItem = function(fileItem, response, status, headers) {
      // console.info('onCancelItem', fileItem, response, status, headers);
    };
    uploader.onCompleteItem = function(fileItem, response, status, headers) {
      console.info('onCompleteItem', fileItem, response, status, headers);
      // 上传成功返回体
      layer.msg(response.message);
      $scope.vehicleFuelInfo.orderCode = response.data;
      $scope.loadExcelData(response.data);
    };
    uploader.onCompleteAll = function() {
      // console.info('onCompleteAll');
    };

    // console.info('uploader', uploader);

    $scope.vehicleFuelInfo = {
      id: '',
      orderCode: '',
      orderNo: '',
      vcstylename: '',
      desName: '',
      remark: '',
      cashCost: '',
      fuelCost: '',
      payDate: '',
      shippingSystem: '',
      updateTime:'',
      createTime: ''
    };

    function cl() {
      $scope.vehicleFuelInfo = {
        id: '',
        orderCode: '',
        orderNo: '',
        vcstylename: '',
        desName: '',
        remark: '',
        cashCost: '',
        fuelCost: '',
        payDate: '',
        shippingSystem: '',
        updateTime:'',
        createTime: ''
      };
    }

    // 当前对象
    $scope.userTruckObj = {
      pageNo: 1,
      pageSize: 10,
      loadMore: function ($event, pageNo) {
        this.pageNo = pageNo;
        if ((this.pageNo > $scope.page.totalPage)) {
          $event.stopPropagation();
          this.pageNo = $scope.page.totalPage;
          return;
        } else if (this.pageNo < 1) {
          $event.stopPropagation();
          this.pageNo = 1;
          return;
        }
        $scope.loadExcelData();
      }
    };

    //
    $scope.loadExcelData = function(data) {
      DriverBillDetailsImport.loadExcelData({
        pageNo: $scope.userTruckObj.pageNo,
        pageSize: $scope.userTruckObj.pageSize,
        orderCode:data
      }).success(function (result) {
        if (result.success) {
          $scope.vehicleList = result.data.list;
          $scope.page = result.data.page;
          buildPage();
        }else {
          layer.msg(result.message || '数据加载异常');
        }
      });
    };

    function buildPage () {
      $scope.item = [];
      var end = ($scope.page.totalPage - $scope.userTruckObj.pageNo) > 5?($scope.userTruckObj.pageNo == 1?(parseInt($scope.userTruckObj.pageNo)+5):(parseInt($scope.userTruckObj.pageNo)+5)):$scope.page.totalPage;
      var start = $scope.userTruckObj.pageNo > 5?($scope.userTruckObj.pageNo == 1?(parseInt($scope.userTruckObj.pageNo)-5):(parseInt($scope.userTruckObj.pageNo)-5)):1;
      for(var i=start; i <= end; i++){
        $scope.item.push(i);
      }
    };

    $scope.del = function (item) {
      var index = layer.confirm('确定删除该数据？', {
        btn: ['确定','取消'], //按钮,
        skin: 'admin-confirm-btn-class',
        title: '提示'
      }, function(){
        layer.close(index);
        if (item.id == '') {
          layer.msg('删除标识不能为空');
          return false;
        }
        DriverBillDetailsImport.del({id: item.id, orderCode:$scope.vehicleFuelInfo.orderCode}).success(function (result) {
          if (result.success) {
            $scope.loadExcelData($scope.vehicleFuelInfo.orderCode);
          } else {
            layer.msg(result.message || '删除数据异常');
          }
        });
      });
    };

    $scope.delete = function () {
      var index = layer.confirm('确定取消导入？', {
        btn: ['确定', '取消'], //按钮,
        skin: 'admin-confirm-btn-class',
        title: '提示'
      }, function () {
        layer.close(index);
        window.location.reload();
        // DriverBillDetailsImport.delete().success(function (result) {
        //     if (result.success) {
        //         layer.msg(result.message || '取消导入成功');
        //         $scope.loadExcelData($scope.vehicleFuelInfo.batchId);
        //     } else {
        //         layer.msg(result.message || '取消导入异常');
        //     }
        // });
      });
    };


    $scope.importData = function (item) {
      console.log('importData')
      var index = layer.confirm('确定导入？', {
        btn: ['确定', '取消'], //按钮,
        skin: 'admin-confirm-btn-class',
        title: '提示'
      }, function () {
        layer.close(index);
        if (item.orderCode != null) {
          layer.msg('导入数据有错误不能导入！');
          return false;
        }
        DriverBillDetailsImport.importData({orderCode:$scope.vehicleFuelInfo.orderCode}).success(function (result) {
          if (result.success) {
            $scope.loadExcelData($scope.vehicleFuelInfo.orderCode);
            layer.msg(result.message || '导入成功');
          } else {
            layer.msg(result.message || '导入数据异常');
          }
        });
      });
    };
  }])
           

三 总结

总结点1 需要熟悉 node.js express框架 了解中间件转发 此前有自学写一些node的框架 express 或koa2写过一些后端服务

总结点2 需要熟悉 angular-file-upload 插件的使用

总结点3 需要熟悉  uploadDriverBillExcelService() 逻辑编辑，中间一度卡壳在此处

总结点4 调试过程出现问题，需要排查问题  这写的 upload-service.js 无法使用console.log(）导致无法进行出错问题锁定位置 一度抓瞎 导致排查问题点很困难

总结点5 需要一种比对的异同逻辑 来一步步的排查问题 锁定问题