Gox语言进行Excel文件处理的实例-GX35.1

Gox语言0.995版之后内置了Excelize包，可以方便地处理微软的Excel格式的文件。
下面是一个某企业中对Excel文件进行处理的实例，非常有助于理解Gox语言中处理Excel的过程。
先说说背景，该企业六一儿童节需要给有孩子的员工发放礼物，礼物有两种，可编程的机器人车和可以响音乐的玩具套件。目前收集了两个Excel文件分别是要求领取这两种礼品的员工记录，里面一行一条记录，包含了申请员工的姓名、电话等信息；另有一个单位通讯录的Excel文件，保存了全体员工的姓名、电话、部门、子女情况等个人信息，也是一行一条记录。现在要分别处理两个领取礼品的文件，将其中的员工按照姓名去通讯录文件中查询出完整的个人信息，然后分别写入两个新的Excel文件中，这样，负责发放礼品的人员就可以清晰地知道每个领取礼品员工的个人信息以便核对了。
下面就是这个实例代码：
// 设置处理Excel包的简称为excelize
excelize = github_360EntSecGroupSkylar_excelize

// errorList用于存放处理中发现有错的条目
errorList := excelize.NewFile()

// errorList的计数，每增加一条错误记录，该数字会加一
errorCount := 0

// ft1是准备写入选择编程车的员工条目
ft1 := excelize.NewFile()

// ft1的计数，每增加一条记录，该数字会加一
ft1Count := 0

// ft2是准备写入选择音乐套件的员工条目
ft2 := excelize.NewFile()

// ft2的计数，每增加一条记录，该数字会加一
ft2Count := 0

// 打开总表用于查询
f0, err := excelize.OpenFile(`d:\tmpx\2020年六一家长及儿童统计表.xlsx`)
if err != nil {
    tk.Pl("failed to open file 0: %v", err)
    return
}

// 打开第一个子表进行查询
f1, err = excelize.OpenFile(`d:\tmpx\六一活动编程车“名单.xlsx`)
if err != nil {
    tk.Pl("failed to open file 1: %v", err)
    return
}

// 取出子表第一个sheet中的所有数据
rows, errT := f1.GetRows(f1.GetSheetName(0))

// 发生错误则输出错误信息后终止程序运行
tk.CheckError(errT)

