前端下载模板、填写后上传并存储在数据库、查询导出数据库返回excel表。
前端:
<template>
<div>
<el-button class="" type="primary" @click="downloadFile">
下载模板
</el-button>
</div>
<el-upload
ref="uploadRef"
class="upload-demo"
:auto-upload="false"
:http-request="uploadHttpRequest"
:before-upload="beforeUpload"
>
<template #trigger>
<el-button type="primary">导入</el-button>
</template>
<div>
<el-button class="ml-3" type="success" @click="submitUpload">
上传
</el-button>
</div>
</el-upload>
<el-button class="downloadall" type="warning" @click="downloadAll">
导出所有数据
</el-button>
</template>
<script lang="ts" setup>
import type { UploadProps } from 'element-plus'
import type { UploadInstance } from 'element-plus'
import { ref } from 'vue'
import axios from 'axios'
const uploadRef = ref<UploadInstance>()
const allowedTypes = ['application/vnd.openxmlformats-officedocument.spreadsheetml.sheet', 'application/vnd.ms-excel'];
// 下载文件逻辑
const downloadFile = () => {
const url = './static/template.xlsx'; // 模板文件下载路径
const link = document.createElement('a'); // 临时创建超链接
link.href = url;
link.download = 'template.xlsx';
link.click();
}
// 判断文件是不是excel格式
const beforeUpload:UploadProps['beforeUpload'] = (rawFile:any) => {
const isExcel = allowedTypes.includes(rawFile.type);
const isExcelExtension = rawFile.name.endsWith('.xlsx') || rawFile.name.endsWith('.xls');
if (!isExcel || !isExcelExtension) {
ElMessage.error('上传的文件必须是 Excel 文件 (.xlsx 或 .xls)');
return false // 上传前钩子,停止后续生命周期
}
return true; // 继续完成后续生命周期
}
// 上传文件的逻辑
const uploadHttpRequest = async (fileObject:any) => {
// 转换为二进制对象格式,存储在formdata中,用来发送http请求
/*Blob 和 FormData 对象都不直接继承自 Object 对象,但它们是 JavaScript 的内置对象,用于处理不同的用途:
Blob 对象:表示原始数据的不可变的原始数据块。它用于处理二进制数据,如文件或字节流,常用于文件上传或下载操作。
FormData 对象:用于构造表示表单数据的对象,能够以键值对的形式封装数据,便于通过 XMLHttpRequest 或 fetch 发起 HTTP 请求。
虽然它们都不是直接从 Object 继承,但所有 JavaScript 对象,包括 Blob 和 FormData,最终都继承自 Object,因此具有 Object 的所有基础属性和方法。*/
const blob = new Blob([fileObject.file], { type: 'application/octet-stream' });
const formData = new FormData();
formData.append('file', blob, fileObject.file.name);
try {
// 后端接口
const response = await axios({
url: 'http://192.168.40.182:9000/api/upload',
method: 'POST',
data: formData,
})
if (response.data.msg === 'success') {
ElMessage.success('上传成功');
uploadRef.value?.clearFiles();
} else {
ElMessage.error('上传失败');
uploadRef.value?.clearFiles();
}
}catch (error) {
// 失败处理
if (axios.isAxiosError(error)) {
const errorMessage = error.response?.data?.msg || '上传失败';
ElMessage.error(`上传失败: ${errorMessage}`);
}
uploadRef.value?.clearFiles();
}
}
// 手动提交
const submitUpload = () => {
if (uploadRef.value) {
uploadRef.value!.submit(); // 触发上传
} else {
console.error('上传组件引用为空');
}
}
// 导出所有数据
const downloadAll = async () => {
try {
const response = await axios.get('http://192.168.40.182:9000/api/export', {
responseType: 'blob' // 重要:设置响应类型为 blob
});
const url = window.URL.createObjectURL(new Blob([response.data]));
const link = document.createElement('a');
link.href = url;
link.setAttribute('download', 'data.xlsx'); // 设置下载的文件名
document.body.appendChild(link);
link.click();
link.remove(); // 记得移除 DOM 元素
} catch (error) {
console.error('导出数据失败', error);
}
}
</script>
<style scoped>
* {
margin-top: 10px;
}
.downloadall {
margin-top: 100px;
}
</style>
后端:
package main
import (
"database/sql"
"fmt"
"log"
"net/http"
"strconv"
"github.com/gin-gonic/gin"
_ "github.com/go-sql-driver/mysql"
"github.com/xuri/excelize/v2"
)
var db *sql.DB
// 初始化数据库
func init() {
var err error
dsn := "dbuser1:NSD2021@tedu.cn@tcp(192.168.40.199:13306)/mall"
db, err = sql.Open("mysql", dsn)
if err != nil {
log.Fatal(err)
}
}
// 相同主机不同端口需要解决跨域问题
func Cors() gin.HandlerFunc {
return func(c *gin.Context) {
method := c.Request.Method
origin := c.Request.Header.Get("Origin")
if origin != "" {
c.Header("Access-Control-Allow-Origin", "*") // 可将将 * 替换为指定的域名
c.Header("Access-Control-Allow-Methods", "POST, GET, OPTIONS, PUT, DELETE, UPDATE")
c.Header("Access-Control-Allow-Headers", "Origin, X-Requested-With, Content-Type, Accept, Authorization")
c.Header("Access-Control-Expose-Headers", "Content-Length, Access-Control-Allow-Origin, Access-Control-Allow-Headers, Cache-Control, Content-Language, Content-Type")
c.Header("Access-Control-Allow-Credentials", "true")
}
if method == "OPTIONS" {
c.AbortWithStatus(http.StatusNoContent)
}
c.Next()
}
}
// 导入数据至数据库
func uploadHandler(c *gin.Context) {
file, _, err := c.Request.FormFile("file")
if err != nil {
c.JSON(http.StatusBadRequest, gin.H{"msg": "文件上传失败"})
return
}
f, err := excelize.OpenReader(file)
if err != nil {
c.JSON(http.StatusBadRequest, gin.H{"msg": "文件解析失败"})
return
}
sheet := f.GetSheetName(0)
rows, err := f.GetRows(sheet)
if err != nil {
c.JSON(http.StatusInternalServerError, gin.H{"msg": "读取数据失败"})
return
}
// 列名是否被修改
if rows[0][0] != "ip" || rows[0][1] != "hostname" {
c.JSON(http.StatusBadRequest, gin.H{"msg": "列名不匹配,请勿修改"})
return
}
// 判断是否有不合归的列
for index, row := range rows {
if index == 0 {
continue
}
if len(row) != 2 {
msg := fmt.Sprintf("第%d列数据不合法", index)
c.JSON(http.StatusBadRequest, gin.H{"msg": msg})
return
}
}
for index, row := range rows {
if index == 0 {
continue
}
ip := row[0]
hostname := row[1]
_, err := db.Exec("INSERT INTO test (IP, HOSTNAME) VALUES (?, ?)", ip, hostname)
if err != nil {
c.JSON(http.StatusInternalServerError, gin.H{"msg": "数据库写入失败"})
return
}
}
c.JSON(http.StatusOK, gin.H{"msg": "success"})
}
// 导出数据
func exportHandler(c *gin.Context) {
defer db.Close()
// 查询数据
rows, err := db.Query("SELECT ip,hostname FROM test")
if err != nil {
fmt.Println("err:", err)
c.JSON(http.StatusInternalServerError, gin.H{"error": err.Error()})
return
}
// 创建 Excel 文件
f := excelize.NewFile()
sheet, _ := f.NewSheet("Sheet1")
f.SetActiveSheet(sheet)
f.SetCellValue("Sheet1", "A1", "ip")
f.SetCellValue("Sheet1", "B1", "hostname")
defer f.Close()
// 将数据填入 Excel
rowNum := 2
for rows.Next() {
var ip string
var hostname string
if err := rows.Scan(&ip, &hostname); err != nil {
c.JSON(http.StatusInternalServerError, gin.H{"error": err.Error()})
return
}
f.SetCellValue("Sheet1", "A"+strconv.Itoa(rowNum), ip)
f.SetCellValue("Sheet1", "B"+strconv.Itoa(rowNum), hostname)
rowNum++
}
// 保存 Excel 文件
buf, err := f.WriteToBuffer()
if err != nil {
c.JSON(http.StatusInternalServerError, gin.H{"error": err.Error()})
return
}
c.Header("Content-Disposition", "attachment; filename=data.xlsx")
c.Header("Content-Type", "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
c.Writer.Write(buf.Bytes())
}
func main() {
r := gin.Default()
r.Use(Cors())
r.POST("/api/upload", uploadHandler)
r.GET("/api/export", exportHandler)
r.Run("192.168.40.182:9000")
}
以数据库有ip,hostname为例。