asp.net MVC Import Excel Data Tutorial detailed

First on a little brother's idea diagram:

(Note: Do not install the software to do flowcharts!) Just make it look.

2 get to the point.

First with the younger brother to create a default MVC project (I believe everyone will create, here is not demonstrated)

First step

Create an entity class, which is the object to import into the database. For simplicity, the younger brother created a student object (his name, age, scores).

First of all, this is just teaching code, for demonstration purposes only, you really do product code can not be so simple to define the entity.

public class Student
{

Public Student ()
{
Id = Guid.NewGuid ();
}

Name
public string Name {get; set;}

Age
public int Age {get; set;}

Language results
public int Chinesescore {get; set;}
English score
public int Englishscore {get; set;}

Math Scores
public int Mathscore {get; set;}
24
}

Second Step

After the entity is created, we then create a empty controller (the name takes its own liking), (this is too simple, I don't show it here) directly on the result:

public class Uploadexcelcontroller:controller
{
Get:/uploadexcel
Public ActionResult Index ()
{
return View ();
}
}

Third Step

Controller created what's the next thing to do? I guess you guessed that all of this is not the beginning of the interface! When users visit/uploadexcel/index, they always have to come up with something interactive. Then create an interactive view, move the cursor to return view (), and right-click to create the Add view

There are a number of ways to create a view, and I just chose a simple and specific look at personal habits to create.

Once the view is created, we first construct an HTML tag that browses the file

@{
Viewbag.title = "View";
}

<div class= "Input-group" >
text box for @* file path *@
<input id= "Txt_path" type= "text" class= "Form-control" >

@* Browse Local File button *@
<span id= "Btn_browse" style= "Cursor:pointer" ("onclick=" $ (' input[id=fileupload] '). Click (); "Class=" Input-group-addon ">
<i class= "Glyphicon glyphicon-folder-open" ></i>&nbsp;&nbsp;&nbsp; Browse files
</span>
</div>

Little brother here is the MVC with the bootstrap framework, I believe we can also understand, if you do not understand those signs! It is recommended that you take 1 days to start the BOOTSTAP framework.

In uploading files to the server there is a common HTML tag is <input id= "FileUpload" type= "file", yes, the younger brother also use this tag, but it is too ugly I put it to hide! Replaced it with the HTML tag above me. Next we start creating it.

<div class= "Input-group" >
text box for @* file path *@
<input id= "Txt_path" type= "text" class= "Form-control" >

@* Browse Local File button *@
<span id= "Btn_browse" style= "Cursor:pointer" ("onclick=" $ (' input[id=fileupload] '). Click (); "Class=" Input-group-addon ">
<i class= "Glyphicon glyphicon-folder-open" ></i>&nbsp;&nbsp;&nbsp; Browse files
</span>
</div>

<br/><br/>

@using (Html.BeginForm ("Browse", "Uploadexcel", FormMethod.Post, new {enctype = "multipart/form-data", id = "Form_upload") " }))
{
@Html. AntiForgeryToken ()//prevention of cross-site requests forgery (Csrf:cross-site request forgery) attack
<input id= "FileUpload" type= "file" name= "file" style= "Display:none" >//Hide FileUpload, cause it's so ugly
}

The red code is new, the Html.BeginForm extension method if you're unfamiliar with it, you think of it as form, because it eventually generates form forms

<form id= "Form_upload" action= "/uploadexcel/browse" method= "post" enctype= "Multipart/form-data" >

.......

</form>

Oh, yes! This is the basic knowledge, I believe we all know, (digression: When you can use the @html extension method, I suggest that you do not use native HTML tags, why?) because if you understand the mechanics of the @html extension method, you know what I mean. Just like Html.BeginForm it is based on routing to generate the URL, smart! safe! If you write native HTML tags, you will inevitably produce unsafe url! ）

The form form is where we upload the Excel file to the server,

See the action= "/uploadexcel/browse" of form forms, so we also create a second name in the Uploadexcelcontroller controller called Browse action

public class Uploadexcelcontroller:controller
{
Get:/uploadexcel
Public ActionResult Index ()
{
return View ();
}

10
[HttpPost]
[Validateantiforgerytoken]
[HandleError (View = "~/views/shared/error.cshtml")]
Public ActionResult Browse (httppostedfilebase file)
{

}
}

The red part is my new action action, which I said was created to upload the file to the server, so this operation is to deal with the upload file action, because it will change the server state, so I defined as Post method. The remaining 2 features are used in conjunction with the @html extension method, one to prevent Cross-site request forgery (Csrf:cross-site request forgery) attacks, and one to handle exception pages uniformly. You can also not add, and we today's example does not matter. Let's not write this action code, so we'll go back to the view page we created.

Unexpectedly we want to use <input id= "FileUpload" type= "file" name= "file" > function, but also dislike it too ugly! So easy just bind the HTML tag we constructed to it. Add the following JS code to the view page

@section scripts{

<script type= "Text/javascript" >
$ (' input[id=fileupload] '). Change (function () {
$ (' #txt_Path '). Val ($ (this). Val ());
$ (' #form_Upload '). Submit ();
});

</script>}

This JS code function is in <input id= "FileUpload" type= "file" name= "file" style= "Display:none" > select files after the file path assigned to our own constructed text box, Then it is submitting the form, uploading its selected files to our server.

Notice how the gray code is bound to the FileUpload function with our own Build button

1 @* Browse local file button *@
2 <span id= "Btn_browse" style= "Cursor:pointer" ("onclick=" $ (' input[id=fileupload] '). Click (); "Class=" Input-group-addon ">
3 <i class= "Glyphicon glyphicon-folder-open" ></i>&nbsp;&nbsp;&nbsp; Browse files
4 </span>

To see how the file responds, we add a small section of CSS code to the view page

@section scripts{

<style type= "Text/css" >
#btn_Browse: hover {
Color: #3C763D;
}
</style>

<script type= "Text/javascript" >
$ (' input[id=fileupload] '). Change (function () {
$ (' #txt_Path '). Val ($ (this). Val ());
$ (' #form_Upload '). Submit ();
});

</script>}

Fifth Step

Here our files can be uploaded to our service, if you do not believe you in the browse operation to break a breakpoint, see the file parameter is not already accepted the document, if received the description has succeeded half! We do not write the browse operation of the code of the Excel file, the focus is still on the view page, in the first image of this blog, you see the browse file has a table below? The younger brother creates this form just for better interaction effect, lets use the person to be more intuitive just. And it's simple!

Next we'll build it and add the table's code to the view page

@model Student
@using school.entity

<table class= "table  table-striped  table-hover table-bordered"
<tr>
<th > @Html. displaynamefor (model => model. Name) </th>
<th> @Html. Displaynamefor (model => model. Age) </th>
<th> @Html. Displaynamefor (model => model. Chinesescore) </th>
<th> @Html. Displaynamefor (model => model. Englishscore) </th>
<th> @Html. Displaynamefor (model => model. Mathscore) </th>
</tr>

@if (viewbag.data!= null)
{
Generate the first 10 data fill table tables
foreach (var item in (Viewbag.data as ienumerable<student>). Take (10))
{
<tr>
<td> @Html. Displayfor (Model => item. Name) </td>
<td> @Html. Displayfor (Model => item. Age) </td>
<td> @Html. Displayfor (Model => item. Chinesescore) </td>
<td> @Html. Displayfor (Model => item. Englishscore) </td>
<td> @Html. Displayfor (Model => item. Mathscore) </td>
</tr>
}
}
</table>

Show first 10 records by default

Here I still use the @html auxiliary method, if you still do not use it, hurriedly spend a day to learn it, the introduction is very simple! Very powerful! Imagine how much hard code you have to write without @html extension methods!

The header here is in English, if you want to become Chinese, you can add data annotation attributes to the entity (as follows)

public class Student
{

[Display (name= "Chinese score")]
public int Chinesescore {get; set;}

}

Right also forgot a thing, is to upload the submit button, we now to build it! Under the View Page Form form, add the following code

@using (Html.BeginForm ("Browse", "Uploadexcel", FormMethod.Post, new {enctype = "multipart/form-data", id = "Form_upload") " }))
{
@Html. AntiForgeryToken ()
<input id= "FileUpload" type= "file" name= "file" style= "Display:none" >
}

The red part is the upload submit button I built.
<div class= "Input-group pull-right" style= "margin:0" 0 5px 0 ">
@Html. RouteLink ("Start Submission", New {action = "Upload"}, new {id= "submit", @class = "btn btn-primary Ladda-button", Data_styl E = "Expand-right"})
</div>

@Html. The RouteLink extension method generates a <a> anchor tag based on the route I define, and finally generates the following Html markup

<a id= "Submit" class= "btn btn-primary Ladda-button" data-style= "Expand-right" href= "/uploadexcel/upload" > Start submitting </a>

And here I disguised it as a button.

Data-style= "Expand-right" These properties are I use bootstrap add a 5 cents of special effects, you can not pipe, you can use your own special effects. The function of the upload submit button is the last function to import data from the browse operation into list<t> into our database. So far our Import Excel page has been completed, of course, my aesthetic and front-end technology is slag, so please forgive brother! href= "/uploadexcel/upload" this <a> anchor tag accesses the Upload operation of the Uploadexcelcontroller controller, so I add the last action. Add the following code to the controller

public class Uploadexcelcontroller:controller
{
Get:/uploadexcel
Public ActionResult Index ()
{
return View ();
}

[HttpPost]
[Validateantiforgerytoken]
[HandleError (View = "~/views/shared/error.cshtml")]
Public ActionResult Browse (httppostedfilebase file)
{

return null;
}

The red section is my new action action, which is to convert the browse operation to the list<t> through the Business Services layer to import our database
[HandleError (View = "~/views/shared/error.cshtml")]
Public ActionResult Upload ()
{
Return View ("uploadsuccess"); Import a successful page This page will be left to the design of their own
}

}

Now we focus on the logical processing of Excel files, we first start from the browse operation, because this operation is responsible for the upload of our Excel file to the list entity object, as long as the conversion to the collection object you can think how to insert the following how to insert! Want to insert MSSQL MYSQL, such as different data can hehe! Because we use the ORM Framework!

According to the thought map I uploaded, I think I'll deal with the validation first! First determine if the format of the file is Excel. (Excel is formatted according to version 2007-2010 is xlsx,2003 is xls) Here I only default to 2007-2010.

Add the following code in the browse operation

[HttpPost]
[Validateantiforgerytoken]
[HandleError (View = "~/views/shared/error.cshtml")]
Public ActionResult Browse (httppostedfilebase file)
{

if (string. Empty.equals (file. FileName) | | ". xlsx"!= path.getextension (file. FileName))
{
throw new ArgumentException ("The current file format is not correct, please ensure the correct Excel file format!");
}

var Severpath = this. Server.MapPath ("/files/"); Get the current virtual file path

var Savepath = Path.Combine (severpath, file. FileName); Stitching save File path

Try
{
File. SaveAs (Savepath);
Stus = excelhelper.readexceltoentitylist<student> (Savepath);
Viewbag.data = Stus;
Return View ("Index");
}
Finally
{
System.IO.File.Delete (Savepath)//delete file every time you upload.
}

}

I created a folder of files in the root directory of the MVC project to save the uploaded Excel files. The file is then converted to the list entity object, which is then passed to the view we created. This allows you to select an Excel file to display the data on the page, the core of the conversion data is this code

Stus = excelhelper.readexceltoentitylist<student> (Savepath);

Excelhelper is a tool library that I encapsulate myself, and I'm going to create it now. Add a folder to the root directory, and then add a class

Ppublic class Excelhelper
{
Excel data Transfer list<t>
public static ilist<t> readexceltoentitylist<t> (String filePath) where T:class, new ()
{
DataTable tbl = readexceltodatatable (FilePath);//Read Excel data to DataTable

ilist<t> list = datatabletolist<t> (TBL);

return list;

}

OLE DB read using Excel data to DataTable
public static DataTable readexceltodatatable (String filePath)
{

if (FilePath = = string. Empty) throw new ArgumentNullException ("Path parameter cannot be null");
String ConnectionString = "provider=microsoft.ace.oledb.12.0;" Persist Security Info=false;data source= "+ FilePath +"; Extended properties= ' Excel 8.0; Hdr=yes;imex=1 ' ";
OleDbDataAdapter adapter = new OleDbDataAdapter ("SELECT * from[sheet1$]", ConnectionString); The default read Sheet1, you can also put it encapsulated variables, dynamically read your sheet worksheet
DataTable table = new DataTable ("TempTable");
Adapter. Fill (table);
return table;
}


DataTable Turn List<t>
public static list<t> datatabletolist<t> (DataTable dt) where T:class, new ()
{

if (dt = null) return null;

list<t> list = new list<t> ();

Traverse all data rows in the DataTable
foreach (DataRow dr in Dt. Rows)
{
T t = new t ();

propertyinfo[] Propertys = T.gettype (). GetProperties ();

foreach (PropertyInfo Pro in Propertys)
{
Check if the DataTable contains this column (column name = = object's property name)
if (dt. Columns.contains (pro. Name))
{
Object value = Dr[pro. Name];

Value = Convert.changetype (value, Pro. PropertyType);//cast type

If not null, the property assigned to the object PropertyInfo
if (value!= dbnull.value)
{
Pro. SetValue (t, value, NULL);
}
}
}
object is added to the generic collection
List. ADD (t);
}

return list;

}
}

The code is very simple I do not translate, is reading Excel data to various C # objects, but this is the teaching code is not the product code, I am very rude encapsulation. If you want to use the build environment, you have to add a variety of logical validation and testing!

Write here, feel the last feature to import the List<t> collection into the database everyone should be, I do not want to continue to write down. But still want to say the place of attention is to import the data must support things rollback function, is even if the previous has imported dozens of data, if a dirty data caused the insertion of an exception, you must roll back to determine that all import failed. Avoid duplicate imports, resulting in database dirty data.

I paste the last Import Database code is upload operation, I am in the DAL layer is the use of things to deal with, support rollback!

[HandleError (View = "~/views/shared/error.cshtml")]
Public ActionResult Upload ()
{
var result= ioc.service.iocbll<istudentbll>. Provide.insert (Stus);

if (string. Empty!=result. Success)
Viewbag.info = result. Info;

Return View ("uploadsuccess");

}