B/S模式下导出动态生成Exel文件

第一种方式

        /// <summary>
        /// 导出Excel 自定义列名
        /// </summary>
        /// <param name="dt">DataTable数据源</param>
        /// <param name="fileName">文件名</param>
        /// <param name="list">列名集合 如: “UsernName|用户名”</param>
        public static void CreateExcel(DataTable dt, string fileName, ArrayList list)
        {
            HttpResponse Response = HttpContext.Current.Response;
            Response.Buffer = true;
            Response.ClearContent();
            Response.ClearHeaders();
            Response.AppendHeader("Content-Disposition", "attachment;filename=" + fileName);
            Response.ContentType = "application/vnd.ms-excel";
            Response.ContentEncoding = System.Text.Encoding.GetEncoding("gb2312");
            Response.Charset = "gb2312"; 

            try
            {
                StringBuilder text = new StringBuilder();
                //标题
                text.Append("<tr>");
                foreach (object obj in list)
                {
                    string[] str = obj.ToString().Split('|');
                    text.Append("<td>" + str[1] + "</td>");
                }
                text.Append("</tr>");
                //内容
                double outResul = 0;
                foreach (DataRow row in dt.Rows)
                {
                    text.Append("<tr>");
                    foreach (object obj in list)
                    {
                        string[] str = obj.ToString().Split('|');

                        if (double.TryParse(row[str[0]].ToString(), out outResul))
                        {
                            text.Append("<td style=\"vnd.ms-excel.numberformat:@\">" + row[str[0]].ToString() + "</td>");
                        }
                        else
                        {
                            text.Append("<td>" + row[str[0]].ToString() + "</td>");
                        }
                    }
                    text.Append("</tr>");
                }
                Response.Write("<meta http-equiv=\"content-type\" content=\"application/ms-excel; charset=gb2312\"/> <table border='1' >" + text.ToString() + "</table>");
            }
            catch { }

            Response.End();
        }

 

第二种方法

      

        //排班数据导出操作
        public void ArrangeDataExport(HttpContext context, int departmentID, string departmentName, int year, int month)
        {
            StringBuilder sb_title = new StringBuilder(); //标题
            StringBuilder sb_head_f = new StringBuilder();//月份
            StringBuilder sb_head_s = new StringBuilder();//星期
            StringBuilder sb_body = new StringBuilder();  //内容

            ArrayList arrayList = arrangeBll.EmployeesInfoList(departmentID);               //获取所有员工信息          
            tb_ArrangeTable arrangeTable = null;                                            //才创建排班表对象
            string name = string.Format("{0}{1}年{2}月排班表", departmentName, year, month);//命名格式
            int allDays = Utility.DaysInMonth(year, month);                                 //月中的所有天数
            List<DayOfWeek> lst_DayOfWeek = Utility.AllWeek(year, month);                   //指定月中所有星期集合

            using (StringWriter sw = new StringWriter())
            {
                sb_head_f.Append("\"\"\t\"\"\t\"\"\t");
                for (int j = 1; j <= allDays; j++)
                {
                    if (j == allDays)
                        sb_head_f.AppendFormat("{0}", j);
                    else
                        sb_head_f.AppendFormat("{0}\t", j);
                }
                sw.WriteLine(sb_head_f.ToString());//输出月份

                sb_head_s.Append("职务\t序号\t姓名\t");
                for (int j = 0; j < lst_DayOfWeek.Count; j++)
                {
                    if (j == lst_DayOfWeek.Count - 1)
                        sb_head_s.AppendFormat("{0}", Utility.ConvetWeek(lst_DayOfWeek[j]));
                    else
                        sb_head_s.AppendFormat("{0}\t", Utility.ConvetWeek(lst_DayOfWeek[j]));
                }
                sw.WriteLine(sb_head_s.ToString());//输入星期

                for (int i = 0; i < arrayList.Count; i++)
                {
                    //获取员工及部门信息
                    tb_EmployeesInfoTable employee = (tb_EmployeesInfoTable)arrayList[i];
                    sb_body.Clear();//清空内容

                    arrangeTable = new tb_ArrangeTable();
                    arrangeTable.EmployeeInfoID = employee.ID;
                    arrangeTable.SerialNumber = employee.SerialNumber;
                    arrangeTable.IsDel = false;

                    ArrayList arrangeList = arrangeBll.ArrangeList(arrangeTable, year, month); //获取指定员工的指定月份的所有考勤信息

                    //设置人员信息
                    sb_body.AppendFormat("{0}\t{1}\t{2}\t", employee.Job.G_CName, (i + 1).ToString(), employee.Name);

                    for (int j = 0; j < arrangeList.Count; j++)
                    {
                        //赋值排班信息
                        if (j == arrangeList.Count - 1)
                            sb_body.AppendFormat("{0}", Utility.ReturnMsg((tb_ArrangeTable)arrangeList[j]));
                        else
                            sb_body.AppendFormat("{0}\t", Utility.ReturnMsg((tb_ArrangeTable)arrangeList[j]));

                    }
                    sw.WriteLine(sb_body.ToString());//一个人的排班信息
                }

                //context.Response.AddHeader("Content-Disposition", "attachment; filename=test.csv");
                //context.Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", name));
                context.Response.AddHeader("Content-Disposition", "attachment; filename=ArrangeTable.xls");
                context.Response.ContentType = "application/ms-excel";
                context.Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
                context.Response.Write(sw);
                context.Response.End();
            }
        }