导出EXCEL

using System;
using System.Collections.Generic;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using Aspose.Cells;
using System.Data;
using System.IO;
using Sohu.Crm.ExportIqrDetail.SQLUilty;


namespace Sohu.Crm.ExportIqrDetail
{
    public partial class ExportDetail : System.Web.UI.Page
    {
        private DbManage Tool;
        public Aspose.Cells.Style NomalStyle ;  //Excel单元格普通样式
        public Aspose.Cells.Style DateStyle;    //Excel单元格日期样式
        public Aspose.Cells.Style AdviceStyle;    //建议填写字段样式
        public Aspose.Cells.Style RequireStyle;    //必填字段样式
        public Aspose.Cells.Style MiddleStyle;    //居中字体
        public string Iqrid = string.Empty;
        public int WaitIqrDetailCount = 0;

      //发件人地址(固定）
        protected void Page_Load(object sender, EventArgs e)
        {

            Tool = new DbManage();
            if (!IsPostBack)
            {
                if (string.IsNullOrEmpty(this.Request.QueryString["Iqrid"]))
                {
                    this.Response.Write("<script>alert('传入的ID号为空!')</script>");
                    this.Response.Write("<script language=javascript>window.opener = null;window.open('','_self'); window.close();window.parent.close()</script>");
      
                }
                else
                {
                    Iqrid = Request.QueryString["Iqrid"].ToString();
                    DataSet ds = Tool.GetAllIqrDetailInfo(Iqrid); //获取询价单下所有的询价明细信息
                    WaitIqrDetailCount = ds.Tables[0].Rows.Count;
                    if (WaitIqrDetailCount == 0)
                    {
                        this.Response.Write("<script>alert('此张询价单还没有生成明细!')</script>");
                        this.Response.Write("<script language=javascript>window.opener = null;window.open('','_self'); window.close();window.parent.close()</script>");
                    }
                }
            }
        }

