创建客户报表Excel-优快云博客

using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Web; using System.Web.UI; using System.Web.UI.WebControls; using Excel = Microsoft.Office.Interop.Excel; using System.IO; using Byecity2009.Erp.Data.Report; using Byecity2009.Erp.BusinessFacade.Report; using Byecity2009.Erp.BusinessFacade; namespace Byecity2009.Erp.SharePoint { public class RAAllClientReport : UserControl { #region [全局变量] Button btnExcel; //private string strAddress = @"F:/Byecity Work/Byecity2009/Project Solution/Byecity2009.Erp/Byecity2009.Erp.WebApplication/WordModule"; private string strAddress = @"C:/Program Files/Common Files/Microsoft Shared/web server extensions/12/TEMPLATE/LAYOUTS/ERPResources";//存放路径（word模版以及生成的word）--服务器 #endregion protected void Page_Load(object sender, EventArgs e) { btnExcel = (Button)FindControl("btnExcel"); #region [moss里用Response生成Excel或word以后页面按钮失效问题,解决办法] string beforeSubmitJS = "/nvar exportRequested = false; /n"; beforeSubmitJS += "var beforeFormSubmitFunction = theForm.onsubmit;/n"; beforeSubmitJS += "theForm.onsubmit = function(){ /n"; beforeSubmitJS += "var returnVal = beforeFormSubmitFunction(); /n"; beforeSubmitJS += "if(exportRequested && returnVal) {_spFormOnSubmitCalled=false; exportRequested=false;} /n"; beforeSubmitJS += "return returnVal; /n"; beforeSubmitJS += "}; /n"; this.Page.ClientScript.RegisterStartupScript(this.GetType(), "alterFormSubmitEvent", beforeSubmitJS, true); this.btnExcel.Attributes["onclick"] = "javascript:exportRequested=true;"; #endregion if (!Page.IsPostBack) { Page.DataBind(); } } #region [属性] public AchievementData.RPClientAllAchievementDataTable RpAllClientTable { get { DateTime dtCountDate = Convert.ToDateTime(string.Format("{0}-{1}-26", DateTime.Now.Year, DateTime.Now.Month));//财务结算时间如:2010-09-26至2010-09-25 string strWhere = DateTime.Now.Day > 25 ? string.Format(" and CONVERT(varchar(10),OutTeamDate,120) between '{0}' and '{1}' ", dtCountDate.AddMonths(-1), dtCountDate.AddDays(-1)) : string.Format(" and OutTeamDate between '{0}' and '{1}' ", dtCountDate.AddMonths(-2), dtCountDate.AddMonths(-1).AddDays(-1)); if (!string.IsNullOrEmpty(DateStart) && !string.IsNullOrEmpty(DateEnd)) { strWhere = string.Format(" and CONVERT(varchar(10),OutTeamDate,120) between '{0}' and '{1}' ", DateStart, DateEnd); } else if (!string.IsNullOrEmpty(DateStart)) { strWhere = string.Format(" and CONVERT(varchar(10),OutTeamDate,120) ='{0}' ", DateStart); } strWhere = BFAction.CreateUserDataAction_ETable("00298") + strWhere; return new BFAchievement().GetClientAllAchievementByWhere(strWhere, " order by CustomType,ProvinceName,CityName, CompanyName,DepartmentName,ClientName"); } } #endregion #region [创建Excel] private void CreateExcel(string path) { #region excel表头 List<string> listTitle = new List<string>(); listTitle.Add("序号"); listTitle.Add("客户类型"); listTitle.Add("客户省份"); listTitle.Add("客户城市"); listTitle.Add("客户公司"); listTitle.Add("客户部门"); listTitle.Add("客户姓名"); listTitle.Add("报名人数"); listTitle.Add("占位人数"); listTitle.Add("费用人数"); listTitle.Add("实际金额"); listTitle.Add("预占人数"); listTitle.Add("预报人数"); listTitle.Add("取消人数"); listTitle.Add("转团人数"); listTitle.Add("出签人数"); listTitle.Add("不走人数"); listTitle.Add("拒签人数"); #endregion //请求一个Excel的类 Excel.ApplicationClass excel = null; Excel._Workbook workbook = null; //工作薄 Excel._Worksheet worksheet = null; //Sheet页 try { excel = new Excel.ApplicationClass(); //要保存的文件名 string FullFileName = path; object missing = System.Reflection.Missing.Value; try { workbook = excel.Workbooks.Add(true); int titIndex = 0; int colIndex = 1; //列 //激活 workbook.Activate(); worksheet = (Excel.Worksheet)workbook.Sheets[1];//指定操作第一个表 worksheet.Name = string.Format("客户总报表" + "{0:yyyy-MM-dd}", DateTime.Now); worksheet.Cells.Borders.LineStyle = 1; worksheet.Columns.AutoFit(); //自动调整大小 worksheet.Cells.Font.Size = 10; //默认大小 worksheet.Cells.RowHeight = 16.5; //行高 //给主表添加数据 worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, listTitle.Count]).Merge(missing); worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, listTitle.Count]).Font.Size = 22; worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, listTitle.Count]).RowHeight = 32.25; worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, listTitle.Count]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter; worksheet.Cells[1, 1] = "客户总报表"; worksheet.get_Range(worksheet.Cells[2, 5], worksheet.Cells[2, listTitle.Count]).Merge(true); worksheet.get_Range(worksheet.Cells[2, 1], worksheet.Cells[2, 1]).Font.Bold = true; worksheet.get_Range(worksheet.Cells[2, 3], worksheet.Cells[2, 3]).Font.Bold = true; worksheet.get_Range(worksheet.Cells[2, 1], worksheet.Cells[2, 1]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter; worksheet.Cells[2, 1] = "出团日期"; worksheet.Cells[2, 2] = DateStart; worksheet.Cells[2, 3] = "至"; worksheet.Cells[2, 4] = DateEnd; //需要显示 Title for (int title = 0; title <div class="share_buttons" id="sharePanel"></div> <div class="article_next_prev"> <li class="prev_article"> <span>上一篇：</span><a href="http://blog.youkuaiyun.com/ououou123456789/article/details/6166742">数据SQl分类汇总方法</a> </li> <li class="next_article"> <span>下一篇：</span><a href="http://blog.youkuaiyun.com/ououou123456789/article/details/6406686">关于数据库还原时出现的因为数据库正在使用，所以无法获得对数据库的独占访问权“的解决方案</a> </li> </div> </string></string>

无模板导出EXCEL