asp.net (C #) reading Excel's file content _ Practical Tips

. xls format Office2003 and the following versions
. xlsx format Office2007 and above version
. csv format a comma-delimited string literal (you can save both file types in this format)
Two different methods are used to read the first two formats and read the latter format.

Look at the program below:
Front page:

Copy Code code as follows:

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

Background code:

Copy Code code as follows:

Declaring variables (properties)
String Currfilepath = String. Empty; Full path to the file to be read
String currfileextension = String.  Empty; File name 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//inside of the 3 methods will be given below
protected void Btnread_click (Object Sender,eventargs e)
{
Upload (); Upload File method
if (this.currfileextension = = ". xlsx" | | this.currfileextension = ". xls")
{
DataTable dt = readexceltotable (Currfilepath); Reading Excel files (. xls and. xlsx formats)
}
else if (this.currfileextension = ". csv")
{
DataTable dt = Readexcelwidthstream (Currfilepath); Reading a. csv format file
}
}

The following list of 3 methods in the button click event

Copy Code code as follows:

<summary>
Upload files to temp directory
</ummary>
private void Upload ()
{
Httppostedfile file = This.fileSelect.PostedFile;
String fileName = file. FileName;
String temppath = System.IO.Path.GetTempPath (); Get System Temp File path
filename = System.IO.Path.GetFileName (filename); Get file name (without path)
This.currfileextension = System.IO.Path.GetExtension (fileName); To get the file name extension
This.currfilepath = TempPath + fileName; Gets the uploaded file path record to the previously declared global variable
File. SaveAs (This.currfilepath); Upload
}

<summary>
Ways to read Excel files in xls\xlsx format
</ummary>
<param name= "path" > Read all paths to Excel </param>
<returns></returns>
Private DataTable readexceltotable (string path)
{
Connection string
String connstring = "Provider=microsoft.ace.oledb.12.0;data source=" + path + "; Extended properties= ' Excel 8.0; Hdr=no;imex=1 '; '; Office 07 and above cannot have 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 '; '; Office 07 The following version because I use Office2010 so I didn't use this connection string can be selected according to their own circumstances or the program to determine which connection string to use
using (OleDbConnection conn = new OleDbConnection (connstring))
{
Conn. Open ();
DataTable sheetsname = conn. GetOleDbSchemaTable (Oledbschemaguid.tables,new object[]{null,null,null, "Table"}); Get all the names of sheet.
String firstsheetname = Sheetsname.rows[0][2]. ToString (); Get the name of the first sheet.
String sql = string. Format ("select * from [{0}],firstsheetname];") Query string
OleDbDataAdapter ada =new OleDbDataAdapter (sql,connstring);
DataSet set = new DataSet ();
Ada. Fill (set);
return set. Tables[0];

}
}

<summary>
Ways to read Excel files in CSV format
</ummary>
<param name= "path" > Read all paths to Excel </param>
<returns></returns>
Private DataTable Readexcelwithstream (string path)
{
DataTable dt = new DataTable ();
BOOL Isdthascolumn = false; Mark whether a DataTable has already generated a column
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); Reading a row to an array in comma-separated form
DataRow row = dt. NewRow ();
for (int i = 0;i<splitresult.length;i++)
{
if (!isdthascolumn)//If no columns have been generated
{
Dt. Columns.Add ("column" + i,typeof (string));
}
Row[i] = Splitresult[i];
}
Dt. Rows.Add (row); Add rows
Isdthascolumn = true; The column is no longer generated after the first row is read and the column is marked and then the row is read.
}
return DT;
}