        public void ExportDataToExcel(DataTable dt)
        {
            try
            {
                //string Path = Server.MapPath(@"\Files");//文件存放路径
                string Path = Server.MapPath(@"\sohu\ExportIqrDetail\Files");//文件存放路径
                Aspose.Cells.Workbook Excel = new Aspose.Cells.Workbook();
                if (!File.Exists(Path + @"\样式表.xls"))       //读取Excel单元格样式表
                {
                    this.Response.Write("<script>alert('没有找到样式表,请联系管理员!')</script>");
                    //this.Response.Write("<script language=javascript>window.opener = null;window.open('','_self'); window.close();window.parent.close()</script>");
                }
                Excel.Open(Path + @"\样式表.xls");//打开模板excel
                NomalStyle = Excel.Worksheets[0].Cells["A1"].Style;//普通样式
                DateStyle = Excel.Worksheets[0].Cells["B1"].Style;//日期样式 
                AdviceStyle = Excel.Worksheets[0].Cells["C1"].Style;//建议填写
                RequireStyle = Excel.Worksheets[0].Cells["D1"].Style;//必须填写
                MiddleStyle = Excel.Worksheets[0].Cells["E1"].Style;//居中字体


                Excel.Worksheets.Clear();
                Aspose.Cells.Worksheet sheet = Excel.Worksheets.Add("询价单明细");//添加sheet

                //添加列标题
                sheet.Cells["A1"].PutValue("");
                sheet.Cells["A1"].SetStyle(AdviceStyle);

                sheet.Cells["B1"].PutValue("建议供应商填写");
                sheet.Cells["B1"].SetStyle(NomalStyle);
                sheet.Cells["C1"].SetStyle(NomalStyle);

                sheet.Cells.Merge(0, 1, 1, 2);

                sheet.Cells["D1"].PutValue("");
                sheet.Cells["D1"].SetStyle(RequireStyle);

                sheet.Cells["E1"].PutValue("供应商必填内容");
                sheet.Cells["E1"].SetStyle(NomalStyle);
                sheet.Cells["F1"].SetStyle(NomalStyle);
               

                sheet.Cells.Merge(0, 4, 1, 2);


                sheet.Cells["A3"].PutValue("礼品");
                sheet.Cells["A3"].SetStyle(NomalStyle);

                sheet.Cells["B3"].PutValue("采购要求");
                sheet.Cells["B3"].SetStyle(NomalStyle);

                sheet.Cells["C3"].PutValue("采购数量");
                sheet.Cells["C3"].SetStyle(NomalStyle);

                sheet.Cells["D3"].PutValue("规格信息");
                sheet.Cells["D3"].SetStyle(AdviceStyle);

                sheet.Cells["E3"].PutValue("报价(单价)");
                sheet.Cells["E3"].SetStyle(RequireStyle);

                sheet.Cells["F3"].PutValue("报价(总价)");
                sheet.Cells["F3"].SetStyle(RequireStyle);

                sheet.Cells["G3"].PutValue("价格说明");
                sheet.Cells["G3"].SetStyle(RequireStyle);


                sheet.Cells["H3"].PutValue("确定打样天数");
                sheet.Cells["H3"].SetStyle(RequireStyle);

                sheet.Cells["I3"].PutValue("大活生产天数");
                sheet.Cells["I3"].SetStyle(RequireStyle);


                sheet.Cells["J3"].PutValue("价格有效日期");
                sheet.Cells["J3"].SetStyle(RequireStyle);


                sheet.Cells["K3"].PutValue("最早供货日期");
                sheet.Cells["K3"].SetStyle(AdviceStyle);

                sheet.Cells["L3"].PutValue("最迟供货日期");
                sheet.Cells["L3"].SetStyle(AdviceStyle);

                sheet.Cells["M3"].PutValue("说明");
                sheet.Cells["M3"].SetStyle(AdviceStyle);

                sheet.Cells["N3"].PutValue("供应商");
                sheet.Cells["N3"].SetStyle(RequireStyle);


                sheet.Cells.ImportDataColumn(dt, false, 3, 0, 4, false);//把 "礼品名称" 列放到Excel第一列
                sheet.Cells.ImportDataColumn(dt, false, 3, 2, 5, false);//把 "采购数量" 列放到Excel第二列
                sheet.Cells.ImportDataColumn(dt, false, 3, 3, 6, false);//把 "规格信息" 列放到Excel第三列
                sheet.Cells.ImportDataColumn(dt, false, 3, 1, 9, false);//把 "采购要求" 列放到Excel第四列
                sheet.Cells.ImportDataColumn(dt, false, 3, 11, 10, false);//把 "备注" 列放到Excel第六列
                //sheet.Cells.ImportDataColumn(dt, false, 3, 11, 12, false);//把 "供应商名称" 列放到Excel第十二列

                for (int j = 0; j < dt.Rows.Count; j++)
                {
                    for (int k = 0; k < 14; k++)
                    {
                        sheet.Cells[j + 3, k].SetStyle(MiddleStyle);//设置单元格的样式
                    }
                }
                sheet.AutoFitColumns();

                //生成文件名规则 年月日小时分钟秒.xls
                string iqrno = dt.Rows[0]["IqrName"].ToString();//取得供应商名称
                DateTime now = DateTime.Now;
                string FileName = now.Year.ToString() + (now.Month < 10 ? "0" : "") + now.Month.ToString() + (now.Day < 10 ? "0" : "") + now.Day.ToString();
                FileName = FileName + "搜狐询价单(" + iqrno + ").xls";
                DelYestodayFile();
                if (File.Exists(Path + @"\" + FileName))
                {
                    File.Delete(Path + @"\" + FileName);
                }
                Excel.Save(Path + @"\" + FileName);//保存文件
                DownLoadFile(Path + @"\" + FileName, FileName);
            }
            catch (Exception ex)
            {
                throw ex;
            }
           
        }
        protected void DelYestodayFile()
        {
            DateTime yestoday = DateTime.Now.AddDays(-1);
            string Delname = yestoday.Year.ToString() + yestoday.Month.ToString() + yestoday.Day.ToString();
            //string foldername = Server.MapPath(@"Files");//排期表所在目录
            string foldername = Server.MapPath(@"\Sohu\ExportIqrDetail\Files");//排期表所在目录
            string[] filesname = Directory.GetFiles(foldername);
            foreach (string name in filesname)
            {
                string Lastdir = name.Substring(name.LastIndexOf("\\"));
                if (Lastdir.IndexOf(Delname) > 0)
                {
                    File.Delete(name);
                }
            }
        }
        public void DownLoadFile(string filePath,string FileName)
        {
            FileInfo fileInfo = new FileInfo(filePath);
            if (fileInfo.Exists)//判断文件是否存在
            {
                Response.Clear();
                Response.ClearContent(); 
                Response.ClearHeaders();
                Response.Buffer = false;
                Response.AddHeader("Content-Disposition", "attachment;filename=" + Server.UrlEncode(FileName));//显示给用户的文件名
                Response.AddHeader("Content-Length", fileInfo.Length.ToString());//文件的大小
                Response.AddHeader("Content-Transfer-Encoding", "binary");
                Response.ContentType = "application/octet-stream";
                Response.ContentEncoding = System.Text.Encoding.GetEncoding("gb2312");//编码方式
                Response.WriteFile(fileInfo.FullName);
                Response.Flush();//下载文件
                Response.End();
            }
            else
            {
                this.Response.Write("<script>alert('文件不存在')</script>");
            }
        }

        protected void DownMyFile_Click(object sender, EventArgs e)
        {
           
            DataSet ds = Tool.GetAllIqrDetailInfo(this.HideIqrid.Text); //获取询价单下所有的询价明细信息
            WaitIqrDetailCount = ds.Tables[0].Rows.Count;
            if (WaitIqrDetailCount > 0)
            {
               
                Tool.ChangeIqrState(this.HideIqrid.Text);
                ExportDataToExcel(ds.Tables[0]);
                this.Response.Write("<script>window.returnValue='导出明细成功'</script>");
            }
            else
            {
                this.Response.Write("<script>alert('此张询价单还没有生成明细!')</script>");
                this.Response.Write("<script language=javascript>window.opener = null;window.open('','_self'); window.close();window.parent.close()</script>");
            }
            
        }
    }
}