使用go语言来完成复杂excel表的导出导入

使用go语言来完成复杂excel表的导出导入（一）

1.复杂表的导入

   开发需求是需要在功能页面上开发一个excel文件的导入导出功能，
   这里的复杂指定是表内数据夹杂着一对多，多对一的形式，如下图所示。数据杂乱而且对应不统一。

首先我们先设计一个页面处理器，也就是一个前端页面用来上传要处理的excel文件，注意这里应该是可以处理多个Sheet，代码如下图。

func UploadPage(w http.ResponseWriter, r *http.Request) {
	if r.Method == http.MethodGet {
		// 渲染上传页面
		tmpl := template.Must(template.ParseFiles(filepath.Join("template", "index.html")))
		err := tmpl.Execute(w, nil)
		if err != nil {
			http.Error(w, fmt.Sprintf("Failed to render template: %v", err), http.StatusInternalServerError)
		}
	}
}

这段代码的主要作用就是用户在前端点击按钮之后，接收一个excel文件。接收文件之后，就进行处理。
首先就是先从上传的excel文件中获取所有的Sheet，然后再进行数据库的连接，
根据表的特性设计代码提取每一列的数据插入到数据库中，在这里为了确保插入时表的完整性需要使用数据库的事务，我还在这里添加了检查数据是否重复的报错功能，来确保数据行列中不会出现重复数据。代码如下，

