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));
}