Asp. NET Excel Operations (OLE DB mode)

One:OLE DB way to manipulate Excel's personal understanding

is to manipulate the Excel as a database, all of the operation of Excel, it becomes the "database" operation. Then you need to have a connection string for the database.

The code is as follows:

"provider=microsoft.ace.oledb.12.0; "Data source=";" ; Extended properties=\ "Excel 12.0; Hdr=yes\ "";      

Where strFileName refers to the Excel file name.

Two: Read the Excel file and read the contents into the DataTable.

The code is as follows:

/// <summary>        ///Read Excel/// </summary>        /// <param name= "strFileName" >Excel file name</param>        /// <param name= "FileType" ></param>        /// <param name= "SheetName" >The name of the sheet in Excel</param>         Public StaticDataTable Excelreader (stringstrFileName,stringFileType,stringsheetname) {            stringConnStr ="";                DataTable _table; ConnStr="provider=microsoft.ace.oledb.12.0;"+"Data source="+ strFileName +";"+"; Extended properties=\ "Excel 12.0; Hdr=yes\ ""; Try            {                using(OleDbConnection conn =NewOleDbConnection (CONNSTR)) {Conn.                    Open (); OleDbDataAdapter mycommand=NewOleDbDataAdapter (string. Format ("SELECT * FROM [{0}$]", SheetName), conn); DataSet myDataSet=NewDataSet (); inti =Mycommand.fill (myDataSet); _table= myDataSet = =NULL?NULL: MyDataSet.Tables.Count = =0?NULL: mydataset.tables[0]; }                return_table; }            Catch(Exception ex) {return NULL; }        }

The returned result is a DataTable.
Three: Save the DataTable as an Excel file

The code is as follows:

        ///<summary>///Reading data from a DataTable into Excel///</summary>///<param name= "DT" >The Incoming DataTable</param>PublicStaticvoidDttoexcel (DataTable DT) {String fileName = ((String. IsNullOrEmpty (dt. TableName))?"Excel": dt. TableName) +". xls"; HttpContext.Current.Response.AppendHeader ("Content-disposition","Attachment;filename=" +FileName); HttpContext.Current.Response.Charset ="utf-8 System.Text.Encoding.Default; HttpContext.Current.Response.ContentType =  " "; GridView GridView1 = new GridView (); Gridview1.datasource = DT; Gridview1.databind (); StringWriter tw = new StringWriter (); HtmlTextWriter HW = new HtmlTextWriter (TW); Gridview1.rendercontrol (HW); HttpContext.Current.Response.Write (TW. ToString ()); HttpContext.Current.Response.End (); }

When you export an Excel file using a DataTable, you need to create a DataTable, following the creation of a small instance of the DataTable:

DataTable dt =NewDataTable (); string[] Strarr =New string[]            {                            "1",                            "2222",                            "333333333",                            "444"            };  for(inti =0; I < Strarr.count (); i++) {dt.            Columns.Add (Strarr[i]); } for(inti =0; I < Strarr.count (); i++) {DataRow DR2=dt.                NewRow (); dr2[0] ="Always remember"; dr2[1] =" One"; dr2[2] =" A"; dr2[3] =" -"; Dt.           Rows.Add (DR2); }            

Asp. NET Excel Operations (OLE DB mode)