说说关于.net中导入excel的问题(项目中用到的)

     好久没写博客了，上个月到现在在赶一个小项目，天天忙，好多时候也加班，有时间了知识来园子里逛逛，看看高手写的，自己学习学习。

     由于做项目也没多久，新手，只能边学习边做项目。在项目中用到一个导入excel的问题，用到的excel模板大概是这样的，如下图

其他都一样，但是后边的选项（选项表tbl_QuestionOption不确定，每个题目的选项都不确定。还有一个问题就是参考答案要跟后边对应，根据ABCD确定那个是参考答案，将参考答案存到问题表（tbl_Question)表，题目的题型有01单选，02多选，03判断，04简答，先说说思路吧。

 开始要根据文件的扩展名确定是03版还是07版，然后读取excel内容（只有一个sheet)，将读取到的被容放在datatable中，在根据实际情况循环每行每列，执行相应操作。

View Code

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

 

在导入的过程中还要根据题目标题判断数据库中是否有该题，有的话执行更新操作。

欢迎高手指点。。

转载于:https://www.cnblogs.com/liu411323/archive/2012/12/19/2824852.html