asp.net导出excle表

最新推荐文章于 2023-01-30 22:44:09 发布

uniqer

最新推荐文章于 2023-01-30 22:44:09 发布

阅读量957

点赞数

分类专栏： asp.net 文章标签： asp.net string exception object null list

本文链接：https://blog.csdn.net/uniqer/article/details/7889932

版权

asp.net 专栏收录该内容

13 篇文章 0 订阅

订阅专栏

public class ToExcel<T>
    {
        public static void DataBindTitleExcel(System.Web.HttpResponse response, List<T> list, List<string> columnName, List<string> propertyName, string ExcelTitle, string strUserMsg)
        {

            if (list.Count == 0)
            {
                response.Write("<script>alert('对不起,没有查询到任何记录,导出失败!')</script>");
                response.End();
            }
            response.ContentEncoding = Encoding.GetEncoding("GB2312");
            response.ContentType = "application/ms-excel";
            response.AppendHeader("Content-Disposition", "attachment;filename=ExportSmsList.xls");
            int count = columnName.Count;
            StringBuilder builder = new StringBuilder();

            builder.Append("<html><head>\n");
            builder.Append("<meta http-equiv=/\"Content-Language/\" content=/\"zh-cn/\">\n");
            builder.Append("<meta http-equiv=/\"Content-Type/\" content=/\"text/html; charset=gb2312/\">\n");
            builder.Append("</head>\n");
            builder.Append("<table border=1>");
            if (ExcelTitle != "")
            {
                string str = "<font size=4><b>" + ExcelTitle + "</b></font>";
                if (strUserMsg != "")
                {
                    str = str + "（" + strUserMsg + "）";
                }
                builder.Append(string.Concat(new object[] { "<tr style=\"height:40px;text-align:center;\"><td colspan=", count, ">", str, "</td></tr>" }));
            }
            builder.Append("<tr style=\"height:40px;text-align:right;\"><td colspan=" + count + " valign=middle height=24>");
            builder.Append("查询时间：" + DateTime.Now.ToString("G") + "</td></tr>");
            builder.Append("<tr>\n");
            //for (int i = 0; i < columnName.Count; i++)
            //{

            //    builder.Append("<td style=\"background:#5D7B9D; color:White; height:40px;font-weight:bold;\" >" + columnName[i] + "</td>\n");

            //}

            foreach (string str in columnName)
            {
                builder.Append("<td style=\"background:#5D7B9D; color:White; height:40px;font-weight:bold;\" >" + str + "</td>\n");
            }

            Type objType = typeof(T);
            BindingFlags bf = BindingFlags.Instance | BindingFlags.NonPublic | BindingFlags.Public | BindingFlags.Static;//反射标识 
            PropertyInfo[] propInfoArr = objType.GetProperties(bf);

            //foreach (T model in list)
            //{

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

            //    foreach (PropertyInfo propInfo in propInfoArr)
            //    {

            //        foreach (string propName in propertyName)
            //        {

            //            if (string.Compare(propInfo.Name.ToUpper(), propName.ToUpper()) == 0)
            //            {

            //                PropertyInfo modelProperty = model.GetType().GetProperty(propName);
            //                if (modelProperty != null)
            //                {

            //                    object objResult = modelProperty.GetValue(model, null);
            //                    builder.Append("<td style='vnd.ms-excel.numberformat:@'>" + ((objResult == null) ? string.Empty : objResult) + "</td>");
            //                }
            //                else
            //                {
            //                    throw new Exception("属性名称可能不存在!");
            //                }
            //            }
            //        }
            //    }
            //    builder.Append("</tr>\n");
            //}

            foreach (T model in list)
            {

                builder.Append("<tr>");

                foreach (string propName in propertyName)
                {

                    foreach (PropertyInfo propInfo in propInfoArr)
                    {



                        if (string.Compare(propInfo.Name.ToUpper(), propName.ToUpper()) == 0)
                        {

                            PropertyInfo modelProperty = model.GetType().GetProperty(propName);
                            if (modelProperty != null)
                            {

                                object objResult = modelProperty.GetValue(model, null);
                                builder.Append("<td style='vnd.ms-excel.numberformat:@'>" + ((objResult == null) ? string.Empty : objResult) + "</td>");
                            }
                            else
                            {
                                throw new Exception("属性名称可能不存在!");
                            }
                        }
                    }
                }
                builder.Append("</tr>\n");
            }


            builder.Append("</table>\n");
            response.Write(builder.ToString());
            response.End();
        }



    }

这是一个生成excle的方法，下面在页面中调用这个方法即可：

        protected void BtnExport_Click(object sender, EventArgs e)
        {
            List<string> columnName = new List<string>();
            string[] name = { "用户名", "姓名", "年级", "手机号码", "注册日期", "订制情况" };//生成excle表中标题的字段
            foreach (string str in name)
            {
                columnName.Add(str);//遍历加到集合中
            }

            List<string> propertyName = new List<string>();
            string[] property = { "UserId", "UserName", "Grade", "CellPhoneNumber", "RegTime", "type" };//标题对应的字段，注意区分大小写
            foreach (string str in property)
            {
                propertyName.Add(str);
            }
            List<Models.Users> users = (List<Models.Users>)ViewState["UsersList"];
            
            List<Models.nuser> lst=new List<Models.nuser>();
            foreach(Models.Users user in users)//将字段中由数字变成具体代表的意思
            {
                Models.nuser nuser = new Models.nuser();
                nuser.UserId = user.UserId;
                nuser.UserName = user.UserName;
                nuser.Sex= user.Sex.YiDongToSex();
                nuser.Grade= user.Grade.YiDongToGrade();
                nuser.CellPhoneNumber = user.CellPhoneNumber;
                nuser.RegTime= user.RegTime.YiDongToDate();
                nuser.type= user.type.YiDongtoFreeList(Convert.ToDateTime(user.RegTime));
                lst.Add(nuser);
            }
            YiDong.BLL.ToExcel<Models.nuser>.DataBindTitleExcel(
                this.Page.Response, lst,columnName, propertyName, "用户列表", "**公司");//调用导出excle方法
        }