beego导入导出

/*前端就不贴了，在router写了请求路径的情况下，前端一个点击事件进入方法*/

//导出

func (c *ConfigController) Export() {

	var file *xlsx.File
	var sheet *xlsx.Sheet
	var row *xlsx.Row
	var cell *xlsx.Cell

	file = xlsx.NewFile()
	sheet, _ = file.AddSheet("sheet")
	//设置表格头
	headers := []string{"设备SN", "SIP账号1", "SIP1用户名", "SIP1密码", "SIPURI1", "SIP1是否可用(true:可用,false:不可用)", "SIP账号2", "SIP2用户名", "SIP2密码", "SIPURI2", "SIP2是否可用(true:可用,false:不可用)", "语言", "NTP服务器1", "NTP服务器2"}
	row = sheet.AddRow()
	for _, header := range headers {
		cell = row.AddCell()
		cell.Value = header
	}
	//读取config数据库信息
	configData := make([]*models.Config, 0)
	orm.NewOrm().QueryTable(models.ConfigTBName()).All(&configData)
	//fmt.Println("configData:",configData[0])
	//写入数据
	//datas := [][]string {{"data1","data2","data3"},{"data1","data2","data3"},}
	for _, data := range configData { //row
		row = sheet.AddRow()
		//col
		cell = row.AddCell()
		cell.Value = data.SN
		cell = row.AddCell()
		cell.Value = data.SipAccount1
		cell = row.AddCell()
		cell.Value = data.SipAuthUserName1
		cell = row.AddCell()
		cell.Value = data.SipAuthPassword1
		cell = row.AddCell()
		cell.Value = data.SipURI1
		cell = row.AddCell()
		cell.Value = data.SipEnable1
		cell = row.AddCell()
		cell.Value = data.SipAccount2
		cell = row.AddCell()
		cell.Value = data.SipAuthUserName2
		cell = row.AddCell()
		cell.Value = data.SipAuthPassword2
		cell = row.AddCell()
		cell.Value = data.SipURI2
		cell = row.AddCell()
		cell.Value = data.SipEnable2
		cell = row.AddCell()
		cell.Value = data.Language
		cell = row.AddCell()
		cell.Value = data.NTPServer1
		cell = row.AddCell()
		cell.Value = data.NTPServer2
	}
	//保存路径
	if !FileExists("static/tmp") {
		os.MkdirAll("static/tmp", os.ModePerm)
	}
	filename := "static/tmp/" + cast.ToString(time.Now().Unix()) + ".xlsx"
	err := file.Save(filename)
	defer os.Remove(filename)
	if err != nil {
		fmt.Println(err)
		c.jsonResult(enums.JRCodeFailed, "文件打开失败!", 0)
	}
	//读取execl文件to浏览器下载
	now := time.Now()
	nowStr := fmt.Sprintf("%d%d%d%d%d%d", now.Year(), now.Month(), now.Day(), now.Hour(), now.Minute(), now.Second())
	//fmt.Println("nowStr:",nowStr)
	servername := "telpoOMC_config_" + nowStr + ".xlsx"
	c.Ctx.Output.Download(filename, servername)

}

//导入

/*第一个点击事件，返回选择文件按钮视图，第二个点击保存事件，router内正则匹配post进入ImportSave()方法，总之可以取到前端的文件url给到后台ImportSave()就行*/

func (c *ConfigController) Import() {
	if c.Ctx.Request.Method == "POST" {
		c.ImportSave()
	}
	c.setTpl("config/import.html", "shared/layout_pullbox.html")
	c.LayoutSections = make(map[string]string)
	c.LayoutSections["footerjs"] = "config/import_footerjs.html"
}

