//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;
}
