源地址：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");
            //先判斷網格是否為空，或者當數(shù)據(jù)超過255條是提示數(shù)據(jù)過多。
            if (count1 == 0)
            {
                Response.Write("<script>'alert('網格沒有數(shù)據(jù),不能導出')</script>");
            }
            if (count1 > 255)
            {
                Response.Write("<script>'alert('數(shù)據(jù)量超過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的所有數(shù)據(jù)
                    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, "報價單編號");//獲得主鍵數(shù)據(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;
                        //聯(lián)系電話
                        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;

                        ////明細表數(shù)據(jù)
                        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++;
                        }
                        //先清除模板頁的數(shù)據(jù)，然后新增一個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";

                    // 把文件流發(fā)送到客戶端
                    Response.WriteFile(file.FullName);
                    Response.Flush();//這個語句必須有，否則就不回彈出保存的對話框，搞了N久
                    // 停止頁面的執(zhí)行
                    Response.End();
                   
                }
                catch (Exception ex)
                {
                }
                finally
                {
                    if (File.Exists(filepath))
                    {
                        File.Delete(filepath);
                    }
                }
            }
        }