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

忘是亡心i 2021-12-12 10:39 361阅读 0赞

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

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

![19134917-5003fadacf76474b89d0cd613b0e775e.jpg][]

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

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

![ContractedBlock.gif][] ![ExpandedBlockStart.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);
                }
            }

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

欢迎高手指点。。

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

[19134917-5003fadacf76474b89d0cd613b0e775e.jpg]: /images/20211211/170c037b09364c45b53f001e5d22a192.png
[ContractedBlock.gif]: https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif
[ExpandedBlockStart.gif]: /images/20211211/5bd0ae9d56fc4e28aa4a9ec55d1885e7.png