【导出】数据Excel

简要描述：

• 导出通过把获取分页数据的参数传递给服务端，服务端调用分页方法获取数据，然后把数据写入到Excel文件。
• 数据写入成功后返回excel所在的服务端地址，然后调用下载接口，进行导出数据。

/// <summary>
// 对应Excel的使用如值
/// <![CDATA[
/// 1、&=[].fieldName    根数据    data
/// 2、&=[images].fieldName  根下面节点
/// ]]>
/// </summary>
public string Key { get; set; }

• 出的模板行暂时不支持合并列

接口版本：

请求URL:

• http://{url参数}/bo/api/v3/excel/export/{mkey}/res/{resField}?pageIndex=1&pageSize=15

• &_nodejs=common-data

• http://localhost:58582/bo/api/v3/excel/export/bo-area/res/manager?pageIndex=1&pageSize=15

• {mkey} 对应mapper文件
• {resField} 对应 {mkey}里面 responseField键

请求方式：

• POST

请求头：

请求参数:

{
        url:
          "fi9BcHBfRGF0YS9UZW1wbGF0ZXMvYmFzaXMvYm8tYXJlYS90ZW1wbGF0ZV9iby1hcmVhX2Jhc2lzLnhsc3g=",
        content:null,
        urlB:true
 }

如果数据直接传递，将作为结果数据进行导出
{urlB:true,fromDataKey:data,data:[]}  
{urlB:true,fromDataKey:data,data:{}}
urlB：是否输出 base64结果
fromDataKey：数据来源的key, 默认data

返回示例:

正确时返回:

{
        data: {
           urlB:"",
          url:
            "~/App_Export/Excels/rg/rg-20200601-main/bo-area/user-ip/ipip1/2021-01-27/rg-bo-area-0af13a65-cba0-4628-9e2f-5e353ab0d4b9.xlsx",
          fileName: "Sheet1",
        },
        code: 0,
        success: true,
      }

错误时返回:

{
    "code": 0,
    "message": ""
    "error":{
      errorCode:null，
      errorText:null
    }
    "success": false,
}

返回参数说明:

.netcore服务端实现代码参考:

/// <summary>
        /// 导出模板数据
        /// </summary>
        /// <param name="body"></param>
        /// <param name="mkey"></param>
        /// <param name="resField"></param>
        /// <returns></returns>
        [HttpPost("export/{mkey}/res/{resField}")]
        public async Task<IActionResult> ExportByPathAndJsonByMkey([FromBody] FileData body, string mkey, string resField)
        {
            if (body == null || string.IsNullOrEmpty(body.Url))
            {
                throw new BizException(ErrorCodeConst.FILE_17001.ErrorCode, "url必须传递");
            }
            string templatePath = body.Url;
            //base64 编码中使用了 +号，+号通过URL传递时会变成空格，因为编码的方式的问题
            //前台使用:Ext.encode(title_text.getValue().replace(/\\+/ g, '%2B'))就可以了
            //也就是说可以通过替换的方式，或者写成“% 2B”的形式也可以
            //url编码的表示方式，对于每个字节，可以用 % 后面跟ASCII码的两位十六进制值表示。
            //templatePath = templatePath.Replace(" ", "+");
            templatePath = _base64Service.Base64Decode(templatePath);
            if (string.IsNullOrEmpty(templatePath))
            {
                throw new BizException(ErrorCodeConst.FILE_17001.ErrorCode, "url格式不正确");
            }

            if (!FileUtil.ExistsFilesByVirPath(templatePath))
            {
                throw new BizException(ErrorCodeConst.FILE_17001.ErrorCode, "模板【" + templatePath + "】文件未找到。");
            }

            var pf = new PFGlobalParameter
            {
                OwnerId = RequestPFUtil.GetOwnerId(this.HttpContext),
                SysId = RequestPFUtil.GetSysId(this.HttpContext),
                ModuleKey = mkey,
                ReponseField = resField,
                UserId = RequestPFUtil.GetUserIdByHeader(this.HttpContext),
                ClientUserIp = HttpContextUtil.GetClientUserIp(this.HttpContext, false).Replace(".", "-").Replace(":", "ip")
            };

            JToken jToken = null;
            if (!string.IsNullOrEmpty(body.Content))
            {
                jToken = JsonUtil.Deserialize<JToken>(body.Content);
            }
            else
            {
                //控制导出,特别对于加密数据,需要二次处理配置信息
                if (resField.Contains("_detail"))
                {
                    resField = resField.Replace("_detail", string.Empty);
                    resField = $"{resField}-export_detail";
                }
                else
                {
                    resField = $"{resField}-export";
                }
                var resultT = await _pagerService.PageUsed($"{mkey}", resField,null, this.HttpContext);
                string value = JsonUtil.Serialize(resultT, true, false, false);

                if (body.Debug == true)
                {
                    //开启调试模式
                    FileUtil.CreateFileAndContentAsync($"~/App_Logs/Excels/{pf.OwnerId}/{pf.ModuleKey}/{resField}/{DateTime.Now.ToString("yyyy-MM-dd")}.json", value);
                }
                jToken = JsonUtil.Deserialize<JToken>(value)["value"];
            }
            ResultObject<object> result = _ExcelRepository.DoExportExcelJToken(pf, templatePath, jToken);
            return await Task.Run(() => new JsonResult(result));

            //return JsonUtil.Serialize(result, true, true, true);
        }

前端使用代码

downTemplateUrl: function () {
      this.$bsAjax.doDown(this.excelTemplates.current.template, null, true);
    },

