public FileResult Export_Position([DataSourceRequest] DataSourceRequest request) { if (asset.Export) { //Get the data representing the current grid state - page, sort and filter IEnumerable datas = Deca_Position.GetAllDeca_Positions().ToDataSourceResult(request).Data; //Create new Excel workbook FileStream fs = new FileStream(Server.MapPath(@"~\ExportExcelFile\Deca_Position.xls"), FileMode.Open, FileAccess.Read); var workbook = new HSSFWorkbook(fs, true); //Create new Excel sheet var sheet = workbook.GetSheet("Position"); int rowNumber = 1; //Populate the sheet with values from the grid data foreach (Deca_Position data in datas) { //Create a new row var row = sheet.CreateRow(rowNumber++); //Set values for the cells row.CreateCell(0).SetCellValue(data.PositionID); row.CreateCell(1).SetCellValue(data.PositionName); row.CreateCell(2).SetCellValue(data.Active); } //Write the workbook to a memory stream MemoryStream output = new MemoryStream(); workbook.Write(output); //Return the result to the end user return(File(output.ToArray(), //The binary data of the XLS file "application/vnd.ms-excel", //MIME type of Excel files "Deca_Position_" + DateTime.Now.ToString("yyyyMMdd_HHmmss") + ".xls")); //Suggested file name in the "Save as" dialog which will be displayed to the end user } else { ModelState.AddModelError("", "You don't have permission to export data"); return(File("", //The binary data of the XLS file "application/vnd.ms-excel", //MIME type of Excel files "Deca_Position_" + DateTime.Now.ToString("yyyyMMdd_HHmmss") + ".xls")); //Suggested file name in the "Save as" dialog which will be displayed to the end user } }
public ActionResult ImportFromExcel_Position() { if (asset.Export) { try { if (ModelState.IsValid) { List <Deca_Position> listData = new List <Deca_Position>(); int total = 0; if (Request.Files["FileUpload"] != null && Request.Files["FileUpload"].ContentLength > 0) { string fileExtension = System.IO.Path.GetExtension(Request.Files["FileUpload"].FileName); if (fileExtension == ".xls" || fileExtension == ".xlsx") { // Create a folder in App_Data named ExcelFiles because you need to save the file temporarily location and getting data from there. string fileLocation = string.Format("{0}/{1}", Server.MapPath("~/Excel"), "[" + currentUser.UserName + "-" + DateTime.Now.ToString("yyyyMMddHHmmss") + "]" + Request.Files["FileUpload"].FileName); if (System.IO.File.Exists(fileLocation)) { System.IO.File.Delete(fileLocation); } Request.Files["FileUpload"].SaveAs(fileLocation); string excelConnectionString = string.Empty; excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\""; OleDbConnection excelConnection = new OleDbConnection(excelConnectionString); excelConnection.Open(); DataTable dt = new DataTable(); dt = excelConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); if (dt == null) { return(null); } String[] excelSheets = new String[dt.Rows.Count]; int t = 0; //excel data saves in temp file here. foreach (DataRow row in dt.Rows) { excelSheets[t] = row["TABLE_NAME"].ToString(); t++; } OleDbConnection excelConnection1 = new OleDbConnection(excelConnectionString); DataSet ds = new DataSet(); string query = string.Format("Select * from [{0}]", excelSheets[0]); using (OleDbDataAdapter dataAdapter = new OleDbDataAdapter(query, excelConnection1)) { dataAdapter.Fill(ds); } List <string> err = new List <string>(); //Chạy vòng vòng để check dữ liệu for (int i = 0; i < ds.Tables[0].Rows.Count; i++) { string positionid = ds.Tables[0].Rows[i]["PositionID"].ToString(); if (positionid.ToString() == "") { positionid = "0"; } string positionname = ds.Tables[0].Rows[i]["PositionName"].ToString(); if (positionname.ToString() == "") { positionname = ""; } string active = ds.Tables[0].Rows[i]["Active"].ToString(); if (active.ToString() == "") { active = ""; } try { var _list = Deca_Position.GetDeca_Position(int.Parse(positionid)); // kiểm tra id đã có trong db chưa if (_list == null) // Chưa có thì insert { try { Deca_Position meta = new Deca_Position(); meta.PositionName = positionname; meta.Active = bool.Parse(active); meta.CreatedDatetime = DateTime.Now; meta.CreatedUser = currentUser.UserName; meta.LastUpdatedDateTime = DateTime.Now; meta.LastUpdatedUser = currentUser.UserName; meta.Save(); } catch (Exception e) { } } else // Có rồi thì update { try { _list.PositionID = int.Parse(positionid); _list.PositionName = positionname; _list.Active = bool.Parse(active); _list.LastUpdatedUser = currentUser.UserName; _list.LastUpdatedDateTime = DateTime.Now; _list.Update(); } catch (Exception e) { } } total++; } catch (Exception e) { continue; } } } else { ModelState.AddModelError("", "Please select Excel File."); } } return(Json(new { success = true, data = listData.Select(a => a.PositionName).ToList(), total = total })); } else { return(Json(new { success = false })); } } catch (Exception ex) { return(Json(new { success = false })); } } else { return(RedirectToAction("NoAccessRights", "Error")); } }