求解asp.net mvc 中上传excel 如何导入到数据库中！！！

如何在mvc中实现excel的导入，并将导入的数据，先存到临时表，然后根据选择性的数据，插入到数据库。。。求各位ＭＶＣ高手指教！！！最好附加个例子！！源码更好，借鉴研究一下！　Ｏ（∩＿∩）Ｏ谢谢各位喽！

22 个解决方案

#1

ASP.NET将EXCEL导入SQL SERVER数据库的代码 

#2

比较简单，就是用Microsoft.Jet.OLEDB.4.0的驱动，将excel作为数据源，读取里面的数据，直接把excel转为一个DataTable，然后，接下来就好做了，你可以把它制成一个xml文档，传递至一个存储过程，然后，在存储过程中，做insert操作

#3

跟 mvc 没关系，webform怎么用 还是怎么用，网上搜一下 。

#4

给一个详细点的代码，让偶瞅瞅

引用 3 楼 claymore1114 的回复:

跟 mvc 没关系，webform怎么用 还是怎么用，网上搜一下 。

#5

MVC需要在controller中处理。。。该怎么做，急求！！

#6

OleDb不就可以啦

#7

不要沉额。各位高手速来指教额， 给力啊！！！！

#8

做法一样的啊，在controller中，读取页面上选择的excel的路径，然后用上面的方法读取excel的数据，插入数据库，接着，要不查询，要不给个提示，就完事了。

#9

求列子额！！！！！！！！！！！！

#10

导出CSV文件的例子：

public ActionResult ExportQualifiedExaminee(int serviceid, int funcid, string condition)
        {

            RegistrationBLL bll = new RegistrationBLL();
            IList<QualifiedExamineeEn> list = bll.GetQualifiedExaminees(serviceid, condition);

            StringBuilder sb = new StringBuilder();
            sb.Append("序号,姓,名,性别,出生日期,登录名,工号,部门,准考证号,证件类型,证件号码,学历,专业,考场地址,手机,Email,企业名称,国籍\r\n");
            if (list != null && list.Count > 0)
            {
                int i = 0;
                foreach (var item in list)
                {
                    i++;
                    sb.Append(i.ToString());
                    sb.Append(",\t" + item.FirstName.Replace(",", "，"));
                    sb.Append(",\t" + item.LastName.Replace(",", "，"));
                    if (item.IDNumber != null && item.IDNumber.Trim().Length > 0)
                    {
                        sb.Append("," + (item.Gender == true ? "男" : "女"));
                        sb.Append(",\t" + item.Birthday);
                    }
                    else
                    {
                        sb.Append("," + "");
                        sb.Append("," + "");
                    }
                    sb.Append(",\t" + item.LogonName.Replace(",", "，"));
                    sb.Append(",\t" + item.EmployeeNum.Replace(",", "，"));
                    sb.Append(",\t" + item.Department.Replace(",", "，"));
                    sb.Append(",\t" + item.AdmissionFormId.Replace(",", "，")); //增加\t表示导出csv时，为文本数据
                    sb.Append(",\t" + item.IDTypeName);
                    sb.Append(",\t" + item.IDNumber.Replace(",", "，"));
                    sb.Append(",\t" + item.EducationName.Replace(",", "，"));
                    sb.Append(",\t" + item.EducationMajorName.Replace(",", "，"));
                    sb.Append(",\t" + item.LocationAddress.Replace(",", "，"));
                    sb.Append(",\t" + item.Contact.Replace(",", "，"));
                    sb.Append(",\t" + item.Email.Replace(",", "，"));
                    sb.Append(",\t" + item.CompanyName.Replace(",", "，"));
                    sb.Append(",\t" + item.National.Replace(",", "，"));
                    sb.Append("\r\n");
                }

            }
            else
            {
                return Alert("没有准考证信息", "~/Views/ExamService/SaveSuccess.aspx", new { controller = "Registration", action = "GetExamineeByPage", serviceid = serviceid, funcid = funcid });
            }
            ExcelExportHandler.ExportFile(sb.ToString(), "application/ms-excel", "UTF-8", "GB2312", "合格考生.csv");

            return new EmptyResult();
        }

#11

读取excel模板，导出excel的例子：

        public ActionResult ExportQualifiedExaminees(int serviceid, int funcid, string condition)
        {

            RegistrationBLL bll = new RegistrationBLL();
            IList<QualifiedExamineeEn> list = bll.GetQualifiedExaminees(serviceid, condition);
            if (list == null || list.Count == 0)
            {
                return Alert("没有准考证信息", "~/Views/ExamService/SaveSuccess.aspx", new { controller = "Registration", action = "GetExamineeByPage", serviceid = serviceid, funcid = funcid });
            }

            using (MemoryStream m = bll.ExportQualifiedExaminees(Server.MapPath("~/Resources/考生签到表导出模版.xls"), list1[0].fServiceName, list, Server.MapPath("~/Common/Images/toeic_log.PNG")))
            {
                ExcelExportHandler.ExportFile(m, "application/ms-excel", "UTF-8", "GB2312", "考生签到表.xls");
            }

            return new EmptyResult();
        }

