internal DataTable fillCompanyName(ExcelImportModel objExcel)
{
try
{
ClsCon.cmd = new SqlCommand();
ClsCon.cmd.CommandType = CommandType.StoredProcedure;
ClsCon.cmd.CommandText = "SPMasters";
ClsCon.cmd.CommandType = CommandType.StoredProcedure;
ClsCon.cmd.Parameters.AddWithValue("@DataInd", objExcel.Ind);
con = ClsCon.SqlConn();
ClsCon.cmd.Connection = con;
dt = new DataTable();
ClsCon.da = new SqlDataAdapter(ClsCon.cmd);
ClsCon.da.Fill(dt);
dt.TableName = "success";
}
catch (Exception)
{
dt = new DataTable();
dt.TableName = "error";
return(dt);
}
finally
{
con.Close();
con.Dispose();
ClsCon.da.Dispose();
ClsCon.cmd.Dispose();
}
return(dt);
}
internal DataTable ExcelItemData(ExcelImportModel objpl)
{
try
{
ClsCon.cmd = new SqlCommand();
ClsCon.cmd.CommandType = CommandType.StoredProcedure;
ClsCon.cmd.CommandText = "SPImportAccountExcel";
ClsCon.cmd.Parameters.AddWithValue("@Ind", objpl.Ind);
ClsCon.cmd.Parameters.AddWithValue("@ClientCode", objpl.ClientCode);
ClsCon.cmd.Parameters.AddWithValue("@tableItem", objpl.dt);
con = ClsCon.SqlConn();
ClsCon.cmd.Connection = con;
dt = new DataTable();
ClsCon.da = new SqlDataAdapter(ClsCon.cmd);
ClsCon.da.Fill(dt);
dt.TableName = "success";
}
catch (Exception)
{
dt = new DataTable();
dt.TableName = "error";
return(dt);
}
finally
{
con.Close();
con.Dispose();
ClsCon.da.Dispose();
ClsCon.cmd.Dispose();
}
return(dt);
}
static void Main(string[] args)
{
var fileFullPath = @"E:\Shared\IAP.xlsx";
FileStream fs = new FileStream(fileFullPath, FileMode.Open, FileAccess.Read, FileShare.Read);
var import = new ExcelImportModel<Ysd.DataAccessLayer.Models.Biz_Wealth>();
import.InjectPropertyMaps(GetExcelPropertyMap());
var models = import.ToModels(fs, 1);
}
static void Main(string[] args)
{
var fileFullPath = @"E:\Shared\IAP.xlsx";
FileStream fs = new FileStream(fileFullPath, FileMode.Open, FileAccess.Read, FileShare.Read);
var import = new ExcelImportModel <Ysd.DataAccessLayer.Models.Biz_Wealth>();
import.InjectPropertyMaps(GetExcelPropertyMap());
var models = import.ToModels(fs, 1);
}
public DataTable FillcompanyNames(int Ind = 0)
{
ExcelImportModel objpl = new ExcelImportModel();
objdl = new ExcelImportDataAccess();
objpl.Ind = Ind;
DataTable dt = objdl.fillCompanyName(objpl);
return(dt);
}
public DataTable ExcelImportData(ExcelImportModel obj)
{
objdl = new ExcelImportDataAccess();
DataTable dt = new DataTable();
if (obj.Ind == 1)
{
dt = objdl.ExcelImportData(obj);
}
else
{
dt = objdl.ExcelItemData(obj);
}
return(dt);
}
public async Task <ActionResult> Import(HttpPostedFileBase file, bool isOverWrite = false)
{
var model = new ExcelImportModel();
if (file == null ||
file.ContentLength <= 0)
{
model.Msg = _htmlHelper.LocalizationString("please_select_file");
return(View(model));
}
var extension = Path.GetExtension(file.FileName);
if (extension != ".xls" &&
extension != ".xlsx")
{
model.Msg = _htmlHelper.LocalizationString("please_select_excel_file");
return(View(model));
}
var excelName = string.Format("{0:yyyy-MM-dd}-{1}", DateTime.Now, Guid.NewGuid());
var path = string.Format("{0}/{1}{2}", Server.MapPath("~/Public/files"), excelName, extension);
if (System.IO.File.Exists(path))
{
System.IO.File.Delete(path);
}
file.SaveAs(path);
var existingFile = new FileInfo(path);
using (var package = new ExcelPackage(existingFile))
{
var workBook = package.Workbook;
if (workBook == null ||
workBook.Worksheets.Count <= 0)
{
model.Msg = _htmlHelper.LocalizationString("excel_file_has_problems");
return(View(model));
}
var currentWorksheet = workBook.Worksheets.First();
var count = currentWorksheet.Dimension.End.Column;
if (count <= 2)
{
for (var i = 2; i < currentWorksheet.Dimension.End.Row; i++)
{
var key = currentWorksheet.Cells[i, 1].Value.ToString();
var tag = currentWorksheet.Cells[i, 2].Value.ToString();
var wordModels = new WordModel {
Key = key, Tag = tag, CreatedBy = User.Identity.GetUserId()
};
try
{
if (isOverWrite)
{
await _wordService.Update(wordModels);
}
else
{
await _wordService.Create(wordModels);
}
}
catch
{
model.Msg = _htmlHelper.LocalizationString("please_try_again");
return(View(model));
}
}
}
else
{
for (var i = 2; i < currentWorksheet.Dimension.End.Row; i++)
{
var key = currentWorksheet.Cells[i, 1].Value.ToString();
var desc = currentWorksheet.Cells[i, 2].Value.ToString();
var tag = currentWorksheet.Cells[i, 3].Value.ToString();
var translationTR = currentWorksheet.Cells[i, 4].Value.ToString();
var translationEN = currentWorksheet.Cells[i, 5].Value.ToString();
var translationAZ = currentWorksheet.Cells[i, 6].Value.ToString();
var translationCN = currentWorksheet.Cells[i, 7].Value.ToString();
var translationFR = currentWorksheet.Cells[i, 8].Value.ToString();
var translationGR = currentWorksheet.Cells[i, 9].Value.ToString();
var translationIT = currentWorksheet.Cells[i, 10].Value.ToString();
var translationKZ = currentWorksheet.Cells[i, 11].Value.ToString();
var translationRU = currentWorksheet.Cells[i, 12].Value.ToString();
var translationSP = currentWorksheet.Cells[i, 13].Value.ToString();
var translationTK = currentWorksheet.Cells[i, 14].Value.ToString();
var wordModel = new WordModel {
Key = key, Description = desc, Tag = tag, CreatedBy = User.Identity.GetUserId()
};
try
{
if (isOverWrite)
{
await _wordService.Update(wordModel);
}
else
{
await _wordService.Create(wordModel);
}
await _wordService.Translate(key, "TR", translationTR);
await _wordService.Translate(key, "EN", translationEN);
await _wordService.Translate(key, "AZ", translationAZ);
await _wordService.Translate(key, "CN", translationCN);
await _wordService.Translate(key, "FR", translationFR);
await _wordService.Translate(key, "GR", translationGR);
await _wordService.Translate(key, "IT", translationIT);
await _wordService.Translate(key, "KZ", translationKZ);
await _wordService.Translate(key, "RU", translationRU);
await _wordService.Translate(key, "SP", translationSP);
await _wordService.Translate(key, "TK", translationTK);
}
catch
{
model.Msg = _htmlHelper.LocalizationString("please_try_again");
return(View(model));
}
}
}
}
model.Msg = _htmlHelper.LocalizationString("import_successful");
model.IsSuccess = true;
return(View(model));
}