Getting new requirements today requires that the data in the Excel table be displayed on the page.
My personal analysis, first of all, to the Excel data stored in the database, and then the page display, I rookie level, has not done reading Excel data, studied a bit (mainly to see other people's data), write the implementation process, I would like to write a few about Excel, The first is the rule of Excel data import, and then there is the irregular Excel data import, and is based on the data generated Excel.
start with: Import a rule's Excel into a database
First look at the Excel structure, as shown in figure:
This is a simple, structured Excel format that you import into your database
Copy Code code as follows:
View Code
protected void Btnimport_click (object sender, EventArgs e)
{
if (Fileupload1.hasfile = False)//hasfile is used to check whether FileUpload has the specified file
{
Response.Write ("<script>alert (' please choose Excel file ') </script>");
return;//when there is no file, return
}
String Isxls = System.IO.Path.GetExtension (fileupload1.filename). ToString (). ToLower ();//system.io.path.getextension obtain file extension
if (Isxls!= ". xls")
{
if (isxls!= ". xlsx")
{
Response.Write ("<script>alert (' only Excel file ') </script>");
return;//when the Excel file is not selected, returns
}
}
string filename = Fileupload1.filename; Get execle file name datetime date function
String Savepath = Server.MapPath (("upfiles\\") + filename);//server.mappath get Virtual Server relative path
Fileupload1.saveas (Savepath); SaveAs the uploaded file contents on the server
DataSet ds = Excelsqlconnection (Savepath, Filename,isxls); Calling the custom method
datarow[] Dr = ds. Tables[0]. Select (); Defines 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 an Excel table is empty
}
Else
{
for (int i = 0; i < Dr. Length; i++)
{
Before you need to create a "upfiles" folder, the rest is out of the box, you just need to get Excel values in the following way, and then insert those values into the database in your own way.
string title = dr[i]["title"]. ToString ();
String linkurl = dr[i]["link Address"]. ToString ();
String CategoryName = dr[i]["Classification". 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 the DataSet data collection
<summary>
Connect Excel to read Excel data and return to 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 isxls)
{
String strcon = "";
if (isxls== ". xls")
{
Strcon = "Provider=Microsoft.Jet.OLEDB.4.0;Data source=" + filepath + "; Extended properties= ' Excel 8.0; Hdr=yes;imex=1 ' ";
}
Else
{
Strcon = "Provider=microsoft.ace.oledb.12.0;data source=" + filepath + "; Extended properties= ' Excel 12.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;
}
}
#endregio
This code is modified on the basis of other people's code, can only import XLS format, does not support the XLSX format, the main difference between the two formats is
xls format：
Copy Code code as follows:
strconn = "Provider=microsoft.ace.oledb.12.0;data source= '" + serverfilename + "'; Extended properties= ' Excel 12.0; Hdr=yes ' ";
xlsx Format：
Copy Code code as follows:
strconn = "Provider=Microsoft.Jet.OLEDB.4.0;Data source= '" + serverfilename + "'; Extended properties= ' Excel 8.0; Hdr=yes; ' ";
Of course, importing a database also requires connecting to the database, creating the same table as the structure.
