protected void btnUpload_Click(object sender, EventArgs e)
{
if (F_Upload.HasFile)
{
string tmpPath = Server.MapPath("~/Temp/ ");
string tmpName = System.IO.Path.GetRandomFileName();
string tmpFile = tmpPath + "\\\\" + tmpName;
F_Upload.SaveAs(tmpFile);
FileInfo fi = new FileInfo(tmpFile);
bool IsError = false;
string ErrMsg = "";
using (ExcelPackage xlP = new ExcelPackage(fi))
{
ExcelWorksheet wsD = null;
try
{
wsD = xlP.Workbook.Worksheets["Allocation"];
}
catch (Exception ex)
{
string aa = ex.Message;
wsD = null;
}
if (wsD == null)
{
ScriptManager.RegisterClientScriptBlock(Page, Page.GetType(), "", "alert('" + new JavaScriptSerializer().Serialize("Invalid XL File") + "');", true);
xlP.Dispose();
return;
}
SqlConnection con = new SqlConnection("Data Source=192.9.200.150;Initial Catalog=EmpSalary; User Id=sa; Password=isgec12345;");
string querySting = "";
string Allocation = "";
string Sql;
int I = row;
SqlCommand cmda;
while (wsD.Cells[I, 1].Text != "")
{
Allocation = (wsD.Cells[I, col].Text);
CardNo = (wsD.Cells[I, 1].Text);
querySting = "SELECT * FROM AllocationTpye WHERE CardNo=" + "'" + CardNo + "'";
SqlCommand command = new SqlCommand(querySting, con);
con.Open();
SqlDataReader dr = command.ExecuteReader();
if (dr.Read())
{
if (CardNo != "" || Allocation == "")
{
cmda = new SqlCommand("UPDATE AllocationTpye SET AllcationType='" + Allocation + "' WHERE CardNo='" + CardNo + "'", con4update);
con4update.Open();
cmda.ExecuteNonQuery();
con4update.Close();
}
}
else
{
Sql = "insert into AllocationTpye (CardNo,AllcationType) values( '" + CardNo + "', '" + Allocation.ToString() + "')";
SqlCommand cmd = new SqlCommand(Sql, con4update);
con4update.Open();
cmd.ExecuteNonQuery();
con4update.Close();
}
con.Close();
I++;
}
xlP.Dispose();
}
if (IsError)
{
ScriptManager.RegisterClientScriptBlock(Page, Page.GetType(), "", "alert('" + new JavaScriptSerializer().Serialize("ITEMS NOT INSERTED/UPDATED: " + ErrMsg) + "');", true);
}
else
{
ScriptManager.RegisterClientScriptBlock(Page, Page.GetType(), "", "alert('" + new JavaScriptSerializer().Serialize("Upload Successfully") + "');", true);
}
}
}
protected void cmdUpload_Click(object sender, EventArgs e)
{
if (F_Upload.HasFile)
{
string tmpPath = Server.MapPath("~/Temp/ ");
string tmpName = System.IO.Path.GetRandomFileName();
string tmpFile = tmpPath + "\\\\" + tmpName;
F_Upload.SaveAs(tmpFile);
FileInfo fi = new FileInfo(tmpFile);
bool IsError = false;
string ErrMsg = "";
using (ExcelPackage xlP = new ExcelPackage(fi))
{
ExcelWorksheet wsD = null;
try
{
wsD = xlP.Workbook.Worksheets["Employee"];
}
catch (Exception ex)
{
string aa = ex.Message;
wsD = null;
}
if (wsD == null)
{
ScriptManager.RegisterClientScriptBlock(Page, Page.GetType(), "", "alert('" + new JavaScriptSerializer().Serialize("Invalid XL File") + "');", true);
xlP.Dispose();
return;
}
//===========================================
string ForMonth = wsD.Cells[1, 2].Text;
string ForYear = wsD.Cells[2, 2].Text;
int k = ColStart;
if (ForMonth == "" || ForYear == "")
{
ScriptManager.RegisterClientScriptBlock(Page, Page.GetType(), "", "alert('" + new JavaScriptSerializer().Serialize("Month or Year is Blank") + "');", true);
xlP.Dispose();
return;
}
int vJ = RowStart;
while (wsD.Cells[vJ, 1].Text != "")
{
int perc = Convert.ToInt32(wsD.Cells[vJ, 6].Text);
if (perc != 100 && perc != 0)
{
ScriptManager.RegisterClientScriptBlock(Page, Page.GetType(), "", "alert('" + new JavaScriptSerializer().Serialize("Percentage of all Employee shoule be 100") + "');", true);
xlP.Dispose();
return;
}
vJ++;
}
List <string> Projects = new List <string>();
int I = ColStart;
while (wsD.Cells[RowStart - 1, I].Text != "")
{
Projects.Add(wsD.Cells[RowStart - 1, I].Text);
I++;
}
// connection
SqlConnection con = new SqlConnection("Data Source=192.9.200.150;Initial Catalog=EmpSalary; User Id=sa; Password=isgec12345;");
con.Open();
int Cols = Projects.Count + ColStart;
int J = RowStart;
while (wsD.Cells[J, 1].Text != "")
{
string CardNo = wsD.Cells[J, 1].Text;
string Sql;
Sql = "Delete from EmpEffort where Months='" + ForMonth + "' and Years ='" + ForYear + "' and CardNo='" + CardNo + "'";
SqlCommand cmd = new SqlCommand(Sql, con);
cmd.ExecuteNonQuery();
for (I = ColStart; I <= Cols; I++)
{
string perc = wsD.Cells[J, I].Text;
if (perc != "")
{
string Sql2;
Sql2 = "insert into EmpEffort (Months,Years,CardNo,ProjectID,EffortPercentage,EffortValue,Source) values('" + ForMonth + "', '" + ForYear + "', '" + CardNo + "', '" + Projects[I - ColStart] + "', '" + perc + "', 00,'Percentage Based')";
SqlCommand cmd2 = new SqlCommand(Sql2, con);
cmd2.ExecuteNonQuery();
}
}
J++;
}
con.Close();
xlP.Dispose();
if (IsError)
{
ScriptManager.RegisterClientScriptBlock(Page, Page.GetType(), "", "alert('" + new JavaScriptSerializer().Serialize("ITEMS NOT INSERTED/UPDATED: " + ErrMsg) + "');", true);
}
else
{
ScriptManager.RegisterClientScriptBlock(Page, Page.GetType(), "", "alert('" + new JavaScriptSerializer().Serialize("Uploaded successfully") + "');", true);
}
}
}
}
protected void btnUpload_Click(object sender, EventArgs e)
{
if (F_Upload.HasFile)
{
string tmpPath = Server.MapPath("~/Temp/ ");
string tmpName = System.IO.Path.GetRandomFileName();
string tmpFile = tmpPath + "\\\\" + tmpName;
F_Upload.SaveAs(tmpFile);
FileInfo fi = new FileInfo(tmpFile);
bool IsError = false;
string ErrMsg = "";
using (ExcelPackage xlP = new ExcelPackage(fi))
{
ExcelWorksheet wsD = null;
try
{
wsD = xlP.Workbook.Worksheets["EstimateTemplate"];
}
catch (Exception ex)
{
string aa = ex.Message;
wsD = null;
}
if (wsD == null)
{
ScriptManager.RegisterClientScriptBlock(Page, Page.GetType(), "", "alert('" + new JavaScriptSerializer().Serialize("Invalid XL File") + "');", true);
xlP.Dispose();
return;
}
//===========================================
string ForDepartment = wsD.Cells[4, 2].Text;
if (ForDepartment == "")
{
ScriptManager.RegisterClientScriptBlock(Page, Page.GetType(), "", "alert('" + new JavaScriptSerializer().Serialize("Department is a Blank") + "');", true);
xlP.Dispose();
return;
}
// connection
SqlConnection con = new SqlConnection("Data Source=192.9.200.150;Initial Catalog=EmpSalary; User Id=sa; Password=isgec12345;");
con.Open();
int J = RowStart;
int I = ColStart;
while (wsD.Cells[J, 1].Text != "")
{
string ProCode = wsD.Cells[J, 1].Text;
string ProDesc = wsD.Cells[J, 2].Text;
string workersGrade = wsD.Cells[J, 3].Text;
string SubordinateGrade = wsD.Cells[J, 4].Text;
string Supervision = wsD.Cells[J, 5].Text;
string AsstentEng = wsD.Cells[J, 6].Text;
string SrEngineers = wsD.Cells[J, 7].Text;
string DeputyManager = wsD.Cells[J, 8].Text;
string Manager = wsD.Cells[J, 9].Text;
string AgmManager = wsD.Cells[J, 10].Text;
string GMDGM = wsD.Cells[J, 11].Text;
string AssociateVicePer = wsD.Cells[J, 12].Text;
string SeniorVicePer = wsD.Cells[J, 13].Text;
string Coogroop = wsD.Cells[J, 14].Text;
string ExcutiveDirector = wsD.Cells[J, 15].Text;
string TotalManDays = wsD.Cells[J, 16].Text;
string TotalProjectCost = wsD.Cells[J, 17].Text;
if (ForDepartment != "")
{
string Sql2;
Sql2 = "insert into CostEstimation (DepName,ProjectCode,ProjectDescription,WorkersGrade,SubordinateGrade,Supervision,AsstEngineer,SrEngineer,DeputyManager,Manager,AgmSrManager,GmDgm,AssociateVicePresident,SeniorVicePresident,CooGroopHead,ExcutiveDirector,TotalManDay,TotalCostProject) values('" + ForDepartment + "', '" + ProCode + "', '" + ProDesc + "', '" + workersGrade + "', '" + SubordinateGrade + "','" + Supervision + "','" + AsstentEng + "','" + SrEngineers + "','" + DeputyManager + "','" + Manager + "','" + AgmManager + "','" + GMDGM + "','" + AssociateVicePer + "','" + SeniorVicePer + "','" + Coogroop + "','" + ExcutiveDirector + "','" + TotalManDays + "','" + TotalProjectCost + "')";
SqlCommand cmd2 = new SqlCommand(Sql2, con);
cmd2.ExecuteNonQuery();
}
J++;
}
con.Close();
xlP.Dispose();
if (IsError)
{
ScriptManager.RegisterClientScriptBlock(Page, Page.GetType(), "", "alert('" + new JavaScriptSerializer().Serialize("ITEMS NOT INSERTED/UPDATED: " + ErrMsg) + "');", true);
}
else
{
ScriptManager.RegisterClientScriptBlock(Page, Page.GetType(), "", "alert('" + new JavaScriptSerializer().Serialize("Uploaded successfully") + "');", true);
}
}
}
}
protected void bindGridview(object sender, EventArgs e)
{
string connectionString = "";
string servername = ConfigurationManager.AppSettings["ServerName"].ToString();
string domainame = ConfigurationManager.AppSettings["DomainName"].ToString();
string username = ConfigurationManager.AppSettings["UserName"].ToString();
string password = ConfigurationManager.AppSettings["Password"].ToString();
// string FolderPathServerwrite = ConfigurationManager.AppSettings["UploadDocPath"].ToString();
using (NetworkShareAccesser.Access(servername, domainame, username, password))
{
if (F_Upload.HasFile)
{
string savePath = ConfigurationManager.AppSettings["FolderPath"];
Directory.CreateDirectory(savePath);
string strFileType = Path.GetExtension(F_Upload.FileName).ToLower();
string path = Path.Combine(savePath, F_Upload.FileName);
F_Upload.SaveAs(path);
if (strFileType == ".xls")
{
connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
}
else if (strFileType == ".xlsx")
{
connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
}
else
{
validExcel = 0;
return;
}
System.Data.DataTable dt = null;
OleDbConnection conn = new OleDbConnection(connectionString);
conn.Open();
dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
String[] excelSheets = new String[dt.Rows.Count];
int i = 0;
foreach (DataRow row in dt.Rows)
{
excelSheets[i] = row["TABLE_NAME"].ToString();
i++;
}
for (int j = 0; j < excelSheets.Length; j++)
{
string query = "Select * from[" + excelSheets[j] + "]";
//string query = "SELECT * FROM [Projectnvestigator$]";
// OleDbConnection conn = new OleDbConnection(connectionString);
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
OleDbCommand cmd = new OleDbCommand(query, conn);
OleDbDataAdapter da = new OleDbDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
if (excelSheets[j] == "'Project Details$'")
{
GridExcelDataProject.DataSource = ds.Tables[0];
GridExcelDataProject.DataBind();
da.Dispose();
}
if (excelSheets[j] == "'Investigator Details$'")
{
GridExcelDataInvestigator.DataSource = ds.Tables[0];
GridExcelDataInvestigator.DataBind();
da.Dispose();
}
}
conn.Close();
conn.Dispose();
}
}
}
protected void bindGridview(object sender, EventArgs e)
{
string connectionString = "";
if (F_Upload.HasFile)
{
string savePath = ConfigurationManager.AppSettings["UploadPath"];
string strFileType = Path.GetExtension(F_Upload.FileName).ToLower();
// string attachmentFile = F_Upload.PostedFile.FileName;
//string path = string.Concat(Server.MapPath(savePath + F_Upload.FileName));
//F_Upload.PostedFile.SaveAs(path);
string path = string.Concat(savePath + "/" + F_Upload.FileName);
F_Upload.SaveAs(path);
string attachmentFile = F_Upload.PostedFile.FileName;
if (strFileType == ".xls")
{
connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
}
else if (strFileType == ".csv")
{
// connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
// connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties=\"text;HDR=Yes;FMT=Fixed\"";
// connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties=\"Text;HDR=Yes;FMT=Delimited\"";
//connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
connectionString = "Provider=Microsoft.Jet.OleDb.4.0; Data Source = " + System.IO.Path.GetDirectoryName(path) + "; Extended Properties = \"Text;HDR=YES;FMT=Delimited\"";
}
else
{
validExcel = 0;
return;
}
string query = String.Format("SELECT * FROM {0}", Path.GetFileName(path));
// string query = String.Format("SELECT * FROM [S_JVUPLOADcsv$]", Path.GetFileName(path));
string filename = null;
filename = Path.GetFileName(path);
string[] fname = filename.Split('_');
string nmae = fname[0];
// OleDbConnection conn = new OleDbConnection(connectionString);
// OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0; Data Source = " + System.IO.Path.GetDirectoryName(path) + "; Extended Properties = \"Text;HDR=YES;FMT=Delimited\"");
OleDbConnection conn = new OleDbConnection(connectionString);
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
OleDbCommand cmd = new OleDbCommand(query, conn);
OleDbDataAdapter da = new OleDbDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
GridExcelData.DataSource = ds.Tables[0];
GridExcelData.DataBind();
int c = 0;
c = GridExcelData.Rows.Count;
da.Dispose();
conn.Close();
conn.Dispose();
}
}
