ASP.net three ways to read Excel files sample

  This article mainly introduces three methods of ASP.net reading Excel files, including using OLE DB to read Excel files, referencing COM components to read Excel files, read in file streams, and friends who need to refer to the following

Method one: Using OLE DB to read Excel files   to use Excel files as a data source for data reading operations, examples are as follows: The   code is as follows: Public DataSet exceltods (string Path)  & nbsp {   String strconn = "Provider=Microsoft.Jet.OLEDB.4.0 +" + "Data source=" + Path + ";" + "Extended Properties=excel 8.0;";    OleDbConnection conn = new OleDbConnection (strconn);    Conn. Open ();   String strexcel = "";    OleDbDataAdapter mycommand = null;    DataSet ds = null;    strexcel= "SELECT * from [sheet1$]";    mycommand = new OleDbDataAdapter (Strexcel, strconn);    ds = new DataSet ();    Mycommand.fill (ds, "Table1");    return DS;   }      for tables in Excel that are sheet ([sheet1$]) if not fixed, you can get the   code as follows: string strconn = "Provider =microsoft.jet.oledb.4.0 "+" "Data source=" + Path + ";" + "Extended Properties=excel 8.0;";   OleDbConnection conn = new OleDbConnection (strconn);  DataTable schematable = objconn.getoledbschematable ( System.dAta. Oledb.oledbschemaguid.tables,null);  string tablename=schematable.rows[0][2]. ToString (). Trim ();      Additionally: You can also write to Excel files, as follows:     Code as follows: public void Dstoexcel (String path,dataset oldds) &n bsp;  {  //first to get a summary of Excel datasets The main purpose is to obtain Excel's structure in the dataset    string Strcon = "Provider = Microsof t.jet.oledb.4.0; Data Source = "+path1+"; Extended Properties=excel 8.0 ";    OleDbConnection myconn = new OleDbConnection (Strcon);    string strcom= "select * from [sheet1$]";    MyConn.Open ();    OleDbDataAdapter mycommand = new OleDbDataAdapter (strcom, myconn);    System. Data.OleDb.OleDbCommandBuilder builder=new OleDbCommandBuilder (mycommand);   //quoteprefix and QuoteSuffix are primarily used when generating insertcomment commands for builder.    Builder. Quoteprefix= "["; Gets the reserved character (starting position)    Builder in the INSERT statement. Quotesuffix= "]"; Gets the reserved character (end position)    DataSet Newds=new DataSet () in the INSERT statement;    MyCoMmand. Fill (Newds, "Table1");    for (int i=0;i<oldds. Tables[0]. rows.count;i++)    {  //You cannot use the ImportRow method to import a row into news,   // Because ImportRow retains all the settings of the original DataRow (DataRowState State is unchanged).   //Newds within ImportRow after use, but cannot be updated to Excel because all imported rows have datarowstate!=added    DataRow nrow= adataset.tables["Table1"]. NewRow ();    for (int j=0;j<newds. Tables[0]. columns.count;j++)    {   Nrow[j]=oldds. Tables[0]. ROWS[I][J];   }    Newds. tables["Table1"]. Rows.Add (Nrow);   }    mycommand.update (Newds, "Table1");    Myconn.close ();   }      method Two: Referenced COM component: Microsoft.Office.Interop.Excel.dll read Excel file   First is Excel.dll's acquisition, Copy the Excel.exe files under the Office installation directory to the Dotnet Bin directory, cmd to the directory, and run TLBIMP EXCEL. EXE Excel.dll Get DLL file.   Add references to the DLL file in the project   code is as follows://Read Excel method     (read data in range area)    private void Openexcel (string strFileName)    { &NBSp Object missing = System.Reflection.Missing.Value;    Application Excel = New application ()//lauch Excel application        if (Excel = null)    {       Response.Write ("<script>alert (' Can ' t access Excel ') </script>");   }        Else    {   Excel. Visible = false;   Excel. UserControl = true;   //Open Excel file in read-only form    Workbook WB = Excel. Application.Workbooks.Open (strFileName, Missing, true, missing, missing, missing,    missing, missing, Missing, true, missing, missing, missing, missing, missing);   //Get the first job thin worksheet ws = (worksheet) WB. Worksheets.get_item (1); Gets the total number of rows (including the header column) int rowsint = ws. UsedRange.Cells.Rows.Count; Get row number//int Columnsint = mysheet.usedrange.cells.columns.count;//Get column number//Get data range area (excluding header column) range rng1 = ws. Cells.get_range ("B2", "B" + rowsint);//item Range rng2 = ws. Cells.get_range ("K2", "K" + roWsint);  //customer object[,] arryitem= (object[,]) rng1. Value2;//get Range ' s value object[,] Arrycus = (object[,]) rng2. value2; //assigns the new value to an array string[,] Arry = new string[rowsint-1, 2]; for (int i = 1; I <= rowsint-1 i++) {//item_code column arry[i-1, 0] =arryitem[i, 1]. ToString (); Customer_name row arry[i-1, 1] = arrycus[i, 1]. ToString (); Response.Write (arry[0, 0] + "/" + arry[0, 1] + "#" + arry[rowsint-2, 0] + "/" + arry[rowsint-2, 1]); }  excel. Quit (); excel = null; process[] procs = Process.getprocessesbyname ("Excel"); foreach (Process Pro in procs) {Pro. Kill ();//no better method, only the process} GC. Collect (); }     Method III: Converts an Excel file into a CSV (comma-delimited) file, read by file stream (equivalent to reading a txt text file).   Reference namespaces first:     code as follows: using System.Text; and using System.IO;    FileStream fs = new FileStream ("D:customer.csv", FileMode.Open, FileAccess.Read, Fileshare.none);    StreamReader sr = new StreamReader (FS, System.Text.Encoding.GetEncoding (936));    string str = "";    string s = Console.ReadLine ();    while (str!= null)    {           str = Sr. ReadLine ();        string[] Xu = new string[2];        Xu = str. Split (', ');        String ser = Xu[0];        String dse = Xu[1];                       if (Ser = s)        {&N Bsp           Console.WriteLine (DSE);       }   }       Sr. Close ();     Alternatively, you can import database data into a TXT file, as follows:   code://txt filename    string fn = DateTime.Now.ToString (" Yyyymmddhhmmss ") +"-"+" PO014 "+". txt ";    OleDbConnection con = new OleDbConnection (CONSTR);    con. Open ();    String sql = "Select   Item,reqd_date,qty,pur_flg,po_num from tsd_po014";           /oleDbCommand mycom = new OleDbCommand ("SELECT * from tsd_po014", mycon);   //oledbdatareader myreader = mycom. ExecuteReader ();  //can also read data with Reader    DataSet ds = new DataSet ();    OleDbDataAdapter ODA = new OleDbDataAdapter (sql, con);    ODA. Fill (ds, "PO014");    DataTable dt = ds. Tables[0];    FileStream fs = new FileStream (Server.MapPath ("download/" + fn), FileMode.Create, FileAccess.ReadWrite);    StreamWriter strmwriter = new StreamWriter (FS);    //into a text file   //write title to the. txt file   //for (int i = 0; I <dt. columns.count;i++)   //{  //    strmwriter.write (dt. Columns[i]. ColumnName + " ");   //}    foreach (DataRow dr in Dt. Rows)    {   string str0, str1, str2, STR3;    string str = ' | ';  //data with ' | ' Separate open    str0=dr[0]. ToString (); STR1=DR[1]. ToString (); STR2=DR[2]. ToString (); str3=dr[3]. ToString (); STR4=DR[4]. ToString (). Trim (); Strmwriter.write (STR0); Strmwriter.write (str); Strmwriter.write (STR1); Strmwriter.write (str); Strmwriter.write (STR2); Strmwriter.write (str); Strmwriter.write (STR3); Strmwriter.writeline ();//newline} strmwriter.flush (); Strmwriter.close (); if (Con. State==connectionstate.open) {con. Close (); }