public static IEnumerable <DataSet> ReadExcelFile(string excelPath)
        {
            _excelConn    = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\"Excel 8.0; HDR = YES; IMEX = 1\";", excelPath);
            _excelConnFix = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0; HDR = YES; IMEX = 1\";", excelPath);
            OleDbConnection conn = OpenConn(_excelConnFix);

            if (conn == null)
            {
                throw new Exception("无法读取Excel文件.请检查Excel文件格式是否合法");
            }
            var            sheets     = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new[] { null, null, null, "TABLE" });
            List <DataSet> result     = new List <DataSet>();
            List <string>  sheetCache = new List <string>();

            foreach (DataRow sheet in sheets.Rows)
            {
                string tableName = RowUtil.GetString(sheet, "TABLE_NAME").Substring(0, RowUtil.GetString(sheet, "TABLE_NAME").IndexOf('$'));
                if (sheetCache.Contains(tableName))
                {
                    continue;
                }
                sheetCache.Add(tableName);
                DataSet ds = new DataSet(tableName);
                try
                {
                    string           excelSql = $"select * from [{tableName}$]";
                    OleDbDataAdapter da       = new OleDbDataAdapter(excelSql, conn);
                    da.Fill(ds);
                    result.Add(ds);
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
            return(result);
        }
 public async Task <IActionResult> UploadQuestionFile([FromForm] FileUpload fileUpload, [FromServices] IWebHostEnvironment enviroment)
 {
     if (ModelState.IsValid)
     {
         var filePath = Path.Combine(enviroment.WebRootPath, "Upload", loginUser.UserName, DateTime.Now.ToString("yyyyMMdd"));
         if (!Directory.Exists(filePath))
         {
             Directory.CreateDirectory(filePath);
         }
         var fileName = string.Format("{0}{1}", Guid.NewGuid(), Path.GetExtension(fileUpload.UploadFile.FileName));
         fileName = Path.Combine(filePath, fileName);
         using (FileStream fs = new FileStream(fileName, FileMode.Append, FileAccess.Write))
         {
             fileUpload.UploadFile.CopyTo(fs);
         }
         IEnumerable <DataSet> excelData = ReadQuestionFromExcel.ReadExcelFile(fileName);
         //解析Excel数据
         foreach (DataSet ds in excelData)
         {
             DataTable excelTable = ds.Tables[0];
             if (excelTable == null || excelTable.Rows.Count < 2)
             {
                 continue;
             }
             DataRow    titleRow = excelTable.Rows[0];        //拿到标题行
             int        columnNum = excelTable.Columns.Count; //得到列数
             int        questionColumnIndex = 0, answerColumnIndex = 0;
             List <int> solutionColumnIndexs = new List <int>();
             //得到题目列,答案列,正确答案列的索引
             for (int i = 0; i < columnNum; i++)
             {
                 string rowValue = RowUtil.GetString(titleRow, excelTable.Columns[i].ColumnName);
                 if (rowValue.Equals("题目"))
                 {
                     questionColumnIndex = i;
                 }
                 if (rowValue.StartsWith("选项"))
                 {
                     if (!solutionColumnIndexs.Contains(i))
                     {
                         solutionColumnIndexs.Add(i);
                     }
                 }
                 if (rowValue.Equals("答案"))
                 {
                     answerColumnIndex = i;
                 }
             }
             DataColumn   questionColumn  = excelTable.Columns[questionColumnIndex];
             DataColumn   answerColumn    = excelTable.Columns[answerColumnIndex];
             DataColumn[] solutionColumns = new DataColumn[solutionColumnIndexs.Count];
             //移除标题行
             excelTable.Rows.Remove(titleRow);
             for (int i = 0; i < solutionColumnIndexs.Count; i++)
             {
                 solutionColumns[i] = excelTable.Columns[solutionColumnIndexs[i]];
             }
             //生成问题和答案
             for (int i = 0; i < excelTable.Rows.Count; i++)
             {
                 DataRow currentRow = excelTable.Rows[i];
                 //得到题目
                 string questionName = RowUtil.GetString(currentRow, questionColumn.ColumnName);
                 if (string.IsNullOrEmpty(questionName))
                 {
                     continue;
                 }
                 //得到正确答案序号
                 string        answer    = RowUtil.GetString(currentRow, answerColumn.ColumnName);
                 List <string> solutions = new List <string>();
                 //得到所有答案
                 foreach (DataColumn column in solutionColumns)
                 {
                     string solution = RowUtil.GetString(currentRow, column.ColumnName);
                     solutions.Add(solution);
                 }
                 CreateQuestionInfo questionInfo = new CreateQuestionInfo();
                 questionInfo.Name              = questionName;
                 questionInfo.QuestionType      = QuestionType.Radio;
                 questionInfo.QuestionTypeInt   = (int)questionInfo.QuestionType;
                 questionInfo.QuestionScore     = 1;
                 questionInfo.QuestionGroupName = fileUpload.UploadFile.FileName.Replace(Path.GetExtension(fileUpload.UploadFile.FileName), null);
                 questionInfo.SolutionNames     = solutions.AsEnumerable();
                 List <double> solutionScore = new List <double>();
                 string        regexStr      = @"^[A-Za-z]+";
                 //计算选项得分
                 foreach (string item in questionInfo.SolutionNames)
                 {
                     //如果正确答案是A~Z,就判断所有选项的开头,否则判断答案是否包含
                     if (RegularUtil.Regex(regexStr, answer))
                     {
                         if (item.StartsWith(answer)) //选项的开头和答案一致,证明是正确答案
                         {
                             solutionScore.Add(1);
                         }
                         else
                         {
                             solutionScore.Add(0);
                         }
                     }
                     else
                     {
                         if (item.Contains(answer)) //选项包含答案,就认为是正确答案
                         {
                             solutionScore.Add(1);
                         }
                         else
                         {
                             solutionScore.Add(0);
                         }
                     }
                 }
                 questionInfo.SolutionScore = solutionScore.AsEnumerable();
                 //去掉选项前边的序号
                 List <string> solutons1 = new List <string>();
                 foreach (string solution in solutions)
                 {
                     int    index         = solution.IndexOf('.');
                     string solutionValue = solution.Substring(index + 1, solution.Length - index - 1);
                     solutons1.Add(solutionValue);
                 }
                 questionInfo.SolutionNames = solutons1.AsEnumerable();
                 CreateQuestion(questionInfo);
             }
         }
         return(RedirectToAction(nameof(Index)));
     }
     ModelState.AddModelError("UploadFile", "目前只支持Excel文件(.xls/.xlsx)哦");
     return(View(fileUpload));
 }