您的位置:首页 > 财经 > 金融 > 使用go语言来完成复杂excel表的导出导入

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

2024/12/23 9:06:46 来源:https://blog.csdn.net/qq_45490502/article/details/139981144  浏览:    关键词:使用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 和 beamNumbervar currentSpanNumber stringvar currentBeamNumber string// 跳过前两行for i, row := range rows[2:] {// 跳过空行if len(row) == 0 {continue}// 检查行的长度是否足够goif 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 变化,重置 subSpanNumberSetif spanNumber != currentSpanNumber {subSpanNumberSet = make(map[string]int)currentSpanNumber = spanNumber}// 如果 beamNumber 变化,重置 beamNumberSetif 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 := sheetNamestatus := "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数据导入成功")
}

处理完成之后的效果就是(部分效果图)
在这里插入图片描述
**

版权声明:

本网仅为发布的内容提供存储空间,不对发表、转载的内容提供任何形式的保证。凡本网注明“来源:XXX网络”的作品,均转载自其它媒体,著作权归作者所有,商业转载请联系作者获得授权,非商业转载请注明出处。

我们尊重并感谢每一位作者,均已注明文章来源和作者。如因作品内容、版权或其它问题,请及时与我们联系,联系邮箱:809451989@qq.com,投稿邮箱:809451989@qq.com