Export2Excels.js

/* eslint-disable */
import {
  saveAs
} from 'file-saver'
import XLSX from 'xlsx'
import common from "~/config/Declare/Common/common";
import rcp from "~/config/rcpmis/common";
import CodeTable from "~/config/rcpmis/transbouIncomeBase";
import rmbtransboubuysale from "~/config/rcpmis/rmbtransboubuysale";
import Codes from "~/config/CodeTable";
import rmbIntbankAcc from "~/config/rcpmis/rmbIntbankAcc";
import intbankAccBalance from "~/config/rcpmis/intbankAccBalance";
import bankDocSetFin from "~/config/rcpmis/bankDocSetFin";
import rmbamttransfer from "~/config/rcpmis/rmbamttransfer";
import creditfinancing from "~/config/rcpmis/creditfinancing";
import transboupayout from "~/config/rcpmis/transboupayout";
import unrsdntinvestacc from "~/config/rcpmis/unrsdntinvestacc";
import unrsdntinvestaccbal from "~/config/rcpmis/unrsdntinvestaccbal";
import guaranteeenroll from "~/config/rcpmis/guaranteeenroll";
import peraccountbalance from "~/config/rcpmis/peraccountbalance";
import currency from "~/config/rcpmis/cny";
import request from "~/utils/request";
import falG01 from "~/config/declare/safe/falG01";
import falA011 from "~/config/declare/safe/falA011";
import falA012 from "~/config/declare/safe/falA012";
import falA021 from "~/config/declare/safe/falA021";
import falA022 from "~/config/declare/safe/falA022";
import falA023 from "~/config/declare/safe/falA023";
import falB01 from "~/config/declare/safe/falB01";
import falB03 from "~/config/declare/safe/falB03";
import falB05 from "~/config/declare/safe/falB05";
import falB06 from "~/config/declare/safe/falB06";
import falC01 from "~/config/declare/safe/falC01";
import falD03 from "~/config/declare/safe/falD03";
import falH01 from "~/config/declare/safe/falH01";
import falH02 from "~/config/declare/safe/falH02";
import falCode from "~/config/declare/safe/falCode";
import fal from "~/config/Declare/safe/fal";
import falCommon from "~/config/declare/safe/common";
import cwdCode from "~/config/declare/safe/cwdCode";
import exdCode from "~/config/declare/safe/exdCode";
import bnkFin2103Code from "~/config/declare/rcpmis/bnkFin2103Code";
import finDer2180Code from "~/config/declare/rcpmis/finDer2180Code";
import bopCode from "~/config/declare/safe/bop";

function generateArray(table) {
  var out = [];
  var rows = table.querySelectorAll('tr');
  var ranges = [];
  for (var R = 0; R < rows.length; ++R) {
    var outRow = [];
    var row = rows[R];
    var columns = row.querySelectorAll('td');
    for (var C = 0; C < columns.length; ++C) {
      var cell = columns[C];
      var colspan = cell.getAttribute('colspan');
      var rowspan = cell.getAttribute('rowspan');
      var cellValue = cell.innerText;
      if (cellValue !== "" && cellValue == +cellValue) cellValue = +cellValue;

      //Skip ranges
      ranges.forEach(function (range) {
        if (R >= range.s.r && R <= range.e.r && outRow.length >= range.s.c && outRow.length <= range.e.c) {
          for (var i = 0; i <= range.e.c - range.s.c; ++i) outRow.push(null);
        }
      });

      //Handle Row Span
      if (rowspan || colspan) {
        rowspan = rowspan || 1;
        colspan = colspan || 1;
        ranges.push({
          s: {
            r: R,
            c: outRow.length
          },
          e: {
            r: R + rowspan - 1,
            c: outRow.length + colspan - 1
          }
        });
      };

      //Handle Value
      outRow.push(cellValue !== "" ? cellValue : null);

      //Handle Colspan
      if (colspan)
        for (var k = 0; k < colspan - 1; ++k) outRow.push(null);
    }
    out.push(outRow);
  }
  return [out, ranges];
};

function datenum(v, date1904) {
  if (date1904) v += 1462;
  var epoch = Date.parse(v);
  return (epoch - new Date(Date.UTC(1899, 11, 30))) / (24 * 60 * 60 * 1000);
}

