Vue3+element-plus+gin+excelize 上传下载excel模板、后端写入数据库、导出数据库信息

前端下载模板、填写后上传并存储在数据库、查询导出数据库返回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为例。