<template>
  <div ref="sheetContainer" v-bind:id="sheetContainerId" class="grid"></div>
</template>

<script>
import Spreadsheet from "x-data-spreadsheet";
import zhCN from "x-data-spreadsheet/src/locale/zh-cn";
import _ from "lodash";
import Excel from "exceljs";
import tinycolor from "tinycolor2";

export default {
  name: "excelSpreadSheet",
  props: {
    ColumnCount: {
      type: Number,
      default: () => 50,
    },
    ColumnWidth: {
      type: Number,
      default: () => 100,
    },
    RowCount: {
      type: Number,
      default: () => 9999,
    },
    SheetName: {
      type: String,
      default: () => [],
    },
    Headers: {
      type: Array,
      default: () => [],
    },
    Records: {
      type: Array,
      default: () => [],
    },
    /*表头样式*/
    HeaderStyle: {
      type: Object,
      default: () => {
        return {
          //bgcolor: "#f4f5f8",
          textwrap: true,
          color: "#900b09",
          align: "center",
          valign: "middle",
          border: {
            top: ["thin", "#1E1E1E"],
            bottom: ["thin", "#1E1E1E"],
            right: ["thin", "#1E1E1E"],
            left: ["thin", "#1E1E1E"],
          },
          font: {
            bold: true,
          },
        };
      },
    },
    /*表体样式*/
    RecordStyle: {
      type: Object,
      default: () => {
        return {
          //bgcolor: "#f4f5f8",
          textwrap: true,
          color: "#900b09",
          align: "left",
          valign: "middle",
          border: {
            top: ["thin", "#1E1E1E"],
            bottom: ["thin", "#1E1E1E"],
            right: ["thin", "#1E1E1E"],
            left: ["thin", "#1E1E1E"],
          },
          font: {
            bold: false,
          },
        };
      },
    },
    File: {
      type: null,
      default: () => null,
    },
    ExportJsonProperties: {
      type: Array,
      default: () => [],
    },
  },
  data() {
    return {
      xs: null,
      sheetContainerId: 'sheetContainerId_101',
      DataSource: []
    };
  },
  mounted() {
    this.$nextTick(() => {
      this.init();
    });
  },
  watch: {
    File: {
      handler(newV, oldV) {
        this.$nextTick(() => {
          this.loadExcelFile(newV);
        });
      },
    },
    Headers: {
      deep: true,
      handler(newV) {
        let result = [];
        if (Array.isArray(newV) && newV.length > 0) {
          let headerRow = {cells: []};
          for (let i = 0; i < newV.length; i++) {
            headerRow.cells.push({
              text: newV[i],
              editable: false,
              style: 0,
            });
          }
          result.push(headerRow);
        }
        if (Array.isArray(this.Records) && this.Records.length > 0) {
          for (let i = 0; i < this.Records.length; i++) {
            let recordRow = {cells: []};
            if (JSON.stringify(this.Records[i]) != "{}") {
              for (let k = 0; k < this.ExportJsonProperties.length; k++) {
                recordRow.cells.push({
                  text: this.Records[i][this.ExportJsonProperties[k]] + "",
                  editable: true,
                  style: 1,
                });
              }
            } else {
              for (let i = 0; i < this.ColumnCount; i++) {
                recordRow.cells.push({
                  text: "",
                  editable: true,
                  style: 1,
                });
              }
            }
            result.push(recordRow);
          }
        }
        this.DataSource = result;
      },
    },
    Records: {
      deep: true,
      handler(newV) {
        let result = [];
        if (Array.isArray(this.Headers) && this.Headers.length > 0) {
          let headerRow = {cells: []};
          for (let i = 0; i < this.Headers.length; i++) {
            headerRow.cells.push({
              text: this.Headers[i],
              editable: false,
              style: 0,
            });
          }
          result.push(headerRow);
        }
        if (Array.isArray(newV) && newV.length > 0) {
          for (let i = 0; i < newV.length; i++) {
            let recordRow = {cells: []};
            if (JSON.stringify(newV[i]) != "{}") {
              for (let k = 0; k < this.ExportJsonProperties.length; k++) {
                recordRow.cells.push({
                  text: newV[i][this.ExportJsonProperties[k]] + "",
                  editable: true,
                  style: 1,
                });
              }
            } else {
              for (let i = 0; i < this.ColumnCount; i++) {
                recordRow.cells.push({
                  text: "",
                  editable: true,
                  style: 1,
                });
              }
            }
            result.push(recordRow);
          }
        }
        this.DataSource = result;
      }
    },
    DataSource: {
      deep: true,
      handler(newW) {
        if (this.xs) {
          this.xs.loadData([
            {
              name: this.SheetName,
              styles: [this.HeaderStyle, this.RecordStyle],
              rows: newW,
            },
          ]);
        }
      }
    }
  },
  methods: {
    // 初始化
    init() {
      if (this.$refs.sheetContainer && this.$refs.sheetContainer.offsetHeight && this.$refs.sheetContainer.offsetWidth) {
        //设置中文
        Spreadsheet.locale("zh-cn", zhCN);
        this.xs = new Spreadsheet(
          document.getElementById(this.sheetContainerId),
          {
            mode: "edit",
            showToolbar: true,
            showGrid: true,
            showContextmenu: true,
            showBottomBar: true,
            view: {
              height: () =>
                this.$refs.sheetContainer &&
                this.$refs.sheetContainer.offsetHeight &&
                _.isNumber(this.$refs.sheetContainer.offsetHeight)
                  ? this.$refs.sheetContainer.offsetHeight
                  : 0,
              width: () =>
                this.$refs.sheetContainer &&
                this.$refs.sheetContainer.offsetWidth &&
                _.isNumber(this.$refs.sheetContainer.offsetWidth)
                  ? this.$refs.sheetContainer.offsetWidth
                  : 0,
            },
            formats: [],
            fonts: [],
            formula: [],
            row: {
              len: this.RowCount,
              height: 25,
            },
            col: {
              len: this.ColumnCount,
              width: this.ColumnWidth,
              indexWidth: 60,
              minWidth: 60,
            },
          }
        );
        this.loadData();
      }
    },
    loadData() {
      if (this.xs) {
        this.xs.loadData([
          {
            name: this.SheetName,
            styles: [this.HeaderStyle, this.RecordStyle],
            rows: this.DataSource,
          },
        ]);
      }
    },
    // 将exceljsWorkbook结构体提组装成x-data-spreadsheet结构体
    dataEjstoXs(workbook) {
      let workbookData = [];
      workbook.eachSheet((sheet, sheetIndex) => {
        if (!sheet.columns) {
          return
        }
        // 构造x-data-spreadsheet 的 sheet 数据源结构
        let sheetData = {
          name: sheet.name,
          styles: [],
          rows: {},
          merges: [],
        };
        // 收集合并单元格信息
        let mergeAddressData = [];
        for (let mergeRange in sheet._merges) {
          sheetData.merges.push(sheet._merges[mergeRange].shortRange);
          let mergeAddress = {};
          // 合并单元格起始地址
          mergeAddress.startAddress = sheet._merges[mergeRange].tl;
          // 合并单元格终止地址
          mergeAddress.endAddress = sheet._merges[mergeRange].br;
          // Y轴方向跨度
          mergeAddress.YRange = sheet._merges[mergeRange].model.bottom - sheet._merges[mergeRange].model.top;
          // X轴方向跨度
          mergeAddress.XRange = sheet._merges[mergeRange].model.right - sheet._merges[mergeRange].model.left;
          mergeAddressData.push(mergeAddress);
        }
        // 设置列宽
        sheetData.cols = {};
        for (let i = 0; i < sheet.columns.length; i++) {
          sheetData.cols[i.toString()] = {};
          if (sheet.columns[i].width) {
            // 读取宽度
            sheetData.cols[i.toString()].width = sheet.columns[i].width * 8;
          } else {
            // 默认列宽
            sheetData.cols[i.toString()].width = 100;
          }
        }
        // 遍历行
        sheet.eachRow((row, rowIndex) => {
          sheetData.rows[(rowIndex - 1).toString()] = {cells: {}};
          //includeEmpty = false 不包含空白单元格
          row.eachCell(
            {includeEmpty: true},
            function (cell, colNumber) {
              let cellText = "";
              if (cell.value && cell.value.result) {
                // Excel 单元格有公式
                cellText = cell.value.result;
              } else if (cell.value && cell.value.richText) {
                // Excel 单元格是多行文本
                for (let text in cell.value.richText) {
                  // 多行文本做累加
                  cellText += cell.value.richText[text].text;
                }
              } else {
                // Excel 单元格无公式
                cellText = cell.value;
              }

              //解析单元格,包含样式
              //*********************单元格存在背景色******************************
              // 单元格存在背景色
              let backGroundColor = null;
              if (cell.style.fill && cell.style.fill.fgColor && cell.style.fill.fgColor.argb) {
                // 8位字符颜色先转rgb再转16进制颜色
                backGroundColor = ((val) => {
                  val = val.trim().toLowerCase(); //去掉前后空格
                  let color = {};
                  try {
                    let argb = /^#?([a-f\d]{2})([a-f\d]{2})([a-f\d]{2})([a-f\d]{2})$/i.exec(val);
                    color.r = parseInt(argb[2], 16);
                    color.g = parseInt(argb[3], 16);
                    color.b = parseInt(argb[4], 16);
                    color.a = parseInt(argb[1], 16) / 255;
                    return tinycolor(
                      `rgba(${color.r}, ${color.g}, ${color.b}, ${color.a})`
                    ).toHexString();
                  } catch (e) {
                    console.log(e);
                  }
                })(cell.style.fill.fgColor.argb);
              }
              if (backGroundColor) {
                cell.style.bgcolor = backGroundColor;
              }
              //*************************************************************************** */

              //*********************字体存在背景色******************************
              // 字体颜色
              let fontColor = null;
              if (cell.style.font && cell.style.font.color && cell.style.font.color.argb) {
                // 8位字符颜色先转rgb再转16进制颜色
                fontColor = ((val) => {
                  val = val.trim().toLowerCase(); //去掉前后空格
                  let color = {};
                  try {
                    let argb = /^#?([a-f\d]{2})([a-f\d]{2})([a-f\d]{2})([a-f\d]{2})$/i.exec(val);
                    color.r = parseInt(argb[2], 16);
                    color.g = parseInt(argb[3], 16);
                    color.b = parseInt(argb[4], 16);
                    color.a = parseInt(argb[1], 16) / 255;
                    return tinycolor(
                      `rgba(${color.r}, ${color.g}, ${color.b}, ${color.a})`
                    ).toHexString();
                  } catch (e) {
                    console.log(e);
                  }
                })(cell.style.font.color.argb);
              }
              if (fontColor) {
                cell.style.color = fontColor;
              }
              //************************************************************************ */

              // exceljs 对齐的格式转成 x-date-spreedsheet 能识别的对齐格式
              if (cell.style.alignment && cell.style.alignment.horizontal) {
                cell.style.align = cell.style.alignment.horizontal
              }
              if (cell.style.alignment && cell.style.alignment.vertical) {
                cell.style.valign = cell.style.alignment.vertical
              }

              //处理合并单元格
              let mergeAddress = _.find(mergeAddressData, function (o) {
                return o.startAddress == cell._address;
              });
              if (mergeAddress) {
                // 遍历的单元格属于合并单元格
                if (cell.master.address != mergeAddress.startAddress) {
                  // 不是合并单元格中的第一个单元格不需要计入数据源
                  return;
                }
                // 说明是合并单元格区域的起始单元格
                sheetData.rows[(rowIndex - 1).toString()].cells[(colNumber - 1).toString()] = {
                  text: cellText === 0 ? '0' : cellText,
                  style: 0,
                  merge: [mergeAddress.YRange, mergeAddress.XRange],
                };

                //解析单元格,包含样式
                let xsCellStyle = _.cloneDeep(cell.style);
                xsCellStyle.border = {};
                // 边框线
                if (
                  cell.style.border &&
                  JSON.stringify(cell.style.border) != "{}"
                ) {
                  let coloneStyle = cell.style.border;
                  xsCellStyle.border = {};
                  if (coloneStyle.bottom) {
                    xsCellStyle.border.bottom = [];
                    xsCellStyle.border.bottom[0] =
                      coloneStyle.bottom.style;
                    if (_.isString(coloneStyle.bottom.color)) {
                      xsCellStyle.border.bottom[1] =
                        coloneStyle.bottom.color;
                    } else {
                      xsCellStyle.border.bottom[1] = "#000000";
                    }
                  }
                  if (coloneStyle.right) {
                    xsCellStyle.border.right = [];
                    xsCellStyle.border.right[0] = coloneStyle.right.style;
                    if (_.isString(coloneStyle.right.color)) {
                      xsCellStyle.border.right[1] =
                        coloneStyle.right.color;
                    } else {
                      xsCellStyle.border.right[1] = "#000000";
                    }
                  }
                  if (coloneStyle.left) {
                    xsCellStyle.border.left = [];
                    xsCellStyle.border.left[0] = coloneStyle.left.style;
                    if (_.isString(coloneStyle.left.color)) {
                      xsCellStyle.border.left[1] = coloneStyle.left.color;
                    } else {
                      xsCellStyle.border.left[1] = "#000000";
                    }
                  }
                  if (coloneStyle.top) {
                    xsCellStyle.border.top = [];
                    xsCellStyle.border.top[0] = coloneStyle.top.style;
                    if (_.isString(coloneStyle.top.color)) {
                      xsCellStyle.border.top[1] = coloneStyle.top.color;
                    } else {
                      xsCellStyle.border.top[1] = "#000000";
                    }
                  }
                }

                sheetData.styles.push(xsCellStyle);
                //对应的style存放序号
                sheetData.rows[(rowIndex - 1).toString()].cells[
                  (colNumber - 1).toString()
                  ].style = sheetData.styles.length - 1;
              } else {
                // 非合并单元格
                sheetData.rows[(rowIndex - 1).toString()].cells[(colNumber - 1).toString()] = {text:  cellText === 0 ? '0' : cellText, style: 0};
                //解析单元格,包含样式
                let xsCellStyle = _.cloneDeep(cell.style);
                xsCellStyle.border = {};

                // 边框线
                if (cell.style.border && JSON.stringify(cell.style.border) != "{}") {
                  let coloneStyle = cell.style.border;
                  xsCellStyle.border = {};
                  if (coloneStyle.bottom) {
                    xsCellStyle.border.bottom = [];
                    xsCellStyle.border.bottom[0] = coloneStyle.bottom.style;
                    if (_.isString(coloneStyle.bottom.color)) {
                      xsCellStyle.border.bottom[1] = coloneStyle.bottom.color;
                    } else {
                      xsCellStyle.border.bottom[1] = "#000000";
                    }
                  }
                  if (coloneStyle.right) {
                    xsCellStyle.border.right = [];
                    xsCellStyle.border.right[0] = coloneStyle.right.style;
                    if (_.isString(coloneStyle.right.color)) {
                      xsCellStyle.border.right[1] = coloneStyle.right.color;
                    } else {
                      xsCellStyle.border.right[1] = "#000000";
                    }
                  }
                  if (coloneStyle.left) {
                    xsCellStyle.border.left = [];
                    xsCellStyle.border.left[0] = coloneStyle.left.style;
                    if (_.isString(coloneStyle.left.color)) {
                      xsCellStyle.border.left[1] = coloneStyle.left.color;
                    } else {
                      xsCellStyle.border.left[1] = "#000000";
                    }
                  }
                  if (coloneStyle.top) {
                    xsCellStyle.border.top = [];
                    xsCellStyle.border.top[0] = coloneStyle.top.style;
                    if (_.isString(coloneStyle.top.color)) {
                      xsCellStyle.border.top[1] = coloneStyle.top.color;
                    } else {
                      xsCellStyle.border.top[1] = "#000000";
                    }
                  }
                }

                sheetData.styles.push(xsCellStyle);
                //对应的style存放序号
                sheetData.rows[(rowIndex - 1).toString()].cells[(colNumber - 1).toString()].style = sheetData.styles.length - 1;
              }
            }
          );
        });
        workbookData.push(sheetData);
      });
      return workbookData
    },
    // 导入excel文件
    loadExcelFile(file) {
      if (file) {
        const wb = new Excel.Workbook();
        const reader = new FileReader();
        reader.readAsArrayBuffer(file);
        reader.onload = () => {
          const buffer = reader.result;
          wb.xlsx.load(buffer).then((workbook) => {
            this.xs.loadData(this.dataEjstoXs(workbook));
          });
        };
      }
    },
    // 导入excel文件流
    loadExcelFileStream(buffer) {
      if (buffer) {
        const wb = new Excel.Workbook();
        wb.xlsx.load(buffer).then((workbook) => {
          this.xs.loadData(this.dataEjstoXs(workbook));
        });
      }
    },
    // 将x-data-spreadsheet结构提组装成exceljsWorkbook结构体
    dataXstoEjs(){
      const exceljsWorkbook = new Excel.Workbook();
      exceljsWorkbook.modified = new Date();
      this.xs.getData().forEach(function (xws) {
        let rowobj = xws.rows;
        // 构造exceljs文档结构
        const exceljsSheet = exceljsWorkbook.addWorksheet(xws.name);
        // 读取列宽
        let sheetColumns = [];
        let colIndex = 0;
        for (let col in xws.cols) {
          if (xws.cols[col].width) {
            sheetColumns.push({
              header: colIndex + "",
              key: colIndex + "",
              width: xws.cols[col].width / 8,
            });
          }
          colIndex++;
        }
        exceljsSheet.columns = sheetColumns;
        for (let ri = 0; ri < rowobj.len; ++ri) {
          let row = rowobj[ri];
          if (!row) continue;
          // 构造exceljs的行(如果尚不存在，则将返回一个新的空对象)
          const exceljsRow = exceljsSheet.getRow(ri + 1);
          Object.keys(row.cells).forEach(function (k) {
            let idx = +k;
            if (isNaN(idx)) return;
            const exceljsCell = exceljsRow.getCell(Number(k) + 1);
            exceljsCell.value = row.cells[k].text;
            // 对齐
            if (row.cells[k].style && xws.styles[row.cells[k].style]) {
              // 垂直对齐方式
              if (xws.styles[row.cells[k].style].valign) {
                if (exceljsCell.alignment == undefined || exceljsCell.alignment == null) {
                  exceljsCell.alignment = {};
                }
                exceljsCell.alignment.vertical = xws.styles[row.cells[k].style].valign;
              }
              // 水平对齐方式
              if (xws.styles[row.cells[k].style].align) {
                if (exceljsCell.alignment == undefined || exceljsCell.alignment == null) {
                  exceljsCell.alignment = {};
                }
                exceljsCell.alignment.horizontal = xws.styles[row.cells[k].style].align;
              }
            }
            // 边框
            if (row.cells[k].style && JSON.stringify(xws.styles[row.cells[k].style]) !== "{}" && xws.styles[row.cells[k].style].border && JSON.stringify(xws.styles[row.cells[k].style].border) !== "{}") {
              exceljsCell.border = {};
              // bottom
              if (xws.styles[row.cells[k].style].border.bottom && Array.isArray(xws.styles[row.cells[k].style].border.bottom) && xws.styles[row.cells[k].style].border.bottom.length === 2) {
                exceljsCell.border.bottom = {};
                exceljsCell.border.bottom.style = xws.styles[row.cells[k].style].border.bottom[0];
                exceljsCell.border.bottom.color = {};
                exceljsCell.border.bottom.color = xws.styles[row.cells[k].style].border.bottom[1];
              }
              // left
              if (xws.styles[row.cells[k].style].border.left && Array.isArray(xws.styles[row.cells[k].style].border.left) && xws.styles[row.cells[k].style].border.left.length === 2) {
                exceljsCell.border.left = {};
                exceljsCell.border.left.style = xws.styles[row.cells[k].style].border.left[0];
                exceljsCell.border.left.color = {};
                exceljsCell.border.left.color = xws.styles[row.cells[k].style].border.left[1];
              }
              // right
              if (xws.styles[row.cells[k].style].border.right && Array.isArray(xws.styles[row.cells[k].style].border.right) && xws.styles[row.cells[k].style].border.right.length === 2) {
                exceljsCell.border.right = {};
                exceljsCell.border.right.style = xws.styles[row.cells[k].style].border.right[0];
                exceljsCell.border.right.color = {};
                exceljsCell.border.right.color = xws.styles[row.cells[k].style].border.right[1];
              }
              // top
              if (xws.styles[row.cells[k].style].border.top && Array.isArray(xws.styles[row.cells[k].style].border.top) && xws.styles[row.cells[k].style].border.top.length === 2) {
                exceljsCell.border.top = {};
                exceljsCell.border.top.style = xws.styles[row.cells[k].style].border.top[0];
                exceljsCell.border.top.color = {};
                exceljsCell.border.top.color = xws.styles[row.cells[k].style].border.top[1];
              }
            }
            // 背景色
            if (row.cells[k].style && xws.styles[row.cells[k].style].bgcolor) {
              let rgb = tinycolor(xws.styles[row.cells[k].style].bgcolor).toRgb();
              let rHex = parseInt(rgb.r).toString(16).padStart(2, "0");
              let gHex = parseInt(rgb.g).toString(16).padStart(2, "0");
              let bHex = parseInt(rgb.b).toString(16).padStart(2, "0");
              let aHex = parseInt(rgb.a).toString(16).padStart(2, "0");
              let _bgColor = aHex + rHex + gHex + bHex;
              // 设置exceljs背景色
              exceljsCell.fill = {
                type: "pattern",
                pattern: "solid",
                fgColor: {argb: _bgColor},
              };
            }
            // 字体
            if (row.cells[k].style) {
              exceljsCell.font = xws.styles[row.cells[k].style].font || {};
            }
            // 字体颜色
            if (row.cells[k].style && xws.styles[row.cells[k].style].color) {
              let rgb = tinycolor(xws.styles[row.cells[k].style].color).toRgb();
              let rHex = parseInt(rgb.r).toString(16).padStart(2, "0");
              let gHex = parseInt(rgb.g).toString(16).padStart(2, "0");
              let bHex = parseInt(rgb.b).toString(16).padStart(2, "0");
              let aHex = parseInt(rgb.a).toString(16).padStart(2, "0");
              let _fontColor = aHex + rHex + gHex + bHex;
              exceljsCell.font.color = {argb: _fontColor};
            }
            // 合并单元格
            if (row.cells[k].merge) {
              // 开始行
              let startRow = ri + 1;
              // 结束行,加上Y轴跨度
              let endRow = startRow + row.cells[k].merge[0];
              // 开始列
              let startColumn = Number(k) + 1;
              // 结束列,加上X轴跨度
              let endColumn = startColumn + row.cells[k].merge[1];
              // 按开始行，开始列，结束行，结束列合并
              exceljsSheet.mergeCells(startRow, startColumn, endRow, endColumn);
            }
          });
        }
      });
      return exceljsWorkbook;
    },
    // 导出excel文件,
    exportExcel(fileName) {
      const exceljsWorkbook = this.dataXstoEjs()
      // writeBuffer 把写好的excel 转换成 ArrayBuffer 类型
      exceljsWorkbook.xlsx.writeBuffer().then((data) => {
        const link = document.createElement("a");
        // Blob 实现下载excel
        const blob = new Blob([data], {
          type: "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=UTF-8",
        });
        link.href = window.URL.createObjectURL(blob);
        link.download = `${fileName}.xlsx`;
        link.click();
      });
    },
    // 导出json
    exportJson() {
      let sheetsData = this.xs.getData();
      let rows = Object.entries(sheetsData[0].rows);
      let jsonData = [];
      if (Array.isArray(this.ExportJsonProperties) && this.ExportJsonProperties.length > 0) {
        // 遍历数据，跳过第一行表头
        for (let i = 1; i < rows.length; i++) {
          if (rows[i] && rows[i][1] && rows[i][1].cells) {
            let row = Object.entries(rows[i][1].cells);
            // 构造行对象
            let JsonRow = {};
            for (let k = 0; k < row.length; k++) {
              let cells = row[k];
              JsonRow[this.ExportJsonProperties[k]] = cells[1].text;
            }
            jsonData.push(JsonRow);
          }
        }
      }
      return jsonData;
    },
  },
  destroyed() {
  },
};
</script>

