public bool ReadExcelData() { string path = FileName; //Application exc = null; //Workbooks workBooks = null; //Workbook workBook = null; //Worksheet workSheet = null; //Range r = null; // object oMissing = System.Reflection.Missing.Value; SMT.Foundation.Core.BaseDAL dal = new SMT.Foundation.Core.BaseDAL(); try { dal.BeginTransaction(); //exc = new Application(); //exc.UserControl = true; //exc.Application.Workbooks.Open(path, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing); //workBooks = exc.Workbooks; //workBook = workBooks[1]; //workSheet = (Worksheet)workBook.Worksheets[WorkSheetIndex <= 0 ? 1 : WorkSheetIndex]; //int colCount = workSheet.UsedRange.Columns.Count; //获得列数 //int rowCount = workSheet.UsedRange.Rows.Count; //获得行数 //Dictionary<string, string> dictIndexs = new Dictionary<string, string>(); //dictIndexs.Add("A", "0"); //dictIndexs.Add("B", "1"); //dictIndexs.Add("C", "2"); //dictIndexs.Add("D", "3"); //dictIndexs.Add("E", "4"); //dictIndexs.Add("F", "5"); //dictIndexs.Add("G", "6"); //dictIndexs.Add("H", "7"); //dictIndexs.Add("I", "8"); //dictIndexs.Add("J", "9"); //dictIndexs.Add("K", "10"); //dictIndexs.Add("L", "11"); //dictIndexs.Add("M", "12"); //dictIndexs.Add("N", "13"); //dictIndexs.Add("O", "14"); //dictIndexs.Add("P", "15"); //dictIndexs.Add("Q", "16"); //dictIndexs.Add("R", "17"); //dictIndexs.Add("S", "18"); //dictIndexs.Add("T", "19"); //dictIndexs.Add("U", "20"); //dictIndexs.Add("V", "21"); //dictIndexs.Add("W", "22"); //dictIndexs.Add("X", "23"); //dictIndexs.Add("Y", "24"); //dictIndexs.Add("Z", "25"); Dictionary<string, Int32> dictIndexs = new Dictionary<string, Int32>(); dictIndexs.Add("A", 0); dictIndexs.Add("B", 1); dictIndexs.Add("C", 2); dictIndexs.Add("D", 3); dictIndexs.Add("E", 4); dictIndexs.Add("F", 5); dictIndexs.Add("G", 6); dictIndexs.Add("H", 7); dictIndexs.Add("I", 8); dictIndexs.Add("J", 9); dictIndexs.Add("K", 10); dictIndexs.Add("L", 11); dictIndexs.Add("M", 12); dictIndexs.Add("N", 13); dictIndexs.Add("O", 14); dictIndexs.Add("P", 15); dictIndexs.Add("Q", 16); dictIndexs.Add("R", 17); dictIndexs.Add("S", 18); dictIndexs.Add("T", 19); dictIndexs.Add("U", 20); dictIndexs.Add("V", 21); dictIndexs.Add("W", 22); dictIndexs.Add("X", 23); dictIndexs.Add("Y", 24); dictIndexs.Add("Z", 25); //excel读取开始行号 int beginRow = Convert.ToInt32(ImportConfig.STARTROW.GetValueOrDefault(2)); //excel读取结束行号 int endRow = Convert.ToInt32(ImportConfig.ENDROW.GetValueOrDefault(2)); //if (endRow > rowCount) // endRow = rowCount; int i = 1; System.Text.UTF8Encoding code = new UTF8Encoding(); using (StreamReader sr = new StreamReader(path, Encoding.GetEncoding("gb2312"))) { string line; // Read and display lines from the file until the end of // the file is reached. //while ((line = sr.ReadLine()) != null) while (!string.IsNullOrEmpty(line = sr.ReadLine())) { string[] lineTmp = line.Split(','); if (i >= beginRow && i <= endRow) { //创建一个实列 Type type = EntityInstance.GetType(); object entity = Activator.CreateInstance(type); //得到excel二维数组当前行中A列的值 //r = (Range)workSheet.Cells[i, "A"]; //string value = r.Text.ToString().Trim(); #region 实例化对像 foreach (T_HR_IMPORTSETDETAIL detail in ImportConfig.T_HR_IMPORTSETDETAIL) { if (!string.IsNullOrEmpty(detail.EXECELCOLUMN)) { //r = (Range)workSheet.Cells[i, detail.EXECELCOLUMN]; ////r = (Range)workSheet.Cells[i, "D"]; //string value = r.Text.ToString().Trim(); int index = 0; //把列名拆成字符数组 char[] tmps = detail.EXECELCOLUMN.ToUpper().ToCharArray(); if (tmps.Length == 1) { //列名是一个字母 直接从字典查出列对应的索引 index = dictIndexs[tmps[0].ToString()]; } else { //列名是双字符 计算出列对应的索引 index = (dictIndexs[tmps[0].ToString()] + 1) * 26 + (dictIndexs[tmps[1].ToString()]); } // int index = Convert.ToInt32(dictIndexs[detail.EXECELCOLUMN.ToUpper()]); string value = lineTmp[index].Trim(); PropertyInfo prop = type.GetProperty(detail.ENTITYCOLUMNCODE); if (prop != null) { if (!string.IsNullOrEmpty(value)) { if (prop.PropertyType.BaseType == typeof(System.ValueType)) { if (prop.PropertyType.ToString().Contains(typeof(DateTime).ToString())) { prop.SetValue(entity, Convert.ToDateTime(value), null); } else { decimal tmpValue; decimal.TryParse(value, out tmpValue); prop.SetValue(entity, tmpValue, null); } } else { prop.SetValue(entity, value, null); } } } } } #endregion PropertyInfo prop1 = type.GetProperty("PENSIONDETAILID"); prop1.SetValue(entity, Guid.NewGuid().ToString(), null); PropertyInfo prop2 = type.GetProperty("OWNERCOMPANYID"); prop2.SetValue(entity, paras["OWNERCOMPANYID"], null); PropertyInfo prop3 = type.GetProperty("OWNERDEPARTMENTID"); prop3.SetValue(entity, paras["OWNERDEPARTMENTID"], null); PropertyInfo prop4 = type.GetProperty("OWNERPOSTID"); prop4.SetValue(entity, paras["OWNERPOSTID"], null); PropertyInfo prop5 = type.GetProperty("OWNERID"); prop5.SetValue(entity, paras["OWNERID"], null); PropertyInfo prop6 = type.GetProperty("CREATEUSERID"); prop6.SetValue(entity, paras["CREATEUSERID"], null); PropertyInfo prop7 = type.GetProperty("PENSIONYEAR"); prop7.SetValue(entity, Convert.ToDecimal(paras["YEAR"]), null); PropertyInfo prop8 = type.GetProperty("PENSIONMOTH"); prop8.SetValue(entity, Convert.ToDecimal(paras["MONTH"]), null); PropertyInfo prop9 = type.GetProperty("CREATEDATE"); prop9.SetValue(entity, System.DateTime.Now, null); T_HR_PENSIONDETAIL tmpDetail = entity as T_HR_PENSIONDETAIL; string idnumber = string.Empty; if (string.IsNullOrEmpty(tmpDetail.IDNUMBER)) { idnumber = "Null0"; } else { idnumber = tmpDetail.IDNUMBER.ToString(); } //根据身份证号 查询员工 如果没有找到就不插入此条记录 var employee = from e in dal.GetObjects<T_HR_EMPLOYEE>() where e.IDNUMBER == idnumber && e.EDITSTATE == "1" select e; if (employee.Count() > 0) { //删除旧的记录 var oldDetail = from c in dal.GetObjects<T_HR_PENSIONDETAIL>() where c.PENSIONMOTH == tmpDetail.PENSIONMOTH && c.PENSIONYEAR == tmpDetail.PENSIONYEAR && c.IDNUMBER == idnumber select c; if (oldDetail.Count() > 0) { dal.DeleteFromContext(oldDetail.FirstOrDefault()); } T_HR_EMPLOYEE entCurEmp = employee.FirstOrDefault(); if (tmpDetail.EMPLOYEEID != entCurEmp.EMPLOYEEID) { tmpDetail.EMPLOYEEID = entCurEmp.EMPLOYEEID; entity = tmpDetail; } //插入数据到数据库 dal.AddToContext(entity); // dal.Add(entity); } else { Tracer.Debug("PensionImport:" + i.ToString() + "行,没有员工身份证为此号码:" + idnumber); } } i++; } } dal.SaveContextChanges(); dal.CommitTransaction(); CommDal<T_HR_PENSIONDETAIL> cdal = new CommDal<T_HR_PENSIONDETAIL>(); // string strSql = @" update t_hr_pensiondetail a set a.pensionmasterid = (select pensionmasterid from t_hr_pensionmaster b where a.computerno // = b.computerno),a.employeeid= (select employeeid from t_hr_pensionmaster b where a.computerno = b.computerno) // ,a.CARDID= (select CARDID from t_hr_pensionmaster b where a.computerno = b.computerno)"; //string strSql = @" update t_hr_pensiondetail a set a.employeeid= (select employeeid from t_hr_employee b where a.idnumber = b.IDNUMBER)"; //string strSql = " update t_hr_pensiondetail a set a.pensionmasterid = (select pensionmasterid from t_hr_pensionmaster b where a.computerno" // +"= b.computerno and b.ownercompanyid='" + paras["OWNERCOMPANYID"]+"'),a.employeeid= (select employeeid from t_hr_pensionmaster b where a.computerno = b.computerno and b.ownercompanyid='" + paras["OWNERCOMPANYID"]+"')" // +" ,a.CARDID= (select CARDID from t_hr_pensionmaster b where a.computerno = b.computerno b.ownercompanyid='" + paras["OWNERCOMPANYID"]+"')" // +" ,a.OWNERID=(select employeeid from t_hr_pensionmaster b where a.computerno = b.computerno b.ownercompanyid='" + paras["OWNERCOMPANYID"]+"')" // +" ,a.OWNERPOSTID=(select c.OWNERPOSTID from t_hr_pensionmaster b ,t_hr_employee c where a.computerno = b.computerno and b.employeeid= c.employeeid + b.ownercompanyid='" + paras["OWNERCOMPANYID"]+"') " // +" ,a.OWNERDEPARTMENTID=(select c.OWNERDEPARTMENTID from t_hr_pensionmaster b ,t_hr_employee c where a.computerno = b.computerno and b.employeeid= c.employeeid b.ownercompanyid='" + paras["OWNERCOMPANYID"]+"')" // +" ,a.OWNERCOMPANYID=(select c.OWNERCOMPANYID from t_hr_pensionmaster b ,t_hr_employee c where a.computerno = b.computerno and b.employeeid= c.employeeid b.ownercompanyid='" + paras["OWNERCOMPANYID"]+"')" // +" where a.PENSIONYEAR='" + paras["YEAR"] + "' and a.PENSIONMOTH='" + paras["MONTH"] + "'"; //cdal.ExecuteCustomerSql(strSql); //strSql = "update t_hr_pensiondetail a set a.pensionmasterid = (select pensionmasterid from t_hr_pensionmaster b where a.employeeid = b.employeeid),a.cardid =(select b.cardid from t_hr_pensionmaster b where a.employeeid = b.employeeid)"; string strSql = " update t_hr_pensiondetail a set a.pensionmasterid = (select pensionmasterid from t_hr_pensionmaster b,t_hr_employee c where a.IDNUMBER= c.IDNUMBER and b.employeeid=c.employeeid and b.editstate=1)" + " ,a.employeeid= (select employeeid from t_hr_employee c where a.IDNUMBER = c.IDNUMBER )" + " ,a.CARDID= (select CARDID from t_hr_pensionmaster b ,t_hr_employee c where a.IDNUMBER = c.IDNUMBER and b.employeeid=c.employeeid and b.editstate=1)" + " ,a.OWNERID=(select employeeid from t_hr_employee c where a.IDNUMBER = c.IDNUMBER )" + " ,a.OWNERPOSTID=(select c.OWNERPOSTID from t_hr_employee c where a.IDNUMBER= c.IDNUMBER ) " + " ,a.OWNERDEPARTMENTID=(select c.OWNERDEPARTMENTID from t_hr_employee c where a.IDNUMBER= c.IDNUMBER )" + " ,a.OWNERCOMPANYID=(select c.OWNERCOMPANYID from t_hr_employee c where a.IDNUMBER = c.IDNUMBER )" + " where a.PENSIONYEAR='" + paras["YEAR"] + "' and a.PENSIONMOTH='" + paras["MONTH"] + "' and a.CREATEUSERID='" + paras["CREATEUSERID"] + "'"; cdal.ExecuteCustomerSql(strSql); return true; } catch (Exception ex) { //TODO: 写成导入日志 //throw ex; Tracer.Debug("My error" + ex.Message); dal.RollbackTransaction(); return false; } finally { //关闭当前的excel进程 //exc.Application.Workbooks.Close(); //exc.Quit(); //System.Runtime.InteropServices.Marshal.ReleaseComObject(exc); //IntPtr t = new IntPtr(exc.Hwnd); //exc = null; //int kid = 0; //GetWindowThreadProcessId(t, out kid); //System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(kid); //p.Kill(); } }
public bool ReadExcelData() { string path = FileName; //Application exc = null; //Workbooks workBooks = null; //Workbook workBook = null; //Worksheet workSheet = null; //Range r = null; // object oMissing = System.Reflection.Missing.Value; SMT.Foundation.Core.BaseDAL dal = new SMT.Foundation.Core.BaseDAL(); try { dal.BeginTransaction(); //exc = new Application(); //exc.UserControl = true; //exc.Application.Workbooks.Open(path, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing); //workBooks = exc.Workbooks; //workBook = workBooks[1]; //workSheet = (Worksheet)workBook.Worksheets[WorkSheetIndex <= 0 ? 1 : WorkSheetIndex]; //int colCount = workSheet.UsedRange.Columns.Count; //获得列数 //int rowCount = workSheet.UsedRange.Rows.Count; //获得行数 //Dictionary<string, string> dictIndexs = new Dictionary<string, string>(); //dictIndexs.Add("A", "0"); //dictIndexs.Add("B", "1"); //dictIndexs.Add("C", "2"); //dictIndexs.Add("D", "3"); //dictIndexs.Add("E", "4"); //dictIndexs.Add("F", "5"); //dictIndexs.Add("G", "6"); //dictIndexs.Add("H", "7"); //dictIndexs.Add("I", "8"); //dictIndexs.Add("J", "9"); //dictIndexs.Add("K", "10"); //dictIndexs.Add("L", "11"); //dictIndexs.Add("M", "12"); //dictIndexs.Add("N", "13"); //dictIndexs.Add("O", "14"); //dictIndexs.Add("P", "15"); //dictIndexs.Add("Q", "16"); //dictIndexs.Add("R", "17"); //dictIndexs.Add("S", "18"); //dictIndexs.Add("T", "19"); //dictIndexs.Add("U", "20"); //dictIndexs.Add("V", "21"); //dictIndexs.Add("W", "22"); //dictIndexs.Add("X", "23"); //dictIndexs.Add("Y", "24"); //dictIndexs.Add("Z", "25"); Dictionary <string, Int32> dictIndexs = new Dictionary <string, Int32>(); dictIndexs.Add("A", 0); dictIndexs.Add("B", 1); dictIndexs.Add("C", 2); dictIndexs.Add("D", 3); dictIndexs.Add("E", 4); dictIndexs.Add("F", 5); dictIndexs.Add("G", 6); dictIndexs.Add("H", 7); dictIndexs.Add("I", 8); dictIndexs.Add("J", 9); dictIndexs.Add("K", 10); dictIndexs.Add("L", 11); dictIndexs.Add("M", 12); dictIndexs.Add("N", 13); dictIndexs.Add("O", 14); dictIndexs.Add("P", 15); dictIndexs.Add("Q", 16); dictIndexs.Add("R", 17); dictIndexs.Add("S", 18); dictIndexs.Add("T", 19); dictIndexs.Add("U", 20); dictIndexs.Add("V", 21); dictIndexs.Add("W", 22); dictIndexs.Add("X", 23); dictIndexs.Add("Y", 24); dictIndexs.Add("Z", 25); //excel读取开始行号 int beginRow = Convert.ToInt32(ImportConfig.STARTROW.GetValueOrDefault(2)); //excel读取结束行号 int endRow = Convert.ToInt32(ImportConfig.ENDROW.GetValueOrDefault(2)); //if (endRow > rowCount) // endRow = rowCount; int i = 1; System.Text.UTF8Encoding code = new UTF8Encoding(); using (StreamReader sr = new StreamReader(path, Encoding.GetEncoding("gb2312"))) { string line; // Read and display lines from the file until the end of // the file is reached. //while ((line = sr.ReadLine()) != null) while (!string.IsNullOrEmpty(line = sr.ReadLine())) { string[] lineTmp = line.Split(','); if (i >= beginRow && i <= endRow) { //创建一个实列 Type type = EntityInstance.GetType(); object entity = Activator.CreateInstance(type); //得到excel二维数组当前行中A列的值 //r = (Range)workSheet.Cells[i, "A"]; //string value = r.Text.ToString().Trim(); #region 实例化对像 foreach (T_HR_IMPORTSETDETAIL detail in ImportConfig.T_HR_IMPORTSETDETAIL) { if (!string.IsNullOrEmpty(detail.EXECELCOLUMN)) { //r = (Range)workSheet.Cells[i, detail.EXECELCOLUMN]; ////r = (Range)workSheet.Cells[i, "D"]; //string value = r.Text.ToString().Trim(); int index = 0; //把列名拆成字符数组 char[] tmps = detail.EXECELCOLUMN.ToUpper().ToCharArray(); if (tmps.Length == 1) { //列名是一个字母 直接从字典查出列对应的索引 index = dictIndexs[tmps[0].ToString()]; } else { //列名是双字符 计算出列对应的索引 index = (dictIndexs[tmps[0].ToString()] + 1) * 26 + (dictIndexs[tmps[1].ToString()]); } // int index = Convert.ToInt32(dictIndexs[detail.EXECELCOLUMN.ToUpper()]); string value = lineTmp[index].Trim(); PropertyInfo prop = type.GetProperty(detail.ENTITYCOLUMNCODE); if (prop != null) { if (!string.IsNullOrEmpty(value)) { if (prop.PropertyType.BaseType == typeof(System.ValueType)) { if (prop.PropertyType.ToString().Contains(typeof(DateTime).ToString())) { prop.SetValue(entity, Convert.ToDateTime(value), null); } else { decimal tmpValue; decimal.TryParse(value, out tmpValue); prop.SetValue(entity, tmpValue, null); } } else { prop.SetValue(entity, value, null); } } } } } #endregion PropertyInfo prop1 = type.GetProperty("PENSIONDETAILID"); prop1.SetValue(entity, Guid.NewGuid().ToString(), null); PropertyInfo prop2 = type.GetProperty("OWNERCOMPANYID"); prop2.SetValue(entity, paras["OWNERCOMPANYID"], null); PropertyInfo prop3 = type.GetProperty("OWNERDEPARTMENTID"); prop3.SetValue(entity, paras["OWNERDEPARTMENTID"], null); PropertyInfo prop4 = type.GetProperty("OWNERPOSTID"); prop4.SetValue(entity, paras["OWNERPOSTID"], null); PropertyInfo prop5 = type.GetProperty("OWNERID"); prop5.SetValue(entity, paras["OWNERID"], null); PropertyInfo prop6 = type.GetProperty("CREATEUSERID"); prop6.SetValue(entity, paras["CREATEUSERID"], null); PropertyInfo prop7 = type.GetProperty("PENSIONYEAR"); prop7.SetValue(entity, Convert.ToDecimal(paras["YEAR"]), null); PropertyInfo prop8 = type.GetProperty("PENSIONMOTH"); prop8.SetValue(entity, Convert.ToDecimal(paras["MONTH"]), null); PropertyInfo prop9 = type.GetProperty("CREATEDATE"); prop9.SetValue(entity, System.DateTime.Now, null); T_HR_PENSIONDETAIL tmpDetail = entity as T_HR_PENSIONDETAIL; string idnumber = string.Empty; if (string.IsNullOrEmpty(tmpDetail.IDNUMBER)) { idnumber = "Null0"; } else { idnumber = tmpDetail.IDNUMBER.ToString(); } //根据身份证号 查询员工 如果没有找到就不插入此条记录 var employee = from e in dal.GetObjects <T_HR_EMPLOYEE>() where e.IDNUMBER == idnumber && e.EDITSTATE == "1" select e; if (employee.Count() > 0) { //删除旧的记录 var oldDetail = from c in dal.GetObjects <T_HR_PENSIONDETAIL>() where c.PENSIONMOTH == tmpDetail.PENSIONMOTH && c.PENSIONYEAR == tmpDetail.PENSIONYEAR && c.IDNUMBER == idnumber select c; if (oldDetail.Count() > 0) { dal.DeleteFromContext(oldDetail.FirstOrDefault()); } T_HR_EMPLOYEE entCurEmp = employee.FirstOrDefault(); if (tmpDetail.EMPLOYEEID != entCurEmp.EMPLOYEEID) { tmpDetail.EMPLOYEEID = entCurEmp.EMPLOYEEID; entity = tmpDetail; } //插入数据到数据库 dal.AddToContext(entity); // dal.Add(entity); } else { Tracer.Debug("PensionImport:" + i.ToString() + "行,没有员工身份证为此号码:" + idnumber); } } i++; } } dal.SaveContextChanges(); dal.CommitTransaction(); CommDal <T_HR_PENSIONDETAIL> cdal = new CommDal <T_HR_PENSIONDETAIL>(); // string strSql = @" update t_hr_pensiondetail a set a.pensionmasterid = (select pensionmasterid from t_hr_pensionmaster b where a.computerno // = b.computerno),a.employeeid= (select employeeid from t_hr_pensionmaster b where a.computerno = b.computerno) // ,a.CARDID= (select CARDID from t_hr_pensionmaster b where a.computerno = b.computerno)"; //string strSql = @" update t_hr_pensiondetail a set a.employeeid= (select employeeid from t_hr_employee b where a.idnumber = b.IDNUMBER)"; //string strSql = " update t_hr_pensiondetail a set a.pensionmasterid = (select pensionmasterid from t_hr_pensionmaster b where a.computerno" // +"= b.computerno and b.ownercompanyid='" + paras["OWNERCOMPANYID"]+"'),a.employeeid= (select employeeid from t_hr_pensionmaster b where a.computerno = b.computerno and b.ownercompanyid='" + paras["OWNERCOMPANYID"]+"')" // +" ,a.CARDID= (select CARDID from t_hr_pensionmaster b where a.computerno = b.computerno b.ownercompanyid='" + paras["OWNERCOMPANYID"]+"')" // +" ,a.OWNERID=(select employeeid from t_hr_pensionmaster b where a.computerno = b.computerno b.ownercompanyid='" + paras["OWNERCOMPANYID"]+"')" // +" ,a.OWNERPOSTID=(select c.OWNERPOSTID from t_hr_pensionmaster b ,t_hr_employee c where a.computerno = b.computerno and b.employeeid= c.employeeid + b.ownercompanyid='" + paras["OWNERCOMPANYID"]+"') " // +" ,a.OWNERDEPARTMENTID=(select c.OWNERDEPARTMENTID from t_hr_pensionmaster b ,t_hr_employee c where a.computerno = b.computerno and b.employeeid= c.employeeid b.ownercompanyid='" + paras["OWNERCOMPANYID"]+"')" // +" ,a.OWNERCOMPANYID=(select c.OWNERCOMPANYID from t_hr_pensionmaster b ,t_hr_employee c where a.computerno = b.computerno and b.employeeid= c.employeeid b.ownercompanyid='" + paras["OWNERCOMPANYID"]+"')" // +" where a.PENSIONYEAR='" + paras["YEAR"] + "' and a.PENSIONMOTH='" + paras["MONTH"] + "'"; //cdal.ExecuteCustomerSql(strSql); //strSql = "update t_hr_pensiondetail a set a.pensionmasterid = (select pensionmasterid from t_hr_pensionmaster b where a.employeeid = b.employeeid),a.cardid =(select b.cardid from t_hr_pensionmaster b where a.employeeid = b.employeeid)"; string strSql = " update t_hr_pensiondetail a set a.pensionmasterid = (select pensionmasterid from t_hr_pensionmaster b,t_hr_employee c where a.IDNUMBER= c.IDNUMBER and b.employeeid=c.employeeid and b.editstate=1)" + " ,a.employeeid= (select employeeid from t_hr_employee c where a.IDNUMBER = c.IDNUMBER )" + " ,a.CARDID= (select CARDID from t_hr_pensionmaster b ,t_hr_employee c where a.IDNUMBER = c.IDNUMBER and b.employeeid=c.employeeid and b.editstate=1)" + " ,a.OWNERID=(select employeeid from t_hr_employee c where a.IDNUMBER = c.IDNUMBER )" + " ,a.OWNERPOSTID=(select c.OWNERPOSTID from t_hr_employee c where a.IDNUMBER= c.IDNUMBER ) " + " ,a.OWNERDEPARTMENTID=(select c.OWNERDEPARTMENTID from t_hr_employee c where a.IDNUMBER= c.IDNUMBER )" + " ,a.OWNERCOMPANYID=(select c.OWNERCOMPANYID from t_hr_employee c where a.IDNUMBER = c.IDNUMBER )" + " where a.PENSIONYEAR='" + paras["YEAR"] + "' and a.PENSIONMOTH='" + paras["MONTH"] + "' and a.CREATEUSERID='" + paras["CREATEUSERID"] + "'"; cdal.ExecuteCustomerSql(strSql); return(true); } catch (Exception ex) { //TODO: 写成导入日志 //throw ex; Tracer.Debug("My error" + ex.Message); dal.RollbackTransaction(); return(false); } finally { //关闭当前的excel进程 //exc.Application.Workbooks.Close(); //exc.Quit(); //System.Runtime.InteropServices.Marshal.ReleaseComObject(exc); //IntPtr t = new IntPtr(exc.Hwnd); //exc = null; //int kid = 0; //GetWindowThreadProcessId(t, out kid); //System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(kid); //p.Kill(); } }
public List<T_HR_PENSIONDETAIL> ReadExcelDataFirst() { string StrFile = FileName; List<T_HR_PENSIONDETAIL> ListDetails = new List<T_HR_PENSIONDETAIL>(); try { SMT.Foundation.Core.BaseDAL dal = new SMT.Foundation.Core.BaseDAL(); Dictionary<string, Int32> dictIndexs = new Dictionary<string, Int32>(); ReadExcelColumn(dictIndexs); //excel读取开始行号 int beginRow = Convert.ToInt32(ImportConfig.STARTROW.GetValueOrDefault(2)); //excel读取结束行号 int endRow = Convert.ToInt32(ImportConfig.ENDROW.GetValueOrDefault(2)); int i = 1; System.Text.UTF8Encoding code = new UTF8Encoding(); using (StreamReader sr = new StreamReader(StrFile, Encoding.GetEncoding("gb2312"))) { string line; // Read and display lines from the file until the end of // the file is reached. //while ((line = sr.ReadLine()) != null) while (!string.IsNullOrEmpty(line = sr.ReadLine())) { string[] lineTmp = line.Split(','); if (i >= beginRow && i <= endRow) { //创建一个实列 Type type = EntityInstance.GetType(); object entity = Activator.CreateInstance(type); //得到excel二维数组当前行中A列的值 //r = (Range)workSheet.Cells[i, "A"]; //string value = r.Text.ToString().Trim(); #region 实例化对像 foreach (T_HR_IMPORTSETDETAIL detail in ImportConfig.T_HR_IMPORTSETDETAIL) { if (!string.IsNullOrEmpty(detail.EXECELCOLUMN)) { //r = (Range)workSheet.Cells[i, detail.EXECELCOLUMN]; ////r = (Range)workSheet.Cells[i, "D"]; //string value = r.Text.ToString().Trim(); int index = 0; //把列名拆成字符数组 char[] tmps = detail.EXECELCOLUMN.ToUpper().ToCharArray(); if (tmps.Length == 1) { //列名是一个字母 直接从字典查出列对应的索引 index = dictIndexs[tmps[0].ToString()]; } else { //列名是双字符 计算出列对应的索引 index = (dictIndexs[tmps[0].ToString()] + 1) * 26 + (dictIndexs[tmps[1].ToString()]); } // int index = Convert.ToInt32(dictIndexs[detail.EXECELCOLUMN.ToUpper()]); string value = lineTmp[index].Trim(); PropertyInfo prop = type.GetProperty(detail.ENTITYCOLUMNCODE); if (prop != null) { if (!string.IsNullOrEmpty(value)) { if (prop.PropertyType.BaseType == typeof(System.ValueType)) { if (prop.PropertyType.ToString().Contains(typeof(DateTime).ToString())) { prop.SetValue(entity, Convert.ToDateTime(value), null); } else { decimal tmpValue; decimal.TryParse(value, out tmpValue); prop.SetValue(entity, tmpValue, null); } } else { prop.SetValue(entity, value, null); } } } } } #endregion PropertyInfo prop1 = type.GetProperty("PENSIONDETAILID"); prop1.SetValue(entity, Guid.NewGuid().ToString(), null); PropertyInfo prop2 = type.GetProperty("OWNERCOMPANYID"); prop2.SetValue(entity, paras["OWNERCOMPANYID"], null); PropertyInfo prop3 = type.GetProperty("OWNERDEPARTMENTID"); prop3.SetValue(entity, paras["OWNERDEPARTMENTID"], null); PropertyInfo prop4 = type.GetProperty("OWNERPOSTID"); prop4.SetValue(entity, paras["OWNERPOSTID"], null); PropertyInfo prop5 = type.GetProperty("OWNERID"); prop5.SetValue(entity, paras["OWNERID"], null); PropertyInfo prop6 = type.GetProperty("CREATEUSERID"); prop6.SetValue(entity, paras["CREATEUSERID"], null); PropertyInfo prop7 = type.GetProperty("PENSIONYEAR"); prop7.SetValue(entity, Convert.ToDecimal(paras["YEAR"]), null); PropertyInfo prop8 = type.GetProperty("PENSIONMOTH"); prop8.SetValue(entity, Convert.ToDecimal(paras["MONTH"]), null); PropertyInfo prop9 = type.GetProperty("CREATEDATE"); prop9.SetValue(entity, System.DateTime.Now, null); T_HR_PENSIONDETAIL tmpDetail = entity as T_HR_PENSIONDETAIL; string idnumber = string.Empty; if (string.IsNullOrEmpty(tmpDetail.IDNUMBER)) { idnumber = "Null0"; } else { idnumber = tmpDetail.IDNUMBER.ToString(); } //查询员工身份证号和正式员工是否存在 var employee = from e in dal.GetObjects<T_HR_EMPLOYEE>() where e.IDNUMBER == idnumber && e.EDITSTATE == "1" select e; if (employee.Count() > 0) { T_HR_EMPLOYEE entCurEmp = employee.FirstOrDefault(); if (tmpDetail.EMPLOYEEID != entCurEmp.EMPLOYEEID) { tmpDetail.EMPLOYEEID = entCurEmp.EMPLOYEEID; } } else { tmpDetail.IDNUMBER = idnumber +"此身份证号无效或员工已离职"; //Tracer.Debug("PensionImport:" + i.ToString() + "行,没有员工身份证为此号码:" + idnumber); } ListDetails.Add(tmpDetail); } i++; } } return ListDetails; } catch (Exception ex) { //TODO: 写成导入日志 //throw ex; Tracer.Debug("My error" + ex.Message); return null; } }
public List <T_HR_PENSIONDETAIL> ReadExcelDataFirst() { string StrFile = FileName; List <T_HR_PENSIONDETAIL> ListDetails = new List <T_HR_PENSIONDETAIL>(); try { SMT.Foundation.Core.BaseDAL dal = new SMT.Foundation.Core.BaseDAL(); Dictionary <string, Int32> dictIndexs = new Dictionary <string, Int32>(); ReadExcelColumn(dictIndexs); //excel读取开始行号 int beginRow = Convert.ToInt32(ImportConfig.STARTROW.GetValueOrDefault(2)); //excel读取结束行号 int endRow = Convert.ToInt32(ImportConfig.ENDROW.GetValueOrDefault(2)); int i = 1; System.Text.UTF8Encoding code = new UTF8Encoding(); using (StreamReader sr = new StreamReader(StrFile, Encoding.GetEncoding("gb2312"))) { string line; // Read and display lines from the file until the end of // the file is reached. //while ((line = sr.ReadLine()) != null) while (!string.IsNullOrEmpty(line = sr.ReadLine())) { string[] lineTmp = line.Split(','); if (i >= beginRow && i <= endRow) { //创建一个实列 Type type = EntityInstance.GetType(); object entity = Activator.CreateInstance(type); //得到excel二维数组当前行中A列的值 //r = (Range)workSheet.Cells[i, "A"]; //string value = r.Text.ToString().Trim(); #region 实例化对像 foreach (T_HR_IMPORTSETDETAIL detail in ImportConfig.T_HR_IMPORTSETDETAIL) { if (!string.IsNullOrEmpty(detail.EXECELCOLUMN)) { //r = (Range)workSheet.Cells[i, detail.EXECELCOLUMN]; ////r = (Range)workSheet.Cells[i, "D"]; //string value = r.Text.ToString().Trim(); int index = 0; //把列名拆成字符数组 char[] tmps = detail.EXECELCOLUMN.ToUpper().ToCharArray(); if (tmps.Length == 1) { //列名是一个字母 直接从字典查出列对应的索引 index = dictIndexs[tmps[0].ToString()]; } else { //列名是双字符 计算出列对应的索引 index = (dictIndexs[tmps[0].ToString()] + 1) * 26 + (dictIndexs[tmps[1].ToString()]); } // int index = Convert.ToInt32(dictIndexs[detail.EXECELCOLUMN.ToUpper()]); string value = lineTmp[index].Trim(); PropertyInfo prop = type.GetProperty(detail.ENTITYCOLUMNCODE); if (prop != null) { if (!string.IsNullOrEmpty(value)) { if (prop.PropertyType.BaseType == typeof(System.ValueType)) { if (prop.PropertyType.ToString().Contains(typeof(DateTime).ToString())) { prop.SetValue(entity, Convert.ToDateTime(value), null); } else { decimal tmpValue; decimal.TryParse(value, out tmpValue); prop.SetValue(entity, tmpValue, null); } } else { prop.SetValue(entity, value, null); } } } } } #endregion PropertyInfo prop1 = type.GetProperty("PENSIONDETAILID"); prop1.SetValue(entity, Guid.NewGuid().ToString(), null); PropertyInfo prop2 = type.GetProperty("OWNERCOMPANYID"); prop2.SetValue(entity, paras["OWNERCOMPANYID"], null); PropertyInfo prop3 = type.GetProperty("OWNERDEPARTMENTID"); prop3.SetValue(entity, paras["OWNERDEPARTMENTID"], null); PropertyInfo prop4 = type.GetProperty("OWNERPOSTID"); prop4.SetValue(entity, paras["OWNERPOSTID"], null); PropertyInfo prop5 = type.GetProperty("OWNERID"); prop5.SetValue(entity, paras["OWNERID"], null); PropertyInfo prop6 = type.GetProperty("CREATEUSERID"); prop6.SetValue(entity, paras["CREATEUSERID"], null); PropertyInfo prop7 = type.GetProperty("PENSIONYEAR"); prop7.SetValue(entity, Convert.ToDecimal(paras["YEAR"]), null); PropertyInfo prop8 = type.GetProperty("PENSIONMOTH"); prop8.SetValue(entity, Convert.ToDecimal(paras["MONTH"]), null); PropertyInfo prop9 = type.GetProperty("CREATEDATE"); prop9.SetValue(entity, System.DateTime.Now, null); T_HR_PENSIONDETAIL tmpDetail = entity as T_HR_PENSIONDETAIL; string idnumber = string.Empty; if (string.IsNullOrEmpty(tmpDetail.IDNUMBER)) { idnumber = "Null0"; } else { idnumber = tmpDetail.IDNUMBER.ToString(); } //查询员工身份证号和正式员工是否存在 var employee = from e in dal.GetObjects <T_HR_EMPLOYEE>() where e.IDNUMBER == idnumber && e.EDITSTATE == "1" select e; if (employee.Count() > 0) { T_HR_EMPLOYEE entCurEmp = employee.FirstOrDefault(); if (tmpDetail.EMPLOYEEID != entCurEmp.EMPLOYEEID) { tmpDetail.EMPLOYEEID = entCurEmp.EMPLOYEEID; } } else { tmpDetail.IDNUMBER = idnumber + "此身份证号无效或员工已离职"; //Tracer.Debug("PensionImport:" + i.ToString() + "行,没有员工身份证为此号码:" + idnumber); } ListDetails.Add(tmpDetail); } i++; } } return(ListDetails); } catch (Exception ex) { //TODO: 写成导入日志 //throw ex; Tracer.Debug("My error" + ex.Message); return(null); } }