public ActionResult ImportApplicantArchieve(HttpPostedFileBase fileapplicant)
{
string path = Server.MapPath("~/excel/applicant-archieve" + new Random().Next(1, 1000000) + ".xlsx");
fileapplicant.SaveAs(path);
var dt = ExcelConnection.Datasource("select * from sheet", path);
List <RecruitmentSystem.Models.ImportArchieve.ApplicantArchieve> list = new List <Models.ImportArchieve.ApplicantArchieve>();
foreach (DataRow dr in dt.Rows)
{
if (dr[0] == null || dr[0].ToString() == "")
{
break;
}
list.Add(new Models.ImportArchieve.ApplicantArchieve()
{
DummyId = dr[0].ToString(),
TempId = dr[1].ToString()
});
}
foreach (var i in list)
{
db.sp_import_applicant_archieve(i.TempId, i.DummyId, 1);
}
return(RedirectToAction("ImportManpowerRequestAchieveIndex"));
}
/// <summary>
/// Initializes a new instance of the <see cref="ExcelDataLayerTest"/> class.
/// </summary>
public ExcelDataLayerTest()
{
ExcelConnection connection = new ExcelConnection();
AdoExcelDataLayerFactory factory = new AdoExcelDataLayerFactory(connection);
_context = new ExcelContext(factory);
}
private static int GetRowCount(ExcelConnection connection, string sheetName)
{
var data = new DataTable();
var dataAdapter = new ExcelDataAdapter($"SELECT count(*) from [{sheetName}]", connection);
dataAdapter.Fill(data);
return(Convert.ToInt32(data.Rows[0][0]));
}
private static void DeleteBlankRowsTillHeader(ExcelConnection connection, string sheetName, int headerRowId)
{
var deleteCommand = new ExcelCommand($"delete from [{sheetName}] where RowId='1' ", connection);
for (var i = 1; i < headerRowId; i++)
{
deleteCommand.ExecuteNonQuery();
}
}
/// <summary>
/// Initializes a new instance of the <see cref="ExcelReportsTest"/> class.
/// </summary>
public ExcelReportsTest()
{
DbSqlConnection connection = new DbSqlConnection();
ExcelConnection excelConnection = new ExcelConnection();
AdoSqlServerDataLayerFactory factory = new AdoSqlServerDataLayerFactory(connection);
AdoExcelDataLayerFactory excelFactory = new AdoExcelDataLayerFactory(excelConnection);
_dbContext = new DbContext(factory);
_excelContext = new ExcelContext(excelFactory);
}
private static List <string> GetSheetsInExcelFile(ExcelConnection connection)
{
var sheetsPresent = new List <string>();
var excelSheets = connection.GetSchema("Tables");
foreach (DataRow row in excelSheets.Rows)
{
sheetsPresent.Add(row["TABLE_NAME"].ToString());
}
return(sheetsPresent);
}
public ActionResult importCompany(HttpPostedFileBase file)
{
var excel = ExcelConnection.Datasource("select * from sheet", file, Server.MapPath("~/excel/"));
//foreach(DataRow dr in excel.Rows)
//{
// db.sp_add_companies(Tools.ToTitleCase(dr[0].ToString()), Tools.ToTitleCase(dr[1].ToString()), Tools.ToTitleCase(dr[2].ToString()), Tools.ToTitleCase(dr[3].ToString()), Manpower.GetIndustryID(dr[4].ToString()));
//}
CompanyViewModel model = new CompanyViewModel();
var errorHandler = model.ImportCompany(excel);
return(RedirectToAction("dashboard", errorHandler));
}
public ActionResult ImportPositions(HttpPostedFileBase file)
{
var excel = ExcelConnection.Datasource("select * from sheet", file, Server.MapPath("~/excel/"));
DatabaseModelDataContext db = new DatabaseModelDataContext();
foreach (DataRow dr in excel.Rows)
{
if (dr[0].ToString() == "" || dr[0].ToString() == null)
{
break;
}
db.sp_add_position(Tools.ToTitleCase(dr[0].ToString()));
}
return(RedirectToAction("manageform", new { status = "Successfully Import Position" }));
}
static void Main(string[] args)
{
using (ExcelConnection connection = new ExcelConnection(GetConnectionString()))
{
System.Diagnostics.Stopwatch stopwatch = new System.Diagnostics.Stopwatch();
stopwatch.Start();
connection.Open();
stopwatch.Stop();
Console.WriteLine("Total cost1: " + stopwatch.ElapsedMilliseconds + "ms");
stopwatch = new System.Diagnostics.Stopwatch();
stopwatch.Start();
//string queryString = "select [2015/06/05], [2017/12/31] from Sheet2";
string queryString = "SELECT[T0].[2015/06/05] AS[T0C0], [T0].[2017/12/31] AS[T0C1] FROM [Sheet2] [T0] WHERE [T0].[2015/06/05] LIKE '%n%' LIMIT 100";
//string queryString = "SELECT [T0].[Id] AS [T0C0], [T0].[product] AS [T0C1], [T0].[price] AS [T0C2], [T0].[quantity] AS [T0C3], [T0].[amount] AS [T0C4], [T0].[region] AS [T0C5], [T0].[date] AS [T0C6], [T0].[employee] AS [T0C7], [T0].[Department] AS [T0C8], [T0].[customer] AS [T0C9], [T0].[platform] AS [T0C10], [T0].[payment] AS [T0C11], [T0].[Language] AS [T0C12], [T0].[IsLeader] AS [T0C13] FROM [Sheet1] [T0] WHERE [T0].[product] LIKE 'Spread%'";
//string queryString = "SELECT [T0].[年月] AS [T0C0], [T0].[投資先コード] AS [T0C1], [T0].[投資先] AS [T0C2], [T0].[略称] AS [T0C3], [T0].[投資担当] AS [T0C4], [T0].[カテゴリー] AS [T0C5], [T0].[ステージ] AS [T0C6], [T0].[売上計画] AS [T0C7], [T0].[売上実績] AS [T0C8], [T0].[売上達成率] AS [T0C9], [T0].[売上計画\n（直近12ヵ月累計）] AS [T0C10], [T0].[売上実績\n（直近12ヵ月累計）] AS [T0C11], [T0].[売上達成率\n（直近12ヵ月）] AS [T0C12], [T0].[売上計画\n（会計年度）] AS [T0C13], [T0].[売上実績\n（会計年度）] AS [T0C14], [T0].[売上達成率\n（会計年度）] AS [T0C15], [T0].[当初売上計画\n（単月）] AS [T0C16], [T0].[当初売上達成率] AS [T0C17], [T0].[当初売上計画\n（会計年度）] AS [T0C18], [T0].[当初売上達成率\n（会計年度）] AS [T0C19], [T0].[営利計画] AS [T0C20], [T0].[営利実績] AS [T0C21], [T0].[営利乖離額] AS [T0C22], [T0].[営利計画\n（直近12ヵ月累計）] AS [T0C23], [T0].[営利実績\n（直近12ヵ月累計）] AS [T0C24], [T0].[営利達成率\n（直近12ヵ月）] AS [T0C25], [T0].[営利乖離額\n（直近12ヵ月累計）] AS [T0C26], [T0].[営利計画\n（会計年度）] AS [T0C27], [T0].[営利実績\n（会計年度）] AS [T0C28], [T0].[営利達成率\n（会計年度）] AS [T0C29], [T0].[営利乖離額\n（会計年度）] AS [T0C30], [T0].[当初営利計画\n（単月）] AS [T0C31], [T0].[当初営利達成率] AS [T0C32], [T0].[当初営利計画\n（会計年度）] AS [T0C33], [T0].[当初営利達成率\n（会計年度）] AS [T0C34], [T0].[投資額] AS [T0C35], [T0].[一部売却後投資額] AS [T0C36], [T0].[持分比率] AS [T0C37], [T0].[1株単価] AS [T0C38], [T0].[総株式数\n（顕在）] AS [T0C39], [T0].[持ち株数] AS [T0C40], [T0].[時価総額] AS [T0C41], [T0].[出資時_時価総額] AS [T0C42], [T0].[増加_時価総額] AS [T0C43], [T0].[持分_時価総額] AS [T0C44], [T0].[未実現利益] AS [T0C45], [T0].[現預金] AS [T0C46], [T0].[純資産] AS [T0C47], [T0].[持分純資産] AS [T0C48], [T0].[増減率] AS [T0C49], [T0].[最大減損額] AS [T0C50], [T0].[投資額（売却分）] AS [T0C51], [T0].[売却（清算）額] AS [T0C52], [T0].[損益] AS [T0C53], [T0].[現在簿価] AS [T0C54], [T0].[減損計上額] AS [T0C55], [T0].[想定IRR計算\n_日付] AS [T0C56], [T0].[想定IRR計算\n_投資額] AS [T0C57], [T0].[想定IRR] AS [T0C58], [T0].[確定IRR計算\n_日付] AS [T0C59], [T0].[確定IRR計算\n_投資額] AS [T0C60], [T0].[確定IRR] AS [T0C61], [T0].[トータル\n想定IRR] AS [T0C62], [T0].[トータル\n確定IRR] AS [T0C63] FROM [00_統合用] [T0] LIMIT 100";
ExcelCommand cmd = new ExcelCommand(queryString, connection);
//DataTable table = new DataTable("queryTable");
//ExcelDataAdapter dap = new ExcelDataAdapter(cmd);
//dap.Fill(table);
var oleExcelReader = cmd.ExecuteReader();
//int nOutputRow = 0;
//while (oleExcelReader.Read())
//{
// for (int i = 0; i < oleExcelReader.FieldCount; i++)
// {
// object v = oleExcelReader.GetValue(i);
// Console.Write(v + v.GetType().Name);
// Console.Write(", ");
// }
// Console.WriteLine();
// nOutputRow++;
//}
stopwatch.Stop();
Console.WriteLine("Total cost2: " + stopwatch.ElapsedMilliseconds + "ms");
}
Console.ReadLine();
}
private static int AssignRownAndColumnIndex(ExcelConnection connection, string sheetName, List <Column> columns)
{
var data = new DataTable();
var dataAdapter = new ExcelDataAdapter($"SELECT top 100 * from [{sheetName}]", connection);
dataAdapter.Fill(data);
foreach (DataRow dataRow in data.Rows)
{
var headerRowId = UpdateRowAndColumIndex(dataRow, columns);
if (headerRowId != 0)
{
return(headerRowId);
}
}
return(0);
}
private DataTable GetDataExcel(string excelPath, string sql)
{
try
{
using (OleDbConnection cn = new OleDbConnection(ExcelConnection.Replace("[excelPath]", excelPath)))
{
cn.Open();
using (OleDbCommand cmd = new OleDbCommand(sql, cn))
{
var dt = new DataTable();
dt.Load(cmd.ExecuteReader());
return(dt);
}
}
}
catch (Exception exc)
{
throw exc;
}
}
/// <summary>
/// Initializes a new instance of the <see cref="AdoExcelDataLayerFactory"/> class.
/// </summary>
/// <param name="connection">The connection.</param>
public AdoExcelDataLayerFactory(ExcelConnection connection) : base(connection)
{
}
public ActionResult ImportManpowerRequestAchieveIndex(HttpPostedFileBase file)
{
string filename = Server.MapPath("~/excel/manpower-archieved-" + DateTime.Now.ToString("MM-dd-yy_mm-hh-ss-tt") + ".xlsx");
file.SaveAs(filename);
var dt = ExcelConnection.Datasource("select * from sheet", filename);
List <RecruitmentSystem.Models.ImportArchieve.ManpowerArchieve> list = new List <Models.ImportArchieve.ManpowerArchieve>();
foreach (DataRow dr in dt.Rows)
{
if (dr[0] == null || dr[0].ToString() == "")
{
break;
}
list.Add(new Models.ImportArchieve.ManpowerArchieve()
{
TempMRFID = dr[0].ToString(),
DateRequested = Tools.ToDateTime(dr[1]),
DateofDeployment = Tools.ToDateTime(dr[2]),
RequiredPosition = dr[3].ToString(),
RequiredNumber = Tools.ToInteger(dr[4]),
EducationAttainment = dr[5].ToString(),
Course = dr[6].ToString(),
Gender = dr[7].ToString(),
AgeRequirement = dr[8].ToString(),
SkillType = dr[9].ToString(),
SpecificSkill = dr[10].ToString(),
Certification = dr[11].ToString(),
CostCenter = dr[12].ToString(),
Department = dr[13].ToString(),
JobDescription = dr[14].ToString(),
SalaryDetails = dr[15].ToString(),
Basicpay = Tools.ToDecimal(dr[16].ToString()),
Cola = Tools.ToDecimal(dr[17].ToString()),
Skilled = Tools.ToDecimal(dr[18].ToString()),
Meal = Tools.ToDecimal(dr[19].ToString()),
Transportation = Tools.ToDecimal(dr[20].ToString()),
Gas = Tools.ToDecimal(dr[21].ToString()),
Communication = Tools.ToDecimal(dr[22].ToString()),
Motorcycle = Tools.ToDecimal(dr[23].ToString()),
Clothing = Tools.ToDecimal(dr[24].ToString()),
Medical = Tools.ToDecimal(dr[25].ToString()),
PayoutDate = dr[26].ToString(),
WhoToLookFor = dr[27].ToString(),
Establishment = dr[28].ToString(),
LocationofDeployment = dr[29].ToString(),
OfficeAddresstoReport = dr[30].ToString(),
BusinessUnit = dr[31].ToString(),
Classification = dr[32].ToString(),
CompanyRequested = dr[33].ToString(),
Requestor = dr[34].ToString(),
RequestorContactNumber = dr[35].ToString(),
RequestorEmailAddress = dr[36].ToString(),
Recruiter = UserManager.FindByEmail(dr[38].ToString()).Id,
Coordinator = UserManager.FindByEmail(dr[39].ToString()).Id,
RecruitmentSupervisor = UserManager.FindByEmail("*****@*****.**").Id,
DateCompleted = Tools.ToDateTimeNull(dr[40]),
Status = (Tools.ToDateTimeNull(dr[40]) == null ? "Inviting Applicants" : "Completed"),
AccountManager = UserManager.FindByEmail(dr[37].ToString()).Id,
Pending = Convert.ToInt32(dr[41]),
Closed = Convert.ToInt32(dr[42]),
Cancel = Convert.ToInt32(dr[43]),
OnProcess = Convert.ToInt32(dr[44])
});
}
foreach (var i in list)
{
db.sp_import_mrf_request_archieve(i.Coordinator, i.TempMRFID, i.DateRequested, i.DateofDeployment, i.RequiredPosition, i.RequiredNumber, i.EducationAttainment, i.Course, i.Gender, i.AgeRequirement, i.SkillType, i.SpecificSkill, i.Certification, i.CostCenter, i.Department, i.JobDescription, i.SalaryDetails, i.Basicpay, i.Cola, i.Skilled, i.Meal, i.Transportation, i.Gas, i.Communication, i.Motorcycle, i.Clothing, i.Medical, i.PayoutDate, i.WhoToLookFor, i.Establishment, i.OfficeAddresstoReport, i.LocationofDeployment, i.BusinessUnit, i.Classification, i.CompanyRequested, i.Requestor, i.RequestorContactNumber, i.RequestorEmailAddress, i.Recruiter, i.RecruitmentSupervisor, i.DateCompleted, i.Status, i.AccountManager, i.Cancel);
for (var a = 1; a <= i.Closed; a++)
{
db.sp_import_applicant_archieve(i.TempMRFID, Guid.NewGuid().ToString(), 1);
}
for (var a = 1; a <= i.OnProcess; a++)
{
db.sp_import_applicant_archieve(i.TempMRFID, Guid.NewGuid().ToString(), 0);
}
}
return(RedirectToAction("ImportManpowerRequestAchieveIndex"));
}
/// <summary>
/// Initializes a new instance of the <see cref="ExcelDataLayerFactory"/> class.
/// </summary>
/// <param name="connection">The connection.</param>
/// <exception cref="ArgumentNullException">connection</exception>
public ExcelDataLayerFactory(ExcelConnection connection)
{
_connection = connection ?? throw new ArgumentNullException(nameof(connection));
}
/// <summary>
/// Initializes a new instance of the <see cref="ExcelDataLayer{T}"/> class.
/// </summary>
/// <param name="connection">The connection.</param>
/// <exception cref="ArgumentNullException">connection</exception>
public ExcelDataLayer(ExcelConnection connection)
{
_connection = connection ?? throw new ArgumentNullException(nameof(connection));
_formatter = new SqlCommadFormatter <T>();
}
