ASP. NET imported Excel file to database

Using system;using system.collections.generic;using system.linq;using system.web;using System.Web.UI;using System.web.ui.webcontrols;using system.data;using system.data.oledb;using system.collections;using system.data.sqlclient;using budget.dbutility;namespace budget.web.admin.rmbase.sysuser{public partial class Ceshi:s Ystem.        Web.UI.Page {SqlConnection Sqlcon;        SqlCommand sqlcom;        String Strcon = "Myofficeconnectionstring"; protected void Page_Load (object sender, EventArgs e) {if (!            IsPostBack) {//bind ();            }} protected void button1_click (object sender, EventArgs e) {if (fileupload1.hasfile)                {//Get file path//string path = Fileupload1.PostedFile.FileName; String path = HttpContext.Current.Request.MapPath ("..                /upload/budget Management System User template. xlsx ");    Gets the file name string filename = Fileupload1.filename;            The file format if (Filename.tolower () is determined according to the file end name. Substring (filename.length-3)! = "xls" && filename.tolower (). Substring (filename.length-4)! = "xlsx") {Response.Write ("<script type=" Text/javasc Ript ' >alert (' Please select Excel file!                    ') </script> ");                Return                }//Get the data for Excel datatable datatable dt = GetTable (path, FileName);                using (SqlConnection conn = new SqlConnection ("server=192.168.29.59;database=expensebudget4.0test;uid=sa;pwd=123;")) {Conn.                    Open (); SqlTransaction sqltransaction = conn.                    BeginTransaction ();                    Apply the transaction to command SqlCommand SqlCommand = new SqlCommand ();                    Sqlcommand.connection = conn;                    Sqlcommand.transaction = SqlTransaction; try {foreach (DataRow row iN dt. Rows) {String user_id = Guid.NewGuid (). ToString ().                            ToLower (); String user_code = row["work number"].                            ToString (); String user_name = row["name"].                            ToString (); String Shouji = row["phone number/Phone"].                            ToString (); String user_account = row["Login ID"].                                                   ToString (); String sql= string. Format ("INSERT into Base_userinfo (User_id,user_code,user_name,user_account) VALUES (' {0} ', ' {1} ', ' {2} ', ' {3} ')", User _id, User_code, user_name, user_account);//sql database int a = new Sqlserverhelper ().                                                   Runsqlgetrowcount (SQL); Sqlcommand.commandtext = string. Format ("INSERT into base_appendpropertyinstance (propertyinstance_id,propertyinstance_key,property_control_id,   Propertyinstance_value) VALUES (NEWID (), ' {0} ', ' {1} ', ' {2} ') ", user_id," User_tel ", Shouji);//sql database                         SqlCommand.ExecuteNonQuery ();                        }//successfully submitted Sqltransaction.commit (); Response.Write ("<script type= ' text/javascript ' >alert (' The file was successfully imported!                    ') </script> ");                           } catch (Exception ex) {//Error rollback                        Sqltransaction.rollback (); Response.Write ("<script type= ' text/javascript ' >alert (' Import file failed!                    ') </script> "); } finally {Conn.                    Close (); }}} else {response.write ("<script type= ' Text/javascri PT ' >alert (' Please select the file to import!            ') </script> ");    }}//Read data from Execl to DataTable protected DataTable GetTable (string path, string fileName) {        String connstring = ""; if (Filename.tolower (). IndexOf (". xlsx") < 0) {connstring = string. Format ("Provider=Microsoft.Jet.OLEDB.4.0;Data source= ' {0} ';            Extended properties= ' Excel 8.0;imex=1 ' ", path); } else {connstring = string. Format ("Provider=microsoft.ace.oledb.12.0;data source= ' {0} '; Extended properties= ' Excel 12.0;            Hdr=yes ' ", path);            } System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection (connstring);            String sql = "SELECT * FROM [sheet1$]";            DataTable DT; try {Conn.                Open ();                System.Data.OleDb.OleDbDataAdapter mycommand = new System.Data.OleDb.OleDbDataAdapter (SQL, conn);                DataSet ds = new DataSet ();                Mycommand.fill (ds, "[sheet1$]"); Conn.                Close (); DT = ds.                Tables[0];           Gridview1.datasource = ds;     Gridview1.databind ();            } catch (Exception err) {return null;        } return DT;            }//Upload file Method private bool Upload (FileUpload myfileupload) {BOOL flag = false;            Whether to allow the uploading of bool Fileallow = false;            Set the extension file name type allowed for upload string[] AllowExtensions = {". xls", ". xlsx"}; Get the site root directory path of string path = HttpContext.Current.Request.MapPath (".            /upload/"); if (myfileupload.hasfile) {string fileextension = System.IO.Path.GetExtension (myfileupload.file Name).                ToLower (); for (int i = 0; i < allowextensions.length; i++) {if (fileextension = = Allowextensio                    Ns[i]) {Fileallow = true;  }} if (Fileallow) {try {                      Store file to Folder Myfileupload.saveas (path + myfileupload.filename);                        Lblmes.text = "file import succeeded";                    Flag = true; } catch (Exception ex) {lblmes.text + = ex.                        Message;                    Flag = false; }} else {lblmes.text = "Upload not allowed:" + myfileupload.postedfil E.filename + ", can only upload xls files, please check!                    ";                Flag = false;                }} else {lblmes.text = "Please select the Excel file to import!";            Flag = false;        } return flag; }        }}

　　

ASP. NET imported Excel file to database