// （按行）遍历所有的子表条目进行处理
for i, v := range rows {
    // 第一行是表头，跳过
    if i == 0 {
        continue
    }

    // 取出第一列的内容，即员工姓名
    name := tk.Trim(v[0])

    if name == "" {
        continue
    }

    tk.Pl("name[%v]: %v", i, name)

    // 在总表中查询该姓名
    result, err := f0.SearchSheet(f0.GetSheetName(1), name)

    // 如果查询时发生错误，则将相关信息保存在errorList中
    if err != nil {
        errorList.SetCellValue("Sheet1", "A"+tk.IntToStr(errorCount), name)
        errorList.SetCellValue("Sheet1", "B"+tk.IntToStr(errorCount), "人名在总表中查找时出错")
        errorList.SetCellValue("Sheet1", "C"+tk.IntToStr(errorCount), tk.Trim(v[2]))
        errorList.SetCellValue("Sheet1", "D"+tk.IntToStr(errorCount), tk.Trim(v[1]))
        errorList.SetCellValue("Sheet1", "E"+tk.IntToStr(errorCount), tk.Trim(v[3]))
        errorList.SetCellValue("Sheet1", "F"+tk.IntToStr(errorCount), tk.Trim(v[4]))
        errorList.SetCellValue("Sheet1", "G"+tk.IntToStr(errorCount), "编程车")

        errorCount++

        continue
    }

    // 如果不存在该姓名，则将相关信息保存在errorList中
    if result == nil {
        errorList.SetCellValue("Sheet1", "A"+tk.IntToStr(errorCount), name)
        errorList.SetCellValue("Sheet1", "B"+tk.IntToStr(errorCount), "人名未在总表中找到")
        errorList.SetCellValue("Sheet1", "C"+tk.IntToStr(errorCount), tk.Trim(v[2]))
        errorList.SetCellValue("Sheet1", "D"+tk.IntToStr(errorCount), tk.Trim(v[1]))
        errorList.SetCellValue("Sheet1", "E"+tk.IntToStr(errorCount), tk.Trim(v[3]))
        errorList.SetCellValue("Sheet1", "F"+tk.IntToStr(errorCount), tk.Trim(v[4]))
        errorList.SetCellValue("Sheet1", "G"+tk.IntToStr(errorCount), "编程车")
        errorCount++

        continue
    }

    // 如果不存在该姓名，则将相关信息保存在errorList中
    if len(result) < 1 {
        errorList.SetCellValue("Sheet1", "A"+tk.IntToStr(errorCount), name)
        errorList.SetCellValue("Sheet1", "B"+tk.IntToStr(errorCount), "人名未在总表中找到")
        errorList.SetCellValue("Sheet1", "C"+tk.IntToStr(errorCount), tk.Trim(v[2]))
        errorList.SetCellValue("Sheet1", "D"+tk.IntToStr(errorCount), tk.Trim(v[1]))
        errorList.SetCellValue("Sheet1", "E"+tk.IntToStr(errorCount), tk.Trim(v[3]))
        errorList.SetCellValue("Sheet1", "F"+tk.IntToStr(errorCount), tk.Trim(v[4]))
        errorList.SetCellValue("Sheet1", "G"+tk.IntToStr(errorCount), "编程车")
        errorCount++

        continue
    }

    // 如果有多个该姓名（姓名重复），则将相关信息保存在errorList中
    if len(result) > 1 {
        errorList.SetCellValue("Sheet1", "A"+tk.IntToStr(errorCount), name)
        errorList.SetCellValue("Sheet1", "B"+tk.IntToStr(errorCount), "人名在总表有重复项")
        errorList.SetCellValue("Sheet1", "C"+tk.IntToStr(errorCount), tk.Trim(v[2]))
        errorList.SetCellValue("Sheet1", "D"+tk.IntToStr(errorCount), tk.Trim(v[1]))
        errorList.SetCellValue("Sheet1", "E"+tk.IntToStr(errorCount), tk.Trim(v[3]))
        errorList.SetCellValue("Sheet1", "F"+tk.IntToStr(errorCount), tk.Trim(v[4]))
        errorList.SetCellValue("Sheet1", "G"+tk.IntToStr(errorCount), "编程车")
        errorCount++

        continue
    }

    // 取出查询结果（类似B12）并拆分成行和列，即类似“B”和“12”
    iResult := result[0]

    colT := tk.Trim(iResult[0:1])
    if colT != "B" {
        continue
    }

    rowT := tk.StrToIntWithDefaultValue(iResult[1:], -1)

    if rowT == -1 {
        errorList.SetCellValue("Sheet1", "A"+tk.IntToStr(errorCount), name)
        errorList.SetCellValue("Sheet1", "B"+tk.IntToStr(errorCount), tk.Spr("人名在总表中查询结果错误：%v", iResult))
        errorList.SetCellValue("Sheet1", "C"+tk.IntToStr(errorCount), tk.Trim(v[2]))
        errorList.SetCellValue("Sheet1", "D"+tk.IntToStr(errorCount), tk.Trim(v[1]))
        errorList.SetCellValue("Sheet1", "E"+tk.IntToStr(errorCount), tk.Trim(v[3]))
        errorList.SetCellValue("Sheet1", "F"+tk.IntToStr(errorCount), tk.Trim(v[4]))
        errorList.SetCellValue("Sheet1", "G"+tk.IntToStr(errorCount), "编程车")
        errorCount++

        continue
    }

    // 获取部门、电话等个人信息
    deptName, errT = f0.GetCellValue(f0.GetSheetName(1), "A"+tk.IntToStr(rowT))

    if errT != nil {
        errorList.SetCellValue("Sheet1", "A"+tk.IntToStr(errorCount), name)
        errorList.SetCellValue("Sheet1", "B"+tk.IntToStr(errorCount), tk.Spr("部门错误：%v", deptName))
        errorList.SetCellValue("Sheet1", "C"+tk.IntToStr(errorCount), tk.Trim(v[2]))
        errorList.SetCellValue("Sheet1", "D"+tk.IntToStr(errorCount), tk.Trim(v[1]))
        errorList.SetCellValue("Sheet1", "E"+tk.IntToStr(errorCount), tk.Trim(v[3]))
        errorList.SetCellValue("Sheet1", "F"+tk.IntToStr(errorCount), tk.Trim(v[4]))
        errorList.SetCellValue("Sheet1", "G"+tk.IntToStr(errorCount), "编程车")
        errorCount++

        continue
    }

    deptName = tk.Trim(deptName)

    phoneT, errT = f0.GetCellValue(f0.GetSheetName(1), "D"+tk.IntToStr(rowT))

    if errT != nil {
        errorList.SetCellValue("Sheet1", "A"+tk.IntToStr(errorCount), name)
        errorList.SetCellValue("Sheet1", "B"+tk.IntToStr(errorCount), tk.Spr("电话错误：%v", phoneT))
        errorList.SetCellValue("Sheet1", "C"+tk.IntToStr(errorCount), tk.Trim(v[2]))
        errorList.SetCellValue("Sheet1", "D"+tk.IntToStr(errorCount), tk.Trim(v[1]))
        errorList.SetCellValue("Sheet1", "E"+tk.IntToStr(errorCount), tk.Trim(v[3]))
        errorList.SetCellValue("Sheet1", "F"+tk.IntToStr(errorCount), tk.Trim(v[4]))
        errorList.SetCellValue("Sheet1", "G"+tk.IntToStr(errorCount), "编程车")
        errorCount++

        continue
    }

    phoneT = tk.Trim(phoneT)

    if phoneT == "" {
        phoneT = tk.Trim(v[1])
    }

    doubleEmpl, errT = f0.GetCellValue(f0.GetSheetName(1), "C"+tk.IntToStr(rowT))

    if errT != nil {
        errorList.SetCellValue("Sheet1", "A"+tk.IntToStr(errorCount), name)
        errorList.SetCellValue("Sheet1", "B"+tk.IntToStr(errorCount), tk.Spr("双职工错误：%v", doubleEmpl))
        errorList.SetCellValue("Sheet1", "C"+tk.IntToStr(errorCount), tk.Trim(v[2]))
        errorList.SetCellValue("Sheet1", "D"+tk.IntToStr(errorCount), tk.Trim(v[1]))
        errorList.SetCellValue("Sheet1", "E"+tk.IntToStr(errorCount), tk.Trim(v[3]))
        errorList.SetCellValue("Sheet1", "F"+tk.IntToStr(errorCount), tk.Trim(v[4]))
        errorList.SetCellValue("Sheet1", "G"+tk.IntToStr(errorCount), "编程车")
        errorCount++

        continue
    }

    childCount, errT = f0.GetCellValue(f0.GetSheetName(1), "E"+tk.IntToStr(rowT))

    if errT != nil {
        errorList.SetCellValue("Sheet1", "A"+tk.IntToStr(errorCount), name)
        errorList.SetCellValue("Sheet1", "B"+tk.IntToStr(errorCount), tk.Spr("孩子数量错误：%v", childCount))
        errorList.SetCellValue("Sheet1", "C"+tk.IntToStr(errorCount), tk.Trim(v[2]))
        errorList.SetCellValue("Sheet1", "D"+tk.IntToStr(errorCount), tk.Trim(v[1]))
        errorList.SetCellValue("Sheet1", "E"+tk.IntToStr(errorCount), tk.Trim(v[3]))
        errorList.SetCellValue("Sheet1", "F"+tk.IntToStr(errorCount), tk.Trim(v[4]))
        errorList.SetCellValue("Sheet1", "G"+tk.IntToStr(errorCount), "编程车")
        errorCount++

        continue
    }

    // 写入新表ft1中
    ft1.SetCellValue("Sheet1", "A"+tk.IntToStr(ft1Count), deptName)
    ft1.SetCellValue("Sheet1", "B"+tk.IntToStr(ft1Count), name)
    ft1.SetCellValue("Sheet1", "C"+tk.IntToStr(ft1Count), doubleEmpl)
    ft1.SetCellValue("Sheet1", "D"+tk.IntToStr(ft1Count), phoneT)
    ft1.SetCellValue("Sheet1", "E"+tk.IntToStr(ft1Count), childCount)
    ft1.SetCellValue("Sheet1", "F"+tk.IntToStr(ft1Count), tk.Trim(v[3]))
    ft1.SetCellValue("Sheet1", "G"+tk.IntToStr(ft1Count), "编程车")

    // 记得每写一条新纪录，ft1Count需要加一
    ft1Count++

    // tk.Plvsr(result, err)
}

