Autoflow的运用例--企业培训报名表生成

Autoflow的运用例–企业培训报名表生成

背景

某公司的全员大表和年度员工课程培训表，为了能够清楚明了的查看员工的年度培训情况，需要对两张表进行数据汇总，由于两张表的数据比较复杂，手动进行数据筛选比较费时费力，打算使用Autoflow，通过配置的方式，实现快速自由的进行数据筛选处理。

要求

全员大表保存公司所有员工数据，年度培训表保存每年的员工培训历史记录。根据这两张表，以部门为单位，根据员工级别，培训记录等信息，自动生成每门培训课程可以参加培训的报名表。

配置及实现方式

• 从培训课程一览表中获取培训课程的基本数据，生成一张培训课程的报名统计表

1. 读取培训课程B4：G18区域的数据作为二维数组写入到全局变量v1当中

{
    "type": "ExcelApplicationScope",
    "workbookpath": "C:\\Users\\LVWD\\Desktop\\testf\\计算机培训课程.xlsx",
    "sequences": [
        {
            "type": "ExcelReadRange",
            "sheetname": "课程概要（中文）",
            "range": "B4:G18",
            "variable": "v1",
            "mergecelldatafill": true
        }
    ]
}

2. 将v1全局变量中的培训课程基础数据以ForEach循环的方式写入到培训课程报名统计表中

{
    "type": "ExcelApplicationScope",
    "workbookpath": "C:\\Users\\LVWD\\Desktop\\testf\\计算机培训课程.xlsx",
    "sequences": [
        {
            "type": "ForEach",
            "variable": "v1",
            "loopobj": "row",
            "loopindex": "rowindex",
            "sequences": [
                {
                    "type": "ExcelWriteCell",
                    "sheetname": "目录",
                    "rownum": "3",
                    "colnum": "B",
                    "rowindex": "rowindex",
                    "value": "row[0]"
                },
                {
                    "type": "ExcelWriteCell",
                    "sheetname": "目录",
                    "rownum": "3",
                    "colnum": "C",
                    "rowindex": "rowindex",
                    "value": "row[1]"
                },
                {
                    "type": "ExcelWriteCell",
                    "sheetname": "目录",
                    "rownum": "3",
                    "colnum": "D",
                    "rowindex": "rowindex",
                    "value": "connect(row[2], ' ', row[3])"
                },
                {
                    "type": "ExcelWriteCell",
                    "sheetname": "目录",
                    "rownum": "3",
                    "colnum": "E",
                    "rowindex": "rowindex",
                    "value": "row[4]"
                }
            ]
        },
]
}
3. 按要求对单元格进行合并并且添加超链接

{
“type”: “ExcelMergeCell”,
“sheetName”: “目录”,
“startcell”: “C3”,
“endcell”: “C5”
},
{
“type”: “ExcelSetCellHyperLink”,
“sheetName”: “目录”,
“cellname”: “D3”,
“value”: “#0005688!A1”
}

• 从培训课程一览表中获取培训课程的基本数据，生成每门课程的详细报名表

1. 从培训课程一览表中获取培训课程的基本数据，以循环的方式按照课程编号拷贝模板生成每门课程的详细报名表

{
    "type": "OpenExcelByXw",
    "workbookpath": "C:\\Users\\LVWD\\Desktop\\testf\\计算机培训课程.xlsx",
    "sequences": [
        {
            "type": "ForEach",
            "variable": "v1",
            "loopobj": "row",
            "sequences": [
                {
                    "type": "CopySheetBySheet",
                    "sourcesheetname": "template",
                    "targetsheetname": "row[2]"
                }
            ]
        }
    ]
}

2. 按照课程编号筛选课程，将课程的基本信息写入到每门课程的详细报名表中

{
    "type": "ExcelApplicationScope",
    "workbookpath": "C:\\Users\\LVWD\\Desktop\\testf\\计算机培训课程.xlsx",
    "sequences": [
        {
            "type": "ForEach",
            "variable": "v1",
            "loopobj": "row",
            "loopindex": "rowindex",
            "sequences": [
                {
                    "type": "IF",
                    "conditions": "row[2] == '0005688'",
                    "sequences": [
                        {
                            "type": "ExcelWriteCell",
                            "sheetname": "0005688",
                            "rownum": "2",
                            "colnum": "D",
                            "value": "connect(row[2], ' ', row[3])"
                        },
                        {
                            "type": "ExcelWriteCell",
                            "sheetname": "0005688",
                            "rownum": "3",
                            "colnum": "F",
                            "value": "row[4]"
                        },
                        {
                            "type": "ExcelWriteCell",
                            "sheetname": "0005688",
                            "rownum": "4",
                            "colnum": "F",
                            "value": "connect(row[5], '学時')"
                        },
                        {
                            "type": "ExcelWriteCell",
                            "sheetname": "0005688",
                            "rownum": "3",
                            "colnum": "H",
                            "value": "row[1]"
                        },
                        {
                            "type": "ExcelWriteCell",
                            "sheetname": "0005688",
                            "rownum": "3",
                            "colnum": "D",
                            "value": "'专业类'"
                        },
                        {
                            "type": "ExcelWriteCell",
                            "sheetname": "0005688",
                            "rownum": "4",
                            "colnum": "D",
                            "value": "'E-Learning'"
                        }
                    ]
                },
            ]
        }
    ]
}

