Use MVC to import and export, and use MVC to Import and Export

Use MVC to import and export, and use MVC to Import and Export

The import and export operations are very difficult for the people who have just done so, but it is very easy to understand the idea. I have studied it for a long time before I finally did it. I will share it with you here.

I. First read and export

View Script

<Script type = "text/javascript" language = "javascript"> function selectExcel () {var GUID = document. getElementsByName ("check"); var temp = ""; var strGUID = ""; for (var I = 0; I <GUID. length; I ++) {if (GUID [I]. checked) {temp + = GUID [I]. value + "," ;}} strGUID = temp // alert (strGUID); // test whether the obtained value is correct alert (strGUID ); // $ ("# GUID "). val (strGUID); // assign multiple values to the hidden domain document whose Id is strGUID. getElementById ("strGUID "). value = strGUID ;}</script>

View html code

@ Using (Html. beginForm ("DataIn", "Home", FormMethod. post) {<table> <tr> <td> @ Html. hidden ("strGUID") </td> <input type = "submit" value = "Export" onclick = "selectExcel () "/> </td> </tr> </table>}

Controller code

Public ActionResult DataTableToExcel (string strGUID) {string [] GUID = Request. form ["strGUID"]. split (','); // This is the list to be exported. The data table dt = new able (); dt. columns. add ("Serial Number"); dt. columns. add ("name"); foreach (var aa in GUID) {if (aa! = Null & aa! = "") {Int id = Convert. toInt32 (aa); // List <User> list = db. user. where (p => p. uid = id ). toList (); User model = db. user. find (id); dt. rows. add (model. uid, model. uname) ;}/// = SQLServerDAL. DSalesOrders. salesOrders_GetListExcel (strGUID ). tables [0]; System. web. UI. webControls. dataGrid dgExport = null; // The current Dialog System. web. httpContext curContext = System. web. httpContext. current; // IO is used to export and return the excel file System. IO. StringWriter strWriter = null; System. web. UI. htmlTextWriter htmlWriter = null; string filename = DateTime. now. year + "_" + DateTime. now. month + "_" + DateTime. now. day + "_" + DateTime. now. hour + "_" + DateTime. now. minute; byte [] str = null; if (dt! = Null) {// set the encoding and attachment format curContext. response. charset = "GB2312"; Response. appendHeader ("Content-Disposition", "attachment; filename =" + filename + ". xls "); curContext. response. contentEncoding = System. text. encoding. getEncoding ("GB2312"); // set the output stream to simplified Chinese curContext. response. contentType = "application/vnd. ms-excel "; // System. text. encoding. UTF8; // export the excel file strWriter = new System. IO. stringWriter (); htmlWrit Er = new System. web. UI. htmlTextWriter (strWriter); // to solve the possible paging problem in dgData, You need to redefine a non-Paging DataGrid dgExport = new System. web. UI. webControls. dataGrid (); dgExport. dataSource = dt. defaultView; dgExport. allowPaging = false; dgExport. dataBind (); dgExport. renderControl (htmlWriter); // return the client str = System. text. encoding. UTF8.GetBytes (strWriter. toString ();} return File (str, "attachment; filename =" + filenam E + ". xls") ;}<! -- If a garbled format error is reported, replace the code in the encoding and attachment formats with the following code --> curContext. response. charset = "GB2312"; Response. appendHeader ("Content-Disposition", "attachment; filename =" + filename + ". xls "); curContext. response. contentEncoding = Encoding. default; // set the output stream to simplified Chinese curContext. response. contentType = "application/vnd. ms-excel "; Response. write ("<meta http-equiv = \" content-type \ "content = \" application/vnd. ms-excel; charset = UTF-8 \ "/>"); // The string type is not garbled.

2. Import databases from Excel

View code

@ Using (Html. beginForm ("Show", "Home", FormMethod. post, new {enctype = "multipart/form-data"}) {<p> select a file: <input id = "FileUpload" type = "file" name = "files" style = "width: 250px; height: 24px; background: white "class =" easyui-validatebox "/> </p> <input id =" btnImport "type =" submit "value =" import "style =" width: 60px; height: 28px; "/> </p>}

Controller code

Public ActionResult Show (HttpPostedFileBase filebase)
{
HttpPostedFileBase file = Request. Files ["files"];
String FileName;
String savePath;
If (file = null | file. ContentLength <= 0)
{
ViewBag. error = "file cannot be blank ";
Return View ();
}
Else
{
String filename = Path. GetFileName (file. FileName );
Int filesize = file. ContentLength; // obtain the size of the uploaded file in byte.
String fileEx = System. IO. Path. GetExtension (filename); // get the extension of the uploaded file
String NoFileName = System. IO. Path. GetFileNameWithoutExtension (filename); // obtain a file name without an extension.
Int Maxsize = 4000*1024; // defines the maximum size of the uploaded file as 4 MB.
String FileType = ".xls,.xlsx"; // defines the type string of the uploaded file.

FileName = NoFileName + DateTime. Now. ToString ("yyyyMMddhhmmss") + fileEx;
If (! FileType. Contains (fileEx ))
{
ViewBag. error = "the file type is incorrect. Only files in xls and xlsx formats can be imported ";
Return View ();
}
If (filesize> = Maxsize)
{
ViewBag. error = "the uploaded file exceeds 4 MB and cannot be uploaded ";
Return View ();
}
String path = AppDomain. CurrentDomain. BaseDirectory + "Excel /";
SavePath = Path. Combine (path, FileName );
File. SaveAs (savePath );
}

// String result = string. Empty;
String strConn;
StrConn = "Provider = Microsoft. Ace. OleDb.12.0;" + "data source =" + savePath + "; Extended Properties = 'excel 12.0; HDR = Yes; IMEX = 1 '";
OleDbConnection conn = new OleDbConnection (strConn );
Conn. Open ();
OleDbDataAdapter myCommand = new OleDbDataAdapter ("select * from [Sheet1 $]", strConn );
DataSet myDataSet = new DataSet ();
Try
{
MyCommand. Fill (myDataSet, "ExcelInfo ");
}
Catch (Exception ex)
{
ViewBag. error = ex. Message;
Return View ();
}
DataTable table = myDataSet. Tables ["ExcelInfo"]. DefaultView. ToTable ();

// Reference the transaction mechanism. When an error occurs, the transaction is rolled back.
Using (TransactionScope transaction = new TransactionScope ())
{
For (int I = 0; I <table. Rows. Count; I ++)
{
/// Obtain the region name
// String _ areaName = table. Rows [I] [0]. ToString ();
//// Determine whether a region exists
// If (! _ AreaRepository. CheckAreaExist (_ areaName ))
//{
// ViewBag. error = "imported file:" + _ areaName + "region does not exist. Please add this region first ";
// Return View ();
//}
// Else
//{
// Station station = new Station ();
// Station. AreaID = _ areaRepository. GetIdByAreaName (_ areaName). AreaID;
// Station. StationName = table. Rows [I] [1]. ToString ();
// Station. TerminaAddress = table. Rows [I] [2]. ToString ();
// Station. CapacityGrade = table. Rows [I] [3]. ToString ();
// Station. OilEngineCapacity = decimal. Parse (table. Rows [I] [4]. ToString ());
// _ StationRepository. AddStation (station );
//}
User model = new User ();
Model. Uname = table. Rows [I] [0]. ToString ();
Db. user. Add (model );
Db. SaveChanges ();
}
Transaction. Complete ();
}
ViewBag. error = "imported successfully ";
System. Threading. Thread. Sleep (2000 );
Return Content ("<script> alert ('data imported successfully! '); Location. href ='/Home/Index' </script> ");
}

Iii. Note,

The transaction mechanism in the export must reference using System. Transactions;

If this. dll component is not added, you can add references to the Assembly.