C# 导出 excel 复杂格式 html导出 

/// <summary>
        /// 夜班津贴统计导出
        /// </summary>
        public void ExportOtStat(string data)
        {
            var input = data.DeserializeObject<StructExportOtStat>();
            List<Usp_PRO_ExportOtStatResult> list = null;
            using (var context = PRODBHelper.DataContext)
            {
                list = context.Usp_PRO_ExportOtStat(input.beginDate, input.endDate).ToList();
                //var output = results.ToList().SerializeObject();
                //HttpContext.Current.Response.Write(output);
            }
            //HttpContext.Current.Response.End();
            StringBuilder sbdHTML = new StringBuilder();
            DateTime StartDate = input.beginDate;
            DateTime EndDate = input.endDate;
            Hashtable hshData = new Hashtable();
            string[] arrColumns = {"序号",
                                   "员工编号",
                                   "姓名",
                                   "开始时间",
                                   "结束时间",
                                   "夜班日期",
                                   "津贴标准",
                                   "夜班次数",
                                   "津贴总数"};
            sbdHTML.Append("<HTML>");
            sbdHTML.Append(GetExcelTitleHTML(StartDate));
            sbdHTML.Append("<div style=\"height:50px\"></div>");
            sbdHTML.Append(GetExcelInfo(StartDate));
            sbdHTML.Append("<div style=\"height:10px\"></div>");
            sbdHTML.Append("<Table border=1>");
            sbdHTML.Append(GetOTExcelHeaderHTML(arrColumns));
            foreach (Usp_PRO_ExportOtStatResult item in list)
            {
                sbdHTML.Append(GetExcelRowHTML(item));
            }
            sbdHTML.Append("</Table>");
            sbdHTML.Append("</HTML>");
            HttpResponse resp;
            resp = System.Web.HttpContext.Current.Response;
            resp.Charset = "GB2312";
            resp.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
            resp.AppendHeader("Content-Disposition", "attachment;filename=" 
                + System.Web.HttpUtility.UrlEncode("信息总中心" + StartDate.Month.ToString() + "月夜班津贴统计.xls", System.Text.Encoding.UTF8));
            resp.Write(sbdHTML.ToString());
            resp.End();
        }
        private string GetExcelTitleHTML(DateTime ReportDate)
        {
            StringBuilder sbdHTML = new StringBuilder();
            sbdHTML.AppendFormat("<div align=\"center\" style=\"width:60%; font-size:large; font-weight:bold;\" >{0}年{1}月夜班津贴统计表</div>",
                ReportDate.Year.ToString(),ReportDate.Month.ToString());
            return sbdHTML.ToString();
        }
        private string GetExcelInfo(DateTime ReportDate)
        {
            StringBuilder sbdHTML = new StringBuilder();
            sbdHTML.Append("<div align=\"left\" style=\"width:60%\">部门（盖章）：信息总中心</div>");
            return sbdHTML.ToString();
        }
        private string GetOTExcelHeaderHTML(string[] arrColumns)
        {
            StringBuilder sbdHTML = new StringBuilder();
            sbdHTML.Append("<tr>");
            for (int i = 0; i < arrColumns.Length; i++)
            {
                sbdHTML.AppendFormat("<td>{0}</td>", arrColumns[i]);
            }
            sbdHTML.Append("</tr>");
            return sbdHTML.ToString();
        }
        private string GetExcelRowHTML(Usp_PRO_ExportOtStatResult item)
        {
            StringBuilder sbdHTML = new StringBuilder();
            sbdHTML.Append("<tr>");
            sbdHTML.AppendFormat("<td>{0}</td>", item.ID);
            sbdHTML.AppendFormat("<td>{0}</td>", item.UserID);
            sbdHTML.AppendFormat("<td>{0}</td>", item.UserName);
            sbdHTML.AppendFormat("<td>{0}</td>", item.BeginTime);
            sbdHTML.AppendFormat("<td>{0}</td>", item.EndTime);
            sbdHTML.AppendFormat("<td>{0}</td>", item.NightShiftDates);
            sbdHTML.AppendFormat("<td>{0}</td>", item.SalaryNormal.ToString());
            sbdHTML.AppendFormat("<td>{0}</td>", item.NightShiftNum.ToString());
            sbdHTML.AppendFormat("<td>{0}</td>", item.SalarySum.ToString());
            sbdHTML.Append("</tr>");
            return sbdHTML.ToString();
        }

 

posted @ 2014-10-25 22:14  dapeng888  阅读(1340)  评论(0)    收藏  举报