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)