#12

ExcelExportHandler类的定义：

public class ExcelExportHandler
    {
        public static void ExportFile(string content, string contentType, string charSet, string encodingName, string outPutFileName)
        {
            byte[] htmlBy = System.Text.Encoding.GetEncoding("GB2312").GetBytes(content);
            MemoryStream stream = new MemoryStream(htmlBy);
            ExportFile(stream, contentType, charSet, encodingName, outPutFileName);
        }

        public static void ExportFile(MemoryStream stream, string contentType, string charSet, string encodingName, string outPutFileName)
        {
           
           
            HttpContext.Current.Response.Clear();
            HttpContext.Current.Response.ContentType = contentType;
            HttpContext.Current.Response.Charset = charSet;
            HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding(encodingName);
            String userAgent;
            userAgent = HttpContext.Current.Request.UserAgent;
            if (userAgent != null && userAgent.ToUpper().IndexOf("MSIE") > -1)
            {
                HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(outPutFileName, System.Text.Encoding.UTF8).Replace("+", "%20"));
                // The browser is Microsoft Internet Explorer Version 6.0.
            }
            else if (userAgent != null && userAgent.ToUpper().IndexOf("MOZILLA") > -1)
            {
                HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlDecode(outPutFileName, System.Text.Encoding.UTF8).Replace("+", "%20"));

            }

            HttpContext.Current.Response.HeaderEncoding = System.Text.Encoding.GetEncoding(encodingName);
            stream.WriteTo(HttpContext.Current.Response.OutputStream);
            //HttpContext.Current.Response.End();
        }
    }

#13

啥也不说了。
项一下。
 楼上很全面了。

#14

呵呵，自己已经专研出来了，O(∩_∩)O哈哈~

#15

楼主，把程序帖出来

#16

对啊，楼主把代码帖出来让大家看看，我也不会，急求啊~~~~

#17

 DataTable dt = GeneralRequestDetail.GetGeneralInfoToExcel(new Guid(calendarId),expenseType,startDate,endDate,employeeNo,employeeName,isPass);

           //if (dt.Rows.Count > 0)

           //{

           //    if (excelInfo.DoExport(dt))

           //    {



           //        return RedirectToAction("InvoiceList", new { calendarId = calendarId });

           //    }

           //    return View();

           //}

           //   return RedirectToAction("InvoiceList", new { calendarId = calendarId });

           StringBuilder result = new StringBuilder();

           //result.Append("<meta http-equiv=\"content-type\" content=\"application/ms-excel; charset=UTF-8\"/>"); // the key!!!

           //result.Append("<table>");



           ////prepare header...

           if (dt.Rows.Count > 0)

           {

               result.Append("<html xmlns:o='urn:schemas-microsoft-com:office:office' xmlns:x='urn:schemas-microsoft-com:office:excel' xmlns='http://www.w3.org/TR/REC-html40'>");

               result.Append("<head><title></title><meta http-equiv='Content-Type' content=\"text/html; charset=UTF-8\">");

               //注意：[if gte mso 9]到[endif]之间的代码，用于显示Excel的网格线，若不想显示Excel的网格线，可以去掉此代码

               result.Append("<!--[if gte mso 9]>");

               result.Append("<xml>");

               result.Append(" <x:ExcelWorkbook>");

               result.Append("  <x:ExcelWorksheets>");

               result.Append("   <x:ExcelWorksheet>");

               result.Append("    <x:Name>Sheet1</x:Name>");

               result.Append("    <x:WorksheetOptions>");

               result.Append("      <x:Print>");

               result.Append("       <x:ValidPrinterInfo />");

               result.Append("      </x:Print>");

               result.Append("    </x:WorksheetOptions>");

               result.Append("   </x:ExcelWorksheet>");

               result.Append("  </x:ExcelWorksheets>");

               result.Append("</x:ExcelWorkbook>");

               result.Append("</xml>");

               result.Append("<![endif]-->");

               result.Append("</head><body><table style='border-collapse:collapse;table-layout:fixed;'><tr>");



               //result.Append("<tr>");

               result.Append("<td><b>" + "公司编号" + "</b></td>");

               result.Append("<td><b>" + "公司名称" + "</b></td>");

               result.Append("<td><b>" + "员工编号" + "</b></td>");

               result.Append("<td><b>" + "员工姓名" + "</b></td>");

               result.Append("<td><b>" + "表单编号" + "</b></td>");

               result.Append("<td><b>" + "报销编号" + "</b></td>");

               result.Append("<td><b>" + "发票编号" + "</b></td>");

               result.Append("<td><b>" + "费用类别" + "</b></td>");

               result.Append("<td><b>" + "是否有效" + "</b></td>");

               result.Append("<td><b>" + "备注" + "</b></td>");

               result.Append("<td><b>" + "真伪" + "</b></td>");

               result.Append("<td><b>" + "名称" + "</b></td>");

               result.Append("<td><b>" + "日期" + "</b></td>");

               result.Append("<td><b>" + "金额" + "</b></td>");

               result.Append("</tr>\n");

               for (int j = 0; j < dt.Rows.Count; j++)

               {

                   result.Append("<tr>");

                   for (int k = 0; k < dt.Columns.Count; k++)

                   {

                       object obj = dt.Rows[j][k];

                       //result.Append("<td><b>" + obj.ToString() + "</b></td>");

                       result.AppendFormat("<td style='vnd.ms-excel.numberformat:@'>{0}</td>", obj);

                   }

                   result.Append("</tr>\n");

               }



               Response.Clear();

               Response.Buffer = true;

               Response.ContentEncoding = Encoding.UTF8;

               Response.Charset = "utf-8";

               Response.ContentType = "application/ms-excel";

               Response.AddHeader("Content-Disposition", "attachment; filename=" + HttpUtility.UrlEncode("InvoiceInfo", Encoding.UTF8) + ".xls");

               Response.Write(result.ToString());

               Response.Flush();

               Response.End();

           }

           return RedirectToAction("InvoiceList", new { calendarId = calendarId, expenseType = expenseType, startDate = startDate, endDate = endDate, employeeNo = employeeNo, employeeName = employeeName, isPass = isPass });

