用Visual Studio(C#) 实现Excel数据导入(映射)

原创 2016年06月01日 16:21:16

本方法缺点:数据量有一定的限制,若列数据过多,则只能导入2000条左右。此方法必须对表格中的列进行类声明。

1.get:

 public virtual async Task<ActionResult> ImportStudentMessage(int page = 1)
        {
            return View(await db.StudentInforms.GetPagedDataAsyns(new PageDataParameter<StudentInform, Guid>(page: page)));
        }
``

2.视图页面

@using (Html.BeginForm("ImportStudentMessage", "StudentInforms", FormMethod.Post, new { enctype = "multipart/form-data" }))
{
    <div form-group>
        <div class="row">
            <div class="clearfix">
                <div class="col-xs-4">
                    <div class="pull-left">
                    <input name="file" type="file" class="form-control" accept=".xls,.xlsx" />
                        </div>
                    <div class="pull-right">
                    <input type="submit" value="导入" class="btn btn-success btn-sm" />
                    @Html.ValidationMessage("file", "", new { @class = "text-danger" })
                        </div>
                </div>
            </div>
        </div>
    </div>
}

3.post方法

[HttpPost]
        //[Authorize]
        public virtual ActionResult ImportStudentMessage(string file, bool stopWhenError = false)
        {
            if (Request.Files.Count > 0)
            {
                if (Request.Files[0].ContentLength > 0)
                {
                    string contentType = Request.Files[0].ContentType;
                    Guid fileId = Guid.NewGuid();
                    string path = Server.MapPath("\\Uploads");
                    string fileNameWithoutPath = System.IO.Path.GetFileName(Request.Files[0].FileName);
                    string fileName = fileId.ToString().Replace("-", "") + fileNameWithoutPath;
                    fileName = System.IO.Path.Combine(path, fileName);
                    try
                    {
                        Request.Files[0].SaveAs(fileName);
                        int rows = ImportStudentsFile1(fileName, stopWhenError);
                    }
                    catch (Exception ex)
                    {
                        ModelState.AddModelError("", "导入时发生错误,可能是文件格式非法,请将文件打开后另存为Excel2007以上的格式或联系管理员");
                    }
                }
                else
                    ModelState.AddModelError("", "请选择导入文件");
            }
            else {
                ModelState.AddModelError("", "请选择导入文件");
            }
            //return View(db.StudentInforms.ToList());
            return RedirectToAction("Index");
        }

4.ImportStudentsFile1()方法

 private int ImportStudentsFile1(string fileName, bool stopWhenError = false)
        {
            string[] data = { "考生号", "姓名", "身份证号", "性别代码", "民族代码", "政治面貌代码", "院校代码", "分校名称", "学历代码", "专业代码", "专业方向", "培养方式代码", "定向或委培单位", "生源所在地", "城乡生源", "学制", "入学时间", "毕业时间", "师范生类别代码", "困难生类别代码", "所在院系", "所在班级", "学号", "出生日期", "入学前档案所在单位", "入学前户口所在地派出所", "档案是否转入学校", "户口是否转入学校", "手机号码", "电子邮箱", "QQ号码", "家庭住址", "家庭电话", "家庭邮编", "毕业去向代码", "单位名称", "单位性质代码", "单位行业代码", "单位所在地", "工作职位类别代码", "单位联系人", "联系人电话", "联系人手机", "联系人电子邮箱", "联系人传真", "单位地址", "单位邮编", "报到证签发类别代码", "报到证签往单位名称", "签往单位所在地", "报到证编号", "报到起始时间", "档案转寄单位名称", "档案转寄单位地址", "档案转寄单位邮编", "户口迁转地址","协议书号", "档案所在地", "档案邮寄地址" };
            string[] property = { "No", "Name", "IDNumber", "SexCode", "NationCode", "PoliticalStatusCode", "CollegeCode", "BranchCollege", "DegreeCode", "MajorCode", "MajorField", "CultivationModeCode", "DirectionalUnit", "BirthPlaceCode", "StudentsFromUrbanAndRural", "EducationalSystem", "TermBeginTime", "GraduateTime", "NormalSchoolStudentTypeCode", "PoorStudentTypeCode", "Academy", "Class", "StudentNo", "Birthday", "BeforeEnrolRecordUnit", "BeforeEnrolResidenceLocationPolice", "RecordIsIntoSchool", "ResidenceIsIntoSchool", "Phone", "Email", "QQNumber", "Address", "HomePhone", "HomePostcode", "GraduateWhereaboutsCode", "UnitName", "UnitPropertyCode", "UnitIndustryCode", "UnitPlaceCode", "JobPositionType", "UnitContacts", "ContactPhone", "ContactTelephone", "ContactEmail", "ContactFax", "UnitAddress", "UnitPostcode", "ReportCardIssueTypeCode", "ReportCardToIssueUnitName", "ToIssueUnitPlaceCode", "ReportCardNo", "ReportStartTime", "RecordRedirectUnitName", "RecordRedirectUnitAddress", "RecordRedirectUnitPostcode", "ResidenceMovePlace", "ProtocolId", "Archives", "Postaddress" };
            return Import(fileName, data, property);
        }

5.Import()方法

 private int Import(string fileName, string[] data, string[] property)
        {
            System.Data.OleDb.OleDbConnectionStringBuilder stringBuilder = new System.Data.OleDb.OleDbConnectionStringBuilder();
            stringBuilder.DataSource = fileName;
            stringBuilder.Provider = "Microsoft.ACE.OLEDB.12.0";
            stringBuilder.Add("Extended Properties", "Excel 12.0 Xml;HDR=YES");
            int rows = 0;
            using (System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection(stringBuilder.ConnectionString))
            {
                connection.Open();
                DataTable table = connection.GetSchema("tables", new string[] { null, null, null, "TABLE" });
                if (table.Rows.Count == 0)
                    throw new Exception("未找到导入数据");
                string tableName = table.Rows[0]["TABLE_NAME"].ToString();
                string sql = string.Format("SELECT * FROM [{0}]", tableName);
                System.Data.OleDb.OleDbCommand command = new System.Data.OleDb.OleDbCommand(sql, connection);
                using (var reader = command.ExecuteReader())
                {
                    int[] dadaFielIndex = new int[data.Length];

                    for (int i = 0; i < data.Length; i++)
                    {
                        dadaFielIndex[i] = reader.GetOrdinal(data[i]);
                    }
                    if (dadaFielIndex[1] < 0) throw new Exception("未找到“姓名”列");
                    if (dadaFielIndex[2] < 0) throw new Exception("未找到“身份证号”列");
                    while (reader.Read())
                    {
                        string[] dataInformation = new string[dadaFielIndex.Length];
                        for (int i = 0; i < dadaFielIndex.Length; i++)
                        {
                            dataInformation[i] = reader[dadaFielIndex[i]].ToString().Trim();
                        }
                        string idNumber = reader["身份证号"].ToString().Trim();
                        int count = db.StudentInforms.Where(s => s.IDNumber == idNumber).Count();
                        StudentInform studentInformation = null;
                        if (count > 0)
                        {
                            studentInformation = db.StudentInforms.Where(s => s.IDNumber == idNumber).FirstOrDefault();
                            //stopWhenError = true;
                            //if (stopWhenError)
                            //    throw new Exception(string.Format("身份证“{0}”已经存在", idNumber));
                            //else
                            //    continue;
                        }
                        if (studentInformation == null)
                            studentInformation = new StudentInform();
                        Type t = typeof(StudentInform);

                        //string names = String.Join("\",\"", t.GetProperties().Select(w => w.Name));
                        for (int i = 0; i < property.Length; i++)
                        {
                            PropertyInfo propertyInfo = t.GetProperty(property[i]);
                            if (propertyInfo.PropertyType == typeof(DateTime))
                            {
                                DateTime time;
                                if (dataInformation[i].Length == 8)
                                {
                                    time = DateTime.ParseExact(dataInformation[i], "yyyyMMdd", System.Globalization.CultureInfo.CurrentCulture);
                                }
                                else
                                if (dataInformation[i].Length == 6)
                                {
                                    time = DateTime.ParseExact(dataInformation[i] + "01", "yyyyMMdd", System.Globalization.CultureInfo.CurrentCulture);
                                }
                                else
                                {
                                    time = DateTime.Now;
                                }

                                propertyInfo.SetValue(studentInformation, time, null);
                            }
                            else if (propertyInfo.PropertyType == typeof(bool))
                            {
                                propertyInfo.SetValue(studentInformation, false, null);
                            }
                            //else if (i == 57)
                            //{
                            //    propertyInfo.SetValue(studentInformation, common.encryptPassWord(dataInformation[2].Substring(12, 6)), null);
                            //}
                            else
                                propertyInfo.SetValue(studentInformation, dataInformation[i], null);
                        }
                        //循环赋值 
                        //foreach (var item in t.GetProperties())
                        //{
                        //    item.SetValue(studentInformation, dataInformation[j], null);
                        //    j++;
                        //}
                        //单独赋值 
                        if (count == 0)
                        {
                            t.GetProperty("Id").SetValue(studentInformation, Guid.NewGuid(), null);
                            studentInformation.Password = common.encryptPassWord(studentInformation.IDNumber.Substring(12, 6));
                            studentInformation.PasswordStatus = false;
                            db.StudentInforms.Add(studentInformation);
                        }
                        db.SaveChanges();
                        rows++;
                    }
                    reader.Close();
                }
                connection.Close();
            }
            return rows;
        }
版权声明:本文为博主原创文章,未经博主允许不得转载。

在 VS2008 下操作 Excel 的方法总结

这些天做个软件,需要读取 Excel 并导入到数据库中,所以研究了一下在 VC 下操作 Excel 的方法,这里做个总结,以作备忘。 一、最常用的 OLE 自动化方式这个方式应该说是功能最全的方式,可...
  • DavidHsing
  • DavidHsing
  • 2009年06月01日 11:42
  • 24725

WorkBook的SaveAs方法

语法'声明Sub SaveAs ( _     Filename As Object, _     FileFormat As Object, _     Password As Object, _ ...
  • zyming0815
  • zyming0815
  • 2010年10月13日 19:03
  • 21027

java Excel导入的简单工程,可以自动将Excel内容映射成实体类,方便自动化导入

  • 2017年12月07日 22:28
  • 14.86MB
  • 下载

利用Mapping映射思想实现Excel之间的导入处理

        这么一个多月来,过得比较的沉闷,主要精力都放在了给老师做的多个Excel之间的处理。刚接到任务时还比较的莫名其妙,处理Excel?首先是一片空白,不过马上想到了开源的API,我选了jx...
  • Jawfneo
  • Jawfneo
  • 2007年05月04日 23:19
  • 4884

C# 导出利用模板,映射文件导出复杂excel

  • 2012年03月12日 21:49
  • 146KB
  • 下载

c#Excel导入导出实体代码(GemBox),非常简单好用

  • 2013年07月03日 17:33
  • 503KB
  • 下载

C++使用VS2015导出/导入Excel的环境配置

环境Windows10 Microsoft Visual Studio 2015 Microsoft Office Excel 说明:用的公司的电脑,操作系统和软件都是正版的。新建C++工程,添加...
  • linjingtu
  • linjingtu
  • 2017年05月23日 11:26
  • 1250

Visual Studio 2013开发MFC程序对Excel 2010进行写操作(上)

Visual Studio 2013开发MFC程序对Excel 2010进行写操作
  • Fish_55_66
  • Fish_55_66
  • 2015年10月15日 14:51
  • 3670

C++使用VS2010导出Excel的方法及步骤

C++使用VS2010导出/导入Excel的方法及步骤    最近工作需要将listctrl中的数据导出到Excel中。网上找了很多,但多数是VC6.0的。结合VC6.0导出的方法,...
  • u014682163
  • u014682163
  • 2014年12月03日 21:27
  • 524

在 Visual Studio 中使用代码映射可视化和了解代码

官方地址:http://msdn.microsoft.com/zh-cn/library/jj739835.aspx 您可以将代码中的关系映射到 Visual Studio 旗舰版。...
  • PZ0605
  • PZ0605
  • 2017年02月23日 23:46
  • 511
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:用Visual Studio(C#) 实现Excel数据导入(映射)
举报原因:
原因补充:

(最多只允许输入30个字)