Excel导出通用操作方式

后台导出数据，自己的总结导出经验，适合MVC项目：

遇到的问题是，火狐和非火狐的浏览器，我们必须要处理一下：

下图是火狐乱码问题：

            DataTable dt = new DataTable("用户登陆统计管理");
            DataColumn dc1 = new DataColumn("登录用户", Type.GetType("System.String"));
            DataColumn dc2 = new DataColumn("真实姓名", Type.GetType("System.String"));
            DataColumn dc3 = new DataColumn("合作商名字", Type.GetType("System.String"));
            DataColumn dc4 = new DataColumn("所在区域", Type.GetType("System.String"));
            DataColumn dc5 = new DataColumn("所属区域", Type.GetType("System.String"));
            DataColumn dc6 = new DataColumn("联系人", Type.GetType("System.String"));
            DataColumn dc7 = new DataColumn("家庭地址", Type.GetType("System.String"));
            DataColumn dc8 = new DataColumn("详细地址", Type.GetType("System.String"));
            DataColumn dc9 = new DataColumn("用户角色", Type.GetType("System.String"));
            DataColumn dc10 = new DataColumn("登录时间", Type.GetType("System.String"));
            DataColumn dc11 = new DataColumn("登录次数", Type.GetType("System.String"));
            dt.Columns.Add(dc1);
            dt.Columns.Add(dc2);
            dt.Columns.Add(dc3);
            dt.Columns.Add(dc4);
            dt.Columns.Add(dc5);
            dt.Columns.Add(dc6);
            dt.Columns.Add(dc7);
            dt.Columns.Add(dc8);
            dt.Columns.Add(dc9);
            dt.Columns.Add(dc10);
            dt.Columns.Add(dc11);

            foreach (User_Statistics model in list)
            {
                string[] arr = model.LoginDate.Split("".ToCharArray(), StringSplitOptions.RemoveEmptyEntries);
                string strArea = model.AgentProvince + ">" + model.AgentCity + ">" + model.AgentArea;
                string strArea1 = model.Province + "-" + model.City + "-" + model.Area;
                string[] strArr = strArea.Split('>');
                string[] strArr1 = strArea1.Split('-');
                string strAreaJoin = strArea.Length < 3 ? "未知" : strArea;
                string strAreaJoin1 = strArea1.Length < 3 ? "未知" : strArea1;

                string resultArea = String.Empty;
                string strProvince = model.AgentProvince;
                dict.TryGetValue(strProvince, out resultArea);

                DataRow dr = dt.NewRow();
                dr["登录用户"] = model.UserName;
                dr["真实姓名"] = string.IsNullOrEmpty(model.RealName) ? "未知" : model.RealName;
                dr["合作商名字"] = model.AgentName;
                dr["所在区域"] = strAreaJoin;
                dr["所属区域"] = string.IsNullOrEmpty(resultArea) ? "未知" : resultArea;
                dr["联系人"] = model.AgentContact;
                dr["家庭地址"] = strAreaJoin1;
                dr["详细地址"] = string.IsNullOrEmpty(model.Address) ? "未知" : model.Address;
                dr["用户角色"] = (model.UserRole == null) || (model.UserRole == "0") || (model.UserRole == "") ? "未知" : Web.Helper.ViewHelper.GetUserRoleName(model.UserRole);
                dr["登录时间"] = arr[2] + "-" + arr[0] + "-" + arr[1];
                dr["登录次数"] = model.LoginCount ?? "";

                dt.Rows.Add(dr);
            }
            string selRoleName = userRole == "" ? "全部用户" : Web.Helper.ViewHelper.GetUserRoleName(userRole);
            string ExcelFileName = selRoleName + "-日志" + "-" + DateTime.Now.ToString("yyyy-MM-dd HH-mm-ss") + ".xls";

            //火狐浏览器
            if (browserType == "Firefox")
            {
                //附件下载操作
                string contentType = "application/x-xls";
                byte[] byteArr = new Web.Helper.ExportExcelOprate().ExportExcelDT(dt);
                FileContentResult fcResult = new FileContentResult(byteArr, contentType);
                fcResult.FileDownloadName = ExcelFileName;
                fcResult.ExecuteResult(this.ControllerContext);
            }
            //非火狐浏览器
            else
            {
                //附件下载
                Response.ContentType = "application/x-xls";
                Response.ContentEncoding = System.Text.Encoding.GetEncoding("UTF-8");
                string encodFileName = HttpUtility.UrlEncode(System.Text.Encoding.UTF8.GetBytes(ExcelFileName));//解决中文名，乱码问题
                Response.AppendHeader("Content-Disposition", "attachment;filename=" + encodFileName);
                Response.Write(System.Text.Encoding.UTF8.GetString(new Web.Helper.ExportExcelOprate().ExportExcelDT(dt)));
            }

 

ExportExcelOprate类的通用方法：

//创建人：liwei
    //创建时间：2012/10/25
    public class ExportExcelOprate
    {
        /// <summary>
        /// 导出Excel Datatable版本
        /// </summary>
        /// <param name="dt">导出的Datatable</param>
        /// <param name="ExcelName">导出EXCEL的名称 不需要要带有扩展名_xls</param>
        public byte[] ExportExcelDT(DataTable dt)
        {
            System.Text.StringBuilder sbStr = new System.Text.StringBuilder();

            string colHeaders = "", ls_item = "";
            DataRow[] myRow = dt.Select();//可以类似dt.Select("id>10")之形式达到数据筛选目的
            int i = 0;
            int cl = dt.Columns.Count;
            //取得数据表各列标题，各标题之间以t分割，最后一个列标题后加回车符
            sbStr.Append("<html><head><meta http-equiv=\"Content-Type\" content=\"text/html; charset=utf-8\" /></head><body><table border=1><tr style=\"color:Black;border: Gray 1px solid;text-align:center;\">");
            for (i = 0; i < cl; i++)
            {
                colHeaders += "<th>" + dt.Columns[i].Caption.ToString() + "</th>";
            }
            sbStr.Append(colHeaders + "</tr>");
            //向HTTP输出流中写入取得的数据信息
            //逐行处理数据
            foreach (DataRow row in myRow)
            {
                //当前行数据写入HTTP输出流，并且置空ls_item以便下行数据  
                ls_item = "<tr style='text-align:center;Color:Black;font-size:15px;'>";
                for (i = 0; i < cl; i++)
                {
                    if (i == (cl - 1))//最后一列，加n
                    {
                        ls_item += "<td>" + row[i].ToString() + "</td></tr>";
                    }
                    else
                    {
                        ls_item += "<td>" + row[i].ToString() + "</td>";
                    }
                }
                sbStr.Append(ls_item);
            }
            sbStr.Append("</table></body></html>");
            return System.Text.Encoding.UTF8.GetBytes(sbStr.ToString());
        }
    }

 

 

 

转载于:https://www.cnblogs.com/Kummy/archive/2013/04/03/2957243.html