vxe-table + golang后端导出excel

 vxe-table + golang后端导出excel

import XLSX from "xlsx";


    //表格导出 浏览器
    exportEvent({options}) {
      let workBook = this.exportData()
      console.log("---workBook",workBook)
      XLSX.writeFile(workBook, options.filename + '.xlsx')

    },

    //表格导出 基础
    exportData() {
      let header = this.$refs.xGrid.$el.querySelector('.body--wrapper>.vxe-table--header>thead').cloneNode(true)
      let colgroup = this.$refs.xGrid.$el.querySelector('.body--wrapper>.vxe-table--body>colgroup').cloneNode(true)
      let tbody = this.$refs.xGrid.$el.querySelector('.body--wrapper>.vxe-table--body>tbody').cloneNode(true)
      let tfoot = this.$refs.xGrid.$el.querySelector('.body--wrapper>.vxe-table--footer>tfoot').cloneNode(true)

      var main = document.body;
      var table = document.createElement("table");

      let table_id = "download_xlsx_" + random_string(5);

      table.id = table_id
      table.style.display = "none"
      main.appendChild(table)
      var download_xlsx_dom = document.getElementById(table_id)

      download_xlsx_dom.appendChild(colgroup);
      download_xlsx_dom.appendChild(header);
      download_xlsx_dom.appendChild(tbody);
      download_xlsx_dom.appendChild(tfoot);
      const workBook = XLSX.utils.table_to_book(
          download_xlsx_dom
      )
      return workBook

    },

    //表格导出 服务端
    exportsServe() {
      let workBook = this.exportData()
      let Sheet1 = workBook.Sheets.Sheet1

      //组装数据
      let excel_data = []
      let merges = []
      for (const key in Sheet1) {
        // console.log(key)
        //合并单元格数据
        if(key === "!merges"){
          merges = Sheet1["!merges"]
        }
        if(key.indexOf("!") !== -1){
          continue;
        }
        let tem = {}
        tem[key] = Sheet1[key]
        excel_data.push(tem)
        // excel_data[key] = Sheet1[key]
      }

      let post_data = []
      post_data["id"] = this.value
      post_data["merges"] = merges
      post_data["excel_data"] = excel_data
      console.log("---workBook post_data",post_data)

      report_view_save_excel_json(post_data).then(function (res) {
        console.log("---report_view_save_excel_json", res)
      })

    },

后端

// ExportExcel 导出目录下所有组件
func ExportExcel(c *gin.Context) {
	var u reportViewDelStruct
	if err := c.ShouldBind(&u); err != nil {
		common.Error(c, "", "参数获取失败")
		return
	}
	//检查数据是否存在
	regionWhere := make(map[string]string)
	regionWhere["field"] = "id"
	regionWhere["value"] = strconv.Itoa(u.ID)

	reportViewInfo, err := model.ReportViewDetail(regionWhere)
	if err != nil {
		common.Error(c, "", fmt.Sprintf("%s", err))
		return
	}

	if reportViewInfo.Id == 0 {
		common.Error(c, "", "数据不存在")
		return
	}

	//查询目录下的组件列表

	list, err := model.GetListByParentId(u.ID)
	if err != nil {
		common.Error(c, "", "数据不存在")
		return
	}

	//创建excel
	f := excelize.NewFile()

	for key, view := range list {
		buildTableExcelFile(f,key+1,view)
	}

	//删除默认工作表
	f.DeleteSheet("Sheet1")

	c.Header("Content-Type", "application/vnd.ms-excel")
	//xlsx
	c.Header("Content-Type", "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")

	var buffer bytes.Buffer
	if err := f.Write(&buffer); err != nil {
		common.Error(c, "", fmt.Sprintf("%s", err))
		return
	}
	r := bytes.NewReader(buffer.Bytes())
	http.ServeContent(c.Writer, c.Request, "Workbook.xlsx", time.Now(), r)

}

// 表格类型excel
func buildTableExcelFile(f *excelize.File,key int,table model.ReportView)  {
	//sheet := "Sheet"+strconv.Itoa(key)
	sheet := table.Name

	// 创建一个工作表
	index := f.NewSheet(sheet)

	//修改默认工作表
	if key == 1{
		f.SetActiveSheet(index)
	}

	if table.Type == "widgetTable"{
		excelJson := table.ExcelJson

		excelData := make(map[string]interface{})
		err := json.Unmarshal([]byte(excelJson), &excelData)
		if err != nil {
			fmt.Println(err)
			return
		}

		CellValueList := make(map[string]interface{})
		for _, val := range excelData["excel_data"].([]interface {}) {
			for callIndex, callData := range val.(map[string]interface{}) {
				CellValueList[callIndex] = callData.(map[string]interface{})["v"]
			}
		}

		for index, val := range CellValueList {
			err := f.SetCellValue(sheet, index, val)
			if err != nil {
				fmt.Println(err)
				return 
			}
		}

		//获取合并数据
		var mergesList  []map[string]string
		for _, val := range excelData["merges"].([]interface {}) {
			// val  map[e:map[c:0 r:1] s:map[c:0 r:0]]
			startLetterIndex := val.(map[string]interface{})["s"].(map[string]interface{})["c"]
			startLetter := getLetterByIndex(gconv.Int(startLetterIndex))//A、B、C

			startNumber := val.(map[string]interface{})["s"].(map[string]interface{})["r"]
			startNumber = gconv.Int(startNumber) + 1
			startNumberStr := gconv.String(startNumber)
			start := startLetter + startNumberStr

			endLetterIndex := val.(map[string]interface{})["e"].(map[string]interface{})["c"]
			endLetter := getLetterByIndex(gconv.Int(endLetterIndex))//A、B、C

			endNumber := val.(map[string]interface{})["e"].(map[string]interface{})["r"]
			endNumber = gconv.Int(endNumber) + 1
			endNumberStr := gconv.String(endNumber)
			end := endLetter + endNumberStr

			tem := make(map[string]string)
			tem["start"] = start
			tem["end"] = end

			mergesList = append(mergesList,tem)

		}

		if len(mergesList) >0 {
			for _, mergesVal := range mergesList {
				_ = f.MergeCell(sheet, mergesVal["start"], mergesVal["end"])
			}
		}

	}


}


//获取英文字母通过索引
func getLetterByIndex(index int) string {
	list := []string{"A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z"}
	return list[index]
}