excel大批量导出

导出服务类：

    public static class ExcelHelper
    {
        /// <summary>
        ///
        /// </summary>
        /// <typeparam name="T">Model类型</typeparam>
        /// <param name="list">列表</param>
        /// <param name="tmppath">模板文件（空的excel文件）地址</param>
        /// <param name="outputpath">要输出的地址</param>
        /// <param name="sheetname">excel的sheet名称</param>
        /// <param name="propdict">要导出的列名列表</param>
        /// <returns></returns>
        public static bool ExportToExcel<T>(IList<T> list, string tmppath, string outputpath, string sheetname, NameValueCollection propdict)
            where T : class
        {
            if (list == null || list.Count == 0)
            {
                throw new ArgumentNullException("list", "该列表没有实际的值");
            }

            #region 判断模板文件、保存路径
            if (!File.Exists(tmppath))
            {
                throw new FileNotFoundException("模板文件路径不正确，找不到模板文件");
            }
            if (File.Exists(outputpath))
            {
                File.Delete(outputpath);
            }
            File.Copy(tmppath, outputpath);
            #endregion
            sheetname = string.IsNullOrEmpty(sheetname) ? "Sheet1" : sheetname;
            string excelconntext = "Provider=Microsoft.Jet.OleDB.4.0;Data Source=" + outputpath + ";Extended Properties = \"Excel 8.0;HDR=Yes;\"";

            using (OleDbConnection cn = new OleDbConnection(excelconntext))
            {
                cn.Open();
                OleDbCommand com = cn.CreateCommand();
                PropertyInfo[] ps = typeof(T).GetProperties();
                List<PropertyInfo> proplist = new List<PropertyInfo>();
                StringBuilder sb = new StringBuilder();
                sb.Append("Create Table [" + sheetname + "$](");

                StringBuilder sb2 = new StringBuilder();
                sb2.Append("Insert into [" + sheetname + "$] values(");
                foreach (var item in ps)
                {
                    string head = propdict[item.Name];
                    if (string.IsNullOrEmpty(head)) continue;
                    proplist.Add(item);
                    sb.Append(head + " ");
                    switch (item.PropertyType.Name)
                    {
                        case "String": sb.Append("Char,"); break;
                        case "Decimal": sb.Append("Decimal,"); break;
                        case "DateTime": sb.Append("Char,"); break;
                        case "Int32": sb.Append("Integer,"); break;
                        default: sb.Append("Char,");
                            break;
                    }
                    sb2.Append("@" + item.Name + " ,");
                }
                com.CommandText = sb.ToString().Trim(',') + ")";
                com.ExecuteNonQuery();//创建表头

                com.CommandText = sb2.ToString().Trim(',') + ")";

                foreach (var item in list)
                {
                    OleDbParameter[] pars = new OleDbParameter[proplist.Count];
                    for (int i = 0; i < proplist.Count; i++)
                    {
                        pars[i] = new OleDbParameter(proplist[i].Name, proplist[i].GetValue(item, null) ?? "");
                    }
                    com.Parameters.Clear();
                    com.Parameters.AddRange(pars);
                    com.ExecuteNonQuery();//写入每行的数据
                }
            }

            return true;
        }

        public static bool ExportToExcel<T>(IList<T> list, string tmppath, string outputpath, string sheetname, List<DbcolParse> dbParse)
            where T : class
        {
            if (list == null || list.Count == 0)
            {
                throw new ArgumentNullException("list", "该列表没有实际的值");
            }

            #region 判断模板文件、保存路径
            if (!File.Exists(tmppath))
            {
                throw new FileNotFoundException("模板文件路径不正确，找不到模板文件");
            }
            if (File.Exists(outputpath))
            {
                File.Delete(outputpath);
            }
            File.Copy(tmppath, outputpath);
            #endregion
            sheetname = string.IsNullOrEmpty(sheetname) ? "Sheet1" : sheetname;
            string excelconntext = "Provider=Microsoft.Jet.OleDB.4.0;Data Source=" + outputpath + ";Extended Properties = \"Excel 8.0;HDR=Yes;\"";

            using (OleDbConnection cn = new OleDbConnection(excelconntext))
            {
                PropertyInfo[] ps = typeof(T).GetProperties();
                cn.Open();
                OleDbCommand com = cn.CreateCommand();

                StringBuilder sb = new StringBuilder();
                sb.Append("Create Table [" + sheetname + "$](");

                StringBuilder sb2 = new StringBuilder();
                sb2.Append("Insert into [" + sheetname + "$] values(");

                foreach (var item in dbParse)
                {
                    var propinfo = ps.SingleOrDefault(c => c.Name == item.ColumnName);
                    if (propinfo == null)
                    {
                        dbParse.Remove(item);
                        continue;
                    }
                    item.PropInfo = propinfo;

                    sb.Append(item.Title + " ");

                    if (item.DataParse != null)
                        sb.Append("Char,");
                    else
                        switch (propinfo.PropertyType.Name)
                        {
                            case "String": sb.Append("Char,"); break;
                            case "Decimal": sb.Append("Decimal,"); break;
                            case "DateTime": sb.Append("Char,"); break;
                            case "Int32": sb.Append("Integer,"); break;
                            default: sb.Append("Char,");
                                break;
                        }

                    sb2.Append("@" + item.ColumnName + " ,");
                }
                com.CommandText = sb.ToString().Trim(',') + ")";
                com.ExecuteNonQuery();//创建表头

                com.CommandText = sb2.ToString().Trim(',') + ")";//建立SQL语句

                foreach (var item in list)
                {
                    OleDbParameter[] pars = new OleDbParameter[dbParse.Count];
                    for (int i = 0; i < dbParse.Count; i++)
                    {
                        DbcolParse dp = dbParse[i];
                        object orgval = dp.PropInfo.GetValue(item, null);
                        object val = dp.DataParse != null ? (orgval != null ? dp.DataParse(orgval) : string.Empty) : orgval??string.Empty;
                        pars[i] = new OleDbParameter(dbParse[i].ColumnName, val);
                    }
                    com.Parameters.Clear();
                    com.Parameters.AddRange(pars);
                    com.ExecuteNonQuery();//写入每行的数据
                }
            }

            return true;
        }

       
    }


    public class DbcolParse
    {
        public string ColumnName { get; set; }
        public string Title { get; set; }
        public Func<object, string> DataParse { get; set; }
        internal PropertyInfo PropInfo { get; set; }
    }