let data = {
        url: window.btoa(that.excelTemplates.path),
         //content: JSON.stringify({data:[]})
      };
  that.$bsAjax
        .doExport(that.meta.mkey, that.meta.field, that.filter, data)
        .then((res) => {
          if (!res.data || res.data.success !== true) {
            that.$msgBox.error("导出失败");
            that.$store.dispatch("dataLoading", false);
            return;
          } else {
            that.downTemplateUrl(res.data.data.url);
            that.$msgBox.success("导出成功，已下载，请保存。");
            that.$store.dispatch("dataLoading", false);
          }
        });

前端使用代码 (简洁版)

 //获取数据，提供外部使用
    getData() {
      return this.used;
    },
    //导出数据
    doExportData() {
      let that = this;
      that.$bsAjax.doExportBiz(
        that,
        that.used,
        that.rootMeta,
        "~/App_Data/idc/excels/idc-resource-space-statistics/template_idc-resource-space-statistics_settings.xlsx",
        that.filter
      );
    },

模板案例

• &={test3}. 表示 根节点下 test3 数组对象
• &=[test]. 表示根节点下面 test3对象
• &=[] 表示根节点下面data对象或者数组

模板合并行列

• 合并标识目标

• $merged={“sourceC”:”A”,”targetC”:”I”,”sourceR”:0,”targetRs”:1}

• $merged= 关键字

• sourceC 来源列

• sourceR 来源行，（根据存放位置，动态获取）

• targetRs 合并几行， 如果0，表示本行

Json导出数据源码实现

 /// <summary>
        /// 把 jtoken 直接导出到 excel
        /// </summary>
        /// <param name="pf"></param>
        /// <param name="virTemplatePath"></param>
        /// <param name="jToken">
        /// 如果是数组,必须放在 data节点
        /// {data:[{sex:{name:null,value:null},name:null}],dim:{}}
        /// {data:{sex:{name:null,value:null},name:null},dim:{}}
        /// </param>
        /// <param name="phyPathT">如果有值，模板文件和生成文件为同一个</param>
        /// <returns></returns>
        public ResultObject<object> DoExportExcelJToken(PFGlobalParameter pf, string virTemplatePath, JToken jToken, string phyPathT = null)
        {
            IWorkbook iworkbook = InitWorkbook(virTemplatePath);
            string outPath = GetVirOutputPath(pf, virTemplatePath);

            string phyPath = PhyPathUtil.ConvertVirToPhyPath(outPath);
            if (!string.IsNullOrEmpty(phyPathT))
            {
                phyPath = phyPathT;
            }
            IEnumerable<TemplateConfig> lstTC = GetExcelTemplateConfig(iworkbook, true).OrderByDescending(temp => temp.Key);

            //获取作为导出的数据源 data
            JToken jTokenTemp = jToken;
            string fileName = string.Empty;
            string jtokenKey = "data";
            //获取数据来源key
            if (jToken["fromDataKey"] != null)
            {
                jtokenKey = jToken["fromDataKey"].Value<string>();
            }
            for (int sheetIndex = 0; sheetIndex < iworkbook.NumberOfSheets; sheetIndex++)
            {
                string sheetName = iworkbook.GetSheetName(sheetIndex);
                foreach (var tc in lstTC)
                {
                    ISheet sheet = iworkbook.GetSheet(sheetName);

                    SetSheetCellsByJToken(jToken, tc, iworkbook, sheet, jtokenKey);
                }
                if (string.IsNullOrEmpty(fileName))
                {
                    fileName = sheetName;
                }
            }
            WriteExcelToFile(iworkbook, phyPath);

            iworkbook = InitWorkbook(phyPath);
            lstTC = GetExcelTemplateConfig(iworkbook, true).OrderByDescending(temp => temp.Key);
            if (lstTC != null && lstTC.Any() && lstTC.Count() > 0)
            {
                DoExportExcelJToken(pf, phyPath, jToken, phyPath);
            }
            else
            {
                lstTC = GetExcelTemplateConfigChild(iworkbook).OrderByDescending(temp => temp.Key);
                for (int sheetIndex = 0; sheetIndex < iworkbook.NumberOfSheets; sheetIndex++)
                {
                    string sheetName = iworkbook.GetSheetName(sheetIndex);
                    foreach (var tc in lstTC)
                    {
                        ISheet sheet = iworkbook.GetSheet(sheetName);
                        SetSheetCellsByJToken(jToken, tc, iworkbook, sheet, jtokenKey);
                    }
                    if (string.IsNullOrEmpty(fileName))
                    {
                        fileName = sheetName;
                    }
                }
                //合并行列
                IEnumerable<TemplateConfig> lstMerged = GetExcelTemplateMerged(iworkbook);
                for (int sheetIndex = 0; sheetIndex < iworkbook.NumberOfSheets; sheetIndex++)
                {
                    string sheetName = iworkbook.GetSheetName(sheetIndex);
                    foreach (var tc in lstMerged)
                    {
                        ISheet sheet = iworkbook.GetSheet(sheetName);
                        sheet.AddMergedRegion(CellRangeAddress.ValueOf(tc.Merged));

                        var row = sheet.GetRow(tc.SourceR );
                        row.RemoveCell(row.GetCell(tc.ColumnIndex));
                    }
                }
                WriteExcelToFile(iworkbook, phyPath);
            }
            string urlB = null;
            if (jToken["urlB"] != null || jToken["url_b"] != null)
            {
                urlB = _base64Service.Base64Encode(outPath);

            }
            return new ResultObject<object> { Success = true, Data = new { Url = outPath, UrlB = _base64Service.Base64Encode(outPath), FileName = fileName } };
        }