//xi public static void ExportExcelWeekMan(JH_weekmant year, IList<JH_weekman> nrList) { ExcelAccess ex = new ExcelAccess(); SaveFileDialog saveFileDialog1 = new SaveFileDialog(); string fname = Application.StartupPath + "\\00记录模板\\员工工作写实.xls"; ex.Open(fname); int row = 1; int col = 1; int pageRowNum = 7; //加页 int pageindex = 1; if (pageindex < Ecommonjh.GetPagecount(nrList.Count, pageRowNum)) { pageindex = Ecommonjh.GetPagecount(nrList.Count, pageRowNum); } for (int j = 1; j <= pageindex; j++) { ex.ActiveSheet(j);//xjq change 2013-03-01; if (j > 1) { ex.CopySheet(1, 1); } string strWeek = "";//xjq change 2013-03-01; string strYearMonth = ""; int pos = year.标题.IndexOf("第"); if (pos >= 0) { strWeek = year.标题.Substring(pos, 3); strYearMonth = year.标题.Substring(0, pos); } ex.SetCellValue(year.标题.Replace("第", " (第 ").Replace("周", " 周)").Replace("计划", ""), 3, 2); ex.SetCellValue(year.姓名, 4, 2); ex.SetCellValue(year.职务, 4, 5); ex.SetCellValue(year.考核结果, 4, 8); string sdate = string.Format("{0} - {1}", year.开始日期.ToString("yyyy年MM月dd日"), year.结束日期.ToString("yyyy年MM月dd日")); ex.SetCellValue(sdate, 4, 9); ; } //ex.ActiveSheet(1); //string strWeek = ""; //string strYearMonth = ""; //int pos = year.标题.IndexOf("第"); //if (pos >= 0) { // strWeek = year.标题.Substring(pos, 3); // strYearMonth = year.标题.Substring(0, pos); //} //ex.SetCellValue(year.标题.Replace("第"," (第 ").Replace("周"," 周)").Replace("计划",""), 3, 2); //ex.SetCellValue(year.姓名, 4, 2); //ex.SetCellValue(year.职务, 4, 5); //ex.SetCellValue(year.考核结果, 4, 8); //string sdate = string.Format("{0} - {1}", year.开始日期.ToString("yyyy年MM月dd日"), year.结束日期.ToString("yyyy年MM月dd日")); //ex.SetCellValue(sdate, 4, 9); ; //ex.SetCellValue(year.开始日期.ToString("yyyy-MM-dd"), 3, 10); //ex.SetCellValue(year.结束日期.ToString("yyyy-MM-dd"), 3, 12); row += 6; for (int j = 1; j <= pageindex; j++) { ex.ActiveSheet(j); //ex.ReNameWorkSheet(j, "Sheet" + (j)); int prepageindex = j - 1; //主题 int starow = prepageindex * pageRowNum + 1; int endrow = j * pageRowNum; if (nrList.Count > endrow) { for (int i = 0; i < pageRowNum; i++) { ex.SetCellValue(nrList[starow - 1 + i].计划项目, row + i, 3); ex.SetCellValue(nrList[starow - 1 + i].预计时间.ToString("yyyy-MM-dd"), row + i, 5); ex.SetCellValue(nrList[starow - 1 + i].协作人员, row + i, 6); ex.SetCellValue(nrList[starow - 1 + i].工作内容, row + i, 7); ex.SetCellValue(nrList[starow - 1 + i].完成标记, row + i, 8); ex.SetCellValue(nrList[starow - 1 + i].未完成原因, row + i, 9); if (nrList[starow - 1 + i].完成时间.Year != 1900) ex.SetCellValue(nrList[starow - 1 + i].完成时间.ToString("yyyy-MM-dd"), row + i, 10); ex.SetCellValue(nrList[starow - 1 + i].总结提升, row + i, 12); ex.SetCellValue(nrList[starow - 1 + i].评语考核人, row + i, 11); } } else if (nrList.Count <= endrow && nrList.Count >= starow) { for (int i = 0; i < nrList.Count - starow + 1; i++) { ex.SetCellValue(nrList[starow - 1 + i].计划项目, row + i, 3); ex.SetCellValue(nrList[starow - 1 + i].预计时间.ToString("yyyy-MM-dd"), row + i, 5); ex.SetCellValue(nrList[starow - 1 + i].协作人员, row + i, 6); ex.SetCellValue(nrList[starow - 1 + i].工作内容, row + i, 7); ex.SetCellValue(nrList[starow - 1 + i].评语考核人, row + i, 11); ex.SetCellValue(nrList[starow - 1 + i].完成标记, row + i, 8); ex.SetCellValue(nrList[starow - 1 + i].未完成原因, row + i, 9); if (nrList[starow - 1 + i].完成时间.Year != 1900) ex.SetCellValue(nrList[starow - 1 + i].完成时间.ToString("yyyy-MM-dd"), row + i, 10); ex.SetCellValue(nrList[starow - 1 + i].总结提升, row + i, 12); } } } DateTime dt2 = year.结束日期.AddDays(1); JH_weekmant year2 = ClientHelper.PlatformSqlMap.GetOne<JH_weekmant>(string.Format(" where 姓名='{0}' and 开始日期='{1}' and 单位代码='{2}' ", year.姓名, year.结束日期.AddDays(1), year.单位代码)); if (year2 != null) { IList<JH_weekman> nrList2 = ClientHelper.PlatformSqlMap.GetList<JH_weekman>(string.Format("where parentid='{0}'", year2.ID)); row += 6; for (int j = 1; j <= pageindex; j++) { ex.ActiveSheet(j); //ex.ReNameWorkSheet(j, "Sheet" + (j)); int prepageindex = j - 1; //主题 int starow = prepageindex * pageRowNum + 1; int endrow = j * pageRowNum; if (nrList2.Count > endrow) { for (int i = 0; i < pageRowNum; i++) { ex.SetCellValue(nrList2[starow - 1 + i].计划项目, row + i, 3); ex.SetCellValue(nrList2[starow - 1 + i].预计时间.ToString("yyyy-MM-dd"), row + i, 5); ex.SetCellValue(nrList2[starow - 1 + i].协作人员, row + i, 6); ex.SetCellValue(nrList2[starow - 1 + i].工作内容, row + i, 7); //ex.SetCellValue(nrList2[starow - 1 + i].完成标记, row + i, 8); //ex.SetCellValue(nrList2[starow - 1 + i].未完成原因, row + i, 9); //if (nrList[starow - 1 + i].完成时间.Year != 1900) // ex.SetCellValue(nrList2[starow - 1 + i].完成时间.ToString("yyyy-MM-dd"), row + i, 10); //ex.SetCellValue(nrList2[starow - 1 + i].总结提升, row + i, 12); //ex.SetCellValue(nrList2[starow - 1 + i].评语考核人, row + i, 11); } } else if (nrList2.Count <= endrow && nrList2.Count >= starow) { for (int i = 0; i < nrList.Count - starow + 1; i++) { ex.SetCellValue(nrList2[starow - 1 + i].计划项目, row + i, 3); ex.SetCellValue(nrList2[starow - 1 + i].预计时间.ToString("yyyy-MM-dd"), row + i, 5); ex.SetCellValue(nrList2[starow - 1 + i].协作人员, row + i, 6); ex.SetCellValue(nrList2[starow - 1 + i].工作内容, row + i, 7); //ex.SetCellValue(nrList2[starow - 1 + i].评语考核人, row + i, 11); //ex.SetCellValue(nrList2[starow - 1 + i].完成标记, row + i, 8); //ex.SetCellValue(nrList2[starow - 1 + i].未完成原因, row + i, 9); //if (nrList[starow - 1 + i].完成时间.Year != 1900) // ex.SetCellValue(nrList2[starow - 1 + i].完成时间.ToString("yyyy-MM-dd"), row + i, 10); //ex.SetCellValue(nrList2[starow - 1 + i].总结提升, row + i, 12); } } } } ex.ActiveSheet(1); ex.ShowExcel(); }
/// <summary> /// 员工周工作写实 /// </summary> /// <param name="year"></param> /// <param name="nrList"></param> public static void ExportExcelWeekMan1(JH_weekmant year, IList<JH_weekman> nrList) { ExcelAccess ex = new ExcelAccess(); SaveFileDialog saveFileDialog1 = new SaveFileDialog(); string fname = Application.StartupPath + "\\00记录模板\\员工周工作写实.xls"; ex.Open(fname); //加页 ex.ActiveSheet(1); string Title = year.标题.Substring(0, 11) + "员工工作写实"; ex.SetCellValue(Title, 1, 1); ex.SetCellValue(year.姓名, 3, 2); ex.SetCellValue(year.考核结果, 3, 9); int count = 7; if (nrList.Count < 7) count = nrList.Count; for (int i = 0; i < count; i++) { ex.SetCellValue(nrList[i].单位名称, 5 + i, 1); ex.SetCellValue(nrList[i].计划项目, 5 + i, 2); ex.SetCellValue(nrList[i].工作内容, 5 + i, 3); ex.SetCellValue(nrList[i].协作人员, 5 + i, 4); ex.SetCellValue(nrList[i].完成标记, 5 + i, 5); if (nrList[i].完成时间.Year > Convert.ToDateTime("2000-10-10").Year) ex.SetCellValue(nrList[i].完成时间.ToShortDateString(), 5 + i, 6); ex.SetCellValue(nrList[i].未完成原因, 5 + i, 7); ex.SetCellValue(nrList[i].总结提升, 5 + i, 8); } ex.SetCellValue(year.考核人, 12, 9); ex.ActiveSheet(1); ex.ShowExcel(); }
private void RefreshGrid(string where) { if (this.barEditItem3.EditValue == null) return; if (string.IsNullOrEmpty(barEditItem3.EditValue.ToString())) return; this.gridControl1.DataSource = null; IList datalist = Client.ClientHelper.PlatformSqlMap.GetList("Select", where); DataTable dt = DataConvert.HashTablesToDataTable(datalist); if (dt == null) return; List<JH_weekmant> exportList = new List<JH_weekmant>(); List<string> weeknameList = new List<string>(); foreach (DataRow rw in dt.Rows) { if (!weeknameList.Contains(rw["姓名"].ToString())) { weeknameList.Add(rw["姓名"].ToString()); } } foreach (string name in weeknameList) { JH_weekmant newWeekMant = new JH_weekmant(); int score = 0; string detail = ""; foreach (DataRow rw in dt.Rows) { if (rw["姓名"].ToString() == name) { detail = detail + rw["考核结果"] + "|"; score = score + GetScore(rw["考核结果"].ToString()); if (string.IsNullOrEmpty(newWeekMant.单位名称)) newWeekMant.单位名称 = rw["单位名称"].ToString(); if (string.IsNullOrEmpty(newWeekMant.职务)) newWeekMant.职务 = rw["职务"].ToString(); if (string.IsNullOrEmpty(newWeekMant.考核人)) newWeekMant.考核人 = rw["考核人"].ToString(); if (string.IsNullOrEmpty(newWeekMant.考核时间)) newWeekMant.考核时间 = Convert.ToDateTime(rw["考核时间"]).ToShortDateString(); } } if (isYear) { gridView1.Columns["考核结果"].Visible = false; } else { newWeekMant.考核结果 = detail; } newWeekMant.c3 = score.ToString(); newWeekMant.姓名 = name; exportList.Add(newWeekMant); } gridControl1.DataSource = exportList; }