下载：

 

        #region Download
        public static void DownloadFile(string filePath)
        {
            if (!File.Exists(filePath))
            {
                return;
            }
            FileInfo file = new FileInfo(filePath);
            HttpResponse response = HttpContext.Current.Response;
            response.Clear();
            response.AddHeader("Content-Disposition", "attachment; filename=" + HttpUtility.UrlEncode(Path.GetFileName(filePath), Encoding.UTF8));
            response.AddHeader("Content-Length", file.Length.ToString());
            response.ContentType = "application/octet-stream";
            response.Filter.Close();
            response.WriteFile(file.FullName, true);
            response.End();

        }
        #endregion

调用：

 

 

        protected void btn_export_excel_Click(object sender, EventArgs e)
        {
            bool rst = true;
            try
            {
                var param = GetQueryParam();
                param.PageSize = 10000;
                var list = ApplyService.Instance.GetCollection(param, true);
                foreach (var item in list)
                {
                    item.RequestReason = item.RequestReason.Length > 250
                                         ? item.RequestReason.Substring(0, 250)
                                         : item.RequestReason;//申请理由超过250个汉字时截取
                }
                string tmppath = Server.MapPath("~/Files/Template/refundtemp.xls");
                string outputpath = Path.Combine(Server.MapPath("~/Files/Output"), string.Format("退款申请列表_{1:yyyy-MM-dd}_By{0}.xls", CurrentUser.RealName,DateTime.Today));

                List<DbcolParse> colFilter = new List<DbcolParse>();
                colFilter.Add(new DbcolParse { ColumnName = "RequestID", Title = "申请单号" });
                colFilter.Add(new DbcolParse { ColumnName = "State", Title = "申请状态", DataParse = c => Setting.Instance.ReqStatuFilter[(int)c] });
                colFilter.Add(new DbcolParse { ColumnName = "RFID", Title = "订单号" });
                colFilter.Add(new DbcolParse { ColumnName = "UserID", Title = "客户ID" });
                colFilter.Add(new DbcolParse { ColumnName = "RequestDate", Title = "申请日期", DataParse = c => Tool.DateTimeFormatShort((DateTime)c) });
                colFilter.Add(new DbcolParse { ColumnName = "CheckDate", Title = "审核日期", DataParse = c => Tool.DateTimeFormatShort((DateTime)c) });
                colFilter.Add(new DbcolParse { ColumnName = "RequestPersonName", Title = "申请人" });
                colFilter.Add(new DbcolParse { ColumnName = "CheckPerson2", Title = "客服账号" });
                colFilter.Add(new DbcolParse { ColumnName = "RequestReason", Title = "申请理由" });
                colFilter.Add(new DbcolParse { ColumnName = "Remark2", Title = "开户行" });//此处借用了Remark2属性
                colFilter.Add(new DbcolParse { ColumnName = "RefundShould", Title = "应退余额" });
                colFilter.Add(new DbcolParse { ColumnName = "CashAmount", Title = "已退款" });
                colFilter.Add(new DbcolParse { ColumnName = "BalanceAmount", Title = "已退余额" });
                colFilter.Add(new DbcolParse { ColumnName = "CheckPersonName", Title = "审核人" });
                colFilter.Add(new DbcolParse { ColumnName = "Idcompany", Title = "所属机构", DataParse = c => Setting.Instance.DepartMent[(int)c] });
                colFilter.Add(new DbcolParse { ColumnName = "OutStoregeNo", Title = "出库单号" });
                colFilter.Add(new DbcolParse { ColumnName = "ReturnStorageNo", Title = "退库单号" });
                colFilter.Add(new DbcolParse { ColumnName = "ReturnStoreApplyNo", Title = "退库申请单号" });

                if (ExcelHelper.ExportToExcel(list, tmppath, outputpath, string.Empty, colFilter))
                    Tool.DownloadFile(outputpath);
                else
                    rst = false;
            }
            catch (Exception ex)
            {
                rst = false;
                LogManager.GetCurrentClassLogger().ErrorException(ex.Message,ex);
            }

            if (!rst)
            {
                BindGrid(false);
                this.JqueryMesssage("导出失败，请稍候再试或联系技术支持人员", true);
            }
        }