MVC3 Excel to import data to a SQL Server database

MVC Excel import into the database, our customer has a fixed Excel, each month let me manually input into the library (a start will not SQL Import) one or two times I'm OK, quite happy later, I used the SQL comes with the import, but every month, it is too disgusting, In order to highlight that I am a programmer, I told the project manager, do a page upload Excel file page, and then save to the database, let the customer upload, every day upload can, do not find me once a month, and the customer is a woman, the most let me unhappy is she still so Earth, Then the project manager said that since you think of it, then you write it yourself, I have fossils, then a ... GE. I am a man of backbone, so I have to try.

View complete code

@using (Html.BeginForm ("Stationimport", "Home", FormMethod.Post, new {enctype = "multipart/form-data"})
{
<div style= "margin-top:20px;" >
<fieldset id= "Myfieldset1" >
<legend> Base Station bulk information import </legend>
<p>
Select File: <input id= "FileUpload" type= "file" name= "Files" style= "width:250px; height:24px;
Background:white "class=" Easyui-validatebox "/></p>
<p>
<input id= "Btnimport" type= "Submit" value= "import" style= "width:60px"; height:28px; "/></p>
<p style= "color:red; Text-align:center; " > @ViewBag .error</p>
</fieldset>
</div>
}

Background full code

Private Myschoolentities db = new myschoolentities ();
Public ActionResult Index ()
{
return View ();
}
[HttpPost]
Public ActionResult Stationimport (HttpPostedFileBase filebase)
{
HttpPostedFileBase file=request.files["Files";
String FileName = String. Empty;
String strconn = String. Empty;
String Savepath;
if (file = = null| | File. contentlength<=0)
{
Viewbag.error = "File cannot be empty";
return View ();
}
Else
{
String filename= path.getfilename (file. FileName);
int filesize = file.     ContentLength; Gets the size unit of the uploaded file in bytes byte
String Fileex = path.getextension (filename); Get the extension of the uploaded file
String Nofilename =path.getfilenamewithoutextension (filename);//Get file name without extension
int Maxsize = 4000 * 1024;//The maximum space size for the uploaded file is 4M
String FileType = ". Xls,.xlsx";//defines the type string for the uploaded file

FileName = Nofilename + DateTime.Now.ToString ("Yyyymmddhhmmss") + Fileex;
if (! Filetype.contains (Fileex))
{
Viewbag.error = "File type is incorrect, only files in XLS and xlsx format can be imported";
return View ();
}
if (filesize >= Maxsize)
{
Viewbag.error = "Upload file more than 4M, cannot upload";
return View ();
}
String path = AppDomain.CurrentDomain.BaseDirectory + "uploads/excel/";
Savepath = Path.Combine (Path, FileName);
File. SaveAs (Savepath);

if (Fileex.equals (". xlsx"))
strconn = "Provider=microsoft.ace.oledb.12.0;data source=" + Savepath + "; Extended properties= ' Excel 12.0 Xml; Hdr=yes; Imex=1 ' ";
else if (fileex.equals (". xls"))
strconn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data source=" + Savepath + "; Extended properties= ' Excel 8.0; Hdr=yes; Imex=1 ' ";
}
var conn = new OleDbConnection (strconn);
Conn. Open ();
var mycommand = new OleDbDataAdapter ("SELECT * from [sheet1$]", strconn);
var mydataset = new DataSet ();
Try
{
Mycommand.fill (myDataSet, "excelinfo");
}
catch (Exception ex)
{
Viewbag.error = ex. Message;
return View ();
}
DataTable table = mydataset.tables["Excelinfo"]. Defaultview.totable ();
for (int i = 0; i < table. Rows.Count; i++)
{
Get region name
var station = new Tab_student
{
Name = table. Rows[i][0]. ToString (),
Value = table. ROWS[I][1]. ToString (),
};
DB.TAB_STUDENT.ADD (station);
}
Db. SaveChanges ();
Viewbag.error = "Import succeeded";
System.Threading.Thread.Sleep (2000);
Return redirecttoaction ("Index");
}

MVC3 Excel to import data to a SQL Server database