func (c *ConfigController) ImportSave() {
	//获取上传url
	str := c.GetString("Url")
	fmt.Println("Url:", str)
	//read excel
	excelFileName := "static/excel/" + str
	//判断文件后缀 .xlsx
	fmt.Println("文件后缀:",path.Ext(excelFileName))
	if path.Ext(excelFileName) != ".xlsx" {
		c.jsonResult(enums.JRCodeFailed, "文件类型错误,请选择 *.xlsx 文件 !", 0)
	}
	xlFile, err := xlsx.OpenFile(excelFileName)
	//defer os.Remove(excelFileName)
	if err != nil {
		fmt.Printf("open failed: %s\n", err)
		c.jsonResult(enums.JRCodeFailed, "文件打开失败!", 0)
	}

	qs := orm.NewOrm().QueryTable(models.ConfigTBName())
	i, _ := qs.PrepareInsert()
	defer i.Close(); os.Remove(excelFileName)
	succNum := 0
	failNum := 0
	fail := ""
	for _, sheet := range xlFile.Sheets {
		fmt.Printf("Sheet Name: %s\n", sheet.Name)
		if sheet.Name == "sheet" {
			fmt.Printf("Sheet len: %v\n", len(sheet.Rows))
			for j := 1; j < len(sheet.Rows); j++ { //忽略标题行
				fmt.Println("row Cells :", sheet.Rows[j].Cells)
				//fmt.Println("row Cells 0 :", sheet.Rows[j].Cells[0])
				data := models.Config{}
				data.SN = fmt.Sprintf("%s", sheet.Rows[j].Cells[0])
				data.SipAccount1 = fmt.Sprintf("%s", sheet.Rows[j].Cells[1])
				data.SipAuthUserName1 = fmt.Sprintf("%s", sheet.Rows[j].Cells[2])
				data.SipAuthPassword1 = fmt.Sprintf("%s", sheet.Rows[j].Cells[3])
				data.SipURI1 = fmt.Sprintf("%s", sheet.Rows[j].Cells[4])
				if fmt.Sprintf("%s", sheet.Rows[j].Cells[5]) != "" {
					data.SipEnable1 = fmt.Sprintf("%s", sheet.Rows[j].Cells[5])
				}else {
					data.SipEnable1 = "true"
				}
				data.SipAccount2 = fmt.Sprintf("%s", sheet.Rows[j].Cells[6])
				data.SipAuthUserName2 = fmt.Sprintf("%s", sheet.Rows[j].Cells[7])
				data.SipAuthPassword2 = fmt.Sprintf("%s", sheet.Rows[j].Cells[8])
				data.SipURI2 = fmt.Sprintf("%s", sheet.Rows[j].Cells[9])
				if fmt.Sprintf("%s", sheet.Rows[j].Cells[10]) != "" {
					data.SipEnable2 = fmt.Sprintf("%s", sheet.Rows[j].Cells[10])
				}else {
					data.SipEnable2 = "true"
				}
				if fmt.Sprintf("%s", sheet.Rows[j].Cells[11]) != "" {
					data.Language = fmt.Sprintf("%s", sheet.Rows[j].Cells[11])
				}else {
					data.Language = "English"
				}
				data.NTPServer1 = fmt.Sprintf("%s", sheet.Rows[j].Cells[12])
				data.NTPServer2 = fmt.Sprintf("%s", sheet.Rows[j].Cells[13])
				data.Creator = &c.curUser

				//判断sn是否为空
				if data.SN == "" {
					failNum += 1
					fail += "SN不能为空."
				}else {
					confData := models.Config{SN: data.SN}
					err := orm.NewOrm().Read(&confData, "SN")
					if err != nil {
						succNum += 1
						//fmt.Println("查询不到")
						id, _ := i.Insert(&data)
						fmt.Printf("id :%v \n", id)
					}else {
						failNum += 1
						fail += data.SN + "已存在."
						//fmt.Println("查询结果：", confData)
					}
				}

			}
		}
	}
	if succNum > 0 {
		c.jsonResult(enums.JRCodeSucc, "成功数量:"+strconv.Itoa(succNum)+",失败数量:"+strconv.Itoa(failNum)+";原因:"+fail, 0)
		//c.jsonResult(enums.JRCodeSucc, "Success number : " + strconv.Itoa(succNum) +". Failure number : " +strconv.Itoa(failNum)+"; Cause : "+fail, succNum)
	}else {
		c.jsonResult(enums.JRCodeFailed, "失败数量:"+strconv.Itoa(failNum)+";原因:"+fail, 0)
		//c.jsonResult(enums.JRCodeFailed, "Failure number : " +strconv.Itoa(failNum)+". Cause : "+fail, failNum)
	}

}