Today did a project like this, need to import Excel data into the database, although previously also written, but forget, so today find a better, collect up.
Here's the code:
protected void Btnimport_click (object sender, EventArgs e)
{
if (Fileupload1.hasfile = = False)//hasfile is used to check if FileUpload has the specified file
{
Response.Write ("<script>alert (' Please select Excel file ') </script>");
return;//when there is no file, return
}
String Isxls = System.IO.Path.GetExtension (fileupload1.filename). ToString (). ToLower ();//system.io.path.getextension gets the file name extension
if (Isxls! = ". xls")
{
Response.Write ("<script>alert (' can only select Excel files ') </script>");
return;//When you select a file other than Excel, return
}
string filename = Fileupload1.filename; Get execle filename datetime date function
String Savepath = Server.MapPath (("upfiles\\") + filename)//server.mappath obtaining a virtual server-relative path
Fileupload1.saveas (Savepath); SaveAs saves uploaded file contents on the server
DataSet ds = excelsqlconnection (savepath, filename); Calling a custom method
datarow[] Dr = ds. Tables[0]. Select (); Define a DataRow array
int rowsnum = ds. Tables[0]. Rows.Count;
if (Rowsnum = = 0)
{
Response.Write ("<script>alert (' Excel table is empty table, no data! ') </script> "); Prompt the user when the Excel table is empty
}
Else
{
for (int i = 0; i < Dr. Length; i++)
{
In front of you need to create a "upfiles" folder, the others do not have to control, you just need to get the value of Excel in the following way, and then put these values in your way to insert into the database
string title = dr[i]["caption"]. ToString ();
String linkurl = dr[i]["link Address"]. ToString ();
String CategoryName = dr[i]["Classification"]. ToString ();
String customername = dr[i]["Content quotient"]. ToString ();
Response.Write ("<script>alert" (' Import content: ' + ex. Message + "') </script>");
}
Response.Write ("<script>alert (' excle table import succeeded! '); </script> ");
}
}
#region connect Excel to read Excel data and return a DataSet data collection
<summary>
Connect Excel to read Excel data and return DataSet data collection
</summary>
<param name= "filepath" >excel server path </param>
<param name= "tableName" >excel table name </param>
<returns></returns>
public static System.Data.DataSet Excelsqlconnection (string filepath, string tableName)
{
String Strcon = "Provider=Microsoft.Jet.OLEDB.4.0;Data source=" + filepath + "; Extended properties= ' Excel 8.0; Hdr=yes;imex=1 ' ";
OleDbConnection excelconn = new OleDbConnection (Strcon);
Try
{
String strcom = String. Format ("select * from [sheet1$]");
Excelconn.open ();
OleDbDataAdapter mycommand = new OleDbDataAdapter (strcom, excelconn);
DataSet ds = new DataSet ();
Mycommand.fill (ds, "[" + TableName + "$]");
Excelconn.close ();
return DS;
}
Catch
{
Excelconn.close ();
return null;
}
}
#endregion
The format of Excel is as follows:
| Title |
Link Address |
Classification |
Content providers |
| parody of Japanese woman 1 |
Http://fegnhuang.com |
Photo |
Phoenix Video |
| parody of Japanese woman 2 |
Http://fegnhuang.com |
Photo |
Phoenix Video |
| Parody of Japanese woman 3 |
Http://fegnhuang.com |
Funny |
Phoenix Video |
| Parody of Japanese woman 4 |
Http://fegnhuang.com |
Funny |
Phoenix Video |
| Parody of Japanese woman 5 |
Http://fegnhuang.com |
Funny |
Mango TV |
| Parody of Japanese woman 6 |
Http://fegnhuang.com |
Beauty |
Mango TV |
| Parody of Japanese woman 7 |
Http://fegnhuang.com |
Beauty |
Mango TV |
| Parody of Japanese woman 8 |
Http://fegnhuang.com |
Beauty |
Mango TV |
| Parody of Japanese woman 9 |
Http://fegnhuang.com |
Fashion |
Sina Video |
| Parody of Japanese woman 10 |
Http://fegnhuang.com |
Fashion |
Sina Video |
Other implementation options reference: Http://blog.csdn.net/lee576/archive/2011/03/02/6217811.aspx
Reproduced in: http://www.cnblogs.com/xiaopin/archive/2011/03/31/2001199.html
ASP. NET Excel Data Import Database
