.net从项目根目录读取Excel工作簿内容,随机抽取

        //交互接口
        /// <summary>
        /// 选择运输方案(批量生成)
        /// </summary>
        /// <param name="caseId">案例</param>
        /// <returns></returns>
        [HttpPost]
        public ActionResult BatchAddCaseTransportation(string caseId, CaseTransportation proprice, List<CaseTransportationMoney> money, int num)
        {
            var result = new ResultDto();

            var Names = new List<string>(num);

            var caseTran = new List<CaseTransportation>();

            var moneys = new List<CaseTransportationMoney>();

            var floder = Server.MapPath("~/Data/Template/管理员/");
            var path = floder + "企业名称.xls";

            proprice.CaseId = caseId;

            for (int i = 0; i < num; i++)
            {
                var str = GetRandomComPanyName(path, Names);

                var Id = IdentityGenerator.NewSequentialGuid().ToString();

                var CaseTransportation = new CaseTransportation
                {
                    Id = Id,
                    Name = Convert.ToString(str.Data),
                    CaseId = caseId,
                    Calculation = proprice.Calculation,
                    CasePropriceId = proprice.CasePropriceId,
                    CoopCycle = proprice.CoopCycle,
                    CSJTranCycle1 = proprice.CSJTranCycle1,
                    JJJTranCycle2 = proprice.JJJTranCycle2,
                    CYTranCycle3 = proprice.CYTranCycle3,
                    LossRate = proprice.LossRate,
                    Number = proprice.Number,
                    CreateDate = DateTime.Now,
                    UpdateDate = DateTime.Now,
                };

                foreach (var tans in money)
                {
                    var CaseTransportationMoney = new CaseTransportationMoney
                    {
                        Id = IdentityGenerator.NewSequentialGuid().ToString(),
                        CaseTransportationId = Id,
                        MaxSection = tans.MaxSection,
                        MinSection = tans.MinSection,
                        Money = tans.Money,
                        Region = tans.Region,
                        CreateDate = DateTime.Now,
                        UpdateDate = DateTime.Now,
                    };

                    moneys.Add(CaseTransportationMoney);
                }

                Names.Add(CaseTransportation.Name);
                caseTran.Add(CaseTransportation);
            }

            _dbContext.CaseTransportations.BulkInsert(caseTran);
            _dbContext.CaseTransportationMoneys.BulkInsert(moneys);

            result.Success = true;

            return Json(result);
        }

//回调接口(抽取)
  #region 随机抽取公司名称

        /// <summary>
        /// 获取随机公司名称
        /// </summary>
        /// <returns></returns>
        public ResultDto GetRandomComPanyName(string path, List<string> dataTableName)
        {
            var result = new ResultDto();

            var random = "";

            var connectinString = "";

            try
            {
                #region 判断Excel版本

                var workName = "Sheet1";      //工作簿名称

                //Office2003（Microsoft.Jet.Oledb.4.0）
                string strConn2003 = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'", path);

                //Office2007（Microsoft.ACE.OLEDB.12.0）
                string strConn2007 = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0;HDR=Yes;IMEX=1;'", path);

                //Office2010（Microsoft.ACE.OLEDB.12.0）
                string strConn2010 = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0;HDR=Yes;IMEX=1;'", path);

                try
                {
                    using (OleDbConnection conn = new OleDbConnection(strConn2003))
                    {
                        conn.Open();
                        connectinString = strConn2003;
                    }
                }
                catch
                {
                    try
                    {
                        using (OleDbConnection conn = new OleDbConnection(strConn2007))
                        {
                            conn.Open();
                            connectinString = strConn2007;
                        }
                    }
                    catch
                    {
                        try
                        {
                            using (OleDbConnection conn = new OleDbConnection(strConn2010))
                            {
                                conn.Open();
                                connectinString = strConn2010;
                            }
                        }
                        catch (Exception e2)
                        {
                            result.Success = false;
                            result.Message = "操作失败,Excel版本不符合03,07,10版本";
                            result.ExMessage = e2.Message;
                            return result;
                        }
                    }
                }

                #endregion

                using (OleDbConnection connection = new OleDbConnection(connectinString))
                {
                    connection.Open();

                    DataTable dataTable = new DataTable();

                    string strCom = " SELECT * FROM [" + workName + "]";

                    using (OleDbDataAdapter myCommand = new OleDbDataAdapter(strCom, connection))
                    {
                        try
                        {
                            myCommand.Fill(dataTable);
                        }
                        catch
                        {
                            strCom = " SELECT * FROM [" + workName + "$" + "]";

                            using (OleDbDataAdapter myCommandTryAgain = new OleDbDataAdapter(strCom, connection))
                            {
                                myCommandTryAgain.Fill(dataTable);
                            }
                        }

                        if (dataTable.Rows.Count < 1)
                        {
                            result.Success = false;
                            result.Message = "未找到可抽取公司名称列表!";
                            return result;
                        }

                        Random dom = new Random();

                        for (int i = 0; i < 1; i++)
                        {
                            var number = dom.Next(0, 999);

                            random = dataTable.Rows[number].ItemArray[0].ToString();

                            if (dataTableName.Contains(random))
                            {
                                i--;
                            }
                        }
                    }
                }

                result.Data = random;
            }
            catch (Exception e)
            {
                result.Success = false;
                result.ExMessage = e.Message;
            }

            return result;
        }

        #endregion