function sheet_from_array_of_arrays(data, opts) {
  var ws = {};
  var range = {
    s: {
      c: 10000000,
      r: 10000000
    },
    e: {
      c: 0,
      r: 0
    }
  };
  for (var R = 0; R != data.length; ++R) {
    for (var C = 0; C != data[R].length; ++C) {
      if (range.s.r > R) range.s.r = R;
      if (range.s.c > C) range.s.c = C;
      if (range.e.r < R) range.e.r = R;
      if (range.e.c < C) range.e.c = C;
      var cell = {
        v: data[R][C]
      };
      if (cell.v == null) continue;
      var cell_ref = XLSX.utils.encode_cell({
        c: C,
        r: R
      });

      if (typeof cell.v === 'number') cell.t = 'n';
      else if (typeof cell.v === 'boolean') cell.t = 'b';
      else if (cell.v instanceof Date) {
        cell.t = 'n';
        cell.z = XLSX.SSF._table[14];
        cell.v = datenum(cell.v);
      } else cell.t = 's';

      ws[cell_ref] = cell;
    }
  }
  if (range.s.c < 10000000) ws['!ref'] = XLSX.utils.encode_range(range);
  return ws;
}

function Workbook() {
  if (!(this instanceof Workbook)) return new Workbook();
  this.SheetNames = [];
  this.Sheets = {};
}

function s2ab(s) {
  var buf = new ArrayBuffer(s.length);
  var view = new Uint8Array(buf);
  for (var i = 0; i != s.length; ++i) view[i] = s.charCodeAt(i) & 0xFF;
  return buf;
}

export function export_table_to_excel(id) {
  var theTable = document.getElementById(id);
  var oo = generateArray(theTable);
  var ranges = oo[1];

  /* original data */
  var data = oo[0];
  var ws_name = "SheetJS";

  var wb = new Workbook(),
    ws = sheet_from_array_of_arrays(data);

  /* add ranges to worksheet */
  // ws['!cols'] = ['apple', 'banan'];
  ws['!merges'] = ranges;

  /* add worksheet to workbook */
  wb.SheetNames.push(ws_name);
  wb.Sheets[ws_name] = ws;

  var wbout = XLSX.write(wb, {
    bookType: 'xlsx',
    bookSST: false,
    type: 'binary'
  });

  saveAs(new Blob([s2ab(wbout)], {
    type: "application/octet-stream"
  }), "test.xlsx")
}

export function export_json_to_excel({
  multiHeader = [],
  header,
  data,
  sheetname,
  filename,
  merges = [],
  autoWidth = true,
  bookType = 'xlsx'
} = {}) {
  /* original data */

  filename = filename || 'excel-list'
  data = [...data]
  for (var i = 0; i < header.length; i++) {
    data[i].unshift(header[i]);
  }
  // data.unshift(header);

  for (let i = multiHeader.length - 1; i > -1; i--) {
    data.unshift(multiHeader[i])
  }

  var ws_name = sheetname;
  var wb = new Workbook(),
    ws = []
  for (var i = 0; i < header.length; i++) {
    ws.push(sheet_from_array_of_arrays(data[i]));
  }

  if (merges.length > 0) {
    if (!ws['!merges']) ws['!merges'] = [];
    merges.forEach(item => {
      ws['!merges'].push(XLSX.utils.decode_range(item))
    })
  }

  if (autoWidth) {
    /*设置worksheet每列的最大宽度*/
    const colWidth = []
    for (var k = 0; k < header.length; k++) {
      colWidth.push(data[k].map(row => row.map(val => {
        /*先判断是否为null/undefined*/
        if (val == null) {
          return {
            'wch': 10
          };
        }
        /*再判断是否为中文*/
        else if (val.toString().charCodeAt(0) > 255) {
          return {
            'wch': val.toString().length * 2
          };
        } else {
          return {
            'wch': val.toString().length
          };
        }
      })))
    }
    // const colWidth = data.map(row => row.map(val => {
    //   /*先判断是否为null/undefined*/
    //   if (val == null) {
    //     return {
    //       'wch': 10
    //     };
    //   }
    //   /*再判断是否为中文*/
    //   else if (val.toString().charCodeAt(0) > 255) {
    //     return {
    //       'wch': val.toString().length * 2
    //     };
    //   } else {
    //     return {
    //       'wch': val.toString().length
    //     };
    //   }
    // }))
    /*以第一行为初始值*/
    let result = [];
    for (var k = 0; k < colWidth.length; k++) {
      result[k] = colWidth[k][0]
      for (let i = 1; i < colWidth[k].length; i++) {
        for (let j = 0; j < colWidth[k][i].length; j++) {
          if (result[k][j]['wch'] < colWidth[k][i][j]['wch']) {
            result[k][j]['wch'] = colWidth[k][i][j]['wch'];
          }
        }
      }
    }
    for (var l = 0; l < result.length; l++) {
      ws[l]['!cols'] = result[l];
    }
  }

  /* add worksheet to workbook */
  for (var k = 0; k < header.length; k++) {
    wb.SheetNames.push(ws_name[k]);
    wb.Sheets[ws_name[k]] = ws[k];
  }

  var wbout = XLSX.write(wb, {
    bookType: bookType,
    bookSST: false,
    type: 'binary'
  });
  saveAs(new Blob([s2ab(wbout)], {
    type: "application/octet-stream"
  }), `${filename}.${bookType}`);
}

