说说关于.net中导入excel的问题(项目中用到的)
好久没写博客了,上个月到现在在赶一个小项目,天天忙,好多时候也加班,有时间了知识来园子里逛逛,看看高手写的,自己学习学习。
由于做项目也没多久,新手,只能边学习边做项目。在项目中用到一个导入excel的问题,用到的excel模板大概是这样的,如下图
其他都一样,但是后边的选项(选项表tbl_QuestionOption不确定,每个题目的选项都不确定。还有一个问题就是参考答案要跟后边对应,根据ABCD确定那个是参考答案,将参考答案存到问题表(tbl_Question)表,题目的题型有01单选,02多选,03判断,04简答,先说说思路吧。
开始要根据文件的扩展名确定是03版还是07版,然后读取excel内容(只有一个sheet),将读取到的被容放在datatable中,在根据实际情况循环每行每列,执行相应操作。
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
public void excelImport(string path, string extName) { DataSet ds = new DataSet(); OleDbConnection Conn = null; if (extName.Equals(".xls")) { //03版excel,加上HDR=YES;IMEX=1;解决数字、文本混合录入出现null值的情况 Conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";" + "Extended Properties='Excel 8.0;HDR=YES;IMEX=1;'"); } else { Conn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties='Excel 12.0;HDR=YES;IMEX=1;'");//07的连接串 } Conn.Open(); DataTable schemaTable = Conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null); string tableName = "考核题目导入模版$"; DataRow[] drs = schemaTable.Select("TABLE_NAME = '" + tableName + "'"); if (drs == null || drs.Length == 0) { throw new Exception("模版中不存在名为 考核题目导入模版 的Sheet。"); } string str = "select * from [" + tableName + "]"; OleDbCommand objCmd = new OleDbCommand(str, Conn); OleDbDataAdapter myData = new OleDbDataAdapter(str, Conn); myData.Fill(ds, tableName);//填充数据 DataTable sub = new DataTable(); sub = ds.Tables[0];//.Tables[tableName].Clone(); int recordcount = 0; int fail = 0; StringBuilder sbMessage = new StringBuilder(); IEntityDbHelper dbHelper = Profile.NewDbHelper(); //试题类型 01:单选 02:多选 03:判断 04:简答 try { //选项开始列所在索引 const int OptionStartIndex = 7; for (int i = OptionStartIndex; i < 26 + OptionStartIndex; i++) { //65 -- A myDictionary.Add(i, ((char)(65 + i - OptionStartIndex)).ToString()); } DataTable dt = Profile.ServiceFactory.GetAssessQuestionService().GetIsHasQuestion(hdItemId.Value); for (int i = 0; i < sub.Rows.Count; i++) { dbHelper.BeginTransaction();//开始事务 try { string questionTitle = sub.Rows[i][1].ToString().Trim(); if (string.IsNullOrEmpty(questionTitle)) { break; } AssessQuestion aq = new AssessQuestion(dbHelper); string questionId = Guid.NewGuid().ToString(); string questionTypeId = sub.Rows[i][2].ToString().Substring(0, 2);//问题类型 string difficult = sub.Rows[i][3].ToString();//难易度 string answer = sub.Rows[i][5].ToString().Trim();//数据库读取到的参考答案 string referenceAnswerIds = string.Empty; string answerDescription = sub.Rows[i][6].ToString().Trim();//答案说明 DataRow[] dr = dt.Select("QuestionTitle='" + questionTitle + "'"); if (dr == null || dr.Length == 0)//题库中没有该题目时 { aq.Id = questionId; aq.AssessItemId = hdItemId.Value; aq.QuestionTitle = questionTitle; } else//题库中要该题目 { questionId = dr[0]["QuestionId"].ToString(); //先删除已有的选项 Sql sql = new Sql(); sql.SetText(@"DELETE FROM tbl_Assess_QuestionOption WHERE QuestionId=?"); sql.Parameters.Add("QuestionId", questionId); dbHelper.Execute(sql); } //单选 多选 if (questionTypeId.Equals(QuestionType.DANXUAN) || questionTypeId.Equals(QuestionType.DUOXUAN)) { char[] ch = answer.ToCharArray(); int columnCount = sub.Columns.Count; //选项 for (int j = OptionStartIndex; j < columnCount; j++) { string option = sub.Rows[i][j].ToString().Trim(); if (string.IsNullOrEmpty(option)) { break; } AssessQuestionOption aqo = new AssessQuestionOption(dbHelper); aqo.Id = Guid.NewGuid().ToString(); aqo.QuestionId = questionId; List<string> optionItem = option.Split("//".ToCharArray()).ToList(); optionItem.RemoveAll(item => string.IsNullOrEmpty(item)); //选项标题 string optionTitle = string.Empty; //选项说明 string optionDesc = string.Empty; if (optionItem.Count > 0) { optionTitle = optionItem[0]; if (optionItem.Count > 1) { optionDesc = optionItem[1]; } } aqo.OptionTitle = optionTitle; aqo.State = 0; aqo.SequenceNumber = j - 7; aqo.Insert(); //如果参考答案中包括该选项 if (answer.Contains(myDictionary[j])) { referenceAnswerIds += aqo.Id; referenceAnswerIds += ","; } } if (referenceAnswerIds.Length > 0) { referenceAnswerIds = referenceAnswerIds.Substring(0, referenceAnswerIds.Length - 1); } aq.ReferenceAnswer = referenceAnswerIds; } else { //判断 if (questionTypeId.Equals(QuestionType.PANDUAN)) { AssessQuestionOption aqo = new AssessQuestionOption(dbHelper); aqo.Id = Guid.NewGuid().ToString(); aqo.QuestionId = questionId; aqo.OptionTitle = "对"; aqo.State = 0; aqo.SequenceNumber = 0; aqo.Insert(); if (answer.Equals("A")) { referenceAnswerIds = aqo.Id; } aqo = new AssessQuestionOption(dbHelper); aqo.Id = Guid.NewGuid().ToString(); aqo.QuestionId = questionId; aqo.OptionTitle = "错"; aqo.State = 0; aqo.SequenceNumber = 1; aqo.Insert(); if (answer.Equals("B")) { referenceAnswerIds = aqo.Id; } } else { referenceAnswerIds = answer; } } if (dr == null || dr.Length == 0) { aq.Difficult = sub.Rows[i][3].ToString(); aq.QuestionScore = 0; aq.QuestionTypeId = questionTypeId; aq.AnswerDescription = sub.Rows[i][6].ToString().Trim(); aq.ReferenceAnswer = referenceAnswerIds; aq.QuestionSource = 1; aq.CreateUserId = AssessUser.Id; aq.CreateDateTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"); aq.Insert(); recordcount++; } else { //数据库有该题目时,更新 Sql sql = new Sql(); sql.SetText(@"UPDATE tbl_Assess_Question SET QuestionTitle=?,ReferenceAnswer=?,AnswerDescription=?, Difficult=?,UpdateUserId=?,UpdateDateTime=? WHERE Id=?"); sql.Parameters.Add("QuestionTitle", questionTitle); sql.Parameters.Add("ReferenceAnswer", referenceAnswerIds); sql.Parameters.Add("AnswerDescription", answerDescription); sql.Parameters.Add("Difficult", difficult); sql.Parameters.Add("UpdateUserId", AssessUser.Id); sql.Parameters.Add("UpdateDateTime", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")); sql.Parameters.Add("Id", questionId); dbHelper.Execute(sql); recordcount++; } dbHelper.CommitTransaction(); } catch (Exception ex) { fail++; sbMessage.Append("题号为"+(recordcount+1) + ":"); sbMessage.Append(ex.Message.ToString().Replace("\n", "").Replace("\r", "").Replace("\"", "\\\"").Replace("'", "\'")); sbMessage.Append("\\n"); dbHelper.RollbackTransaction(); Error(ex); } } if (sbMessage.Length > 0) { //sbMessage = sbMessage.Remove(sbMessage.Length - 2, 2); ShowMessage("成功导入" + recordcount + "条记录\\n导入失败" + fail + "条记录\\n" + sbMessage); } else { ShowMessage("成功导入" + recordcount + "条记录\\n失败0条"); } } catch (Exception ex) { MeaningStudio.Framework.Trace.WriteLine(MeaningStudio.Framework.TraceSwitchType.Error, "Import", ex.StackTrace); } }
在导入的过程中还要根据题目标题判断数据库中是否有该题,有的话执行更新操作。
欢迎高手指点。。