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"));
}
}
