VUE和GO实现Excel表的批量导入

 

前言

其实实现excel的导入的话用element ui框架比较好,我试了下,失败了,所以这里用的事原生的h5

不多说,直接上代码

一、前端

1.在vue中使用导入，需要下载依赖包和js包,导出也是

 使用npm安装：

 npm install -S file-saver xlsx（这里安装了2个依赖）

 npm install -D script-loader

使用yarn安装：

yarn add file-saver

yarn add xlsx

yarn add script-loader --dev

2:在项目中新建一个文件夹存放Blob.js和Export2Excel.js两个js文件

 

3、在main.js中全局引入：

import Blob from './excel/Blob.js'
import Export2Excel from './excel/Export2Excel.js'
import xlsx from 'xlsx';
Vue.use(xlsx);

<!--批量导入-->
               <span class="file-box">
                     Excel上传
                 <input type="file"
                        accept=".xls,.xlsx"
                        class="upload-file"
                        @change="Excel($event)"/>
                </span>

 

在script里面导入

import XLSX from 'xlsx';

 Excel (e) {
                let that = this;
                // 错误情况判断
                const files = e.target.files;
                if (files.length <= 0) {
                    return false;
                } else if (!/\.(xls|xlsx)$/.test(files[0].name.toLowerCase())) {
                    this.$message({
                        message: '上传格式不正确，请上传xls或者xlsx格式',
                        type: 'warning'
                    });
                    return false;
                } else {
                    that.upload_file = files[0].name;
                }
                // 读取表格
                const fileReader = new FileReader();
                fileReader.onload = ev => {
                    try {
                        const data = ev.target.result;
                        const workbook = XLSX.read(data, {
                            type: 'binary'
                        });
                        // 读取第一张表
                        const wsname = workbook.SheetNames[0];
                        const ws = XLSX.utils.sheet_to_json(workbook.Sheets[wsname]);
                        // 打印 ws 就可以看到读取出的表格数据
                        console.log(ws);
                        // 定义一个新数组，存放处理后的表格数据
                        that.lists = [];
                        ws.forEach(item => {
                            that.lists.push({
                                // 对ws进行处理后放进lists内
                                name: item.name,
                                loginName: item.loginName,
                                location: item.location,
                                contact: item.contact,
                                tel: item.tel,
                                type: item.type,
                                createTime: item.createTime
                            });
                        });
                        // 调用方法将lists数组发送给后端
                        this.submit_form(that.lists);
                    } catch (e) {
                        return false;
                    }
                };
                fileReader.readAsBinaryString(files[0]);
            },
            submit_form (data) {
                // 在这里发送数据
                this.$axios.post(urls.shop.importE(), {
                    //这里封装的字段一定要和后端对应上
                    addImportRecordParams: data
                }).then(response => {
                    if (response.code == 0) {
                        this.getShop();   // 导入表格之后可以获取导入的数据渲染到页面，此处的方法是获取导入的数据
                    } else {
                        this.$message.error(response.message);
                    }
                });
            },

二、后端

controller层

type ShopController struct {
	beego.Controller
}

func (shopCtrl *ShopController) ImportE() {
	resJson := NewResponseJson(nil)
	defer func() {
		shopCtrl.Data["json"] = string(common.Marshal(resJson))
		shopCtrl.ServeJSON()
	}()
	//	获取参数
	var importEParam AddImportRecordParam
	res := shopCtrl.Ctx.Input.RequestBody
	fmt.Println(res)
	err := json.Unmarshal(res, &importEParam)
	fmt.Println(importEParam)

	//	执行写入方法
	importERecordParam, err := models.AddImportE(importEParam.AddImportRecordParam)
	if err != nil {
		resJson.Success = false
		resJson.Msg = fmt.Sprintf("导入失败C : %s", err.Error())
		return
	}
	resJson.Success = true
	resJson.Msg = "导入成功"
	resJson.Data = importERecordParam
}

//导入Excel传参
type AddImportRecordParam struct {
	AddImportRecordParam []db.ShopList `json:"addImportRecordParams"`
}

Model层

func AddImportE(ShopList []db.ShopList) ([]db.ShopList, error) {
	_, err := db.AddImportE(ShopList)
	if err != nil {
		return ShopList, errors.New("导入失败")
	}
	return ShopList, nil
}

API层:因为我这边是两个表,需要判断插入到哪个表,才加了if else语句

func AddImportE(shopList []ShopList) (result bool, msg error) {
	dbHandler := db_handler.NewDbHandler()
	tx, _ := dbHandler.Db.Begin()
	//	错误处理
	defer common.RecoverHandler(func(rcErr error) {
		_ = tx.Rollback()
		msg = errors.New(fmt.Sprintf("%s%s", "导入Excel数据发生错误！A::", rcErr.Error()))
	})
	shopListArray := new(ShopList)

	if shopListArray.Type == "0" {
		for _, v := range shopList {
			var detailParam []interface{}
			shopKey, keyErr := dbHandler.SelectKey(db_handler.SEQ_SHOP)
			common.ErrorHandler(keyErr, "获取商户key发生错误！")
			detailParam = append(detailParam, v.Name)
			detailParam = append(detailParam, v.LoginName)
			detailParam = append(detailParam, v.Location)
			detailParam = append(detailParam, v.Contact)
			detailParam = append(detailParam, v.Tel)
			detailParam = append(detailParam, v.Type)
			detailParam = append(detailParam, v.CreateTime)
			detailParam = append(detailParam, shopKey)
			shopInsertResult, err1 := tx.Exec(db_handler.INSERT_BUSINESS_IMPORTE_RECORD_SHOP_LIST, detailParam...)
			common.ErrorHandler(err1, "导入Excel数据发生错误!A1")
			logs.Info(shopInsertResult)
		}
		comErr := tx.Commit()
		common.ErrorHandler(comErr)
	} else {
		for _, v := range shopList {
			var detailParam []interface{}
			shopKey, keyErr := dbHandler.SelectKey(db_handler.SEQ_SHOP)
			common.ErrorHandler(keyErr, "获取商户key发生错误！")
			detailParam = append(detailParam, v.Name)
			detailParam = append(detailParam, v.LoginName)
			detailParam = append(detailParam, v.Location)
			detailParam = append(detailParam, v.Contact)
			detailParam = append(detailParam, v.Tel)
			detailParam = append(detailParam, v.Type)
			detailParam = append(detailParam, v.CreateTime)
			detailParam = append(detailParam, shopKey)
			shopInsertResult, err1 := tx.Exec(db_handler.INSERT_BUSINESS_IMPORTE_RECORD_COMPANY_LIST, detailParam...)
			common.ErrorHandler(err1, "导入Excel数据发生错误!A2")
			logs.Info(shopInsertResult)
		}
		comErr := tx.Commit()
		common.ErrorHandler(comErr)
	}
	return true, nil

}