无模板导出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 [获取参数]
//开始时间
public string DateStart
{
get
{
return string.IsNullOrEmpty(Request.QueryString["start"]) ? string.Empty : Request.QueryString["start"];
}
}
//结束时间
public string DateEnd
{
get
{
return string.IsNullOrEmpty(Request.QueryString["end"]) ? string.Empty : Request.QueryString["end"];
}
}
//月
public string Month
{
get
{
return string.IsNullOrEmpty(Request.QueryString["m"]) ? "0" : Request.QueryString["m"];
}
}
//年
public string Year
{
get
{
return string.IsNullOrEmpty(Request.QueryString["y"]) ? "0" : Request.QueryString["y"];
}
}
#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 < listTitle.Count; title++)
{
worksheet.get_Range(worksheet.Cells[3, colIndex], worksheet.Cells[3, colIndex]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;
worksheet.get_Range(worksheet.Cells[3, colIndex], worksheet.Cells[3, colIndex]).Font.Bold = true;

worksheet.Cells[3, colIndex] = listTitle[title].ToString();

colIndex++;
}

//显示的数据行
for (int i = 0; i < RpAllClientTable.Rows.Count; i++)
{
worksheet.Cells[i + 4, 1] = i + 1;
worksheet.get_Range(worksheet.Cells[i + 4, 1], worksheet.Cells[i + 4, 1]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;

worksheet.Cells[i + 4, titIndex + 1] = i + 1; //序号
worksheet.Cells[i + 4, titIndex + 2] = RpAllClientTable[i]["CustomType"].ToString(); //客户类型
worksheet.Cells[i + 4, titIndex + 3] = RpAllClientTable[i]["ProvinceName"].ToString(); //客户省份
worksheet.Cells[i + 4, titIndex + 4] = RpAllClientTable[i]["CityName"].ToString(); //客户城市
worksheet.Cells[i + 4, titIndex + 5] = RpAllClientTable[i]["CompanyName"].ToString(); //客户公司
worksheet.Cells[i + 4, titIndex + 6] = RpAllClientTable[i]["DepartmentName"].ToString(); //客户部门
worksheet.Cells[i + 4, titIndex + 7] = RpAllClientTable[i]["ClientName"].ToString(); //客户名字
worksheet.Cells[i + 4, titIndex + 8] = RpAllClientTable[i]["GuestCount"].ToString(); //客人数量
worksheet.Cells[i + 4, titIndex + 9] = RpAllClientTable[i]["GZW_Count"].ToString(); //占位人数
worksheet.Cells[i + 4, titIndex + 10] = RpAllClientTable[i]["GFY_Count"].ToString(); //费用人数
worksheet.Cells[i + 4, titIndex + 11] = RpAllClientTable[i]["TotalPrice"].ToString(); //实际金额
worksheet.Cells[i + 4, titIndex + 12] = RpAllClientTable[i]["GYZ_Count"].ToString(); //预占人数
worksheet.Cells[i + 4, titIndex + 13] = RpAllClientTable[i]["GYB_Count"].ToString(); //预报人数
worksheet.Cells[i + 4, titIndex + 14] = RpAllClientTable[i]["GQX_Count"].ToString(); //取消人数
worksheet.Cells[i + 4, titIndex + 15] = RpAllClientTable[i]["GZT_Count"].ToString(); //转团人数
worksheet.Cells[i + 4, titIndex + 16] = RpAllClientTable[i]["CQ_Count"].ToString(); //出签人数
worksheet.Cells[i + 4, titIndex + 17] = RpAllClientTable[i]["QCBZ_Count"].ToString(); //签出不走人数
worksheet.Cells[i + 4, titIndex + 18] = RpAllClientTable[i]["JQ_Count"].ToString(); //拒签人数

}

worksheet.Application.DisplayAlerts = false; //不显示提示信息
workbook.SaveAs(FullFileName, missing, missing, missing, missing, missing, Excel.XlSaveAsAccessMode.xlNoChange, missing, missing, missing, missing, missing);
}
finally
{
// 关闭，释放
if (workbook != null)
{
workbook.Close(false, null, null);
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheet);
workbook = null;
worksheet = null;
}
}
}
finally
{
// 关闭，释放
if (excel != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
excel.Quit();
ErpCommon.KillExcel(excel);
excel = null;
}
}
}
#endregion

#region [事件]
protected void Button_Command(object sender, CommandEventArgs e)
{
if (e.CommandName.ToUpper() == "EXCEL")//导出Excel
{
if (RpAllClientTable != null)
{
DateTime dateBegin = DateTime.Now;

string strWordPath = strAddress + @"/erpExcel/"; //存放路径
string strSaveFileName = string.Format("客户总报表{0:yyyyMMdd}.xls", DateTime.Now); //存放名称
string strSourcePath = Request.PhysicalApplicationPath;

if (!Directory.Exists(strWordPath))
{
try
{
Directory.CreateDirectory(strWordPath);//word存放路径
}
catch { }
}
try
{
string strDesFilePath = strWordPath + strSaveFileName;
//创建Excel
CreateExcel(strDesFilePath);

//下载文件
Byecity2009.Erp.Web.CommonMethod.FileDownload(Response, strDesFilePath);
}
catch (Exception ex)
{
throw ex;
}
}
}
}
#endregion
}
}