/// <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("创建个人绩效内存数据出错！");
}
}
