Excel imports the GridView and then sinks the database.

Original: Excel imports the GridView and then sinks the database.

Recently the project has a number of the number to input, I made a use of JavaScript to copy the input box function, you can enter a number of pen number.
However, the user feedback, the material number is too many, there may be hundreds of numbers in the Excel file, so the input is very slow, need to add functionality. So I thought about SQL Server's ability to import Excel.
Declare @strExcelName varchar( -)
Set @strExcelName='C:\sl.xls'
exec('select * into # #tmp from OPENROWSET ("'MICROSOFT. JET. oledb.4.0"',"'Excel 8.0; Hdr=no;imex=1;database='+@strExcelName+" ", [sheet1$])')
Select *  from# #tmp
This allows data to be imported, but the problem comes. My database server and the running Program server are not the same server, unable to import (there may be methods, but the project is tight, there is no time to explore), so think of another method: first upload the Excel file, then read into the dataset into the GridView, and then submitted from the GridView to the database , so there's no problem.
File Upload control:<inputID= "MyFile"runat= "Server"type= "File" />
There's no need to enctype= "Multipart/form-data" in the form.
File Upload code:
            if(MyFile.PostedFile.FileName!= "")
{
//the absolute path of the uploaded file
                stringSFile=MyFile.PostedFile.FileName;
//get full file name
SFile=sfile.substring (Sfile.lastindexof ("\\") + 1);
//Get suffix name
SFile=sfile.substring (Sfile.lastindexof ("."));
if(Sfile.tolower ()!= ". xls")
{
Response.Write (" Please select Excel file! " );
Response.End ();
}
//to prevent duplicate names, obtain a date that is the name of the month day
                stringDatatime=System.DateTime.Now.ToString ("yyymmddhhmmssffff");
//the new name of the file after uploading
SFile=Datatime+SFile;
//AppDomain.CurrentDomain.BaseDirectory.ToString () Gets the root directory of this project
//spath get the path after uploading
                stringspath=AppDomain.CurrentDomain.BaseDirectory.ToString ()+ "excelfiles\\" +SFile;
//Uploading Files
MyFile.PostedFile.SaveAs (spath);
This. Mygridview.datasource=Getexcelcontent (spath);
This. Mygridview.databind ();
}
Read the Excel to DataSet code:
        PrivateDataSet getexcelcontent (stringfilepath)
{
stringStrcon= "Provider=Microsoft.Jet.OLEDB.4.0;Data source=" +filepath+ "; Extended properties= ' Excel 8.0; Hdr=no;imex=1 '";
System.Data.OleDb.OleDbConnection myconn= NewSystem.Data.OleDb.OleDbConnection (Strcon);
stringstrcom= "SELECT F1 as resno,f2 as Resname from [sheet1$]";
MyConn.Open ();
System.Data.OleDb.OleDbDataAdapter mycommand= NewSystem.Data.OleDb.OleDbDataAdapter (strcom, myconn);
//Create a DataSet object
DataSet myDataSet= NewDataSet ();
//Get your own DataSet object
Mycommand.fill (myDataSet);
//close this data link
Myconn.close ();
returnmyDataSet;
}
Finally, the data is submitted to the database code:
            stringStresno= "";
stringStresname= "";
foreach(GridViewRow rowinch  This. mygridview.rows)
{
Label Txtesno=(Label) row. FindControl ("Labresno");
Stresno+=Txtesno. Text.tostring (). Trim (). Replace ("'", ""'") + ";";

Label Txtresname=(Label) row. FindControl ("Labresname");
Stresname+=Txtresname. Text.tostring (). Trim (). Replace ("'", ""'") + ";";
}
Response.Write (Stresno+ "<br/>" +stresname);
Response.End ();

Excel imports the GridView and then sinks the database.