1.修改Excel导入时双模板数据校验方法

yl

2023-12-18 b59b0e3516caee784ab9aa74e5b9dd1dfbffaa2d

 VueWebCoreApi/Tools/ImportExcel.cs

@@ -1148,7 +1148,7 @@
                {

                    mes.code = "300";
                    mes.Message = "文件数据不能为空！";
                    mes.message = "文件数据不能为空！";

                }
            }
@@ -1211,7 +1211,7 @@
                    if (table.Rows.Count <= 0)
                    {
                        mes.code = "300";
                        mes.Message = "主子表文件中数据不能为空！";
                        mes.message = "主子表文件中数据不能为空！";
                    }
                }
            }
@@ -1292,30 +1292,68 @@
                    ExcelErro erro = new ExcelErro();
                    erro.RoeNumber = (rowIndex + 1).ToString();
                    erro.ErrorField = emptyColumn.ColumnName;
                    erro.ErrorCont = "模板表头带*的列中存在空值,第" + (rowIndex + 1).ToString() + "行,必填字段："+ emptyColumn.ColumnName +"为空";
                    erro.ErrorCont = "模板表头带*的列中存在空值,第" + (rowIndex + 1).ToString() + "行,必填字段：" + emptyColumn.ColumnName + "为空";
                    list.Add(erro);
                }
                var duplicateColumn = excelTable[i].Columns.Cast<DataColumn>().FirstOrDefault(
                    column => column.ColumnName.Contains("唯一") && excelTable[i].AsEnumerable().GroupBy(row => row[column]).Any(group => group.Count() > 1));
                if (duplicateColumn != null)
                if (excelTable[i].Columns.Cast<DataColumn>().Any(column => column.ColumnName.Contains("M")))
                {
                    int columnIndex = excelTable[i].Columns.IndexOf(duplicateColumn);
                    var duplicateRows = excelTable[i].AsEnumerable()
                        .Where(row => row[duplicateColumn] != DBNull.Value)
                        .GroupBy(row => row[duplicateColumn])
                        .Where(group => group.Count() > 1)
                        .SelectMany(group => group.ToList())
                    // 先按照列名包含"M"标识的列分组
                    var mColumns = excelTable[i].Columns.Cast<DataColumn>()
                        .Where(c => c.ColumnName.Contains("M"))
                        .ToList();
                    foreach (var row in duplicateRows)

                    var groups = excelTable[i].AsEnumerable()
                        .GroupBy(r => string.Join("-", mColumns.Select(c => r[c])));

                    foreach (var group in groups)
                    {
                        ExcelErro erro = new ExcelErro();
                        erro.RoeNumber = excelTable[i].Rows.IndexOf(row).ToString();
                        erro.ErrorField = duplicateColumn.ColumnName;
                        erro.ErrorCont = "模板表头带(唯一)的列中存在重复值,第" + excelTable[i].Rows.IndexOf(row).ToString() + "行,必填字段：" + duplicateColumn.ColumnName + "重复";
                        list.Add(erro);
                        
                        // 判断列名包含"唯一"标识的列的列值是否重复
                        var uniqueColumnName = excelTable[i].Columns.Cast<DataColumn>()
                            .Where(c => c.ColumnName.Contains("唯一"))
                            .Select(c => c.ColumnName)
                            .FirstOrDefault();

                        var duplicateUniqueValues = group
                            .GroupBy(r => r[uniqueColumnName])
                            .Where(g => g.Count() > 1)
                            .Select(g => g.Key);

                        foreach (var value in duplicateUniqueValues)
                        {
                            ExcelErro erro = new ExcelErro();
                            erro.RoeNumber = "/";
                            erro.ErrorField = mColumns[0].ToString();
                            erro.ErrorCont = "模板表头字段【"+ mColumns[0].ToString() + "】下的值:"+ group.Key + ",对应带(唯一)的列中存在重复值为："+value+"";
                            list.Add(erro);
                        }
                    }
                }
                else
                {
                    var duplicateColumn = excelTable[i].Columns.Cast<DataColumn>().FirstOrDefault(
                        column => column.ColumnName.Contains("唯一") && excelTable[i].AsEnumerable().GroupBy(row => row[column]).Any(group => group.Count() > 1));
                    if (duplicateColumn != null)
                    {
                        int columnIndex = excelTable[i].Columns.IndexOf(duplicateColumn);
                        var duplicateRows = excelTable[i].AsEnumerable()
                            .Where(row => row[duplicateColumn] != DBNull.Value)
                            .GroupBy(row => row[duplicateColumn])
                            .Where(group => group.Count() > 1)
                            .SelectMany(group => group.ToList())
                            .ToList();
                        foreach (var row in duplicateRows)
                        {
                            ExcelErro erro = new ExcelErro();
                            erro.RoeNumber = excelTable[i].Rows.IndexOf(row).ToString();
                            erro.ErrorField = duplicateColumn.ColumnName;
                            erro.ErrorCont = "模板表头带(唯一)的列中存在重复值,第" + excelTable[i].Rows.IndexOf(row).ToString() + "行,必填字段：" + duplicateColumn.ColumnName + "重复";
                            list.Add(erro);

                        }
                    }
                }


            }
            return list;
        }