设备清单导入接口开发，安灯响应接口修改

yl

2022-11-01 cb776a0d9f1257047ae39c740b06e6ab487b1bfc

设备清单导入接口开发，安灯响应接口修改

 .vs/VueWebApi/v16/.suo

Binary files differ

 VueWebApi/Controllers/AppDeviceManageController.cs

 VueWebApi/DLL/DAL/AppDeviceManageDAL.cs

@@ -1120,7 +1120,7 @@
            try
            {
                list.Clear();
                for (int i = 1; i <= json.Count; i++)
                for (int i = 0; i <json.Count; i++)
                {
                    string code = json[i].code.ToString();
                    string name = json[i].name.ToString();

 VueWebApi/Excel/É豸Çåµ¥.xls

Binary files differ

 VueWebApi/Tools/ImportExcel.cs

@@ -271,6 +271,170 @@
            return list;
        }


        /// <summary>
        /// Excel 3个Sheet导入成Datable
        /// </summary>
        /// <param name="file">导入路径(包含文件名与扩展名)</param>
        /// <returns></returns>
        public static List<DataTable> ExcelToThreeTableList(string file)
        {
            List<DataTable> list = new List<DataTable>();
            IWorkbook Workbook;
            try
            {
                using (FileStream fileStream = new FileStream(file, FileMode.Open, FileAccess.Read))
                {
                    //XSSFWorkbook é€‚用XLSX格式，HSSFWorkbook é€‚用XLS格式
                    string fileExt = Path.GetExtension(file).ToLower();
                    if (fileExt == ".xls")
                    {
                        Workbook = new HSSFWorkbook(fileStream);
                    }
                    else if (fileExt == ".xlsx")
                    {
                        Workbook = new XSSFWorkbook(fileStream);
                    }
                    else
                    {
                        Workbook = null;
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            int count = Workbook.NumberOfSheets; //获取所有SheetName
            if (count < 3)
            {
                DataTable table = new DataTable();
                //定位在第一个sheet
                ISheet sheet = Workbook.GetSheetAt(0);
                //第一行为标题行
                IRow headerRow = sheet.GetRow(0);
                if (headerRow == null)
                {
                    return list;
                }
                int cellCount = headerRow.LastCellNum;
                int rowCount = sheet.LastRowNum;

                //循环添加标题列
                for (int i = headerRow.FirstCellNum; i < cellCount; i++)
                {
                    DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
                    table.Columns.Add(column);
                }

                //数据
                for (int i = (sheet.FirstRowNum + 1); i <= rowCount; i++)
                {
                    IRow row = sheet.GetRow(i);
                    DataRow dataRow = table.NewRow();
                    if (row != null && row.Cells.Count > 0)
                    {
                        for (int j = row.FirstCellNum; j < cellCount; j++)
                        {
                            if (row.GetCell(j) != null)
                            {
                                dataRow[j] = GetCellValue(row.GetCell(j)).Trim();
                            }
                        }
                    }
                    table.Rows.Add(dataRow);
                }
                //清除最后的空行
                for (int i = 0; i < table.Rows.Count; i++)
                {
                    bool isnull = true;
                    for (int j = 0; j < table.Columns.Count; j++)
                    {
                        if (table.Rows[i][j].ToString() != null && table.Rows[i][j].ToString() != "")
                        {
                            if (table.Rows[i][j].ToString().Trim() != "" && table.Rows[i][j].ToString().Trim() != null)
                            {
                                isnull = false;
                                break;
                            }
                        }
                    }
                    if (isnull)
                    {
                        table.Rows[i].Delete();
                        i = 0;
                    }
                }
                list.Add(table);
            }
            else
            {
                for (int m = 0; m < count; m++)
                {
                    DataTable table = new DataTable();
                    //定位在第一个sheet
                    ISheet sheet = Workbook.GetSheetAt(m);
                    //第一行为标题行
                    IRow headerRow = sheet.GetRow(0);
                    if (headerRow == null)
                    {
                        return list;
                    }
                    int cellCount = headerRow.LastCellNum;
                    int rowCount = sheet.LastRowNum;

                    //循环添加标题列
                    for (int i = headerRow.FirstCellNum; i < cellCount; i++)
                    {
                        DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
                        table.Columns.Add(column);
                    }

                    //数据
                    for (int i = (sheet.FirstRowNum + 1); i <= rowCount; i++)
                    {
                        IRow row = sheet.GetRow(i);
                        DataRow dataRow = table.NewRow();
                        if (row != null && row.Cells.Count > 0)
                        {
                            for (int j = row.FirstCellNum; j < cellCount; j++)
                            {
                                if (row.GetCell(j) != null)
                                {
                                    dataRow[j] = GetCellValue(row.GetCell(j)).Trim();
                                }
                            }
                        }
                        table.Rows.Add(dataRow);
                    }
                    //清除最后的空行
                    for (int i = 0; i < table.Rows.Count; i++)
                    {
                        bool isnull = true;
                        for (int j = 0; j < table.Columns.Count; j++)
                        {
                            if (table.Rows[i][j].ToString() != null && table.Rows[i][j].ToString() != "")
                            {
                                if (table.Rows[i][j].ToString().Trim() != "" && table.Rows[i][j].ToString().Trim() != null)
                                {
                                    isnull = false;
                                    break;
                                }
                            }
                        }
                        if (isnull)
                        {
                            table.Rows[i].Delete();
                            i = 0;
                        }
                    }
                    list.Add(table);
                }
            }
            return list;
        }


        /// <summary>
        /// èŽ·å–单元格类型
        /// </summary>
@@ -700,7 +864,195 @@
            return list;
        }

        /// <summary>
        /// Excel 3个Sheet æ•°æ®éªŒè¯
        /// </summary>
        /// <param name="file">导入路径(包含文件名与扩展名)</param>
        /// <returns></returns>
        public static List<ExcelErro> ExcelToThreeTableListErro(string file)
        {
            List<ExcelErro> list = new List<ExcelErro>();
            IWorkbook Workbook;
            bool iscell = false;
            int iscellCount = 0;
            try
            {
                using (FileStream fileStream = new FileStream(file, FileMode.Open, FileAccess.Read))
                {
                    //XSSFWorkbook é€‚用XLSX格式，HSSFWorkbook é€‚用XLS格式
                    string fileExt = Path.GetExtension(file).ToLower();
                    if (fileExt == ".xls")
                    {
                        Workbook = new HSSFWorkbook(fileStream);
                    }
                    else if (fileExt == ".xlsx")
                    {
                        Workbook = new XSSFWorkbook(fileStream);
                    }
                    else
                    {
                        Workbook = null;
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            for (int mm = 0; mm < 3; mm++)
            {
                DataTable table = new DataTable();
                //定位在第一个sheet
                ISheet sheet = Workbook.GetSheetAt(mm);
                //sheet.ShiftRows(sheet.FirstRowNum-1, sheet.LastRowNum,1);  
                //第一行为标题行
                IRow headerRow = sheet.GetRow(0);
                int cellCount = headerRow.LastCellNum;
                int rowCount0 = sheet.FirstRowNum;
                //int rowCount = sheet.LastRowNum;
                int rowCount = Rownum(headerRow, cellCount, sheet, sheet.LastRowNum); //获取删除空行之后的数据行 (只针对于最后数据项有空行的)
                if (rowCount - rowCount0 >= 1)
                {
                    //-------------判断必填项是否为空----------------//
                    //循环数据行
                    for (int i = (sheet.FirstRowNum + 1); i <= rowCount; i++)
                    {
                        iscell = false;
                        iscellCount = 0;
                        IRow row = sheet.GetRow(i);
                        //判断是否有空行
                        if (row == null)
                        {
                            ExcelErro erro = new ExcelErro();
                            erro.RoeNumber = (i + 1).ToString();
                            erro.ErrorField = "空行";
                            erro.ErrorCont = "空行";
                            list.Add(erro);
                            continue;
                        }
                        //判断是否存在空数据行
                        for (int c = 0; c < cellCount; c++)
                        {
                            if (GetCellValue(row.GetCell(c)).Trim() != null && GetCellValue(row.GetCell(c)).Trim() != "")
                            {
                                iscell = false;
                            }
                            else
                            {
                                iscell = true;
                                iscellCount++;
                            }
                        }
                        if (iscell == true && iscellCount == cellCount)  //空行
                        {
                            continue;
                        }
                        else
                        {
                            //循环标题列
                            for (int j = headerRow.FirstCellNum; j < cellCount; j++)
                            {
                                //获取单元格的填充色
                                string rgb = headerRow.Cells[j].CellStyle.FillForegroundColor.ToString();
                                if (rgb == "51")  //必填项字段列头填充色
                                {
                                    if (sheet.GetRow(i) == null || sheet.GetRow(i).ToString() == "")  //存在空行
                                    {
                                        ExcelErro erro = new ExcelErro();
                                        erro.RoeNumber = i.ToString();
                                        erro.ErrorField = "{第" + i.ToString() + "行}";
                                        erro.ErrorCont = "{第" + i.ToString() + "}行为空";
                                        list.Add(erro);
                                        break;
                                    }
                                    else
                                    {
                                        string cellValue = GetCellValue(sheet.GetRow(i).GetCell(j)).Trim();   //GetCell:返回包括空列值  Cells:只返回有值的列
                                        if (cellValue == "" || cellValue == null)
                                        {
                                            ExcelErro erro = new ExcelErro();
                                            erro.RoeNumber = (sheet.GetRow(i).RowNum + 1).ToString();
                                            erro.ErrorField = "{" + headerRow.Cells[j].StringCellValue + "}";
                                            erro.ErrorCont = mm == 0 ? "主表中：" + "{" + headerRow.Cells[j].StringCellValue + "}字段不能为空" : "子表中：" + "{" + headerRow.Cells[j].StringCellValue + "}字段不能为空";
                                            list.Add(erro);
                                        }
                                    }
                                }

                            }
                        }
                    }
                    //-------------判断唯一项是否重复----------------//
                    //循环添加标题列
                    for (int i = headerRow.FirstCellNum; i < cellCount; i++)
                    {
                        DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
                        table.Columns.Add(column);
                    }

                    //数据
                    for (int i = (sheet.FirstRowNum + 1); i <= rowCount; i++)
                    {
                        IRow row = sheet.GetRow(i);
                        DataRow dataRow = table.NewRow();
                        if (row != null)
                        {
                            for (int j = row.FirstCellNum; j < cellCount; j++)
                            {
                                if (row.GetCell(j) != null)
                                {
                                    dataRow[j] = GetCellValue(row.GetCell(j)).Trim();
                                }
                            }
                        }
                        table.Rows.Add(dataRow);
                    }
                    //清除最后的空行
                    for (int i = 0; i < table.Rows.Count; i++)
                    {
                        bool isnull = true;
                        for (int j = 0; j < table.Columns.Count; j++)
                        {
                            if (table.Rows[i][j].ToString() != null && table.Rows[i][j].ToString() != "")
                            {
                                if (table.Rows[i][j].ToString().Trim() != "" && table.Rows[i][j].ToString().Trim() != null)
                                {
                                    isnull = false;
                                    break;
                                }
                            }
                        }
                        if (isnull)
                        {
                            table.Rows[i].Delete();
                            i = 0;
                        }
                    }
                    DataView view = new DataView(table);
                    //获取所有唯一列
                    List<string> list0 = new List<string>();
                    //循环标题列
                    for (int j = headerRow.FirstCellNum; j < cellCount; j++)
                    {
                        bool isContain = headerRow.Cells[j].StringCellValue.Contains("唯一");  //标题列中的必填列
                        if (isContain == true)
                        {
                            //
                            if (view.ToTable(true, new string[] { "" + headerRow.Cells[j].StringCellValue + "" }).Rows.Count < table.Rows.Count)
                            {
                                ExcelErro erro = new ExcelErro();
                                erro.RoeNumber = "/";
                                erro.ErrorField = "{" + headerRow.Cells[j].StringCellValue + "}";
                                erro.ErrorCont = mm == 0 ? "主表中：" + "{" + headerRow.Cells[j].StringCellValue + "}字段有重复数据" : "子表中：" + "{" + headerRow.Cells[j].StringCellValue + "}字段有重复数据";
                                list.Add(erro);
                            }
                        }
                    }
                }
            }
            List<ExcelErro> stuList = list.OrderBy(s => s.RoeNumber).ToList<ExcelErro>();
            return list;
        }




 VueWebApi/Tools/ImportExcelData.cs

@@ -568,62 +568,117 @@
        {
            string Message = "";
            code = "";
            DataTable excelTable = new DataTable();
            excelTable = ImportExcel.ExcelToTable(FileCode);
            if (excelTable.Columns.Count != 10)
            List<DataTable> excelTable = new List<DataTable>();
            excelTable = ImportExcel.ExcelToThreeTableList(FileCode);
            if (excelTable.Count != 6)
            {
                code = "300";
                Message = "模板不符合规范,请检查列名";
                Message = "导入模板不符合规范,请检查Sheet数";
            }
            else if (excelTable.Columns[0].ColumnName != "序号")
            else if (excelTable[0].Columns.Count != 4)
            {
                code = "300";
                Message = "模板不符合规范,请检查列名";
                Message = "设备清单模板(设备类型)不符合规范,请检查列名字段数";
            }
            else if (excelTable.Columns[1].ColumnName != "设备编号(唯一)")
            else if (excelTable[1].Columns.Count != 5)
            {
                code = "300";
                Message = "模板不符合规范,请检查列名";
                Message = "设备清单模板(设备组)不符合规范,请检查列名字段数";
            }
            else if (excelTable.Columns[2].ColumnName != "设备名称")
            else if (excelTable[2].Columns.Count != 9)
            {
                code = "300";
                Message = "模板不符合规范,请检查列名";
                Message = "设备清单模板(设备清单)不符合规范,请检查列名字段数";
            }
            else if (excelTable.Columns[3].ColumnName != "设备类型")
            else if (excelTable[0].Columns[0].ColumnName != "序号")
            {
                code = "300";
                Message = "模板不符合规范,请检查列名";
                Message = "设备清单模板(设备类型)：表头信息不符合规范,第1列应为{序号}";
            }
            else if (excelTable.Columns[4].ColumnName != "生产车间")
            else if (excelTable[0].Columns[1].ColumnName != "设备类型编号(唯一)")
            {
                code = "300";
                Message = "模板不符合规范,请检查列名";
                Message = "设备清单模板(设备类型)：表头信息不符合规范,第2列应为{设备类型编号(唯一)}";
            }
            else if (excelTable.Columns[5].ColumnName != "所属产线")
            else if (excelTable[0].Columns[2].ColumnName != "设备类型名称")
            {
                code = "300";
                Message = "模板不符合规范,请检查列名";
                Message = "设备清单模板(设备类型)：表头信息不符合规范,第3列应为{设备类型名称}";
            }
            else if (excelTable.Columns[6].ColumnName != "投入日期")
            else if (excelTable[0].Columns[3].ColumnName != "设备类型描述")
            {
                code = "300";
                Message = "模板不符合规范,请检查列名";
                Message = "设备清单模板(设备类型)：表头信息不符合规范,第4列应为{设备类型描述}";
            }
            else if (excelTable.Columns[7].ColumnName != "稼动率（%）")
            else if (excelTable[1].Columns[0].ColumnName != "序号")
            {
                code = "300";
                Message = "模板不符合规范,请检查列名";
                Message = "设备清单模板(设备组)：表头信息不符合规范,第1列应为{序号}";
            }
            else if (excelTable.Columns[8].ColumnName != "使用状态")
            else if (excelTable[1].Columns[1].ColumnName != "设备组编号(唯一)")
            {
                code = "300";
                Message = "模板不符合规范,请检查列名";
                Message = "设备清单模板(设备组)：表头信息不符合规范,第2列应为{设备组编号(唯一)}";
            }
            else if (excelTable.Columns[9].ColumnName != "数采标识1")
            else if (excelTable[1].Columns[2].ColumnName != "设备组名称")
            {
                code = "300";
                Message = "模板不符合规范,请检查列名";
                Message = "设备清单模板(设备组)：表头信息不符合规范,第3列应为{设备组名称}";
            }
            else if (excelTable[1].Columns[3].ColumnName != "设备类型编号(唯一)")
            {
                code = "300";
                Message = "设备清单模板(设备组)：表头信息不符合规范,第4列应为{设备类型编号(唯一)}";
            }
            else if (excelTable[1].Columns[4].ColumnName != "设备组描述")
            {
                code = "300";
                Message = "设备清单模板(设备组)：表头信息不符合规范,第5列应为{设备组描述}";
            }
            else if (excelTable[2].Columns[0].ColumnName != "序号")
            {
                code = "300";
                Message = "设备清单模板(设备清单)：表头信息不符合规范,第1列应为{序号}";
            }
            else if (excelTable[2].Columns[1].ColumnName != "设备编号(唯一)")
            {
                code = "300";
                Message = "设备清单模板(设备清单)：表头信息不符合规范,第1列应为{设备编号(唯一)}";
            }
            else if (excelTable[2].Columns[2].ColumnName != "设备名称")
            {
                code = "300";
                Message = "设备清单模板(设备清单)：表头信息不符合规范,第1列应为{设备名称}";
            }
            else if (excelTable[2].Columns[3].ColumnName != "设备类型编号(唯一)")
            {
                code = "300";
                Message = "设备清单模板(设备清单)：表头信息不符合规范,第1列应为{设备类型编号(唯一)}";
            }
            else if (excelTable[2].Columns[4].ColumnName != "设备组编号(唯一)")
            {
                code = "300";
                Message = "设备清单模板(设备清单)：表头信息不符合规范,第1列应为{设备组编号(唯一)}";
            }
            else if (excelTable[2].Columns[5].ColumnName != "投入日期")
            {
                code = "300";
                Message = "设备清单模板(设备清单)：表头信息不符合规范,第1列应为{投入日期}";
            }
            else if (excelTable[2].Columns[6].ColumnName != "生产车间")
            {
                code = "300";
                Message = "设备清单模板(设备清单)：表头信息不符合规范,第1列应为{生产车间}";
            }
            else if (excelTable[2].Columns[7].ColumnName != "使用状态")
            {
                code = "300";
                Message = "设备清单模板(设备清单)：表头信息不符合规范,第1列应为{使用状态}";
            }
            else if (excelTable[2].Columns[8].ColumnName != "稼动率（%）")
            {
                code = "300";
                Message = "设备清单模板(设备清单)：表头信息不符合规范,第1列应为{稼动率（%）}";
            }
            else
            {
@@ -2113,9 +2168,127 @@
            StuCode = "";
            count = 0;
            string sql = "";
            var dynamicParams = new DynamicParameters();
            DataTable dt;
            List<ExcelErro> list = new List<ExcelErro>();

            List<DataTable> excelTable = new List<DataTable>();
            list = ImportExcel.ExcelToThreeTableListErro(FileCode);    //验证Excel数据必填字段是否为空、唯一字段是否重复
            excelTable = ImportExcel.ExcelToThreeTableList(FileCode);  //获取Excel数据
            //设备类型表,判断设备类型是否存在
            for (int i = 0; i < excelTable[0].Rows.Count; i++)
            {
                if (excelTable[0].Rows[i][1].ToString().Trim() != null && excelTable[0].Rows[i][1].ToString().Trim() != "")
                {
                    sql = @"select *  from TEqpType where code=@code";
                    dynamicParams.Add("@code", excelTable[0].Rows[i][1].ToString().Trim());
                    dt = DapperHelper.selectdata(sql, dynamicParams);
                    if (dt.Rows.Count > 0)
                    {
                        ExcelErro erro = new ExcelErro();
                        erro.RoeNumber = "/";
                        erro.ErrorField = "{设备类型编号(唯一)}";
                        erro.ErrorCont = "设备类型表:{设备类型编号(唯一)}字段" + excelTable[0].Rows[i][1].ToString().Trim() + "已存在";
                        list.Add(erro);
                    }
                }
            }
            //设备组表,判断设备组是否存在
            for (int j = 0; j < excelTable[1].Rows.Count; j++)
            {
                if (excelTable[1].Rows[j][1].ToString().Trim() != null && excelTable[1].Rows[j][1].ToString().Trim() != "")
                {
                    sql = @"select *  from TEqpGroup where code=@code";
                    dynamicParams.Add("@code", excelTable[1].Rows[j][1].ToString().Trim());
                    dt = DapperHelper.selectdata(sql, dynamicParams);
                    if (dt.Rows.Count > 0)
                    {
                        ExcelErro erro = new ExcelErro();
                        erro.RoeNumber = "/";
                        erro.ErrorField = "{设备组编号(唯一)}";
                        erro.ErrorCont = "设备组表:{设备组编号(唯一)}字段" + excelTable[1].Rows[j][1].ToString().Trim() + "已存在";
                        list.Add(erro);
                    }
                }
            }
            //判断子表外键不存在于主表主键中的数据
            var dt3 = from r in excelTable[1].AsEnumerable()
                      where !(
                          from rr in excelTable[0].AsEnumerable()
                          select rr.Field<string>("设备类型编号(唯一)")
                      ).Contains(r.Field<string>("设备类型编号(唯一)"))
                      select r;
            List<DataRow> listRow = dt3.ToList();
            if (listRow.Count > 0)
            {
                ExcelErro erro = new ExcelErro();
                erro.RoeNumber = "/";
                erro.ErrorField = "{设备类型编号(唯一)}";
                erro.ErrorCont = "设备组信息:{设备类型编号(唯一)}字段中有值在设备类型信息:{设备类型编号(唯一)}中不存在";
                list.Add(erro);
            }
            //设备清单表
            for (int k = 0; k < excelTable[2].Rows.Count; k++)
            {
                if (excelTable[2].Rows[k][1].ToString().Trim() != null && excelTable[2].Rows[k][1].ToString().Trim() != "")
                {
                    sql = @"select *  from TEqpInfo where code=@code";
                    dynamicParams.Add("@code", excelTable[2].Rows[k][1].ToString().Trim());
                    dt = DapperHelper.selectdata(sql, dynamicParams);
                    if (dt.Rows.Count > 0)
                    {
                        ExcelErro erro = new ExcelErro();
                        erro.RoeNumber = "/";
                        erro.ErrorField = "{设备编号(唯一)}";
                        erro.ErrorCont = "设备表:{设备编号(唯一)}字段" + excelTable[2].Rows[k][1].ToString().Trim() + "已存在";
                        list.Add(erro);
                    }
                    //判断设备组是否对应正确的设备类型
                    var yourGetData = excelTable[1].AsEnumerable().Where(x => (x.Field<string>("设备类型编号(唯一)") == excelTable[2].Rows[k][3].ToString().Trim()) && x.Field<string>("设备组编号(唯一)") == excelTable[2].Rows[k][4].ToString().Trim()).CopyToDataTable();
                    if (yourGetData.Rows.Count <= 0)
                    {
                        ExcelErro erro = new ExcelErro();
                        erro.RoeNumber = "/";
                        erro.ErrorField = "{设备类型编号(唯一)}/{设备组编号(唯一)}";
                        erro.ErrorCont = "设备表:{设备编号(唯一)}字段" + excelTable[2].Rows[k][1].ToString().Trim() + "对应{设备类型编号(唯一)}:" + excelTable[2].Rows[k][3].ToString().Trim() + "/{设备组编号(唯一)}:" + excelTable[2].Rows[k][4].ToString().Trim() + "与设备组清单中设置对不上";
                        list.Add(erro);
                    }
                }
            }
            //车间判断
            for (int m = 0; m < excelTable[1].Rows.Count; m++)
            {
                if (excelTable[1].Rows[m][6].ToString().Trim() != null && excelTable[1].Rows[m][6].ToString().Trim() != "")
                {
                    sql = @"select *  from TOrganization where org_name=@code and description='W'";
                    dynamicParams.Add("@code", excelTable[1].Rows[m][6].ToString().Trim());
                    dt = DapperHelper.selectdata(sql, dynamicParams);
                    if (dt.Rows.Count <= 0)
                    {
                        ExcelErro erro = new ExcelErro();
                        erro.RoeNumber = "/";
                        erro.ErrorField = "{生产车间}";
                        erro.ErrorCont = "设备表:{生产车间}字段" + excelTable[1].Rows[m][6].ToString().Trim() + "不存在";
                        list.Add(erro);
                    }
                }
            }
            if (list.Count > 0)
            {
                int index = 0;
                foreach (ExcelErro item in list)
                {
                    index++;
                    item.Seq = index.ToString();
                }
                StuCode = "301";
                message = "数据验证失败";
            }
            else
            {
                StuCode = "200";
                message = "数据验证成功";
                count = excelTable[0].Rows.Count + excelTable[1].Rows.Count;
            }
            return list;
        }
        #endregion
@@ -2975,12 +3148,96 @@
        #region【Excel数据上传,设备清单】
        public static string NineSubmit(string FileCode, string User, out string StuCode)
        {
            string message = "";
            StuCode = "";
            string message = ""; StuCode = "";
            string sql = "";
            List<object> list = new List<object>();
            var dynamicParams = new DynamicParameters();
            try
            {
                list.Clear();
                List<DataTable> excelTable = new List<DataTable>();
                excelTable = ImportExcel.ExcelToThreeTableList(FileCode);
                //写入设备类型
                for (int i = 0; i < excelTable[0].Rows.Count; i++)
                {
                    sql = @"insert into TEqpType(code,name,lm_user,lm_date,remark) 
                            values(@code,@name,@lm_user,@lm_date,@remark)";
                    list.Add(new
                    {
                        str = sql,
                        parm = new
                        {
                            code = excelTable[0].Rows[i][1].ToString().Trim(),
                            name = excelTable[0].Rows[i][2].ToString().Trim(),
                            lm_user = User,
                            lm_date = DateTime.Now.ToString(),
                            remark = excelTable[0].Rows[i][3].ToString().Trim()

            }
                    });
                }
                //写入设备组
                for (int j = 0; j < excelTable[1].Rows.Count; j++)
                {
                    sql = @"insert into TEqpGroup(code,name,lm_user,lm_date,remark,eqptype_code) 
                            values(@code,@name,@lm_user,@lm_date,@remark,@eqptype_code)";
                    list.Add(new
                    {
                        str = sql,
                        parm = new
                        {
                            code = excelTable[1].Rows[j][1].ToString().Trim(),
                            name = excelTable[1].Rows[j][2].ToString().Trim(),
                            lm_user = User,
                            lm_date = DateTime.Now.ToString(),
                            remark = excelTable[1].Rows[j][4].ToString().Trim(),
                            eqptype_code = excelTable[1].Rows[j][3].ToString().Trim()

                        }
                    });
                }
                //导入设备清单
                for (int k = 0; k < excelTable[2].Rows.Count; k++)
                {
                    //获取车间编码
                    string sql0 = @"select code,name from TOrganization where org_name=@wkshp and description='W' and is_delete<>'1'";
                    dynamicParams.Add("@wkshp", excelTable[2].Rows[k][6].ToString());
                    var data = DapperHelper.selectdata(sql0, dynamicParams);

                    sql = @"insert into TEqpInfo(code,name,eqptype_code,eqpgroup_code,wksp_code,input_date,operation_ration,enable,opc_uom,lm_user,lm_date) 
                            values(@devicecode,@devicename,@devicetype,@devicegroup,@workshop,@importdate,@ratio,@status,@uom,@lm_user,@lm_date)";
                    list.Add(new
                    {
                        str = sql,
                        parm = new
                        {
                            devicecode = excelTable[2].Rows[k][1].ToString().Trim(),
                            devicename = excelTable[0].Rows[k][2].ToString().Trim(),
                            devicetype = excelTable[0].Rows[k][3].ToString().Trim(),
                            devicegroup = excelTable[0].Rows[k][4].ToString().Trim(),
                            workshop = excelTable[0].Rows[k][6].ToString().Trim(),
                            importdate = excelTable[0].Rows[k][5].ToString().Trim(),
                            ratio = excelTable[0].Rows[k][8].ToString().Trim(),
                            status = excelTable[0].Rows[k][7].ToString().Trim(),
                            uom = "台",
                            lm_user = User,
                            lm_date = DateTime.Now.ToString()
                        }
                    });
                }

                bool aa = DapperHelper.DoTransaction(list);
                if (aa)
                {
                    StuCode = "200";
                    message = "导入成功！";
                }
                else
                {
                    StuCode = "300";
                    message = "导入失败！";
                }
            }
            catch (Exception e)
            {
                StuCode = "300";

 VueWebApi/VueWebApi.csproj

@@ -523,6 +523,7 @@
    <Content Include="Excel\设备点检项目.xls" />
    <Content Include="Excel\设备保养项目.xls" />
    <Content Include="Excel\节拍工价.xls" />
    <Content Include="Excel\设备清单.xls" />
    <None Include="Scripts\jquery-3.4.1.intellisense.js" />
    <Content Include="Scripts\jquery-3.4.1.js" />
    <Content Include="Scripts\jquery-3.4.1.min.js" />

 VueWebApi/obj/Release/DesignTimeResolveAssemblyReferencesInput.cache

Binary files differ