mvc将数据导入到Excel中

1.数据导入Excel方法:

 public ActionResult ExportToExcel()

{        

  //定义数据源

   var list = _DB.Records
                .Where(condition)
                .OrderByDescending(a => a.DateTime)
                .ToList();

            var date = Convert.ToDateTime("0001-1-1 0:00:00");          
        
            if (date == null)
                date = DateTime.Now;

            string fileName = string.Format("MemberUpgrade{0}.xls", date.ToString("yyyy_MM_dd"));//生成的Excel名称
            string filePath = string.Format("{0}\\{1}", Server.MapPath("~/Temp"), fileName);
            FileInfo file = new FileInfo(filePath);
            if (!Directory.Exists(file.Directory.FullName))
                Directory.CreateDirectory(file.Directory.FullName);
            if (System.IO.File.Exists(filePath))
                System.IO.File.Delete(filePath);
            try
            {
                using (ExcelHelper excelHelper = new ExcelHelper(filePath))
                {
                    excelHelper.HDR = "YES";
                    excelHelper.IMEX = "0";

          //Excel文件列名
                    Dictionary<string, string> tableDefinition = new Dictionary<string, string>();
                    tableDefinition.Add("序号", "nvarchar(100)");
                    tableDefinition.Add("会员编号", "nvarchar(100)");
                    tableDefinition.Add("会员姓名", "nvarchar(100)");
                    tableDefinition.Add("备注", "nvarchar(100)");
                    tableDefinition.Add("时间", "nvarchar(100)");

            //Excel内表名
                    excelHelper.WriteTable("MemberUpgrade", tableDefinition);
                    string decimalQuoter = (CultureInfo.CurrentCulture.NumberFormat.NumberDecimalSeparator.Equals(",") ? "\"" : String.Empty);
                    var i = 0;

//导入数据
                    foreach (var c in list)
                    {
                        i++;

                        StringBuilder sb = new StringBuilder();
                        sb.Append("INSERT INTO [MemberUpgrade] (序号,会员编号,会员姓名,备注,时间) VALUES (");
                        sb.Append('"'); sb.Append(i.ToString()); sb.Append("\",");//序号
                        sb.Append('"'); sb.Append(c.Account.UserName); sb.Append("\",");
                        sb.Append('"'); sb.Append(c.Account.AccountName); sb.Append("\",");
                        sb.Append('"'); sb.Append(c.Remark); sb.Append("\",");
                        sb.Append('"'); sb.Append(c.DateTime.ToString("yyyy-MM-dd HH:mm"));sb.Append('"');
                        sb.Append(")");
                        excelHelper.ExecuteCommand(sb.ToString());
                    }
                    excelHelper.Dispose();
                }

                return RedirectToAction("Download", "Finance", new { id = fileName });//生成并下载Excel
            }
            catch (Exception)
            {
                return RedirectToAction("Index", "Result", new { s = false, m = "对不起！当前程序所在服务器不支持自动生成Excel表格！" });
            }

}

 

 

2.生成并下载Execl(FinanceController下)

    public ActionResult Download(string id)
        {
            var filePath = Server.MapPath("~/Temp/" + id);
            Response.Clear();
            Response.Charset = "utf-8";
            Response.HeaderEncoding = Encoding.UTF8;
            Response.AddHeader("content-type", "application/x-msdownload");
            Response.AddHeader("content-disposition", string.Format("attachment; filename={0}", id));
            Response.ContentType = "text/xls";
            Response.BinaryWrite(System.IO.File.ReadAllBytes(filePath));
            Response.End();
            return null;
        }

 

 备注：在view页面之间调用ExportToExcel即可；