• 从全员大表中获取人员信息，从年度课程培训报名表中获取人员培训报名信息，将筛选整合后的数据写入到每门课程的详细报名表中

1. 从全员大表中读取人员信息的“员工编号，姓名，公司，事业部，部门，职种，职级划分，社龄”等信息写入到全局变量v2中

{
    "type": "ExcelReadRangeByHeader",
    "workbookpath": "C:\\Users\\LVWD\\Desktop\\testf\\全员大表2019.xlsx",
    "sheetName": "全员大表",
    "range": "A4:AG545",
    "header": 3,
    "usecols": "B,C,E,G,I,K,L,N,V",
    "variable": "v2"
}

2. 从年度培训报名表中获取“员工ID，培训名称，课程完成情况”等课程报名信息写入到全局变量v3中

{
    "type": "ExcelReadRangeByHeader",
    "workbookpath": "C:\\Users\\LVWD\\Desktop\\testf\\2019年度培训.xlsx",
    "sheetName": "2019",
    "range": "A3:T544",
    "header": 2,
    "usecols": "C,K,Z",
    "variable": "v3"
}

3. 将人员信息和课程报名信息通过“员工编号”联合起来组成新的课程报名信息写入到全局变量v4中

{
    "type": "ExcelMergeDataByHeader",
    "leftvar": "v2",
    "rightvar": "v3",
    "mergevar": "v4",
    "mergetype": "left",
    "leftkeys": ["新员工编号"],
    "rightkeys": ["员工ID"]
}

4. 通过课程名称筛选课程报名信息，将筛选后的信息分别写入到每门课程的详细报名表中

{
    "type": "ExcelScreenDataByHeader",
    "conditions": "data['培训名称']=='0005688 编译原理'",
    "variable": "v4",
    "screendata": "screendata",
    "sequences": [
        {
            "type": "ForEach",
            "variable": "screendata",
            "loopobj": "row",
            "loopindex": "rowindex",
            "sequences": [
                 {
                    "type": "ExcelWriteCell",
                    "sheetname": "0005688",
                    "rownum": "7",
                    "colnum": "D",
                    "rowindex": "rowindex",
                    "value": "row[2]"
                },
                {
                    "type": "ExcelWriteCell",
                    "sheetname": "0005688",
                    "rownum": "7",
                    "colnum": "E",
                    "rowindex": "rowindex",
                    "value": "row[3]"
                },
                {
                    "type": "ExcelWriteCell",
                    "sheetname": "0005688",
                    "rownum": "7",
                    "colnum": "F",
                    "rowindex": "rowindex",
                    "value": "row[4]"
                },
                {
                    "type": "ExcelWriteCell",
                    "sheetname": "0005688",
                    "rownum": "7",
                    "colnum": "G",
                    "rowindex": "rowindex",
                    "value": "row[1]"
                },
                {
                    "type": "ExcelWriteCell",
                    "sheetname": "0005688",
                    "rownum": "7",
                    "colnum": "H",
                    "rowindex": "rowindex",
                    "value": "str(row[0])"
                },
                {
                    "type": "ExcelWriteCell",
                    "sheetname": "0005688",
                    "rownum": "7",
                    "colnum": "I",
                    "rowindex": "rowindex",
                    "value": "row[8]"
                },
                {
                    "type": "ExcelWriteCell",
                    "sheetname": "0005688",
                    "rownum": "7",
                    "colnum": "J",
                    "rowindex": "rowindex",
                    "value": "row[6]"
                },
                {
                    "type": "ExcelWriteCell",
                    "sheetname": "0005688",
                    "rownum": "7",
                    "colnum": "K",
                    "rowindex": "rowindex",
                    "value": "row[7]"
                },
                {
                    "type": "ExcelWriteCell",
                    "sheetname": "0005688",
                    "rownum": "7",
                    "colnum": "L",
                    "rowindex": "rowindex",
                    "value": "row[11]"
                }
            ]
        }
    ]
}