C # Export Excel (DataTable export Excel)

Using System; Using System.Collections.Generic; Using System.Text; Using System.Data; Using System.IO; Using System.Web; Using Microsoft.Office.Interop.Excel; Using System.Reflection;

/* Developer: Hisen * Time: November 24, 2008 * Function: Export data to Excel */namespace XT. Litree.logic {public class Excelexport {private Excelexport () {} private static Excelex Port _instance = null;

        public static Excelexport Instance          {            Get              {                 if (_instance = = null) _instance = new Excelexport ();                 return _instance;            }        }

       //<summary>        // DataTable directly exports Excel, this method will open the DataTable data in Excel, and then manually save to the exact location        ///</ Summary>        //<param name= "DT" > to export Excel datatable</param>        //<returns></returns>          public bool Doexport (DataTable dt)         {             Application app = new ApplicationClass ();             if (app = = null)              {                 throw new Exception ("Excel cannot Start");            }             app. Visible = true;             Workbooks WBS = app. Workbooks;             Workbook wb = WBS. ADD (Missing.Value);             Worksheet ws = (Worksheet) WB. WORKSHEETS[1];

            int cnt = dt. Rows.Count;             int columncnt = dt. Columns.count;

           //***************** Get Data ********************             object[,] objdata = new object[cnt + 1, columncnt];  //Create cache data            //Get column headings              for (int i = 0; i < columncnt; i++)      & nbsp;      {                 objdata[0, I] = dt. Columns[i]. ColumnName;            }             //Get Specific data             for (int i = 0; i < CNT; i++)             {                 System.Data.DataRow dr = dt. Rows[i];                 for (int j = 0; J < columncnt; J + +)                 {                     objdata[ i + 1, j] = Dr[j];                }             }

Write excel****************** Range r = Ws.get_range (app. Cells[1, 1], app.             cells[cnt + 1, columncnt]);             R.numberformat = "@";             R = R.get_resize (cnt+1, columncnt);             R.value2 = Objdata; R.entirecolumn.autofit ();

App = null;         return true; }

       //<summary>        // DataTable exported via stream Excel        ////</summary>         //<param name= "DS" > Data source dataset</param>        //< param name= Column name in "Columns" >datatable (can be in Chinese), or null to fetch field names in DataTable </param>         //<param name= "filename" > Save file Name (ex: a.xls) </param>         ///<returns></returns>         public bool Streamexport (DataTable DT, string[] columns, string fileName, System.Web.UI.Page pages)         {            if (dt. Rows.Count > 65535)//total number of rows greater than Excel             {                 throw new Exception ("Total number of pre-exported data rows greater than Excel lines") ;            }              if (string. IsNullOrEmpty (FileName)) return false;                          StringBuilder content = new StringBuilder ();             StringBuilder strtitle = new StringBuilder ();             content. Append ("

               //<summary>         ///Export Excel        //</directly from the GridView Summary>        //<param name= "CTL" > Control (typically GridView) </param>        //<param name= "filename" > exported filename </param>         //<param name= "Removeindexs" > an indexed array of columns to remove (because sometimes we don't want to export all the columns in the GridView) </ Param>        //<param name= "pages" ></param>          public void Controltoexcel (System.Web.UI.WebControls.GridView ctl, String FileName, String[] Removeindexs, System.Web.UI.Page pages)         {             if (removeindexs! = null)             {                 foreach (string index in Removeindexs)                  {                     ctl. Columns[int. Parse (index)]. Visible = false;                }             }              pages. Response.Charset = "UTF-8";             pages. response.contentencoding = System.Text.Encoding.UTF7;             pages. Response.ContentType = "Application/ms-excel";             filename = System.Web.HttpUtility.UrlEncode (filename, System.Text.Encoding.UTF8);             pages. Response.appendheader ("Content-disposition", "attachment;filename=" + "+" + filename);             ctl. Page.enableviewstate = false;             System.IO.StringWriter tw = new System.IO.StringWriter ();             System.Web.UI.HtmlTextWriter HW = new System.Web.UI.HtmlTextWriter (TW);             ctl. RenderControl (HW);             pages. Response.Write (TW. ToString ());             HttpContext.Current.ApplicationInstance.CompleteRequest ();        }    }}

C # Export Excel (DataTable export Excel)