Start: Import a rule's Excel into a database
XLS format:
| The code is as follows |
Copy Code |
strconn = "Provider=microsoft.ace.oledb.12.0;data source= '" + serverfilename + "'; Extended properties= ' Excel 12.0; Hdr=yes ' "; |
XLSX format:
| The code is as follows |
Copy Code |
strconn = "Provider=Microsoft.Jet.OLEDB.4.0;Data source= '" + serverfilename + "'; Extended properties= ' Excel 8.0; Hdr=yes; ' "; |
First look at the Excel structure, as shown in figure:
This is a simple, structured Excel format that you import into your database
| The code is as follows |
Copy 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 |
Another instance
Upload the XLS file first, then connect the Excel file with OLE DB, import the Excel data into the dataset, and then import the dataset data into the database.
Here is the keynote process
| The code is as follows |
Copy Code |
| string filename = string. Empty;
Try
{
filename = Updatexls (this. FILEEXCEL);//upload xls file
Importxlstodata (filename);//import xls file contents into
deleting files
if (filename!= string. Empty && file.exists (filename)
{
File.delete (filename);//delete uploaded files
}
}
catch (Exception ex)
{
This. Writeerrorxml (ex);//error handling
This.lblMessage.Text = ex. message;//Error Display
}
Finally
{
Reload page
LoadData ();
} |
Where Importxlstodata is using OLE DB to link an Excel file and import its data into a dataset;
Updatexls is upload xls file (in fact, should be written as uploadxls......-_-!);
Adddatasettosql is to import the dataset into the database, where the Save function inserts a row of records (this function is not given here).
| The code is as follows |
Copy Code |
| /**////<summary>
Extract data from Excel----"dataset"
</summary>
<param name= "filename" >excel file path name </param>
private void Importxlstodata (string fileName)
{
Try
{
if (FileName = = string. Empty)
{
throw new ArgumentNullException ("Upload file failed!") ");
}
//
string oledbconnstring = String.Empty;
oledbconnstring = "provider=microsoft.jet.oledb.4.0;";
Oledbconnstring + + "Data source=";
Oledbconnstring + = FileName;
Oledbconnstring + = "; Extended Properties=excel 8.0; ";
//
OleDbConnection oledbconn = null;
OleDbDataAdapter oleadmaster = null;
DataTable m_tablename=new DataTable ();
DataSet ds=new DataSet (); Oledbconn = new OleDbConnection (oledbconnstring);
Oledbconn.open ();
M_tablename=oledbconn.getoledbschematable (Oledbschemaguid.tables,null); if (m_tablename!= null && m_tableName.Rows.Count > 0)
{ M_tablename.tablename =m_tablename.rows[0]["table_name"]. ToString (); }
String Sqlmaster;
Sqlmaster= "SELECT * FROM [" +m_tablename.tablename+ "]";
Oleadmaster=new OleDbDataAdapter (Sqlmaster,oledbconn);
Oleadmaster.fill (ds, "M_tablename");
Oleadmaster.dispose ();
Oledbconn.close ();
Oledbconn.dispose (); Test whether to extract data
This. DataGrid1.DataSource = ds. tables["M_tablename"];
This. Datagrid1.databind ();
Import data from a dataset into SQL
Adddatasettosql (DS); }
catch (Exception ex)
{
Throw ex;
}
} Uploading Excel Files
private String Updatexls (System.Web.UI.HtmlControls.HtmlInputFile inputfile)
{
String orifilename = String. Empty;
String Uploadfilepath = String. Empty;
String modifyfilename = String. Empty;
String fileextend = "";//File name extension
int fileSize = 0;//File size
Try
{
if (inputfile. Value!= String. Empty)
{
Get the size of the file
FileSize = Inputfile. Postedfile.contentlength;
if (fileSize = 0)
{
throw new Exception ("The file cannot be found!") ");
}
Get extension
Fileextend = Inputfile. Value.substring (Inputfile. Value.lastindexof (".") +1);
if (Fileextend.tolower ()!= "XLS")
{
throw new Exception ("Please verify that the file you are importing is an Excel file!! ");
}
Path
Uploadfilepath = System.Web.HttpContext.Current.Server.MapPath (".") +path;
New file name
Modifyfilename = DateTime.Now.Year.ToString () + DateTime.Now.Month.ToString ()
+ DateTime.Now.Day.ToString () + DateTime.Now.Hour.ToString ()
+ DateTime.Now.Minute.ToString () + DateTime.Now.Second.ToString ()
+ DateTime.Now.Millisecond.ToString ();
Modifyfilename = "." +inputfile. Value.substring (Inputfile. Value.lastindexof (".") +1);
Determine if there is a directory
System.IO.DirectoryInfo dir = new System.IO.DirectoryInfo (Uploadfilepath);
if (!dir. Exists)
{
Dir. Create ();
}
Orifilename = Uploadfilepath+modifyfilename;
If present, delete file
if (file.exists (orifilename))
{
File.delete (Orifilename);
}
Uploading files
Inputfile. Postedfile.saveas (Orifilename);
}
Else
{
throw new Exception ("No Excel file selected!");
}
}
catch (Exception ex)
{
Throw ex;
}
return orifilename;
} Import the contents of a dataset into SQL
private bool Adddatasettosql (DataSet PDS)
{
int Ic,ir;
IC = PDS. Tables[0]. Columns.count;
if (PDS. Tables[0]. Columns.count < 7)
{
throw new Exception ("Import Excel format Error!") Excel only "+ IC". ToString () + "column");
}
IR = PDS. Tables[0]. Rows.Count;
if (PDS!= null && PDS. Tables[0]. Rows.Count > 0)
{
for (int i = 0;i < PDS. Tables[0]. rows.count;i++)
{
Save (PDS. Tables[0]. Rows[i][0]. ToString (), PDS. Tables[0]. ROWS[I][1]. ToString (),
Pds. Tables[0]. ROWS[I][2]. ToString (), PDS. Tables[0]. ROWS[I][3]. ToString (),
Pds. Tables[0]. ROWS[I][4]. ToString (), PDS. Tables[0]. ROWS[I][5]. ToString (),
Pds. Tables[0]. ROWS[I][6]. ToString ());
}
}
Else
{
throw new Exception ("Import data is empty!") ");
}
return true;
} |
