关闭

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

标签: excelc#
412人阅读 评论(0) 收藏 举报
分类:

本方法缺点:数据量有一定的限制,若列数据过多,则只能导入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;
        }
0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:811次
    • 积分:65
    • 等级:
    • 排名:千里之外
    • 原创:6篇
    • 转载:0篇
    • 译文:0篇
    • 评论:0条
    文章分类
    文章存档