ASP. NET (C #) Read the file contents of Excel

. xls format Office2003 and the following versions
. xlsx format Office2007 and later
Comma-delimited string literals in the. csv format (you can save both of these file types in this format)
There are two different ways to read the first two formats and to read the latter format.

The following program:
Page Front Desk:

The <div>       <%--file Upload control is  used to upload the file that will be read and get the file information through this control--%>      <asp:fileupload id= "Fileselect" runat= " Server "/>          <%--Click this button to execute the Read method--%>       <asp:button id=" Btnread "runat=" Server "text=" Readstart "/> </div>

Background code:

Declares a variable (property) string Currfilepath = String. Empty; The full path of the file to be read  string currfileextension = string. Empty;  File extension  //page_load Event Registration button Click event  protected void Page_Load (Object Sender,eventargs e)  {      This.btnRead.Click + = new EventHandler (Btnread_click);  }  button click event   //The 3 methods inside will give protected void Btnread_click (Object Sender,eventargs e) {     Upload () below;  Upload File Method     if (this.currfileextension = = ". xlsx" | | this.currfileextension = = ". xls")       {            DataTable dt = Readexceltotable (Currfilepath);  Read Excel files (. xls and. xlsx format)       }       else if (this.currfileextension = = ". csv")         {               DataTable dt = Readexcelwidthstream (Currfilepath);  Read. csv format file         }}

The 3 methods in the button click event are listed below

<summary>///upload file to temp directory///</ummary>private void Upload () {Httppostedfile file = this.fileselect.postedfile;string fileName = file. Filename;string TempPath = System.IO.Path.GetTempPath (); Gets the system temp file path filename = System.IO.Path.GetFileName (filename); Get file name (without path) this.currfileextension = System.IO.Path.GetExtension (filename); Get the file extension This.currfilepath = TempPath + fileName; Gets the uploaded file path recorded to the previously declared global variable file. SaveAs (This.currfilepath); Upload}///<summary>///to read xls\xlsx formatted Excel file///</ummary>///<param name= "path" > Full path of Excel to be read < /param>///<returns></returns>private DataTable readexceltotable (string path) {//connection string connstring = "Provider=microsoft.ace.oledb.12.0;data source=" + path + "; Extended properties= ' Excel 8.0; Hdr=no;imex=1 '; "; Office 07 and later versions cannot appear with extra spaces and semicolons Note//string connstring = Provider=Microsoft.Jet.OLEDB.4.0;Data source= "+ path +"; Extended properties= ' Excel 8.0; Hdr=no;imex=1 '; "; The following versions of Office 07 do not use this connection string because I use Office2010, which can be based on selfThe choice or program determines which connection string to use using (OleDbConnection conn = new OleDbConnection (connstring)) {Conn. Open ();D atatable sheetsname = conn. GetOleDbSchemaTable (Oledbschemaguid.tables,new object[]{null,null,null, "Table"}); Get the name of all sheet string firstsheetname = sheetsname.rows[0][2]. ToString (); Get the first sheet name string sql = string. Format ("select * from [{0}],firstsheetname)"; Query string OleDbDataAdapter ada =new OleDbDataAdapter (sql,connstring);D ataset set = new DataSet (); Ada. Fill (set); return set. Tables[0];}} <summary>///ways to read Excel files in CSV format///</ummary>///<param name= "path" > Full path of Excel to be read </param> <returns></returns>private DataTable Readexcelwithstream (string path) {DataTable dt = new DataTable (); BOOL Isdthascolumn = false; Mark whether the DataTable has generated columns StreamReader reader = new StreamReader (Path,system.text.encoding.default); Data flow while (!reader. Endofstream) {String meaage = reader. ReadLine (); string[] Splitresult = message. Split (New char[]{', '},stringsplitoption.none); Read a comma-delimited lineDeposit the array datarow row = dt. NewRow (); for (int i = 0;i<splitresult.length;i++) {if (!isdthascolumn)//If not yet generated column {dt. Columns.Add ("column" + i,typeof (String));} Row[i] = Splitresult[i];} Dt. Rows.Add (row); Add row Isdthascolumn = true; Column}return DT is no longer generated when the first line is read and a row is marked for the existing column before it is read.