/// <summary> /// 创建任务明细表中没有列出的员工的绩效内存数据 /// </summary> /// <param name="persons">汇总表中的所有员工</param> /// <param name="pps">从明细表生成的绩效内存数据</param> /// <param name="department">部门</param> /// <param name="month">月份</param> /// <returns>个人绩效</returns> public static List <PersonalPerformance> CreatePersonalPerformance(List <Person> persons, List <PersonalPerformance> pps, string department, int month) { List <PersonalPerformance> personPS = new List <PersonalPerformance>(); bool bFind = false; //找出还没有创建个人绩效内存数据的员工 foreach (Person p in persons) { bFind = false; foreach (var pp in pps) { if (pp.Person.Name == p.Name && pp.Person.Number == p.Number) { bFind = true; break; } } if (!bFind) { //创建绩效内存数据 PersonalPerformance personperformance = new PersonalPerformance(department, month, p); personPS.Add(personperformance); } } return(personPS); }
/// <summary> /// 创建个人绩效单列表 /// </summary> /// <param name="dt">明细表生产的内存数据</param> /// <param name="persons">统计表中的所有员工</param> /// <param name="department">部门</param> /// <param name="month">月分</param> /// <returns>个人绩效单列表</returns> public static List <PersonalPerformance> CreatePersonalPerformance(DataTable dt, List <Person> persons, string department, int month) { string name = ""; string number = ""; int index = 0; PersonalPerformance pp = null; List <PersonalPerformance> pps = new List <PersonalPerformance>(); int rowCount = dt.Rows.Count; try { for (int i = 0; i < rowCount; i++) { //归属条线 if (dt.Rows[i][0] != null && !string.IsNullOrEmpty(dt.Rows[i][0].ToString())) { continue; } //个人绩效 if (dt.Rows[i][1] != null && !string.IsNullOrEmpty(dt.Rows[i][1].ToString())) { name = dt.Rows[i][1].ToString(); //index = name.IndexOf(":"); //name = name.Substring(index + 1).Trim(); //number = dt.Rows[i + 1]["工号"].ToString(); Person person = GetPerson(persons, name, number); if (person == null) { LogHelper.WriteWarnInfo(string.Format("在统计表中没有获取到{0}({1})的信息,已取消为其生成个人绩效单!", name, number)); continue; } pp = new PersonalPerformance(department, month, person); pps.Add(pp); continue; } else { //添加任务信息 string taskName = ""; if (dt.Rows[i]["任务说明"] != null) { taskName = dt.Rows[i]["任务说明"].ToString(); } int taskScore = 0; if (dt.Rows[i]["任务得分"] != null) { int.TryParse(dt.Rows[i]["任务得分"].ToString(), out taskScore); } CTask task = new CTask(taskName, taskScore); pp.Tasks.Add(task); } } return(pps); } catch (Exception) { throw new Exception("创建个人绩效内存数据出错!"); } }
/// <summary> /// 创建个人绩效单列表 /// </summary> /// <param name="dt"></param> /// <returns></returns> public static List <PersonalPerformance> CreatePersonalPerformance(DataTable dt, string department, int month) { string name = ""; string number = ""; int index = 0; PersonalPerformance pp = null; List <PersonalPerformance> pps = new List <PersonalPerformance>(); int rowCount = dt.Rows.Count; try { for (int i = 0; i < rowCount; i++) { //归属条线 if (dt.Rows[i][0] != null && !string.IsNullOrEmpty(dt.Rows[i][0].ToString())) { continue; } //个人绩效 if (dt.Rows[i][1] != null && !string.IsNullOrEmpty(dt.Rows[i][1].ToString())) { name = dt.Rows[i][1].ToString(); index = name.IndexOf(":"); name = name.Substring(index + 1).Trim(); number = dt.Rows[i + 1]["工号"].ToString(); pp = new PersonalPerformance(department, month, new Person(name, number)); pps.Add(pp); continue; } else { //添加任务信息 string taskName = ""; if (dt.Rows[i]["任务说明"] != null) { taskName = dt.Rows[i]["任务说明"].ToString(); } int taskScore = 0; if (dt.Rows[i]["任务得分"] != null) { taskScore = int.Parse(dt.Rows[i]["任务得分"].ToString()); } CTask task = new CTask(taskName, taskScore); pp.Tasks.Add(task); } } return(pps); } catch (Exception) { throw new Exception("创建个人绩效内存数据出错!"); } }
/// <summary> /// 创建Excel /// </summary> /// <param name="pperformance">个人绩效内存数据</param> /// <param name="path">附件所在路径</param> /// <param name="scores">排序后的分数列表</param> public static void Create(PersonalPerformance pperformance, string path, List<int> scores) { try { XSSFWorkbook workbook = new XSSFWorkbook(); CreateStyle(workbook); ISheet sheet = workbook.CreateSheet("Sheet1"); //设置列宽 sheet.SetColumnWidth(0, (int)(10 + 0.72) * 256); sheet.SetColumnWidth(1, (int)(15 + 0.72) * 256); sheet.SetColumnWidth(2, (int)(65 + 0.72) * 256); sheet.SetColumnWidth(3, (int)(10 + 0.72) * 256); sheet.SetColumnWidth(4, (int)(10 + 0.72) * 256); //创建行和列 //行一 IRow row = sheet.CreateRow(0); row.HeightInPoints = 30; ICell cell = row.CreateCell(0); XSSFRichTextString richText = new XSSFRichTextString(" 个人绩效对账单"); richText.ApplyFont(_normalFont); cell.CellStyle = _aquaCellStyle; cell.SetCellValue(richText); SetCellRangeAddress(sheet, 0, 0, 0, 1); cell = row.CreateCell(2); cell.CellStyle = _aquaCellStyle; cell = row.CreateCell(3); cell.CellStyle = _aquaCellStyle; cell.SetCellValue(pperformance.Department); SetCellRangeAddress(sheet, 0, 0, 3, 4); //行二 row = sheet.CreateRow(1); row.HeightInPoints = 90; cell = row.CreateCell(0); int totalScore = 0; pperformance.Tasks.ForEach(task => totalScore += task.Score); string value = string.Format(" {0} {1}月得分{2}", pperformance.Person.Name, pperformance.Month, totalScore); int startIndex = value.IndexOf('分') + 1; int endIndex = value.Length; richText = new XSSFRichTextString(value); richText.ApplyFont(startIndex, endIndex, _scoreFont); cell.CellStyle = _normalLeftCellStyle; cell.SetCellValue(richText); SetCellRangeAddress(sheet, 1, 1, 0, 4); //行三 row = sheet.CreateRow(2); row.HeightInPoints = 30; cell = row.CreateCell(0); int index = scores.FindIndex(s => s == totalScore); float winRate = ((float)index / (float)scores.Count) * 100f; //value = string.Format(" 你打败了{0}{1}%的成员", pperformance.Department, winRate.ToString("f0")); string str1 = string.Format(" 你打败了{0}", pperformance.Department); richText = new XSSFRichTextString(str1); richText.Append(string.Format("{0}%", winRate.ToString("f0")), _winRateFont as XSSFFont); richText.Append("的成员", _normalFont as XSSFFont); cell.SetCellValue(richText); cell.CellStyle = _aquaCellStyle; SetCellRangeAddress(sheet, 2, 2, 0, 4); //行四 row = sheet.CreateRow(3); row.HeightInPoints = 30; cell = row.CreateCell(0); cell.SetCellValue("序号"); cell.CellStyle = _greycellStyle; cell = row.CreateCell(1); cell.SetCellValue("任务名称"); cell.CellStyle = _greycellStyle; cell = row.CreateCell(2); SetCellRangeAddress(sheet, 3, 3, 1, 2); cell.CellStyle = _greycellStyle; cell = row.CreateCell(3); cell.SetCellValue("任务得分"); SetCellRangeAddress(sheet, 3, 3, 3, 4); cell.CellStyle = _greycellStyle; //添加任务 int i = 1; int rowIndex = 4; foreach (var task in pperformance.Tasks) { row = sheet.CreateRow(rowIndex); row.HeightInPoints = 30; cell = row.CreateCell(0); cell.SetCellValue(i); if (i % 2 == 0) { cell.CellStyle = _greycellStyle; } else { cell.CellStyle = _normalCenterCellStyle; } cell = row.CreateCell(1); cell.SetCellValue(task.Name); if (i % 2 == 0) { cell.CellStyle = _greycellStyle; } else { cell.CellStyle = _normalCenterCellStyle; } cell = row.CreateCell(2); SetCellRangeAddress(sheet, rowIndex, rowIndex, 1, 2); if (i % 2 == 0) { cell.CellStyle = _greycellStyle; } else { cell.CellStyle = _normalCenterCellStyle; } cell = row.CreateCell(3); cell.SetCellValue(task.Score); SetCellRangeAddress(sheet, rowIndex, rowIndex, 3, 4); if (i % 2 == 0) { cell.CellStyle = _greycellStyle; } else { cell.CellStyle = _normalCenterCellStyle; } i++; rowIndex++; } using (FileStream stream = File.OpenWrite(path)) { workbook.Write(stream); stream.Close(); } } catch (Exception) { throw new Exception("创建Excel出错!"); } }
/// <summary> /// 创建Excel /// </summary> /// <param name="pperformance">个人绩效内存数据</param> /// <param name="path">附件所在路径</param> /// <param name="scores">排序后的分数列表</param> public static void Create(PersonalPerformance pperformance, string path, List <int> scores) { try { XSSFWorkbook workbook = new XSSFWorkbook(); CreateStyle(workbook); ISheet sheet = workbook.CreateSheet("Sheet1"); //设置列宽 sheet.SetColumnWidth(0, (int)(10 + 0.72) * 256); sheet.SetColumnWidth(1, (int)(15 + 0.72) * 256); sheet.SetColumnWidth(2, (int)(65 + 0.72) * 256); sheet.SetColumnWidth(3, (int)(10 + 0.72) * 256); sheet.SetColumnWidth(4, (int)(10 + 0.72) * 256); //创建行和列 //行一 IRow row = sheet.CreateRow(0); row.HeightInPoints = 30; ICell cell = row.CreateCell(0); XSSFRichTextString richText = new XSSFRichTextString(" 个人绩效对账单"); richText.ApplyFont(_normalFont); cell.CellStyle = _aquaCellStyle; cell.SetCellValue(richText); SetCellRangeAddress(sheet, 0, 0, 0, 1); cell = row.CreateCell(2); cell.CellStyle = _aquaCellStyle; cell = row.CreateCell(3); cell.CellStyle = _aquaCellStyle; cell.SetCellValue(pperformance.Department); SetCellRangeAddress(sheet, 0, 0, 3, 4); //行二 row = sheet.CreateRow(1); row.HeightInPoints = 90; cell = row.CreateCell(0); int totalScore = 0; pperformance.Tasks.ForEach(task => totalScore += task.Score); string value = string.Format(" {0} {1}月得分{2}", pperformance.Person.Name, pperformance.Month, totalScore); int startIndex = value.IndexOf('分') + 1; int endIndex = value.Length; richText = new XSSFRichTextString(value); richText.ApplyFont(startIndex, endIndex, _scoreFont); cell.CellStyle = _normalLeftCellStyle; cell.SetCellValue(richText); SetCellRangeAddress(sheet, 1, 1, 0, 4); //行三 row = sheet.CreateRow(2); row.HeightInPoints = 30; cell = row.CreateCell(0); int index = scores.FindIndex(s => s == totalScore); float winRate = ((float)index / (float)scores.Count) * 100f; //value = string.Format(" 你打败了{0}{1}%的成员", pperformance.Department, winRate.ToString("f0")); string str1 = string.Format(" 你打败了{0}", pperformance.Department); richText = new XSSFRichTextString(str1); richText.Append(string.Format("{0}%", winRate.ToString("f0")), _winRateFont as XSSFFont); richText.Append("的成员", _normalFont as XSSFFont); cell.SetCellValue(richText); cell.CellStyle = _aquaCellStyle; SetCellRangeAddress(sheet, 2, 2, 0, 4); //行四 row = sheet.CreateRow(3); row.HeightInPoints = 30; cell = row.CreateCell(0); cell.SetCellValue("序号"); cell.CellStyle = _greycellStyle; cell = row.CreateCell(1); cell.SetCellValue("任务名称"); cell.CellStyle = _greycellStyle; cell = row.CreateCell(2); SetCellRangeAddress(sheet, 3, 3, 1, 2); cell.CellStyle = _greycellStyle; cell = row.CreateCell(3); cell.SetCellValue("任务得分"); SetCellRangeAddress(sheet, 3, 3, 3, 4); cell.CellStyle = _greycellStyle; //添加任务 int i = 1; int rowIndex = 4; foreach (var task in pperformance.Tasks) { row = sheet.CreateRow(rowIndex); row.HeightInPoints = 30; cell = row.CreateCell(0); cell.SetCellValue(i); if (i % 2 == 0) { cell.CellStyle = _greycellStyle; } else { cell.CellStyle = _normalCenterCellStyle; } cell = row.CreateCell(1); cell.SetCellValue(task.Name); if (i % 2 == 0) { cell.CellStyle = _greycellStyle; } else { cell.CellStyle = _normalCenterCellStyle; } cell = row.CreateCell(2); SetCellRangeAddress(sheet, rowIndex, rowIndex, 1, 2); if (i % 2 == 0) { cell.CellStyle = _greycellStyle; } else { cell.CellStyle = _normalCenterCellStyle; } cell = row.CreateCell(3); cell.SetCellValue(task.Score); SetCellRangeAddress(sheet, rowIndex, rowIndex, 3, 4); if (i % 2 == 0) { cell.CellStyle = _greycellStyle; } else { cell.CellStyle = _normalCenterCellStyle; } i++; rowIndex++; } using (FileStream stream = File.OpenWrite(path)) { workbook.Write(stream); stream.Close(); } } catch (Exception) { throw new Exception("创建Excel出错!"); } }
/// <summary> /// 创建Excel /// </summary> /// <param name="pperformance">个人绩效内存数据</param> /// <param name="path">附件所在路径</param> /// <param name="scores">排序后的分数列表</param> public static void Create(PersonalPerformance pperformance, string path, List<float> scores) { try { XSSFWorkbook workbook = new XSSFWorkbook(); CreateStyle(workbook); ISheet sheet = workbook.CreateSheet("Sheet1"); //设置列宽 sheet.SetColumnWidth(0, (int)(10 + 0.72) * 256); sheet.SetColumnWidth(1, (int)(15 + 0.72) * 256); sheet.SetColumnWidth(2, (int)(65 + 0.72) * 256); sheet.SetColumnWidth(3, (int)(10 + 0.72) * 256); sheet.SetColumnWidth(4, (int)(10 + 0.72) * 256); //创建行和列 //行一 IRow row = sheet.CreateRow(0); row.HeightInPoints = 30; ICell cell = row.CreateCell(0); XSSFRichTextString richText = new XSSFRichTextString(" 个人绩效对账单"); richText.ApplyFont(_normalFont); cell.CellStyle = _aquaCellStyle; cell.SetCellValue(richText); SetCellRangeAddress(sheet, 0, 0, 0, 1); cell = row.CreateCell(2); cell.CellStyle = _aquaCellStyle; cell = row.CreateCell(3); cell.CellStyle = _aquaCellStyle; cell.SetCellValue(pperformance.Department); SetCellRangeAddress(sheet, 0, 0, 3, 4); //行二 row = sheet.CreateRow(1); row.HeightInPoints = 90; cell = row.CreateCell(0); string g = string.Empty; float score = pperformance.Person.Score; if (!string.IsNullOrEmpty(pperformance.Person.Grade)) { string[] gs = pperformance.Person.Grade.Split('-'); g = gs[0]; } string str = string.Format(" {0} {1}月绩效等级{2},绩效分值{3}", pperformance.Person.Name, pperformance.Month, g, score); cell.CellStyle = _normalLeftCellStyle; cell.SetCellValue(str); SetCellRangeAddress(sheet, 1, 1, 0, 4); //行三 row = sheet.CreateRow(2); row.HeightInPoints = 30; cell = row.CreateCell(0); int index = scores.FindIndex(sc => sc == score) + 1; float winRate = 0; if (index != 1) { winRate = ((float)index / (float)(scores.Count)) * 100f; } str = string.Format(" 你打败了{0}", pperformance.Department); richText = new XSSFRichTextString(str); richText.Append(string.Format("{0}%", winRate.ToString("f0")), _winRateFont as XSSFFont); richText.Append("的成员", _normalFont as XSSFFont); cell.SetCellValue(richText); cell.CellStyle = _aquaCellStyle; SetCellRangeAddress(sheet, 2, 2, 0, 4); //行四 row = sheet.CreateRow(3); row.HeightInPoints = 30; cell = row.CreateCell(0); cell.SetCellValue("序号"); cell.CellStyle = _greycellStyle; cell = row.CreateCell(1); cell.SetCellValue("任务名称"); cell.CellStyle = _greycellStyle; cell = row.CreateCell(2); SetCellRangeAddress(sheet, 3, 3, 1, 2); cell.CellStyle = _greycellStyle; cell = row.CreateCell(3); cell.SetCellValue("任务得分"); SetCellRangeAddress(sheet, 3, 3, 3, 4); cell.CellStyle = _greycellStyle; //添加任务 int i = 1; int rowIndex = 4; foreach (var task in pperformance.Tasks) { row = sheet.CreateRow(rowIndex); row.HeightInPoints = 30; cell = row.CreateCell(0); cell.SetCellValue(i); if (i % 2 == 0) { cell.CellStyle = _greycellStyle; } else { cell.CellStyle = _normalCenterCellStyle; } cell = row.CreateCell(1); cell.SetCellValue(task.Name); if (i % 2 == 0) { cell.CellStyle = _greycellStyle; } else { cell.CellStyle = _normalCenterCellStyle; } cell = row.CreateCell(2); SetCellRangeAddress(sheet, rowIndex, rowIndex, 1, 2); if (i % 2 == 0) { cell.CellStyle = _greycellStyle; } else { cell.CellStyle = _normalCenterCellStyle; } cell = row.CreateCell(3); cell.SetCellValue(task.Score); SetCellRangeAddress(sheet, rowIndex, rowIndex, 3, 4); if (i % 2 == 0) { cell.CellStyle = _greycellStyle; } else { cell.CellStyle = _normalCenterCellStyle; } i++; rowIndex++; } //简评 row = sheet.CreateRow(rowIndex); row.HeightInPoints = 30; cell = row.CreateCell(0); cell.SetCellValue(" 简评"); cell.CellStyle = _aquaCellStyle; SetCellRangeAddress(sheet, rowIndex, rowIndex, 0, 4); row = sheet.CreateRow(++rowIndex); row.HeightInPoints = 30; cell = row.CreateCell(0); str = string.Format(" {0}月工作包数量{1},平均分值{2}", pperformance.Month, pperformance.Person.WorkBagNum, pperformance.Person.AvgScore); cell.SetCellValue(str); cell.CellStyle = _normalLeftCellStyle; SetCellRangeAddress(sheet, rowIndex, rowIndex, 0, 4); row = sheet.CreateRow(++rowIndex); row.HeightInPoints = 30; cell = row.CreateCell(0); str = string.Format(" {0}月加减分值{1},加减分值说明:{2}", pperformance.Month, pperformance.Person.AddSubScore, pperformance.Person.AddSubExplain); cell.SetCellValue(str); cell.CellStyle = _normalLeftCellStyle; SetCellRangeAddress(sheet, rowIndex, rowIndex, 0, 4); row = sheet.CreateRow(++rowIndex); row.HeightInPoints = 30; cell = row.CreateCell(0); str = string.Format("领导评语:{0}", pperformance.Person.LeadComment); cell.SetCellValue(str); cell.CellStyle = _normalLeftCellStyle; SetCellRangeAddress(sheet, rowIndex, rowIndex, 0, 4); row = sheet.CreateRow(++rowIndex); row.HeightInPoints = 40; cell = row.CreateCell(0); str = "说明:绩效分值参考工作包平均分值采取10分制:A级10分,B级8分;C级5分;D级0分;加减分项在此基础上执行。"; cell.SetCellValue(str); cell.CellStyle = _justifyLeftCellStyle; SetCellRangeAddress(sheet, rowIndex, rowIndex, 0, 4); using (FileStream stream = File.OpenWrite(path)) { workbook.Write(stream); stream.Close(); } } catch (Exception) { throw new Exception("创建Excel出错!"); } }
/// <summary> /// 创建任务明细表中没有列出的员工的绩效内存数据 /// </summary> /// <param name="persons">汇总表中的所有员工</param> /// <param name="pps">从明细表生成的绩效内存数据</param> /// <param name="department">部门</param> /// <param name="month">月份</param> /// <returns>个人绩效</returns> public static List<PersonalPerformance> CreatePersonalPerformance(List<Person> persons, List<PersonalPerformance> pps, string department, int month) { List<PersonalPerformance> personPS = new List<PersonalPerformance>(); bool bFind = false; //找出还没有创建个人绩效内存数据的员工 foreach (Person p in persons) { bFind = false; foreach (var pp in pps) { if (pp.Person.Name == p.Name && pp.Person.Number == p.Number) { bFind = true; break; } } if (!bFind) { //创建绩效内存数据 PersonalPerformance personperformance = new PersonalPerformance(department, month, p); personPS.Add(personperformance); } } return personPS; }
/// <summary> /// 创建个人绩效单列表 /// </summary> /// <param name="dt">明细表生产的内存数据</param> /// <param name="persons">统计表中的所有员工</param> /// <param name="department">部门</param> /// <param name="month">月分</param> /// <returns>个人绩效单列表</returns> public static List<PersonalPerformance> CreatePersonalPerformanceNew(DataTable dt, List<Person> persons, string department, int month) { string name = ""; string number = ""; //int index = 0; PersonalPerformance pp = null; List<PersonalPerformance> pps = new List<PersonalPerformance>(); int rowCount = dt.Rows.Count; try { for (int i = 0; i < rowCount; i++) { //姓名 if (dt.Rows[i]["姓名"] != null && !string.IsNullOrEmpty(dt.Rows[i]["姓名"].ToString())) { if (name != dt.Rows[i]["姓名"].ToString()) { name = dt.Rows[i]["姓名"].ToString(); Person person = GetPerson(persons, name, number); if (person == null) { LogHelper.WriteWarnInfo(string.Format("在统计表中没有获取到{0}({1})的信息,已取消为其生成个人绩效单!", name, number)); continue; } //添加个人绩效 pp = new PersonalPerformance(department, month, person); pps.Add(pp); } //添加任务列表 string taskName = ""; if (dt.Rows[i]["任务名称"] != null) { taskName = dt.Rows[i]["任务名称"].ToString(); } int taskScore = 0; if (dt.Rows[i]["评分"] != null) { int.TryParse(dt.Rows[i]["评分"].ToString(), out taskScore); } CTask task = new CTask(taskName, taskScore); pp.Tasks.Add(task); } } return pps; } catch (Exception) { throw new Exception("创建个人绩效内存数据出错!"); } }
/// <summary> /// 创建Excel /// </summary> /// <param name="pperformance">个人绩效内存数据</param> /// <param name="path">附件所在路径</param> /// <param name="scores">排序后的分数列表</param> public static void Create(PersonalPerformance pperformance, string path, List <float> scores) { try { XSSFWorkbook workbook = new XSSFWorkbook(); CreateStyle(workbook); ISheet sheet = workbook.CreateSheet("Sheet1"); //设置列宽 sheet.SetColumnWidth(0, (int)(10 + 0.72) * 256); sheet.SetColumnWidth(1, (int)(15 + 0.72) * 256); sheet.SetColumnWidth(2, (int)(65 + 0.72) * 256); sheet.SetColumnWidth(3, (int)(10 + 0.72) * 256); sheet.SetColumnWidth(4, (int)(10 + 0.72) * 256); //创建行和列 //行一 IRow row = sheet.CreateRow(0); row.HeightInPoints = 30; ICell cell = row.CreateCell(0); XSSFRichTextString richText = new XSSFRichTextString(" 个人绩效对账单"); richText.ApplyFont(_normalFont); cell.CellStyle = _aquaCellStyle; cell.SetCellValue(richText); SetCellRangeAddress(sheet, 0, 0, 0, 1); cell = row.CreateCell(2); cell.CellStyle = _aquaCellStyle; cell = row.CreateCell(3); cell.CellStyle = _aquaCellStyle; cell.SetCellValue(pperformance.Department); SetCellRangeAddress(sheet, 0, 0, 3, 4); //行二 row = sheet.CreateRow(1); row.HeightInPoints = 90; cell = row.CreateCell(0); string g = string.Empty; float score = pperformance.Person.Score; if (!string.IsNullOrEmpty(pperformance.Person.Grade)) { string[] gs = pperformance.Person.Grade.Split('-'); g = gs[0]; } string str = string.Format(" {0} {1}月绩效等级{2},绩效分值{3}", pperformance.Person.Name, pperformance.Month, g, score); cell.CellStyle = _normalLeftCellStyle; cell.SetCellValue(str); SetCellRangeAddress(sheet, 1, 1, 0, 4); //行三 row = sheet.CreateRow(2); row.HeightInPoints = 30; cell = row.CreateCell(0); int index = scores.FindIndex(sc => sc == score) + 1; float winRate = 0; if (index != 1) { winRate = ((float)index / (float)(scores.Count)) * 100f; } str = string.Format(" 你打败了{0}", pperformance.Department); richText = new XSSFRichTextString(str); richText.Append(string.Format("{0}%", winRate.ToString("f0")), _winRateFont as XSSFFont); richText.Append("的成员", _normalFont as XSSFFont); cell.SetCellValue(richText); cell.CellStyle = _aquaCellStyle; SetCellRangeAddress(sheet, 2, 2, 0, 4); //行四 row = sheet.CreateRow(3); row.HeightInPoints = 30; cell = row.CreateCell(0); cell.SetCellValue("序号"); cell.CellStyle = _greycellStyle; cell = row.CreateCell(1); cell.SetCellValue("任务名称"); cell.CellStyle = _greycellStyle; cell = row.CreateCell(2); SetCellRangeAddress(sheet, 3, 3, 1, 2); cell.CellStyle = _greycellStyle; cell = row.CreateCell(3); cell.SetCellValue("任务得分"); SetCellRangeAddress(sheet, 3, 3, 3, 4); cell.CellStyle = _greycellStyle; //添加任务 int i = 1; int rowIndex = 4; foreach (var task in pperformance.Tasks) { row = sheet.CreateRow(rowIndex); row.HeightInPoints = 30; cell = row.CreateCell(0); cell.SetCellValue(i); if (i % 2 == 0) { cell.CellStyle = _greycellStyle; } else { cell.CellStyle = _normalCenterCellStyle; } cell = row.CreateCell(1); cell.SetCellValue(task.Name); if (i % 2 == 0) { cell.CellStyle = _greycellStyle; } else { cell.CellStyle = _normalCenterCellStyle; } cell = row.CreateCell(2); SetCellRangeAddress(sheet, rowIndex, rowIndex, 1, 2); if (i % 2 == 0) { cell.CellStyle = _greycellStyle; } else { cell.CellStyle = _normalCenterCellStyle; } cell = row.CreateCell(3); cell.SetCellValue(task.Score); SetCellRangeAddress(sheet, rowIndex, rowIndex, 3, 4); if (i % 2 == 0) { cell.CellStyle = _greycellStyle; } else { cell.CellStyle = _normalCenterCellStyle; } i++; rowIndex++; } //简评 row = sheet.CreateRow(rowIndex); row.HeightInPoints = 30; cell = row.CreateCell(0); cell.SetCellValue(" 简评"); cell.CellStyle = _aquaCellStyle; SetCellRangeAddress(sheet, rowIndex, rowIndex, 0, 4); row = sheet.CreateRow(++rowIndex); row.HeightInPoints = 30; cell = row.CreateCell(0); str = string.Format(" {0}月工作包数量{1},平均分值{2}", pperformance.Month, pperformance.Person.WorkBagNum, pperformance.Person.AvgScore); cell.SetCellValue(str); cell.CellStyle = _normalLeftCellStyle; SetCellRangeAddress(sheet, rowIndex, rowIndex, 0, 4); row = sheet.CreateRow(++rowIndex); row.HeightInPoints = 30; cell = row.CreateCell(0); str = string.Format(" {0}月加减分值{1},加减分值说明:{2}", pperformance.Month, pperformance.Person.AddSubScore, pperformance.Person.AddSubExplain); cell.SetCellValue(str); cell.CellStyle = _normalLeftCellStyle; SetCellRangeAddress(sheet, rowIndex, rowIndex, 0, 4); row = sheet.CreateRow(++rowIndex); row.HeightInPoints = 30; cell = row.CreateCell(0); str = string.Format("领导评语:{0}", pperformance.Person.LeadComment); cell.SetCellValue(str); cell.CellStyle = _normalLeftCellStyle; SetCellRangeAddress(sheet, rowIndex, rowIndex, 0, 4); row = sheet.CreateRow(++rowIndex); row.HeightInPoints = 40; cell = row.CreateCell(0); str = "说明:绩效分值参考工作包平均分值采取10分制:A级10分,B级8分;C级5分;D级0分;加减分项在此基础上执行。"; cell.SetCellValue(str); cell.CellStyle = _justifyLeftCellStyle; SetCellRangeAddress(sheet, rowIndex, rowIndex, 0, 4); using (FileStream stream = File.OpenWrite(path)) { workbook.Write(stream); stream.Close(); } } catch (Exception) { throw new Exception("创建Excel出错!"); } }
/// <summary> /// 创建个人绩效单列表 /// </summary> /// <param name="dt"></param> /// <returns></returns> public static List<PersonalPerformance> CreatePersonalPerformance(DataTable dt, string department, int month) { string name = ""; string number = ""; int index = 0; PersonalPerformance pp = null; List<PersonalPerformance> pps = new List<PersonalPerformance>(); int rowCount = dt.Rows.Count; try { for (int i = 0; i < rowCount; i++) { //归属条线 if (dt.Rows[i][0] != null && !string.IsNullOrEmpty(dt.Rows[i][0].ToString())) { continue; } //个人绩效 if (dt.Rows[i][1] != null && !string.IsNullOrEmpty(dt.Rows[i][1].ToString())) { name = dt.Rows[i][1].ToString(); index = name.IndexOf(":"); name = name.Substring(index + 1).Trim(); number = dt.Rows[i + 1]["工号"].ToString(); pp = new PersonalPerformance(department, month, new Person(name, number)); pps.Add(pp); continue; } else { //添加任务信息 string taskName = ""; if (dt.Rows[i]["任务说明"] != null) { taskName = dt.Rows[i]["任务说明"].ToString(); } int taskScore = 0; if (dt.Rows[i]["任务得分"] != null) { taskScore = int.Parse(dt.Rows[i]["任务得分"].ToString()); } CTask task = new CTask(taskName, taskScore); pp.Tasks.Add(task); } } return pps; } catch (Exception) { throw new Exception("创建个人绩效内存数据出错!"); } }