. NET Import Excel

Today I was in the import of Excel encountered some problems, by the way I rarely do this! My requirement is to import Excel to verify the correctness of the data and display the data to the page if there is an error message, pop it out.

What is the specific problem?

There are 2 ways to import Excel that depend on your needs

The first kind: directly is the page background write code, very easy to implement. The disadvantage is: the page to refresh and pop-up box only with the system comes with all I took the second way

Page code:

<form id= "FormData" enctype= "Multipart/form-data" method= "post" name= "files[]" action= "/handler/testhandler.ashx "Target=" > "Hidden_frame"

<asp:fileupload id= "Fileselect" runat= "Server"/>
<asp:button id= "Btnread" runat= "Server" text= "import Excel" onclick= "Btnread_click"/>

</div>
<div id= "Listinfo" runat= "Server" ></div>

</from>

Background code:

<summary>
Uploading files to the 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); Gets the file name extension
if (this.currFileExtension.ToLower () = = ". xls" | | this.currFileExtension.ToLower () = = ". xlsx")
{
This.currfilepath = TempPath + fileName; Gets the uploaded file path record to the previously declared global variable
File. SaveAs (This.currfilepath); Upload
}
Else
{
throw new Exception ("The file format you selected is not correct, you can only import Excel files!") ");
}

}

<summary>
Ways to read an Excel file in xls\xlsx format
</ummary>
<param name= "path" > Full path of Excel to be read </param>
<returns></returns>
Private DataTable readexceltotable (string path, String fileextension)
{
string connstring = String.Empty;
Connection string
if (fileextension = = ". xlsx")
{
connstring = "Provider=microsoft.ace.oledb.12.0;data source=" + path + "; Extended properties= ' Excel 8.0; Hdr=no;imex=1 '; "; Office 07 and later cannot have extra spaces and semicolons note

}
Else
{
connstring = "Provider=Microsoft.Jet.OLEDB.4.0;Data source=" + path + "; Extended properties= ' Excel 8.0; Hdr=yes;imex=1 '; "; Office 07 versions Below
}

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>
button click event
</summary>
<param name= "Sender" ></param>
<param name= "E" ></param>
protected void Btnread_click (object sender, EventArgs e)
{

Upload (); Upload File method
if (this.currfileextension = = ". xlsx" | | this.currfileextension = = ". xls")
{
DataTable dt = readexceltotable (Currfilepath, this.currfileextension); Read Excel files (. xls and. xlsx formats)

String list = "<table>";
for (int i = 0; i < dt. Rows.Count; i++)
{
List + = "<tr>";

for (int j = 0; j < dt. Columns.count; J + +)
{

var aa = dt. ROWS[I][J];
var bb = dt. ROWS[I][0];

List + = "<td>" + dt. ROWS[I][J] + "</td>";

}
List + = "</tr>";
}

listinfo.innerhtml = list + "</table>";

}

}

The second kind: is to walk the general processing program so the system's permission also good control some

The way I do this, I learned another point of knowledge is that form submission does not refresh the page

JS Code:

$ (function () {
$ ("#formData"). Submit (function () {
Return false;//prohibit form submission
});
$ ("Form input[type= ' submit ']"). Click (function () {

var url = $ (' form '). attr (' action '); Take the link you want to submit in the form
var param = {}; Assemble send parameters
param[' type '] = $ (' form Input[name=type] '). Val ();
$.post ("/handler/testhandler.ashx", param, Function (DOM) {
$ (' div '). html (DOM);
});
})

});

Although this method can not refresh the page but to upload the file, this way is not feasible

Then say something dry. The following method is the use of the IFRAME to complete its principle is that the form submission page does not refresh actually refreshed is the IFRAME attention to the red part

Page code:

<form id= "FormData" enctype= "Multipart/form-data" method= "post" name= "files[]" action= "/handler/testhandler.ashx " target=" > "Hidden_frame"

<input type= "File" Name= "Fileup" id= "Fileup"/>
<input type= "Submit" id= "Btnconfirm" value= "Import Excel"/>
<input type= "hidden" name= "type" id= "type" value= "108"/>//generic Handler accepted parameters

<iframe name= ' hidden_frame ' id= "hidden_frame" style= ' Display:none ' src= "testexcel.aspx" ></iframe >
</form>
<div class= "Get" id= "get" >

</div>

Background code:

declaring variables (attributes)
String Currfilepath = String. Empty; Full path to the file to be read
String currfileextension = String. Empty; File name extension

The Readexceltotable method is the same as above

Uploading files

private void Upload (HttpContext context)
{
Httppostedfile file = context. request.files["Fileup"];
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 = Path.getextension (fileName); Gets the file name extension
if (this.currFileExtension.ToLower () = = ". xls" | | this.currFileExtension.ToLower () = = ". xlsx")
{
This.currfilepath = TempPath + fileName; Gets the uploaded file path record to the previously declared global variable
File. SaveAs (This.currfilepath); Upload
}
Else
{
throw new Exception ("The file format you selected is not correct, you can only import Excel files!") ");
}

}

Private Resultmessage Showalert (HttpContext context)
{
Try
{
Upload (context); Upload File method
if (this.currfileextension = = ". xlsx" | | this.currfileextension = = ". xls")
{
System.Data.DataTable dt = readexceltotable (Currfilepath, this.currfileextension); Read Excel files (. xls and. xlsx formats)
String list = "<table>";
for (int i = 0; i < dt. Rows.Count; i++)
{
List + = "<tr>";

for (int j = 0; j < dt. Columns.count; J + +)
{

var aa = dt. ROWS[I][J];
var bb = dt. ROWS[I][0];
if (i! = 0)
{

List + = "<td>" + dt. ROWS[I][J] + "</td>";

}
List + = "</tr>";
}

var listinfo = list + "</table>";

You can modify your requirements in the code above

It says that I don't want to use the system's own pop-up window. So how do our data and error messages appear on the page? Take a look at the following code:

if (arr. Count! = 0)//collection of error hints
{

String str = string. Join (",", arr);
Context. Response.Write ("<script>var Msg=parent.document.getelementbyid (' get '); Msg.innerhtml= ' <font color=red > "+ listinfo+" </font> "; Parent.aa (' + str + "');</script>");//Only PARENT.AA () can call to your method on the page this method I was used to pop up the error message
return new Resultmessage (False,listinfo, 1,arr);
}
return new Resultmessage (false);
}
return new Resultmessage (false);
}
catch (Exception ex)
{

return new Resultmessage (false);
}
}

Done! The first time the Little woman wrote a blog! The code is not so far, please advise! In fact, the above code I have seen from the internet to change into their own needs, so that the need for the future!

. NET Import Excel