grence's program note

使用者「轉EXCEL」功能有莫名的偏好，不只是報表，有時候整個網頁都想用 EXCEL開…

之前 ASP.NET 1.1的時候有幾個轉 EXCEL方式：在JavaScript用ActiveX、OFFICE元件，用到後來覺得跳下載視窗比較好用，雖然轉出來的是偽EXCEL檔，但使用者沒有感覺就好。（也許就是因為這種心態才讓「什麼都要想EXCEL」之類奇怪的需求得以延續不斷吧。

public class Utility: System.Web.UI.Page
...{
    public Utility()
    ...{    }
        /**//// <summary>
        /// 把 HTML輸出成 EXCEL檔，ex:ToExcelFromControl(Page, Panel1, "SMGU10CR02T80");
        /// </summary>
        /// <param name="thePage">就傳 Page</param>
        /// <param name="obj">頁面上的物件id，例：panel1, dg</param>
        /// <param name="fileName">傳入 ooxx，會轉存成 ooxx.xls</param>
        public void ToExcelFromControl(System.Web.UI.Page thePage, System.Web.UI.Control obj, string fileName)
        ...{
            /* 參告一些有的沒的，只剩這個找的到網址…
             * http://blog.darkthread.net/blogs/darkthreadtw/archive/2007/10/03/tips-export-gridview-to-excel.aspx
             */

            System.IO.StringWriter stringWrite = new System.IO.StringWriter();
            System.Web.UI.HtmlTextWriter htmlWrite = new System.Web.UI.HtmlTextWriter(stringWrite);

            //System.Web.UI.HtmlControls.HtmlForm hf = new System.Web.UI.HtmlControls.HtmlForm();//不是必要的
            //thePage.Controls.Add(hf);
            //hf.Controls.Add(obj);
            //hf.RenderControl(htmlWrite);

            obj.RenderControl(htmlWrite);

            thePage.Response.Clear();
            thePage.Response.AddHeader("content-disposition", "attachment;filename=" + fileName + ".xls");
            thePage.Response.ContentEncoding = System.Text.Encoding.GetEncoding("utf-8");

            //thePage.Response.Cache.SetCacheability(HttpCacheability.NoCache);//asp.net 2.0 「有時候」會錯
            //好像是postback之後才呼叫轉excel不會錯（正常的submit button），如果是第一次進入網頁就要轉excel就會錯（window.open(ooxx?excel=1)）

            thePage.Response.ContentType = "application/vnd.xls";
            thePage.Response.Write(stringWrite.ToString());
            thePage.Response.End();
        }
}

第一個參數 System.Web.UI.Page thePage純粹是因為程式包裝考量加進去的，在別的Class(.aspx)使用例子：

  protected void Button1_Click(object sender, System.EventArgs e)
  ...{
      new Utility().ToExcelFromControl(Page, Page1, "SMGDRR3T80");
  }

感覺…用起來不算順手，不知道怎改成直接呼叫 ToExcelFromControl(Page1, "SMGDRR3T80"); 的形式…

有這個函數之後可以繼續包：

        public void ToExcelFromDataGrid(System.Web.UI.Page thePage, System.Web.UI.WebControls.DataGrid DG, string fileName)
        ...{
            DG.AllowPaging = false;
            DG.AllowSorting = false;

            ToExcelFromControl(thePage, DG, fileName);
        }

再包：

        public void ToExcelFromDataSet(System.Web.UI.Page thePage, DataSet ds, string fileName)
  ...{
            System.Web.UI.WebControls.DataGrid DG = new System.Web.UI.WebControls.DataGrid();
            DG.DataSource = ds;
            DG.DataBind();

            ToExcelFromDataGrid(thePage, DG, fileName);
        }

有需要可以再寫個 ToExcelFromSQL。

有這麼好打發就不叫使用者了…有時候還想在轉出來的檔案前面加些字（日期、標題之類的）、還有"001"轉檔之後會被變成 1，所以又生出了幾個函數：

        /**//// <summary>
        /// 把 HTML輸出成 EXCEL檔
        /// </summary>
        /// <param name="thePage">就傳 Page</param>
        /// <param name="obj">頁面上的物件id，例：panel1, dg</param>
        /// <param name="fileName">傳入 ooxx，會轉存成 ooxx.xls</param>
        /// <param name="writeSomeThingBefore">在檔案開頭寫些字…style之類</param>
        public void ToExcelFromControl(System.Web.UI.Page thePage, System.Web.UI.Control obj, string fileName, string writeSomeThingBefore)
        ...{
            System.IO.StringWriter stringWrite = new System.IO.StringWriter();
            System.Web.UI.HtmlTextWriter htmlWrite = new System.Web.UI.HtmlTextWriter(stringWrite);

            obj.RenderControl(htmlWrite);

            thePage.Response.Clear();
            thePage.Response.AddHeader("content-disposition", "attachment;filename=" + fileName + ".xls");
            thePage.Response.ContentEncoding = System.Text.Encoding.GetEncoding("utf-8");

            thePage.Response.ContentType = "application/vnd.xls";
            thePage.Response.Write(writeSomeThingBefore);
            thePage.Response.Write(stringWrite.ToString());
            thePage.Response.End();
        }
        /**//// <summary>
        /// DataGrid To Excel，ex: ToExcelFromDataGrid(Page, dgl, "SMGU10CR02T80", new int[] ...{ 0,1, 2, 3, 4, 5 })
        /// </summary>
        /// <param name="thePage"></param>
        /// <param name="DG"></param>
        /// <param name="fileName"></param>
        /// <param name="keepStringCol">"0001"之類的字串避免被 EXCEL轉成 1，數格子</param>
        public void ToExcelFromDataGrid(System.Web.UI.Page thePage, System.Web.UI.WebControls.DataGrid DG, string fileName, int[] keepStringCol)
        ...{
            DG.AllowPaging = false;
            DG.AllowSorting = false;
            foreach (int col in keepStringCol)
            ...{
                //if(col<DG.Columns.Count)//如果是接DataSet，DG.Columns.Count都是零，自動產生的欄位感覺不會算進Count
                ...{
                    try
                    ...{
                        foreach (System.Web.UI.WebControls.DataGridItem dgi in DG.Items)
                        ...{
                            //dgi.Cells[col].Text="'"+dgi.Cells[col].Text;
                            dgi.Cells[col].Attributes.Add("class", "keepText");
                        }
                    }
                    catch ...{}
                }
            }
            ToExcelFromControl(thePage, DG, fileName, @"<style>.keepText ...{ mso-number-format:\@; } </style>");
        }

new Utility().ToExcelFromDataGrid(Page, dgGrid, "SMMT10BR00T80", new int[] { 1, 9, 21 });