export function bopcurFormatFunc(value, config) {
  for (var i = 0; i < config.length; i++) {
    for (var j = 0; j < config[i].length; j++) {
      if (value == config[i][j].value) {
        return config[i][j].label
      }
    }
  }
}

export function codeFormatFunc(value, config) {
  for (var i = 0; i < config.length; i++) {
    if (value == config[i].value) {
      return config[i].label
    }
  }
}

export function formatKey(keyValue, codeTable) {
  for (let i in codeTable) {
    var codes = {
      key: "",
      value: []
    };
    var flag = false;
    for (var j = 0; j < keyValue.length; j++) {
      if (i == keyValue[j].key) {
        keyValue[j].value.push(codeTable[i]);
        flag = true;
      }
    }
    if (!flag) {
      codes.key = i;
      codes.value.push(codeTable[i]);
      keyValue.push(codes);
    }
  }
}

export function formatBopcod(vale, bopcod) {
  for (let i in bopcod) {
    vale.push(bopcod[i]);
  }
}

export function formatJson(filterVal, jsonData, filename, cny, country) {
  var keyValue = [];
  formatKey(keyValue, CodeTable);
  formatKey(keyValue, common);
  formatKey(keyValue, rcp);
  formatKey(keyValue, rmbtransboubuysale);
  formatKey(keyValue, Codes);
  formatKey(keyValue, rmbIntbankAcc);
  formatKey(keyValue, intbankAccBalance);
  formatKey(keyValue, bankDocSetFin);
  formatKey(keyValue, rmbamttransfer);
  formatKey(keyValue, creditfinancing);
  formatKey(keyValue, transboupayout);
  formatKey(keyValue, unrsdntinvestacc);
  formatKey(keyValue, unrsdntinvestaccbal);
  formatKey(keyValue, guaranteeenroll);
  formatKey(keyValue, guaranteeenroll);
  formatKey(keyValue, peraccountbalance);
  formatKey(keyValue, falG01);
  formatKey(keyValue, falA011);
  formatKey(keyValue, falA012);
  formatKey(keyValue, falA021);
  formatKey(keyValue, falA022);
  formatKey(keyValue, falA023);
  formatKey(keyValue, falB01);
  formatKey(keyValue, falB03);
  formatKey(keyValue, falB05);
  formatKey(keyValue, falB06);
  formatKey(keyValue, falC01);
  formatKey(keyValue, falD03);
  formatKey(keyValue, falH01);
  formatKey(keyValue, falH02);
  formatKey(keyValue, falCode);
  formatKey(keyValue, fal);
  formatKey(keyValue, falCommon);
  formatKey(keyValue, cwdCode);
  formatKey(keyValue, exdCode);
  formatKey(keyValue, bnkFin2103Code);
  formatKey(keyValue, finDer2180Code);
  formatKey(keyValue, bopCode);

  var valeIn = [];
  formatBopcod(valeIn, common.bopcodi1);
  formatBopcod(valeIn, common.bopcodi2);
  formatBopcod(valeIn, common.bopcodi3);
  formatBopcod(valeIn, common.bopcodi4);
  formatBopcod(valeIn, common.bopcodi5);
  formatBopcod(valeIn, common.bopcodi6);
  formatBopcod(valeIn, common.bopcodi7);
  formatBopcod(valeIn, common.bopcodi8);
  formatBopcod(valeIn, common.bopcodi9);

  var valeOut = [];
  formatBopcod(valeOut, common.bopcodo1);
  formatBopcod(valeOut, common.bopcodo2);
  formatBopcod(valeOut, common.bopcodo3);
  formatBopcod(valeOut, common.bopcodo4);
  formatBopcod(valeOut, common.bopcodo5);
  formatBopcod(valeOut, common.bopcodo6);
  formatBopcod(valeOut, common.bopcodo7);
  formatBopcod(valeOut, common.bopcodo8);
  formatBopcod(valeOut, common.bopcodo9);

  var cnyCode = [];
  var countryCode = [];
  for (let i in currency) {
    for (let j in currency[i]) {
      if (i == "cny") {
        cnyCode.push([j]);
      }
      if (i == "country") {
        countryCode.push([j]);
      }
    }
  }
  return jsonData.map(v =>
    filterVal.map(j => {
      for (var i = 0; i < keyValue.length; i++) {
        if (j != keyValue[i].key) {
          if (j == "balofpaycode" || j == "txcode" || j == "txcode2") {
            if (filename == "跨境收入信息" || filename == " 涉外收入申报单" || filename == "境内收入申报单") {
              return codeFormatFunc(v[j], valeIn);
            } else {
              return codeFormatFunc(v[j], valeOut);
            }
          } else if (j === "z0304" || j === "z0309" || j === "b0203" || j === "b0208" || j === "b0406" || j === "d0103" || j === "d0202" || j === "d0204" || j === "d0503" || j === "f0104" || j === "d0402" || j === "d0802") {
            return codeFormatFunc(v[j], falCommon.department);
          } else if (j === "b0209" || j === "b0407" || j === "d0104" || j === "b0208" || j === "d0205" || j === "d0504" || j === "f0105" || j === "d0403" || j === "d0803") {
            return codeFormatFunc(v[j], falCommon.relation);
          }
          for (let l in cnyCode) {
            if (cnyCode[l] == j) {
              return codeFormatFunc(v[j], cny);
            }
          }
          for (let k in countryCode) {
            if (countryCode[k] == j) {
              return codeFormatFunc(v[j], country);
            }
          }
          if (i + 1 == keyValue.length) {
            return v[j];
          }
          continue;
        } else {
          return bopcurFormatFunc(v[j], keyValue[i].value);
        }
      }
    })
  );
}