#18

能不能加好友，交流一下~~

#19

引用 18 楼 cc_lq 的回复:

能不能加好友，交流一下~~

可以,我的Q是2511207640

#20

引用 18 楼 cc_lq 的回复:

能不能加好友，交流一下~~

可以,我的Q是2511207640

#21

很有用的功能，正在找相关代码，学习一下。

#22

//控制器
/// <summary>         /// 导入Excel方法         /// </summary>         /// <param name="fileSite"></param>         /// <returns></returns>         [HttpPost]         public ActionResult ImportSiteInfo(string fileSite)         {             if (string.IsNullOrEmpty(fileSite))                 return View();             ImportExcel importEx = new ImportExcel();             //获取Excel中的数据             DataTable dtSite = importEx.ExcelDataSource(fileSite, "Sheet1").Tables[0];             //添加到数据库             foreach (DataRow item in dtSite.Rows)             {                 Site s = new Site()                 {                     Name = item["Name"].ToString().Trim(),                     LocalID = int.Parse(item["LocalID"].ToString().Trim())                 };                 //判断当前站点是否存在                 if (!string.IsNullOrEmpty(SiteManageBusiness.GetSiteNameByName(s.Name)))                     continue;                 SiteManageBusiness.AddSiteInfo(s);             }             /*在跳转页面时传递一个标志位*/            return RedirectToAction("SiteInfoList", "SiteManage", new { import = 1 });         }                   /// <summary>         /// 站点信息一览         /// </summary>         /// <param name="page">当前索引</param>         /// <param name="pageSize">当前页面显示条数</param>         /// <returns></returns>         public ActionResult SiteInfoList(int page = 1, int pageSize = 15)         {             //判断是否为初始化页面             if (Request.QueryString["flagSite"] != null)             {                 Session["site"] = null;             }             /*接一下数值*/            if (Request.QueryString["import"] != null)             {                 ViewBag.flag = 1;             }             SiteModel siteInfo = new SiteModel()             {                 //区域ID值（默认值）                  LocalID = -1             };             //查询条件不为空             if (Session["site"] != null)             {                 siteInfo = Session["site"] as SiteModel;                 //区域名称                 ViewBag.LocalName = siteInfo.LocalName;                 //站点名称                 ViewBag.SiteName = siteInfo.Name;             }             PagerIndexModel model = new PagerIndexModel             {                 CurrentPageIndex = page,                 PageSize = pageSize,                 //确定记录总数（才能计算出PageCount页数）                                 TotalRecordCount = SiteManageBusiness.GetSiteInfoByEntity(siteInfo).ToList<Site>().Count,             };             // 获取当前页的信息                         model.SiteInfomationList = SiteManageBusiness.GetSiteInfoByEntity(siteInfo).OrderBy(p => p.ID).Skip((model.CurrentPageIndex - 1) * model.PageSize).Take(model.PageSize);             return View(model);         } 




//导入Excel方法
     /// <summary>
        /// 获取Excel数据方法
        /// </summary>
        /// <param name="filepath">文件路径</param>
        /// <param name="sheetname">sheet名称</param>
        /// <returns></returns>
        public DataSet ExcelDataSource(string filepath, string sheetname)
        {
            string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties=Excel 8.0;";
            OleDbConnection conn = new OleDbConnection(strConn);
            OleDbDataAdapter oada = new OleDbDataAdapter("select * from [" + sheetname + "$]", strConn);
            DataSet ds = new DataSet();
            oada.Fill(ds);
            return ds;
        }