void BGW_DoWork(object sender, DoWorkEventArgs e)
{
//取得相關的學生資料
//1.基本資料
//2.社團結算成績
GetPoint = new ClubTraMag();
//取得範本
#region 建立範本
Workbook template = new Workbook();
template.Open(new MemoryStream(Properties.Resources.社團成績單_範本), FileFormatType.Excel97To2003);
//Style sy = template.Worksheets[0].Cells[3, 0].Style;
//每一張
Workbook prototype = new Workbook();
prototype.Copy(template);
Worksheet ptws = prototype.Worksheets[0];
#region 建立標頭Column
評量比例 評 = new 評量比例();
ColumnNameList = new List <string>();
ColumnNameList.Add("班級名稱");
ColumnNameList.Add("座號");
ColumnNameList.Add("姓名");
ColumnNameList.Add("學號");
ColumnNameList.Add("性別");
foreach (string each in 評.ColumnDic.Keys)
{
ColumnNameList.Add(each + "(" + 評.ProportionDic[each] + "%)");
}
ColumnNameList.Add("學期成績");
ColumnNameList.Add("社團幹部");
int ColumnNameIndex = 0;
//Jean 更新Aspose
Style style = prototype.CreateStyle();
style.IsTextWrapped = true;
foreach (string each in ColumnNameList)
{
ptws.Cells[2, ColumnNameIndex].SetStyle(style);
ptws.Cells[2, ColumnNameIndex].PutValue(each);
if (ColumnNameIndex >= 5)
{
ptws.Cells.SetColumnWidth(ColumnNameIndex, 8);
tool.SetCellBro(ptws, 2, ColumnNameIndex, 1, 1);
}
ColumnNameIndex++;
}
#endregion
Range ptHeader = ptws.Cells.CreateRange(0, 3, false);
Range ptEachRow = ptws.Cells.CreateRange(3, 1, false);
//建立Excel檔案
Workbook wb = new Workbook();
wb.Copy(prototype);
//取得第一張
Worksheet ws = wb.Worksheets[0];
int dataIndex = 0;
int CountPage = 1;
int DetalIndex = 5;
#endregion
#region 填資料
foreach (string clubID in GetPoint.TraDic.Keys)
{
//每一個社團
ws.Cells.CreateRange(dataIndex, 3, false).CopyStyle(ptHeader);
ws.Cells.CreateRange(dataIndex, 3, false).CopyValue(ptHeader);
CLUBRecord cr = GetPoint.CLUBDic[clubID];
//第一行 - 建立標頭內容
ws.Cells.Merge(dataIndex, 0, 1, ColumnNameList.Count);
string TitleName = string.Format("{0}學年度 第{1}學期 {2} 社團成績單", cr.SchoolYear.ToString(), cr.Semester.ToString(), cr.ClubName);
ws.Cells[dataIndex, 0].PutValue(TitleName);
dataIndex++;
//第二行 - 代碼
ws.Cells.Merge(dataIndex, 0, 1, 3);
ws.Cells[dataIndex, 0].PutValue("代碼：" + cr.ClubNumber);
//第二行 - 老師
//教師 ColumnDic大小後的一格
ws.Cells.Merge(dataIndex, ColumnNameList.Count - 3, 1, 3);
string TeacherString = "教師：" + GetTeacherName(cr);
ws.Cells[dataIndex, ColumnNameList.Count - 3].PutValue(TeacherString);
//SetCellBro(ws, dataIndex, 0, 1, ColumnNameList.Count);
dataIndex += 2;
GetPoint.TraDic[clubID].Sort(SortTraDic);
foreach (ClubTraObj each in GetPoint.TraDic[clubID])
{
ws.Cells.CreateRange(dataIndex, 1, false).CopyStyle(ptEachRow);
ws.Cells.CreateRange(dataIndex, 1, false).CopyValue(ptEachRow);
//基本資料
tool.SetCellBro(ws, dataIndex, 0, 1, 1);
ws.Cells[dataIndex, 0].PutValue(each.student.Class != null ? each.student.Class.Name : "");
tool.SetCellBro(ws, dataIndex, 1, 1, 1);
ws.Cells[dataIndex, 1].PutValue(each.student.SeatNo.HasValue ? each.student.SeatNo.Value.ToString() : "");
tool.SetCellBro(ws, dataIndex, 2, 1, 1);
ws.Cells[dataIndex, 2].PutValue(each.student.Name);
tool.SetCellBro(ws, dataIndex, 3, 1, 1);
ws.Cells[dataIndex, 3].PutValue(each.student.StudentNumber);
tool.SetCellBro(ws, dataIndex, 4, 1, 1);
ws.Cells[dataIndex, 4].PutValue(each.student.Gender);
if (!string.IsNullOrEmpty(each.SCJ.Score))
{
XmlElement xml = DSXmlHelper.LoadXml(each.SCJ.Score);
foreach (XmlElement each1 in xml.SelectNodes("Item"))
{
string name = each1.GetAttribute("Name");
if (評.ColumnDic.ContainsKey(name))
{
tool.SetCellBro(ws, dataIndex, DetalIndex + 評.ColumnDic[name], 1, 1);
ws.Cells[dataIndex, DetalIndex + 評.ColumnDic[name]].PutValue(each1.GetAttribute("Score"));
}
}
}
else
{
for (int x = 4; x < ColumnNameList.Count; x++)
{
tool.SetCellBro(ws, dataIndex, x, 1, 1);
}
}
//學期成績
if (each.RSR != null)
{
ws.Cells.SetColumnWidth(ColumnNameList.Count - 2, 8);
tool.SetCellBro(ws, dataIndex, ColumnNameList.Count - 2, 1, 1);
string Score = each.RSR.ResultScore.HasValue ? each.RSR.ResultScore.Value.ToString() : "";
ws.Cells[dataIndex, ColumnNameList.Count - 2].PutValue(Score);
//ws.Cells[dataIndex, ColumnNameList.Count - 1].Style = sy;
}
//社團幹部
if (each.RSR != null)
{
ws.Cells.SetColumnWidth(ColumnNameList.Count - 1, 8);
tool.SetCellBro(ws, dataIndex, ColumnNameList.Count - 1, 1, 1);
string CardreName = each.RSR.CadreName;
ws.Cells[dataIndex, ColumnNameList.Count - 1].PutValue(CardreName);
//ws.Cells[dataIndex, ColumnNameList.Count - 1].Style = sy;
}
dataIndex++;
}
//頁數
string DateName = "日期：" + DateTime.Now.ToString("yyyy/MM/dd HH:mm") + " 頁數:" + CountPage.ToString();
CountPage++;
ws.Cells.Merge(dataIndex, 0, 1, 5);
//SetCellBro(ws, dataIndex, 0, 1, ColumnNameList.Count);
ws.Cells[dataIndex, 0].PutValue(DateName);
//ws.Cells[dataIndex, 0].Style = sy;
ws.HPageBreaks.Add(dataIndex + 1, ColumnNameList.Count);
dataIndex++;
}
#endregion
e.Result = wb;
}
void BGW_DoWork(object sender, DoWorkEventArgs e)
{
ClassClubTraMag mag = new ClassClubTraMag(_SchoolYear, _Semester, PrintLost);
//依據使用者所選擇的學年期
//取得相關學生之社團結算成績
//列印不及格學生清單時,排除所有學期成績60分(含)以上之學生
//取得範本
#region 建立範本
Workbook template = new Workbook();
template.Open(new MemoryStream(Properties.Resources.班級社團成績單_範本), FileFormatType.Excel97To2003);
if (PrintLost) //不及格確認單
{
PriontName = "班級社團成績不及格(確認單)";
}
else
{
PriontName = "班級社團成績單";
}
Workbook prototype = new Workbook();
prototype.Copy(template);
Worksheet ptws = prototype.Worksheets[0];
#region 建立標頭Column
評量比例 評 = new 評量比例();
if (評._wp == null)
{
e.Cancel = true;
return;
}
ColumnNameList = new List <string>();
ColumnNameList.Add("座號");
ColumnNameList.Add("姓名");
ColumnNameList.Add("學號");
ColumnNameList.Add("性別");
ColumnNameList.Add("社團");
foreach (string each in 評.ColumnDic.Keys)
{
ColumnNameList.Add(each + "(" + 評.ProportionDic[each] + "%)");
}
ColumnNameList.Add("學期成績");
if (PrintLost) //不及格確認單
{
ColumnNameList.Add("簽名");
}
int ColumnNameIndex = 0;
//Jean Aspose更新
Style style = prototype.CreateStyle();
style.IsTextWrapped = true;
foreach (string each in ColumnNameList)
{
ptws.Cells[2, ColumnNameIndex].SetStyle(style);
ptws.Cells[2, ColumnNameIndex].PutValue(each);
if (ColumnNameIndex >= 5)
{
ptws.Cells.SetColumnWidth(ColumnNameIndex, 10);
tool.SetCellBro(ptws, 2, ColumnNameIndex, 1, 1);
}
ColumnNameIndex++;
}
#endregion
Range ptHeader = ptws.Cells.CreateRange(0, 3, false);
Range ptEachRow = ptws.Cells.CreateRange(3, 1, false);
//建立Excel檔案
Workbook wb = new Workbook();
wb.Copy(prototype);
//取得第一張
Worksheet ws = wb.Worksheets[0];
int dataIndex = 0;
int CountPage = 1;
int DetalIndex = 5;
#endregion
#region 填資料
foreach (string classID in mag.TraDic.Keys)
{
if (mag.TraDic[classID].Count == 0)
{
continue;
}
ws.Cells.CreateRange(dataIndex, 3, false).CopyStyle(ptHeader);
ws.Cells.CreateRange(dataIndex, 3, false).CopyValue(ptHeader);
ClassRecord cr = mag.ClassDic[classID];
ws.Cells.Merge(dataIndex, 0, 1, ColumnNameList.Count);
string TitleName = string.Format("{0}學年度 第{1}學期 {2}", _SchoolYear.ToString(), _Semester.ToString(), PriontName);
ws.Cells[dataIndex, 0].PutValue(TitleName);
dataIndex++;
//班級
ws.Cells.Merge(dataIndex, 0, 1, 3);
ws.Cells[dataIndex, 0].PutValue(string.Format("班級：{0}", cr.Name));
ws.Cells.Merge(dataIndex, 4, 1, 3);
//教師
if (!string.IsNullOrEmpty(cr.RefTeacherID))
{
#region 教師
if (mag.TeacherDic.ContainsKey(cr.RefTeacherID))
{
TeacherRecord tr = mag.TeacherDic[cr.RefTeacherID];
//是否有暱稱
if (!string.IsNullOrEmpty(tr.Nickname))
{
string TeacherString = "班導師：" + tr.Name + "(" + tr.Nickname + ")";
ws.Cells[dataIndex, 4].PutValue(TeacherString);
}
else
{
string TeacherString = "班導師：" + mag.TeacherDic[cr.RefTeacherID].Name;
ws.Cells[dataIndex, 4].PutValue(TeacherString);
}
}
#endregion
}
//頁數
ws.Cells.Merge(dataIndex, ColumnNameList.Count - 3, 1, 3);
ws.Cells[dataIndex, ColumnNameList.Count - 3].PutValue("日期：" + DateTime.Now.ToString("yyyy/MM/dd HH:mm") + " 頁數:" + CountPage.ToString());
dataIndex += 2;
mag.TraDic[classID].Sort(SortTraDic);
foreach (ClassClubTraObj each in mag.TraDic[classID])
{
ws.Cells.CreateRange(dataIndex, 1, false).CopyStyle(ptEachRow);
ws.Cells.CreateRange(dataIndex, 1, false).CopyValue(ptEachRow);
ws.Cells[dataIndex, 0].PutValue(each.studentRecord.SeatNo.HasValue ? each.studentRecord.SeatNo.Value.ToString() : "");
ws.Cells[dataIndex, 1].PutValue(each.studentRecord.Name);
ws.Cells[dataIndex, 2].PutValue(each.studentRecord.StudentNumber);
ws.Cells[dataIndex, 3].PutValue(each.studentRecord.Gender);
//社團
if (each.club != null)
{
ws.Cells[dataIndex, 4].PutValue(each.club.ClubName);
}
if (each.SCJoin != null)
{
if (!string.IsNullOrEmpty(each.SCJoin.Score))
{
int x = 0;
XmlElement xml = DSXmlHelper.LoadXml(each.SCJoin.Score);
foreach (XmlElement each1 in xml.SelectNodes("Item"))
{
x++;
string name = each1.GetAttribute("Name");
if (評.ColumnDic.ContainsKey(name))
{
ws.Cells[dataIndex, DetalIndex + 評.ColumnDic[name]].PutValue(each1.GetAttribute("Score"));
}
}
}
}
for (int x = 3; x < ColumnNameList.Count; x++)
{
tool.SetCellBro(ws, dataIndex, x, 1, 1);
}
//學期成績
if (PrintLost) //不及格確認單
{
ws.Cells.SetColumnWidth(ColumnNameList.Count - 1, 14);
if (each.RSR != null)
{
ws.Cells.SetColumnWidth(ColumnNameList.Count - 2, 8);
string Score = each.RSR.ResultScore.HasValue ? each.RSR.ResultScore.Value.ToString() : "";
ws.Cells[dataIndex, ColumnNameList.Count - 2].PutValue(Score);
}
else
{
}
}
else
{
if (each.RSR != null) //有學期成績
{
ws.Cells.SetColumnWidth(ColumnNameList.Count - 1, 8);
string Score = each.RSR.ResultScore.HasValue ? each.RSR.ResultScore.Value.ToString() : "";
ws.Cells[dataIndex, ColumnNameList.Count - 1].PutValue(Score);
}
else
{
}
}
dataIndex++;
}
CountPage++; //每班增加1頁
ws.HPageBreaks.Add(dataIndex, ColumnNameList.Count);
}
#endregion
e.Result = wb;
}
