Glang&Mysql&Excel

package main

import (
	"database/sql"
	"fmt"
	"log"

	_ "github.com/go-sql-driver/mysql"
	"github.com/tealeg/xlsx"
)

func main() {
	// 连接MySQL数据库
	db, err := sql.Open("mysql", "root:123456@tcp(localhost:3306)/cysql")
	if err != nil {
		log.Fatalf("Error connecting to MySQL: %v", err)
	}
	defer func(db *sql.DB) {
		err := db.Close()
		if err != nil {
			// 在关闭数据库连接时处理错误
		}
	}(db)

	// 创建表格
	createTable(db)

	// 打开Excel文件
	xlFile, err := xlsx.OpenFile("高二10班下期末考试成绩.xlsx")
	if err != nil {
		log.Fatalf("Error opening Excel file: %v", err)
	}

	// 遍历每个工作表
	for _, sheet := range xlFile.Sheets {
		// 遍历每一行
		for _, row := range sheet.Rows {
			// 从Excel中读取数据
			var name, class string
			var totalScore, schoolRank, nationalExamRank string
			var chinese, math, english, physics, chemistry, biology string
			if len(row.Cells) >= 11 {
				name = row.Cells[0].String()
				class = row.Cells[1].String()
				totalScore = row.Cells[2].String()
				schoolRank = row.Cells[3].String()
				nationalExamRank = row.Cells[4].String()
				chinese = row.Cells[5].String()
				math = row.Cells[6].String()
				english = row.Cells[7].String()
				physics = row.Cells[8].String()
				chemistry = row.Cells[9].String()
				biology = row.Cells[10].String()
			}

			// 插入数据到MySQL数据库
			_, err := db.Exec("INSERT INTO student_scores (name, class, total_score, school_rank, national_exam_rank, chinese_score, math_score, english_score, physics_score, chemistry_score, biology_score) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", name, class, totalScore, schoolRank, nationalExamRank, chinese, math, english, physics, chemistry, biology)
			if err != nil {
				log.Fatalf("Error inserting data into MySQL: %v", err)
			}
		}
	}

	fmt.Println("Data imported successfully")
}

// 创建表格
func createTable(db *sql.DB) {
	// 准备创建表格的SQL语句
	createTableSQL := `
		CREATE TABLE IF NOT EXISTS student_scores (
			name VARCHAR(20),
			class VARCHAR(10),
			total_score VARCHAR(20),
			school_rank VARCHAR(20),
			national_exam_rank VARCHAR(20),
			chinese_score VARCHAR(20),
			math_score VARCHAR(20),
			english_score VARCHAR(20),
			physics_score VARCHAR(20),
			chemistry_score VARCHAR(20),
			biology_score VARCHAR(20)
		)
	`

	// 执行创建表格的SQL语句
	_, err := db.Exec(createTableSQL)
	if err != nil {
		log.Fatalf("Error creating table: %v", err)
	}

	fmt.Println("Table created successfully")
}