func ImportData(w http.ResponseWriter, r *http.Request) {
	if r.Method != http.MethodPost {
		http.Error(w, "Invalid request method", http.StatusMethodNotAllowed)
		return
	}

	// 从前端上传文件
	file, _, err := r.FormFile("excelFile")
	if err != nil {
		http.Error(w, fmt.Sprintf("Failed to get form file: %v", err), http.StatusInternalServerError)
		return
	}
	defer file.Close()

	// 打开 Excel 文件
	f, err := excelize.OpenReader(file)
	if err != nil {
		http.Error(w, fmt.Sprintf("Failed to open Excel file: %v", err), http.StatusInternalServerError)
		return
	}

	// 获取所有工作表名称
	sheetNames := f.GetSheetMap()
	if len(sheetNames) == 0 {
		http.Error(w, "No sheets found in Excel file", http.StatusInternalServerError)
		return
	}

	// 连接 MySQL 数据库
	dsn := "root:root@tcp(127.0.0.1:3306)/database_4"
	db, err := sql.Open("mysql", dsn)
	if err != nil {
		http.Error(w, fmt.Sprintf("Failed to connect to database: %v", err), http.StatusInternalServerError)
		return
	}
	defer db.Close()

	// 确保连接有效
	if err := db.Ping(); err != nil {
		http.Error(w, fmt.Sprintf("Failed to ping database: %v", err), http.StatusInternalServerError)
		return
	}

	for _, sheetName := range sheetNames {
		// 开始事务
		tx, err := db.Begin()
		if err != nil {
			http.Error(w, fmt.Sprintf("Failed to begin transaction: %v", err), http.StatusInternalServerError)
			return
		}

		// 读取工作表
		rows := f.GetRows(sheetName)
		if len(rows) == 0 {
			http.Error(w, fmt.Sprintf("No rows found in sheet: %v", sheetName), http.StatusInternalServerError)
			tx.Rollback()
			continue
		}

		// 使用 map 分别检查 SubSpanNumber 和 BeamNumber 的重复
		subSpanNumberSet := make(map[string]int)
		beamNumberSet := make(map[string]int)

		// 变量来跟踪当前的 spanNumber 和 beamNumber
		var currentSpanNumber string
		var currentBeamNumber string

		// 跳过前两行
		for i, row := range rows[2:] {
			// 跳过空行
			if len(row) == 0 {
				continue
			}

			// 检查行的长度是否足够go
			if len(row) < 17 {
				log.Printf("Row %d does not have enough columns: %v", i+2, row)
				continue
			}

			// 提取每一列的数据
			id := row[0]
			bridgeName := row[1]
			centerPileNumber := row[2]
			beamType := row[3]
			subSpanNumber := row[4]
			spanNumber := row[5]
			beamNumber := row[6]
			designBeamLength, _ := strconv.ParseFloat(row[7], 64)
			designBeamHeight, _ := strconv.ParseFloat(row[8], 64)
			topPlateWidth, _ := strconv.ParseFloat(row[9], 64)
			bottomPlateWidth, _ := strconv.ParseFloat(row[10], 64)
			webThickness, _ := strconv.ParseFloat(row[11], 64)
			flangeThickness, _ := strconv.ParseFloat(row[12], 64)
			camber := row[13]
			expansionJointType := row[14]
			concreteStrength := row[15]
			concreteUsage, _ := strconv.ParseFloat(row[16], 64)

			// 如果 spanNumber 变化，重置 subSpanNumberSet
			if spanNumber != currentSpanNumber {
				subSpanNumberSet = make(map[string]int)
				currentSpanNumber = spanNumber
			}

			// 如果 beamNumber 变化，重置 beamNumberSet
			if beamNumber != currentBeamNumber {
				beamNumberSet = make(map[string]int)
				currentBeamNumber = beamNumber
			}

			// 跳过 spanNumber 为空格的检查，但仍保留其他数据插入
			if spanNumber != "" {
				// 检查 spanNumber 是否重复
				if _, found := subSpanNumberSet[spanNumber]; found {
					http.Error(w, fmt.Sprintf("在 %v 表 联跨编号列 第%d行出现数据重复: %v", sheetName, i+3, subSpanNumber), http.StatusInternalServerError)
					tx.Rollback()
					return
				}
				subSpanNumberSet[spanNumber] = i + 3 // 保存行号以便于调试
			}

			// 检查 BeamNumber 是否重复
			if _, found := beamNumberSet[beamNumber]; found {
				http.Error(w, fmt.Sprintf("在 %v 表 梁体编号列 第%d行出现数据重复: %v", sheetName, i+3, beamNumber), http.StatusInternalServerError)
				tx.Rollback()
				return
			}
			beamNumberSet[beamNumber] = i + 3 // 保存行号以便于调试

			// 插入数据到数据库
			query := `
            INSERT INTO bridge_data (
                id, bridge_name, center_pile_number, beam_type, sub_span_number, span_number, beam_number,
                design_beam_length, design_beam_height, top_plate_width, bottom_plate_width, web_thickness,
                flange_thickness, camber, expansion_joint_type, concrete_strength, concrete_usage, filename
            ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
            `
			_, err = tx.Exec(query, id, bridgeName, centerPileNumber, beamType, subSpanNumber, spanNumber, beamNumber,
				designBeamLength, designBeamHeight, topPlateWidth, bottomPlateWidth, webThickness,
				flangeThickness, camber, expansionJointType, concreteStrength, concreteUsage, sheetName)
			if err != nil {
				log.Printf("Failed to insert row %d: %v", i+3, err)
				http.Error(w, fmt.Sprintf("Failed to insert row %d: %v", i+3, err), http.StatusInternalServerError)
				tx.Rollback()
				return
			}
		}

		// 插入成功后，添加标志行
		currentTime := time.Now().Format(time.RFC3339)
		fileName := sheetName
		status := "success"

		markQuery := `
        INSERT INTO process_mark (
            timestamp, filename, status
        ) VALUES (?, ?, ?)
        `
		_, err = tx.Exec(markQuery, currentTime, fileName, status)
		if err != nil {
			log.Printf("Failed to insert process mark for sheet %s: %v", sheetName, err)
			http.Error(w, fmt.Sprintf("Failed to insert process mark for sheet %s: %v", sheetName, err), http.StatusInternalServerError)
			tx.Rollback()
			return
		}

		// 提交事务
		if err := tx.Commit(); err != nil {
			http.Error(w, fmt.Sprintf("Failed to commit transaction: %v", err), http.StatusInternalServerError)
			return
		}
	}

	fmt.Fprintln(w, "excel数据导入成功")
}

处理完成之后的效果就是（部分效果图）

**