网页html格式导出Excel.xls

2018-02-28 07:47:06来源:cnblogs.com作者:Smile灬Lucky人点击

分享
第七城市

1.创建excel方法

 /// <summary>        /// 创建Excel表格        /// </summary>        /// <param name="dt">数据流</param>        /// <param name="FileName">文件名称</param>        public static void CreateExcel(DataTable dt, string FileName)        {            StringBuilder strb = new StringBuilder();            strb.Append(" <html xmlns:o=/"urn:schemas-microsoft-com:office:office/"");            strb.Append("xmlns:x=/"urn:schemas-microsoft-com:office:excel/"");            strb.Append("xmlns=/"http://www.w3.org/TR/REC-html40/">");            strb.Append(" <head> <meta http-equiv='Content-Type' content='text/html; charset=gb2312'>");            strb.Append(" <style>");            strb.Append(".xl26");            strb.Append(" {mso-style-parent:style0;");            strb.Append(" font-family:/"Times New Roman/", serif;");            strb.Append(" mso-font-charset:0;");            strb.Append(" mso-number-format:/"@/";}");            strb.Append(" </style>");            strb.Append(" <xml>");            strb.Append(" <x:ExcelWorkbook>");            strb.Append("  <x:ExcelWorksheets>");            strb.Append("  <x:ExcelWorksheet>");            strb.Append("    <x:Name>Sheet1 </x:Name>");            strb.Append("    <x:WorksheetOptions>");            strb.Append("    <x:DefaultRowHeight>285 </x:DefaultRowHeight>");            strb.Append("    <x:Selected/>");            strb.Append("    <x:Panes>");            strb.Append("      <x:Pane>");            strb.Append("      <x:Number>3 </x:Number>");            strb.Append("      <x:ActiveCol>1 </x:ActiveCol>");            strb.Append("      </x:Pane>");            strb.Append("    </x:Panes>");            strb.Append("    <x:ProtectContents>False </x:ProtectContents>");            strb.Append("    <x:ProtectObjects>False </x:ProtectObjects>");            strb.Append("    <x:ProtectScenarios>False </x:ProtectScenarios>");            strb.Append("    </x:WorksheetOptions>");            strb.Append("  </x:ExcelWorksheet>");            strb.Append("  <x:WindowHeight>6750 </x:WindowHeight>");            strb.Append("  <x:WindowWidth>10620 </x:WindowWidth>");            strb.Append("  <x:WindowTopX>480 </x:WindowTopX>");            strb.Append("  <x:WindowTopY>75 </x:WindowTopY>");            strb.Append("  <x:ProtectStructure>False </x:ProtectStructure>");            strb.Append("  <x:ProtectWindows>False </x:ProtectWindows>");            strb.Append(" </x:ExcelWorkbook>");            strb.Append(" </xml>");            strb.Append("");            strb.Append(" </head> <body> <table align=/"center/" style='border-collapse:collapse;table-layout:fixed'> <tr>");            //写列标题                int columncount = dt.Columns.Count;            for (int columi = 0; columi < columncount; columi++)            {                strb.Append(" <td> <b>" + dt.Columns[columi] + " </b> </td>");            }            strb.Append(" </tr>");            //写数据                for (int i = 0; i < dt.Rows.Count; i++)            {                strb.Append(" <tr>");                for (int j = 0; j < dt.Columns.Count; j++)                {                    strb.Append(" <td class='xl26'>" + dt.Rows[i][j].ToString() + " </td>");                }                strb.Append(" </tr>");            }            strb.Append(" </table>");            strb.Append(" </body> </html>");            HttpContext.Current.Response.Clear();            HttpContext.Current.Response.Buffer = true;            HttpContext.Current.Response.Charset = "GB2312";            HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + FileName);            HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");//设置输出流为简体中文                HttpContext.Current.Response.ContentType = "application/ms-excel";//设置输出文件类型为excel文件。                HttpContext.Current.Response.Write(strb);            HttpContext.Current.Response.End();        }

2.List集合转DateTable,根据属性特性[System.ComponentModel.Description("列名称")]指定名称,没有指定名称列则不会导出

#region List集合转DateTable        /// <summary>        /// List集合转DateTable        /// </summary>        /// <typeparam name="T">泛型类</typeparam>        /// <param name="items">参数名</param>        /// <returns></returns>        private DataTable ToDataTable<T>(List<T> items)        {            var tb = new DataTable(typeof(T).Name);            List<PropertyInfo> props = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance).ToList();            #region MyRegion            //foreach (PropertyInfo prop in props)            //{            //    string name = "";            //    //获取特性值            //    object[] da = prop.GetCustomAttributes(typeof(DescriptionAttribute), false);            //    if (da.Length != 0)            //    {            //        name = (da[0] as DescriptionAttribute).Description;            //        Type t = GetCoreType(prop.PropertyType);            //        tb.Columns.Add(name, t);            //    }            //    else            //    {            //        //name = prop.Name;            //        props.Remove(prop);            //    }            //}             #endregion            for (int i = 0; i < props.Count; i++)            {                string name = "";                //获取特性值                object[] da = props[i].GetCustomAttributes(typeof(DescriptionAttribute), false);                if (da.Length != 0)                {                    name = (da[0] as DescriptionAttribute).Description;                    Type t = GetCoreType(props[i].PropertyType);                    tb.Columns.Add(name, t);                }                else                {                    //name = prop.Name;                    props.Remove(props[i]);                    i--;                }            }            foreach (T item in items)            {                var values = new object[props.Count];                for (int i = 0; i < props.Count; i++)                {                    values[i] = props[i].GetValue(item, null);                }                tb.Rows.Add(values);            }            return tb;        }        public static bool IsNullable(Type t)        {            return !t.IsValueType || (t.IsGenericType && t.GetGenericTypeDefinition() == typeof(Nullable<>));        }        public static Type GetCoreType(Type t)        {            if (t != null && IsNullable(t))            {                if (!t.IsValueType)                {                    return t;                }                else                {                    return Nullable.GetUnderlyingType(t);                }            }            else            {                return t;            }        }         #endregion

3.创建数据源,调用方法

    public void UserWithdrawExcelList()        {            //数据源,根据自己需要来取            List<UserWithdrawQueryViewModel> userList = UserWithdrawBLL.GetUserWithdrawListExcel(paramdic);            DataTable dt = ToDataTable<UserWithdrawQueryViewModel>(userList);            #region MyRegion            //DataTable dt = new DataTable();            //dt.Columns.Add("用户名", typeof(String));            //dt.Columns.Add("提现标题", typeof(String));            //dt.Columns.Add("提现总金额", typeof(String));            //dt.Columns.Add("实际到账金额", typeof(String));            //dt.Columns.Add("提现手续费", typeof(String));            //dt.Columns.Add("提现账户", typeof(String));            //dt.Columns.Add("提现状态", typeof(String));            //dt.Columns.Add("创建时间", typeof(DateTime));            //userList.TryForEach(t =>            //{            //    dt.Rows.Add(new object[] {             //        t.UserName,t.WithTitle,t.WithTotalAmount,t.WithAmount,t.CounterFee,t.BankCode,t.StatusName,t.AddTime            //    });            //});             #endregion            DataView dv = dt.DefaultView;//获取表视图            dv.ToTable();//转为表            DataChangeExcel.CreateExcel(dt, DateTime.Now.ToString("yyyyMMddHHmmssffff") + ".xls");        }        
第七城市

最新文章

123

最新摄影

闪念基因

微信扫一扫

第七城市微信公众平台