項(xiàng)目架構(gòu)：Echo+Gorm+excelize
依賴開源項(xiàng)目：github.com/360EntSecGroup-Skylar/excelize
開源項(xiàng)目中文文檔：https://xuri.me/excelize/zh-hans/

excel模板

image.png

func ImportAccountByExcel(c echo.Context) error {
    //文件地址
    path := c.FormValue("path")
    if path == "" {
        return utils.ErrorNull(c, "請(qǐng)上傳excel文件")
    }
    path = strings.TrimLeft(path, "/")
    if flag, _ := utils.PathExists(path); !flag {
        return utils.ErrorNull(c, "未找到excel文件")
    }
    //path := "files/excel/account_template_test.xlsx"
    //excel表
    sheetName := "Sheet1"
    xlsx, err := excelize.OpenFile(path)
    if err != nil {
        return utils.ErrorNull(c, fmt.Sprintf("打開excel失敗,error：%s", err.Error()))
    }
        
    //excel錯(cuò)誤樣式，黃色背景
    style, _ := xlsx.NewStyle(`{"border":[{"type":"left","color":"000000","style":1},{"type":"top","color":"000000","style":1},{"type":"bottom","color":"000000","style":1},{"type":"right","color":"000000","style":1}],"fill":{"type":"pattern","color":["#ffeb00"],"pattern":1},"alignment":{"horizontal":"left","ident":1,"vertical":"center","wrap_text":true}}`)
        //sheet名稱
    rows := xlsx.GetRows(sheetName)

    var ip = c.RealIP()
    var now = time.Now()
    var partyId int64 = 1
    var account *model.Account
    var accountInfo *model.AccountInfo
    var dateStr, tempStr string
    var errorMap = map[int]interface{}{}
    var errorMsg []string
    var org model.PartyOrg
    var partyPost model.PartyPost
        //模板錯(cuò)誤
    if len(rows) <= 2 {
        return utils.ErrorNull(c, "excel格式錯(cuò)誤或無有效數(shù)據(jù)")
    }
        //無有效數(shù)據(jù)
    if len(rows[2]) < 25 {
        return utils.ErrorNull(c, "excel格式錯(cuò)誤或無有效數(shù)據(jù)")
    }
    for rIndex, row := range rows {
        //跳過提示行蝴罪、標(biāo)題行
        if rIndex != 0 && rIndex != 1 {
            errorMsg = []string{}
            accountInfo = new(model.AccountInfo)
            account = new(model.Account)
            //姓名
            account.FullName = utils.Trim(row[0])
            if account.FullName == "" {
                errorMsg = append(errorMsg, "姓名不能為空")
            } else if len(account.FullName) >= 50 {
                errorMsg = append(errorMsg, "姓名字符過長(zhǎng)")
            }

            //性別
            account.Gender = utils.Trim(row[1])
            if account.Gender == "" {
                errorMsg = append(errorMsg, "性別為必填項(xiàng)")
            } else if account.Gender != enum.GENDER_MALE && account.Gender != enum.GENDER_FEMALE {
                errorMsg = append(errorMsg, "性別錯(cuò)誤疾渴，值范圍：男、女")
            }

            //手機(jī)號(hào)
            account.Mobile = utils.Trim(row[2])
            if account.Mobile == "" {
                errorMsg = append(errorMsg, "手機(jī)號(hào)碼為必填項(xiàng)")
            } else if !utils.IsMobile(account.Mobile) {
                errorMsg = append(errorMsg, "手機(jī)號(hào)碼格式錯(cuò)誤")
            }
            account.Name = account.Mobile
            
            //出生日期
            dateStr = utils.Trim(row[3])
            account.DateOfBirth, err = utils.ParseExcelDate(dateStr)
            if err != nil {
                errorMsg = append(errorMsg, "出生日期格式錯(cuò)誤")
            }

            //在崗狀態(tài)
            accountInfo.WorkStatus = utils.Trim(row[4])
            switch accountInfo.WorkStatus {
            case "在崗", "待聘人員", "農(nóng)民工", "停薪留職", "排休人員", "離退休", "其他", "":
                break
            default:
                errorMsg = append(errorMsg, "在崗狀態(tài)錯(cuò)誤，值范圍：在崗, 待聘人員, 農(nóng)民工, 停薪留職, 排休人員, 離退休, 其他")
                break
            }

            //民族
            accountInfo.Nation = utils.Trim(row[5])
            if len(accountInfo.Nation) > 50 {
                errorMsg = append(errorMsg, "民族字符串過長(zhǎng)")
            }

            //籍貫
            accountInfo.NativePlace = utils.Trim(row[6])
            if len(accountInfo.Nation) > 100 {
                errorMsg = append(errorMsg, "籍貫字符串過長(zhǎng)")
            }

            //身份證
            accountInfo.Idcard = utils.Trim(row[7])

            if accountInfo.Idcard != "" {
                if len(accountInfo.Idcard) != 15 && len(accountInfo.Idcard) != 18 {
                    errorMsg = append(errorMsg, "身份證格式錯(cuò)誤僅，支持15、18位")
                }
            }
            //學(xué)歷
            accountInfo.Education = utils.Trim(row[8])
            switch accountInfo.Education {
            case "博士", "碩士", "本科", "屃鲂洌科", "高中及以下", "":
                break
            default:
                errorMsg = append(errorMsg, "學(xué)歷錯(cuò)誤，值范圍：博士,碩士,本科,尠貉椋科,高中及以下")
                break
            }

            //人員類型
            accountInfo.PersonnelType = utils.Trim(row[9])
            if accountInfo.PersonnelType != "" {
                switch accountInfo.PersonnelType {
                case "正式黨員", "預(yù)備黨員", "":
                    break
                default:
                    errorMsg = append(errorMsg, "學(xué)歷錯(cuò)誤捂敌，值范圍：正式黨員,預(yù)備黨員")
                    break
                }
            }

            //黨支部
            tempStr = utils.Trim(row[10])
            if tempStr != "" {
                org, err = GetPartyOrgByName(partyId, tempStr)
                if err != nil {
                    errorMsg = append(errorMsg, "黨支部不存在")
                } else {
                    account.OrgId = org.ID
                }
            } else {
                errorMsg = append(errorMsg, "黨支部為必填項(xiàng)")
            }

            //黨內(nèi)職務(wù)
            tempStr = utils.Trim(row[11])
            if tempStr != "" {
                partyPost, err = GetPartyPostByName(partyId, tempStr)
                if err != nil {
                    errorMsg = append(errorMsg, "黨內(nèi)職務(wù)不存在")
                } else {
                    accountInfo.PartyPostId = partyPost.ID
                }
            }

            //轉(zhuǎn)為預(yù)備黨員日期
            dateStr = utils.Trim(row[12])
            accountInfo.TurnPreparePartyDate, err = utils.ParseExcelDate(dateStr)
            if err != nil {
                errorMsg = append(errorMsg, "轉(zhuǎn)為預(yù)備黨員日期格式錯(cuò)誤")
            }

            //轉(zhuǎn)為正式黨員日期
            dateStr = utils.Trim(row[13])
            accountInfo.TurnPartyDate, err = utils.ParseExcelDate(dateStr)
            if err != nil {
                errorMsg = append(errorMsg, "轉(zhuǎn)為正式黨員日期格式錯(cuò)誤")
            }

            //工作崗位
            accountInfo.Post = utils.Trim(row[14])
            if len(accountInfo.Post) >= 50 {
                errorMsg = append(errorMsg, "工作崗位字符過長(zhǎng)")
            }

            //稅后工資
            tempStr = utils.Trim(row[15])
            if tempStr != "" {
                accountInfo.AfterTaxWages, err = convert.ToFloat64(utils.Trim(row[15]))
                if err != nil || accountInfo.AfterTaxWages < 0 {
                    errorMsg = append(errorMsg, "稅后工資格式錯(cuò)誤")
                }
            }
            //固定電話
            accountInfo.Phone = utils.Trim(row[16])
            if len(accountInfo.Phone) >= 30 {
                errorMsg = append(errorMsg, "固定電話字符過長(zhǎng)")
            }

            //家庭地址
            accountInfo.HomeAddress = utils.Trim(row[17])
            if len(accountInfo.HomeAddress) >= 255 {
                errorMsg = append(errorMsg, "家庭地址字符過長(zhǎng)")
            }

            //黨籍狀態(tài)
            accountInfo.PartyStatus = utils.Trim(row[18])
            switch accountInfo.PartyStatus {
            case "正常", "異常", "":
                break
            default:
                errorMsg = append(errorMsg, "黨籍狀態(tài)錯(cuò)誤，值范圍：正常既琴、異常")
                break
            }

            //是否為失聯(lián)黨員
            accountInfo.PartyLostStatus = utils.Trim(row[19])
            switch accountInfo.PartyLostStatus {
            case "是", "否", "":
                break
            default:
                errorMsg = append(errorMsg, "是否為失聯(lián)黨員錯(cuò)誤黍匾，值范圍：是、否")
                break
            }

            //失去聯(lián)系的日期
            dateStr = utils.Trim(row[20])
            accountInfo.PartyLostDate, err = utils.ParseExcelDate(dateStr)
            if err != nil {
                errorMsg = append(errorMsg, "失去聯(lián)系的日期格式錯(cuò)誤")
            }

            //是否為流動(dòng)黨員
            accountInfo.PartyFlowStatus = utils.Trim(row[21])
            switch accountInfo.PartyFlowStatus {
            case "是", "否", "":
                break
            default:
                errorMsg = append(errorMsg, "是否為流動(dòng)黨員錯(cuò)誤呛梆，值范圍：是、否")
                break
            }

            //外出流向
            accountInfo.OutgoingFlow = utils.Trim(row[22])
            if len(accountInfo.OutgoingFlow) >= 500 {
                errorMsg = append(errorMsg, "外出流向字符過長(zhǎng)")
            }

            //申請(qǐng)入黨日期
            dateStr = utils.Trim(row[23])
            accountInfo.PartyApplyDate, err = utils.ParseExcelDate(dateStr)
            if err != nil {
                errorMsg = append(errorMsg, "申請(qǐng)入黨日期格式錯(cuò)誤")
            }

            //列為積極分子日期
            dateStr = utils.Trim(row[24])
            accountInfo.PartyActivistDate, err = utils.ParseExcelDate(dateStr)
            if err != nil {
                errorMsg = append(errorMsg, "列為積極分子日期格式錯(cuò)誤")
            }

            //列為發(fā)展對(duì)象日期
            dateStr = utils.Trim(row[25])
            accountInfo.PartyDevelopDate, err = utils.ParseExcelDate(dateStr)
            if err != nil {
                errorMsg = append(errorMsg, "列為發(fā)展對(duì)象日期格式錯(cuò)誤")
            }

            //判斷手機(jī)號(hào)碼是否存在
            acc, _ := GetAccountByMobile(account.Mobile)
            if acc.ID > 0 {
                errorMsg = append(errorMsg, "手機(jī)號(hào)碼已存在")
            }

            if len(errorMsg) > 0 {
                //錯(cuò)誤記錄
                xlsx.SetCellDefault(sheetName, fmt.Sprintf("AA%v", rIndex+1), strings.Join(errorMsg, ";\r\n"))
                errorMap[rIndex] = errorMsg
            } else {
                //添加的默認(rèn)數(shù)據(jù)
                account.ID = utils.ID()
                account.Status = enum.NORMAL
                account.CTime = &now
                account.UTime = account.CTime
                account.PartyId = partyId
                account.Ip = ip 

                accountInfo.ID = utils.ID()
                accountInfo.AccountId = account.ID

                //數(shù)據(jù)保存 
                if err := saveImportAccount(account, accountInfo); err != nil {
                    //保存失敗錯(cuò)誤處理
                    errorMsg = append(errorMsg, err.Error())
                    xlsx.SetCellDefault(sheetName, fmt.Sprintf("AA%v", rIndex+1), strings.Join(errorMsg, ";\r\n"))
                    errorMap[rIndex] = errorMsg
                }
            }
            //如果有錯(cuò)誤磕诊，將背景設(shè)為警示顏色
            if len(errorMsg) > 0 {
                xlsx.SetCellStyle(sheetName, fmt.Sprintf("A%v", rIndex+1), fmt.Sprintf("AA%v", rIndex+1), style)
            }
            fmt.Println("-------------------------------------------------------------------------------------------")
        }
    }

    if len(errorMap) > 0 {
        //固定的標(biāo)題欄位置
        xlsx.SetCellDefault(sheetName, "AA2", "錯(cuò)誤說明")
        xlsx.Save()
        return utils.Confirm(c, "導(dǎo)入數(shù)據(jù)異常填物，請(qǐng)下載excel根據(jù)最后一列的錯(cuò)誤說明進(jìn)行修改調(diào)整", fmt.Sprintf("%s", path))
    }
    //需要自己處理返回
    return utils.SuccessNull(c, "導(dǎo)入成功")
}