export async function json2excel(tableJson, filename, autowidth, bookTypes) {
  var tHeader = [];
  var dataArr = [];
  var sheetname = [];
  var cny = [];
  var country = [];
  var params = {
    current: 0,
    size: 0
  };
  let rst = await request.post("v1/pm/curs", params);

  if (rst.code == "000000") {
    for (var i = 0; i < rst.data.length; i++) {
      cny.push({
        value: rst.data[i].currCode,
        label: rst.data[i].currSymbol + "-" + rst.data[i].currCnName
      });
    }
  }

  var info = {
    current: 0,
    size: 0,
    data: {}
  };
  let re = await request.post("v1/pm/Cou/cous", info);

  if (re.code == "000000") {
    for (var i = 0; i < re.data.length; i++) {
      country.push({
        value: re.data[i].countryRegionCode,
        label: re.data[i].countryEnName + "-" + re.data[i].countryCnName
      });
    }
  }

  for (var i in tableJson) {
    tHeader.push(tableJson[i].tHeader);
    dataArr.push(formatJson(tableJson[i].filterVal, tableJson[i].data, filename, cny, country));
    sheetname.push(tableJson[i].sheetname);
  }
  export_json_to_excel({
    header: tHeader,
    data: dataArr,
    filename: filename,
    sheetname: sheetname,
    autowidth: autowidth,
    bookTypes: bookTypes
  });
}