// 第二个子表的处理方式与第一个是一样的，仅某些具体信息不同
f2, err = excelize.OpenFile(`d:\tmpx\六一活动“音乐玩具”领取名单.xlsx`)
if err != nil {
    tk.Pl("failed to open file 1: %v", err)
    return
}

rows, errT = f2.GetRows(f2.GetSheetName(0))

tk.CheckError(errT)

for i, v = range rows {
    if i == 0 {
        continue
    }

    name := tk.Trim(v[0])

    if name == "" {
        continue
    }

    tk.Pl("name[%v]: %v", i, name)

    result, err = f0.SearchSheet(f0.GetSheetName(1), name)

    if err != nil {
        errorList.SetCellValue("Sheet1", "A"+tk.IntToStr(errorCount), name)
        errorList.SetCellValue("Sheet1", "B"+tk.IntToStr(errorCount), "人名在总表中查找时出错")
        errorList.SetCellValue("Sheet1", "C"+tk.IntToStr(errorCount), tk.Trim(v[2]))
        errorList.SetCellValue("Sheet1", "D"+tk.IntToStr(errorCount), tk.Trim(v[1]))
        errorList.SetCellValue("Sheet1", "E"+tk.IntToStr(errorCount), tk.Trim(v[3]))
        errorList.SetCellValue("Sheet1", "F"+tk.IntToStr(errorCount), tk.Trim(v[4]))
        errorList.SetCellValue("Sheet1", "G"+tk.IntToStr(errorCount), "音乐套件")
        errorCount++

        continue
    }

    if result == nil {
        errorList.SetCellValue("Sheet1", "A"+tk.IntToStr(errorCount), name)
        errorList.SetCellValue("Sheet1", "B"+tk.IntToStr(errorCount), "人名未在总表中找到")
        errorList.SetCellValue("Sheet1", "C"+tk.IntToStr(errorCount), tk.Trim(v[2]))
        errorList.SetCellValue("Sheet1", "D"+tk.IntToStr(errorCount), tk.Trim(v[1]))
        errorList.SetCellValue("Sheet1", "E"+tk.IntToStr(errorCount), tk.Trim(v[3]))
        errorList.SetCellValue("Sheet1", "F"+tk.IntToStr(errorCount), tk.Trim(v[4]))
        errorList.SetCellValue("Sheet1", "G"+tk.IntToStr(errorCount), "音乐套件")
        errorCount++

        continue
    }

    if len(result) < 1 {
        errorList.SetCellValue("Sheet1", "A"+tk.IntToStr(errorCount), name)
        errorList.SetCellValue("Sheet1", "B"+tk.IntToStr(errorCount), "人名未在总表中找到")
        errorList.SetCellValue("Sheet1", "C"+tk.IntToStr(errorCount), tk.Trim(v[2]))
        errorList.SetCellValue("Sheet1", "D"+tk.IntToStr(errorCount), tk.Trim(v[1]))
        errorList.SetCellValue("Sheet1", "E"+tk.IntToStr(errorCount), tk.Trim(v[3]))
        errorList.SetCellValue("Sheet1", "F"+tk.IntToStr(errorCount), tk.Trim(v[4]))
        errorList.SetCellValue("Sheet1", "G"+tk.IntToStr(errorCount), "音乐套件")
        errorCount++

        continue
    }

    if len(result) > 1 {
        errorList.SetCellValue("Sheet1", "A"+tk.IntToStr(errorCount), name)
        errorList.SetCellValue("Sheet1", "B"+tk.IntToStr(errorCount), "人名在总表有重复项")
        errorList.SetCellValue("Sheet1", "C"+tk.IntToStr(errorCount), tk.Trim(v[2]))
        errorList.SetCellValue("Sheet1", "D"+tk.IntToStr(errorCount), tk.Trim(v[1]))
        errorList.SetCellValue("Sheet1", "E"+tk.IntToStr(errorCount), tk.Trim(v[3]))
        errorList.SetCellValue("Sheet1", "F"+tk.IntToStr(errorCount), tk.Trim(v[4]))
        errorList.SetCellValue("Sheet1", "G"+tk.IntToStr(errorCount), "音乐套件")
        errorCount++

        continue
    }

    iResult := result[0]

    colT := tk.Trim(iResult[0:1])
    if colT != "B" {
        continue
    }

    rowT := tk.StrToIntWithDefaultValue(iResult[1:], -1)

    if rowT == -1 {
        errorList.SetCellValue("Sheet1", "A"+tk.IntToStr(errorCount), name)
        errorList.SetCellValue("Sheet1", "B"+tk.IntToStr(errorCount), tk.Spr("人名在总表中查询结果错误：%v", iResult))
        errorList.SetCellValue("Sheet1", "C"+tk.IntToStr(errorCount), tk.Trim(v[2]))
        errorList.SetCellValue("Sheet1", "D"+tk.IntToStr(errorCount), tk.Trim(v[1]))
        errorList.SetCellValue("Sheet1", "E"+tk.IntToStr(errorCount), tk.Trim(v[3]))
        errorList.SetCellValue("Sheet1", "F"+tk.IntToStr(errorCount), tk.Trim(v[4]))
        errorList.SetCellValue("Sheet1", "G"+tk.IntToStr(errorCount), "音乐套件")
        errorCount++

        continue
    }

    deptName, errT = f0.GetCellValue(f0.GetSheetName(1), "A"+tk.IntToStr(rowT))

    if errT != nil {
        errorList.SetCellValue("Sheet1", "A"+tk.IntToStr(errorCount), name)
        errorList.SetCellValue("Sheet1", "B"+tk.IntToStr(errorCount), tk.Spr("部门错误：%v", deptName))
        errorList.SetCellValue("Sheet1", "C"+tk.IntToStr(errorCount), tk.Trim(v[2]))
        errorList.SetCellValue("Sheet1", "D"+tk.IntToStr(errorCount), tk.Trim(v[1]))
        errorList.SetCellValue("Sheet1", "E"+tk.IntToStr(errorCount), tk.Trim(v[3]))
        errorList.SetCellValue("Sheet1", "F"+tk.IntToStr(errorCount), tk.Trim(v[4]))
        errorList.SetCellValue("Sheet1", "G"+tk.IntToStr(errorCount), "音乐套件")
        errorCount++

        continue
    }

    deptName = tk.Trim(deptName)

    phoneT, errT = f0.GetCellValue(f0.GetSheetName(1), "D"+tk.IntToStr(rowT))

    if errT != nil {
        errorList.SetCellValue("Sheet1", "A"+tk.IntToStr(errorCount), name)
        errorList.SetCellValue("Sheet1", "B"+tk.IntToStr(errorCount), tk.Spr("电话错误：%v", phoneT))
        errorList.SetCellValue("Sheet1", "C"+tk.IntToStr(errorCount), tk.Trim(v[2]))
        errorList.SetCellValue("Sheet1", "D"+tk.IntToStr(errorCount), tk.Trim(v[1]))
        errorList.SetCellValue("Sheet1", "E"+tk.IntToStr(errorCount), tk.Trim(v[3]))
        errorList.SetCellValue("Sheet1", "F"+tk.IntToStr(errorCount), tk.Trim(v[4]))
        errorList.SetCellValue("Sheet1", "G"+tk.IntToStr(errorCount), "音乐套件")
        errorCount++

        continue
    }

    phoneT = tk.Trim(phoneT)

    if phoneT == "" {
        phoneT = tk.Trim(v[1])
    }

    doubleEmpl, errT = f0.GetCellValue(f0.GetSheetName(1), "C"+tk.IntToStr(rowT))

    if errT != nil {
        errorList.SetCellValue("Sheet1", "A"+tk.IntToStr(errorCount), name)
        errorList.SetCellValue("Sheet1", "B"+tk.IntToStr(errorCount), tk.Spr("双职工错误：%v", doubleEmpl))
        errorList.SetCellValue("Sheet1", "C"+tk.IntToStr(errorCount), tk.Trim(v[2]))
        errorList.SetCellValue("Sheet1", "D"+tk.IntToStr(errorCount), tk.Trim(v[1]))
        errorList.SetCellValue("Sheet1", "E"+tk.IntToStr(errorCount), tk.Trim(v[3]))
        errorList.SetCellValue("Sheet1", "F"+tk.IntToStr(errorCount), tk.Trim(v[4]))
        errorList.SetCellValue("Sheet1", "G"+tk.IntToStr(errorCount), "音乐套件")
        errorCount++

        continue
    }

    childCount, errT = f0.GetCellValue(f0.GetSheetName(1), "E"+tk.IntToStr(rowT))

    if errT != nil {
        errorList.SetCellValue("Sheet1", "A"+tk.IntToStr(errorCount), name)
        errorList.SetCellValue("Sheet1", "B"+tk.IntToStr(errorCount), tk.Spr("孩子数量错误：%v", childCount))
        errorList.SetCellValue("Sheet1", "C"+tk.IntToStr(errorCount), tk.Trim(v[2]))
        errorList.SetCellValue("Sheet1", "D"+tk.IntToStr(errorCount), tk.Trim(v[1]))
        errorList.SetCellValue("Sheet1", "E"+tk.IntToStr(errorCount), tk.Trim(v[3]))
        errorList.SetCellValue("Sheet1", "F"+tk.IntToStr(errorCount), tk.Trim(v[4]))
        errorList.SetCellValue("Sheet1", "G"+tk.IntToStr(errorCount), "音乐套件")
        errorCount++

        continue
    }

    ft2.SetCellValue("Sheet1", "A"+tk.IntToStr(ft2Count), deptName)
    ft2.SetCellValue("Sheet1", "B"+tk.IntToStr(ft2Count), name)
    ft2.SetCellValue("Sheet1", "C"+tk.IntToStr(ft2Count), doubleEmpl)
    ft2.SetCellValue("Sheet1", "D"+tk.IntToStr(ft2Count), phoneT)
    ft2.SetCellValue("Sheet1", "E"+tk.IntToStr(ft2Count), childCount)
    ft2.SetCellValue("Sheet1", "F"+tk.IntToStr(ft2Count), tk.Trim(v[3]))
    ft2.SetCellValue("Sheet1", "G"+tk.IntToStr(ft2Count), "音乐套件")

    ft2Count++
}


// 保存处理出错的记录
err := errorList.SaveAs("errors.xlsx")
if err != nil {
    tk.Pl("failed to save errors: %v", err)
}

// 保存选择了编程车的记录
err := ft1.SaveAs("cars.xlsx")
if err != nil {
    tk.Pl("failed to save errors: %v", err)
}

// 保存选择了音乐套件的记录
err := ft2.SaveAs("music.xlsx")
if err != nil {
    tk.Pl("failed to save errors: %v", err)
}
代码中已有详尽注释，注意运行前将通讯录文件和两个礼品领取表文件复制到D盘tmpx目录下，然后在该目录下运行gox 61.gox即可生成3个文件，其中errors.xlsx是处理中发生错误的员工记录，cars.xlsx与music.xlsx分别是领取两个礼品的员工记录。
最后，本代码需要Gox或Goxc的0.995版本以上，请到这里下载最新版本。