使用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数据导入成功")
}
处理完成之后的效果就是（部分效果图）
**