func saveImportAccount(account *model.Account, accountInfo *model.AccountInfo) error {
    //保存事務(wù)
    tx := global.DB.Begin()
    defer func() {
        if r := recover(); r != nil {
            tx.Rollback()
        }
    }()
    if tx.Error != nil {
        global.Log.Error("tx error：%v", tx.Error.Error())
        return errors.New(fmt.Sprintf("初始化事務(wù)失敗：%s", tx.Error.Error()))
    }
    if err := tx.Create(&account).Error; err != nil {
        tx.Rollback()
        global.Log.Error("tx create account error：%v", err)
        return errors.New(fmt.Sprintf("導(dǎo)入黨員失旜铡：%s", err.Error()))
    }
    if err := tx.Create(&accountInfo).Error; err != nil {
        tx.Rollback()
        global.Log.Error("tx create accountInfo error：%v", err)
        return errors.New(fmt.Sprintf("導(dǎo)入黨員詳細(xì)信息失斨突恰：%s", err.Error()))
    }

    if err := tx.Commit().Error; err != nil {
        global.Log.Error("commit error：%v", err)
        return errors.New(fmt.Sprintf("保存黨員失敗：%s", err.Error()))
    }
    return nil
}

//去除前后所有空格莱褒、空字符串击困、制表符
func Trim(str string) string {
    if str == "" {
        return ""
    }
    return strings.TrimSpace(strings.TrimPrefix(str, string('\uFEFF')))
}

//已處理數(shù)字型日期
func ParseExcelDate(date string) (d *time.Time, err error) {
    if date != "" {
        var date2 time.Time
        if !IsValidNumber(date) {
            date2, err = ParseDate(date)
            if err != nil {
                return
            }
            d = &date2
            return
        } else {
            date2, err = ParseDate("1900-1-1")
            if err != nil {
                return
            }
            days := convert.MustInt(date)
            date2 = date2.AddDate(0, 0, days-2)
            d = &date2
            return
        }
    }
    return
}
 
//字符串日期轉(zhuǎn)換
func ParseDate(date string) (time.Time, error) {
    date = strings.Replace(date, "/", "-", -1)
    date = strings.Replace(date, ".", "-", -1)
    date = strings.Replace(date, "-0", "-", -1)
    local, _ := time.LoadLocation("Local")
    return time.ParseInLocation("2006-1-2", date, local)
}