前言
其实实现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 --dev2:在项目中新建一个文件夹存放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
}