源地址：http://jackyi.javaeye.com/blog/787442
c#调用excel模板页，然后套用模板页导出多个sheet

文章分类:.net编程
//明细导出
        protected void btn_DetailExport_Click(object sender, EventArgs e)
        {
            int count1 = this.ASPxGridView1.VisibleRowCount;
            //Response.Redirect("../Quote/QuoteOutput.aspx");
            //先判断网格是否为空，或者当数据超过255条是提示数据过多。
            if (count1 == 0)
            {
                Response.Write("<script>'alert('网格没有数据,不能导出')</script>");
            }
            if (count1 > 255)
            {
                Response.Write("<script>'alert('数据量超过255条,不能导出')</script>");
            }
            string filepath = null;
            GC.Collect();
            //获得模板页的路径
            string strFileName = Server.MapPath(Request.ApplicationPath) + @"ShoesPic\template.xls";
            Application excel = new Application();//用于导出
            Workbook xBk;
            excel.Visible = true;
            excel.UserControl = true;
            object missing = System.Reflection.Missing.Value;
            //加载Excel模板文件
            xBk = excel.Workbooks.Open(strFileName, missing, true, missing, missing, missing, missing, missing, missing, true, missing, missing, missing, missing, missing);
            Worksheet xSt;
            // 复制count1-1个WorkSheet对象
            for (int i = 1; i < count1; i++)
            {
                ((Worksheet)xBk.Worksheets.get_Item(i)).Copy(missing, xBk.Worksheets[i]);
            }
            if (excel == null)
            {
                Response.Write("<script>alert('Can't access excel')</script>");
            }
            else
            {
                try
                {
                    //xSt.Name = "报价单导出"; //这里修改sheet名称
                    //获得ASPxGridView的所有数据
                    for (int j = 0; j < count1; j++)
                    {
                        xSt = (Worksheet)xBk.Worksheets.get_Item(j+1);
                        //xSt = (Worksheet)xBk.Sheets[j + 1];//第一个sheet页
                        object data = this.ASPxGridView1.GetRowValues(j, "报价单编号");//获得主键数据
                        xSt.Name = data.ToString();
                        Basic.QuoteManager manager = new Basic.QuoteManager();
                        Model.Quote quote = manager.getQuotevalueByNo(data.ToString());
                        string providername = quote.ProviderName;
                        string brand = quote.Brand;
                        string colthnob = quote.Colthnob;
                        string q_user = quote.Q_User;
                        string phone = quote.Phone;
                        string mobile = quote.Mobile;
                        string c_user = quote.C_User;
                        string principal = quote.Principal;
                        string leader = quote.Leader;
                        string picurl = quote.PicUrl;
                        string user = quote.User;
                        string remark = quote.Remark;
                        string q_date = quote.Q_Date.ToString();
                        string contract_price = quote.Contract_Price.ToString();
                        //报价单位
                        xSt.get_Range("B2", missing).Value2 = providername;
                        //品牌
                        xSt.get_Range("B3", missing).Value2 = brand;
                        //款号/工厂货号
                        xSt.get_Range("B4", missing).Value2 = colthnob;
                        //报价人
                        xSt.get_Range("B5", missing).Value2 = q_user;
                        //联系电话
                        xSt.get_Range("B6", missing).Value2 = phone;
                        //移动电话
                        xSt.get_Range("B7", missing).Value2 = mobile;
                        //日期/报价日期
                        xSt.get_Range("D2", missing).Value2 = q_date;
                        //核价人员
                        xSt.get_Range("D3", missing).Value2 = c_user;
                        //品牌负责人
                        xSt.get_Range("D4", missing).Value2 = principal;
                        //分管领导
                        xSt.get_Range("D5", missing).Value2 = leader;
                        //图片/图片地址
                        xSt.get_Range("D6", missing).Value2 = picurl;
                        //制单人
                        xSt.get_Range("D7", missing).Value2 = user;
                        //合同价
                        xSt.get_Range("F2", missing).Value2 = contract_price;
                        //备注
                        xSt.get_Range("F3", missing).Value2 = remark;

                        ////明细表数据
                        Basic.QuoteManager manager1 = new Basic.QuoteManager();
                        Model.QuoteDetail[] quotedetail = manager1.getQuoteDetailvalueByNo(data.ToString());
                        int count = quotedetail.Length;
                        int rowsint = 10;
                        foreach (Model.QuoteDetail i in quotedetail)
                        {
                            //item 项目
                            xSt.Cells.get_Range("A" + rowsint, missing).Value2 = i.Item;
                            //itemname 名称
                            xSt.Cells.get_Range("B" + rowsint, missing).Value2 = i.Itemname;
                            //unit 单位
                            xSt.Cells.get_Range("C" + rowsint, missing).Value2 = i.Unit;
                            //q_dosage 单用量
                            xSt.Cells.get_Range("D" + rowsint, missing).Value2 = i.Q_Dosage;
                            // q_unitprice 单价
                            xSt.Cells.get_Range("E" + rowsint, missing).Value2 = i.Q_UnitPrice;
                            //q_price 金额
                            xSt.Cells.get_Range("F" + rowsint, missing).Value2 = i.Q_Price;
                            rowsint++;
                        }
                        //先清除模板页的数据，然后新增一个sheet
                       
                        //对一个sheet赋值后，需要新增一个sheet，然后再赋值操作
                        //xSt = (Worksheet)excel.Worksheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                    }
                    //保存的时候会先保存在服务器上，然后再从服务器下载下来。
                    filepath = Server.MapPath(Request.ApplicationPath) + @"ShoesPic\报价单导出.xls";
                    xBk.Saved = true;
                    xBk.SaveCopyAs(filepath);
                    //退出excel
                    excel.Quit();
                    excel = null;
                    xBk = null;
                    xSt = null;
                    Process[] procs = Process.GetProcessesByName("excel");

                    foreach (Process pro in procs)
                    {
                        pro.Kill();//没有更好的方法,只有杀掉进程
                    }
                    GC.Collect();

                    System.IO.FileInfo file = new System.IO.FileInfo(filepath);
                    Response.Clear();
                    Response.Charset = "GB2312";
                    Response.ContentEncoding = System.Text.Encoding.UTF8;
                    // 添加头信息，为"文件下载/另存为"对话框指定默认文件名
                    Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode(file.Name));
                    // 添加头信息，指定文件大小，让浏览器能够显示下载进度
                    Response.AddHeader("Content-Length", file.Length.ToString());

                    // 指定返回的是一个不能被客户端读取的流，必须被下载
                    Response.ContentType = "application/ms-excel";

                    // 把文件流发送到客户端
                    Response.WriteFile(file.FullName);
                    Response.Flush();//这个语句必须有，否则就不回弹出保存的对话框，搞了N久
                    // 停止页面的执行
                    Response.End();
                   
                }
                catch (Exception ex)
                {
                }
                finally
                {
                    if (File.Exists(filepath))
                    {
                        File.Delete(filepath);
                    }
                }
            }
        }