public static void Run()
{
//Source directory
string sourceDir = RunExamples.Get_SourceDirectory();
//Output directory
string outputDir = RunExamples.Get_OutputDirectory();
// Instantiate a new workbook
// Open an existing excel file
Workbook workbook = new Workbook(sourceDir + "sampleCopyingSingleRow.xlsx");
// Get the first worksheet cells
Cells cells = workbook.Worksheets[0].Cells;
//Copy the first row to next 10 rows
for (int i = 1; i <= 10; i++)
{
cells.CopyRow(cells, 0, i);
}
// Save the excel file
workbook.Save(outputDir + "outputCopyingSingleRow.xlsx");
Console.WriteLine("CopyingSingleRow executed successfully.");
}
public static void Run()
{
// ExStart:CopyingSingleRow
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
// Instantiate a new workbook
// Open an existing excel file
Workbook workbook = new Workbook(dataDir + "aspose-sample.xlsx");
// Get the first worksheet cells
Cells cells = workbook.Worksheets[0].Cells;
//Copy the first row to next 10 rows
for (int i = 1; i <= 10; i++)
{
cells.CopyRow(cells, 0, i);
}
// Save the excel file
workbook.Save(dataDir + "outaspose-sample.out.xlsx");
// ExEnd:CopyingSingleRow
}
public ActionResult ExportExcel(string queryJson, int mode)
{
string fileName = string.Empty;
Pagination pagination = new Pagination();
pagination.page = 1;
pagination.rows = 100000000;
pagination.sidx = " nvl(punishdate,sysdate-10000) desc,nvl(createdate,sysdate -10000) desc,deptsort asc,sortcode asc,userid";
pagination.sord = " desc";
Operator curUser = ERCHTMS.Code.OperatorProvider.Provider.Current();
string userId = curUser.UserId;
try
{
//取出扣分数据源
DataTable exportTable = lllegaldeductmarksbll.GetLllegalRecordInfo(pagination, queryJson);
//取出人员积分数据源
pagination.sidx = " deptsort asc,sortcode asc,userid";
DataTable userTable = lllegaldeductmarksbll.GetLllegalPointInfo(pagination, queryJson);
Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook();
//生产部门
if (mode == 0)
{
wb.Open(Server.MapPath("~/Resource/ExcelTemplate/生产部门违章档案(厂部)模板.xls"));
fileName = "生产部门违章档案(厂部)" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
}
else
{
wb.Open(Server.MapPath("~/Resource/ExcelTemplate/外协单位违章档案(厂部)模板.xls"));
fileName = "外协单位违章档案(厂部)" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
}
string title = string.Empty;
string title0 = string.Empty;
//第一张表
Aspose.Cells.Worksheet sheet = wb.Worksheets[0] as Aspose.Cells.Worksheet;
Cells cells = sheet.Cells;
//第二张表
Aspose.Cells.Worksheet sheet1 = wb.Worksheets[1] as Aspose.Cells.Worksheet;
Cells cells1 = sheet1.Cells;
Aspose.Cells.Cell cell0 = sheet.Cells[0, 0];
Aspose.Cells.Cell cell00 = sheet1.Cells[0, 0];
if (mode == 0)
{
title = curUser.OrganizeName + "生产部门个人违章扣分表";
cell0.PutValue(title);
title0 = curUser.OrganizeName + "生产部门个人违章档案(厂级)";
cell00.PutValue(title0);
}
else
{
title = curUser.OrganizeName + "外协单位个人违章扣分表";
cell0.PutValue(title);
title0 = curUser.OrganizeName + "外协单位个人违章档案(厂级)";
cell00.PutValue(title0);
}
int rowIndex = 2;
//遍历行
#region 第一张表
foreach (DataRow row in exportTable.Rows)
{
if (rowIndex - 2 < exportTable.Rows.Count - 1)
{
cells.CopyRow(cells, rowIndex, rowIndex + 1);
}
string username = !string.IsNullOrEmpty(row["username"].ToString()) ? row["username"].ToString() : "";
Aspose.Cells.Cell rcell1 = sheet.Cells[rowIndex, 1];
rcell1.PutValue(username);
string deptname = !string.IsNullOrEmpty(row["deptname"].ToString()) ? row["deptname"].ToString() : "";
Aspose.Cells.Cell rcell2 = sheet.Cells[rowIndex, 2];
rcell2.PutValue(deptname);
string teamname = !string.IsNullOrEmpty(row["teamname"].ToString()) ? row["teamname"].ToString() : "";
Aspose.Cells.Cell rcell3 = sheet.Cells[rowIndex, 3];
rcell3.PutValue(teamname);
string dutyname = !string.IsNullOrEmpty(row["dutyname"].ToString()) ? row["dutyname"].ToString() : "";
Aspose.Cells.Cell rcell4 = sheet.Cells[rowIndex, 4];
rcell4.PutValue(dutyname);
string lllegaldescribe = !string.IsNullOrEmpty(row["lllegaldescribe"].ToString()) ? row["lllegaldescribe"].ToString() : "";
Aspose.Cells.Cell rcell5 = sheet.Cells[rowIndex, 5];
rcell5.PutValue(lllegaldescribe);
string lllegaltypename = !string.IsNullOrEmpty(row["lllegaltypename"].ToString()) ? row["lllegaltypename"].ToString() : "";
Aspose.Cells.Cell rcell6 = sheet.Cells[rowIndex, 6];
rcell6.PutValue(lllegaltypename);
string punishdate = !string.IsNullOrEmpty(row["punishdate"].ToString()) ? Convert.ToDateTime(row["punishdate"].ToString()).ToString("yyyy-MM-dd") : "";
Aspose.Cells.Cell rcell7 = sheet.Cells[rowIndex, 7];
rcell7.PutValue(punishdate);
string punishresult = !string.IsNullOrEmpty(row["punishresult"].ToString()) ? row["punishresult"].ToString() : "0";
Aspose.Cells.Cell rcell8 = sheet.Cells[rowIndex, 8];
rcell8.PutValue(row["punishresult"].ToString());
string punishpoint = !string.IsNullOrEmpty(row["punishpoint"].ToString()) ? row["punishpoint"].ToString() : "0";
Aspose.Cells.Cell rcell9 = sheet.Cells[rowIndex, 9];
rcell9.PutValue(punishpoint);
rcell9.R1C1Formula = "=I" + (rowIndex + 1).ToString() + "/100";
rowIndex += 1;
}
#endregion
rowIndex = 2;
#region 第二张表
foreach (DataRow row in userTable.Rows)
{
if (rowIndex - 2 < userTable.Rows.Count - 1)
{
cells1.CopyRow(cells1, rowIndex, rowIndex + 1);
}
string username = !string.IsNullOrEmpty(row["username"].ToString()) ? row["username"].ToString() : "";
Aspose.Cells.Cell rcell1 = sheet1.Cells[rowIndex, 1];
rcell1.PutValue(username);
string deptname = !string.IsNullOrEmpty(row["deptname"].ToString()) ? row["deptname"].ToString() : "";
Aspose.Cells.Cell rcell2 = sheet1.Cells[rowIndex, 2];
rcell2.PutValue(deptname);
string teamname = !string.IsNullOrEmpty(row["teamname"].ToString()) ? row["teamname"].ToString() : "";
Aspose.Cells.Cell rcell3 = sheet1.Cells[rowIndex, 3];
rcell3.PutValue(teamname);
string dutyname = !string.IsNullOrEmpty(row["dutyname"].ToString()) ? row["dutyname"].ToString() : "";
Aspose.Cells.Cell rcell4 = sheet1.Cells[rowIndex, 4];
rcell4.PutValue(dutyname);
string initpoint = !string.IsNullOrEmpty(row["initpoint"].ToString()) ? row["initpoint"].ToString() : "100";
Aspose.Cells.Cell rcell5 = sheet1.Cells[rowIndex, 5];
rcell5.PutValue(initpoint);
Aspose.Cells.Cell rcell6 = sheet1.Cells[rowIndex, 6];
if (mode == 0)
{
rcell6.R1C1Formula = "=SUMIF(生产部门违章扣分表!$B$3:$J$10000,B" + (rowIndex + 1).ToString() + ",生产部门违章扣分表!$J$3:$J$10000)";
}
else
{
rcell6.R1C1Formula = "=SUMIF(外协单位违章扣分表!$B$3:$J$10000,B" + (rowIndex + 1).ToString() + ",外协单位违章扣分表!$J$3:$J$10000)";
}
Aspose.Cells.Cell rcell7 = sheet1.Cells[rowIndex, 7];
rcell7.R1C1Formula = "=F" + (rowIndex + 1).ToString() + "-G" + (rowIndex + 1).ToString();
rowIndex += 1;
}
#endregion
string tempSavePath = Server.MapPath("~/Resource/Temp/") + fileName;
wb.Save(tempSavePath);
string url = "../../Utility/DownloadFile?filePath=~/Resource/Temp/" + fileName + "&speed=10240000&newFileName=" + fileName;
return(Redirect(url));
}
catch (Exception ex)
{
return(Error(ex.Message));
}
}
private void buttonX1_Click(object s, EventArgs ea)
{
string schoolYear = comboBox_ChooseSchoolYear.Text;
List <String> Error_List = new List <string>();
BackgroundWorker BGW = new BackgroundWorker();
BGW.WorkerReportsProgress = true;
#region 資料處理
BGW.DoWork += delegate(object sender, DoWorkEventArgs e)
{
BGW.ReportProgress(5);
// 為列印Excel 先New 物件，注意下行方法只能參考新的 Aspose.Cell_201402，如果用舊的話會有錯誤
Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook(new MemoryStream(Properties.Resources.全校體適能中等以上_含中等_各項目百分比統計表));
Cells cs0 = wb.Worksheets[0].Cells;
FISCA.UDT.AccessHelper accessHelper = new FISCA.UDT.AccessHelper();
//沒任何列印邏輯?? - 先註解掉(by Dylan)
//Aspose.Words.Document Template;
//Template = new Aspose.Words.Document(new MemoryStream(Properties.Resources.班級體適能確認單範本1));
// 存放學生ID 與班級名稱的對照
Dictionary <string, string> studentID_to_className = new Dictionary <string, string>();
// 存放學生ID 與學生名稱的對照
Dictionary <string, string> studentID_to_studentName = new Dictionary <string, string>();
//目前體適能一共五個等級，分別為金牌、銀牌、銅牌、中等、請加強，下面字典為蒐集每班各體適能項目"請加強"的人數使用
Dictionary <string, to_be_Improve_counter> dic_class_fitness_to_be_Improve = new Dictionary <string, to_be_Improve_counter>();
// 計算BGW進度使用
int progress = 0;
//統計"全校" 體適能項目 "請加強"人數使用
to_be_Improve_counter Total_school_to_be_Improve_counter = new to_be_Improve_counter();
//蒐集全校正常在學有班級學生之全部ID
var studentIDList = new List <string>();
//蒐集全校正常在學有班級且有"體適能"資料學生之全部ID ，注意此項與studentIDList不一定永遠一樣
var studentIDList_fitness = new List <string>();
var studentRecordList = new List <StudentRecord>();
#region 取得全校班級，並將全校學生做班級分類
// 取得選取班級，(不給使用者指定選取了，直接選全校)
//List<ClassRecord> ClassList = K12.Data.Class.SelectByIDs(K12.Presentation.NLDPanels.Class.SelectedSource);
studentRecordList = K12.Data.Student.SelectAll();
foreach (var stuRec in studentRecordList)
{
// 0 = 一般生 ，如此一來可以避免選到畢業班，另外也必須要有班級才行
if (stuRec.Status == 0 && stuRec.Class != null)
{
studentIDList.Add(stuRec.ID);
studentID_to_className.Add(stuRec.ID, stuRec.Class.Name);
studentID_to_studentName.Add(stuRec.ID, stuRec.Name);
if (!dic_class_fitness_to_be_Improve.ContainsKey(stuRec.Class.Name))
{
dic_class_fitness_to_be_Improve.Add(stuRec.Class.Name, new to_be_Improve_counter());
}
}
}
// 穎驊筆記，下面的方法註解掉，原因是如果直接選取全校班級Class.SelectAll() ，會選到已經畢業的班級，就現階段來說比較麻煩處理，
//不如像上面直接找尋全部學生再配給他們班級
//List<ClassRecord> ClassList = K12.Data.Class.SelectAll();
//foreach (ClassRecord classrecord in ClassList)
//{
// foreach (var studentRec in classrecord.Students)
// {
// studentIDList.Add(studentRec.ID);
// studentID_to_className.Add(studentRec.ID, classrecord.Name);
// }
// dic_class_fitness_to_be_Improve.Add(classrecord.Name, new to_be_Improve_counter());
//}
#endregion
BGW.ReportProgress(20);
#region 取得體適能資料並分類
//取得全學生的體適能資料
var studentFitnessRecordList = accessHelper.Select <StudentFitnessRecord>(string.Format("ref_student_id in ('{0}') AND school_year = {1}", string.Join("','", studentIDList), schoolYear));
// 把各學生的體適能資料和班級做集合整理
foreach (var fitnessRec in studentFitnessRecordList)
{
//蒐集全校正常在學有班級且有"體適能"資料學生之全部ID ，注意此項與studentIDList不一定永遠一樣，因為很有可能有在studentIDList的學生卻沒有體適能資料，
//自然在取得全學生體適能資料studentFitnessRecordList就不會出現，但我們又必須要將沒有資料的人視為"缺考"、"零分"、"待加強"，所以要再出一個studentIDList_fitness
//之後與studentIDList內的ID做比較，找出其班級，把總人數、待加強人數加上去
studentIDList_fitness.Add(fitnessRec.StudentID);
// 計算坐姿體前彎各班待加強人數
if (fitnessRec.SitAndReachDegree == "請加強")
{
dic_class_fitness_to_be_Improve[studentID_to_className[fitnessRec.StudentID]].SitAndReachDegree_failed_counter++;
dic_class_fitness_to_be_Improve[studentID_to_className[fitnessRec.StudentID]].SitAndReachDegree_total++;
}
// 用來處理假如該學生沒有常模的狀況，此時不能將之算入不及格或是總母群內，另存錯誤提醒視窗
//2016/7/21 修正，因為實際拿各個學校資料測試，發現其實真實學校資料都缺蠻多的，會造成錯誤提醒視窗一大包
//恩正說學生沒有體適能資料、沒有體適能常模，不是我們的責任，是各個學校應該要自己負責，所以資料不齊者視為"0分"、"缺考"、"不及格"
else if (fitnessRec.SitAndReachDegree == "")
{
Error_List.Add("班級:" + studentID_to_className[fitnessRec.StudentID] + "，" + "學生:" + studentID_to_studentName[fitnessRec.StudentID] + "沒有坐姿體前彎常模資料，將不會納入計算，請確認是否忘記常模計算");
//缺體適能常模資料，就當你不合格要"請加強"
dic_class_fitness_to_be_Improve[studentID_to_className[fitnessRec.StudentID]].SitAndReachDegree_failed_counter++;
dic_class_fitness_to_be_Improve[studentID_to_className[fitnessRec.StudentID]].SitAndReachDegree_total++;
}
else
{
dic_class_fitness_to_be_Improve[studentID_to_className[fitnessRec.StudentID]].SitAndReachDegree_total++;
}
// 計算立定跳遠各班待加強人數
if (fitnessRec.StandingLongJumpDegree == "請加強")
{
dic_class_fitness_to_be_Improve[studentID_to_className[fitnessRec.StudentID]].StandingLongJumpDegree_failed_counter++;
dic_class_fitness_to_be_Improve[studentID_to_className[fitnessRec.StudentID]].StandingLongJumpDegree_total++;
}
// 用來處理假如該學生沒有常模的狀況，此時不能將之算入不及格或是總母群內，另存錯誤提醒視窗
//2016/7/21 修正，因為實際拿各個學校資料測試，發現其實真實學校資料都缺蠻多的，會造成錯誤提醒視窗一大包
//恩正說學生沒有體適能資料、沒有體適能常模，不是我們的責任，是各個學校應該要自己負責，所以資料不齊者視為"0分"、"缺考"、"不及格"
else if (fitnessRec.StandingLongJumpDegree == "")
{
Error_List.Add("班級:" + studentID_to_className[fitnessRec.StudentID] + "，" + "學生:" + studentID_to_studentName[fitnessRec.StudentID] + "沒有立定跳遠常模資料，將不會納入計算，請確認是否忘記常模計算");
//缺體適能常模資料，就當你不合格要"請加強"
dic_class_fitness_to_be_Improve[studentID_to_className[fitnessRec.StudentID]].StandingLongJumpDegree_failed_counter++;
dic_class_fitness_to_be_Improve[studentID_to_className[fitnessRec.StudentID]].StandingLongJumpDegree_total++;
}
else
{
dic_class_fitness_to_be_Improve[studentID_to_className[fitnessRec.StudentID]].StandingLongJumpDegree_total++;
}
// 計算坐仰臥起坐各班待加強人數
if (fitnessRec.SitUpDegree == "請加強")
{
dic_class_fitness_to_be_Improve[studentID_to_className[fitnessRec.StudentID]].SitUpDegree_failed_counter++;
dic_class_fitness_to_be_Improve[studentID_to_className[fitnessRec.StudentID]].SitUpDegree_total++;
}
// 用來處理假如該學生沒有常模的狀況，此時不能將之算入不及格或是總母群內，另存錯誤提醒視窗
//2016/7/21 修正，因為實際拿各個學校資料測試，發現其實真實學校資料都缺蠻多的，會造成錯誤提醒視窗一大包
//恩正說學生沒有體適能資料、沒有體適能常模，不是我們的責任，是各個學校應該要自己負責，所以資料不齊者視為"0分"、"缺考"、"不及格"
else if (fitnessRec.SitUpDegree == "")
{
Error_List.Add("班級:" + studentID_to_className[fitnessRec.StudentID] + "，" + "學生:" + studentID_to_studentName[fitnessRec.StudentID] + "沒有仰臥起坐常模資料，將不會納入計算，請確認是否忘記常模計算");
//缺體適能常模資料，就當你不合格要"請加強"
dic_class_fitness_to_be_Improve[studentID_to_className[fitnessRec.StudentID]].SitUpDegree_failed_counter++;
dic_class_fitness_to_be_Improve[studentID_to_className[fitnessRec.StudentID]].SitUpDegree_total++;
}
else
{
dic_class_fitness_to_be_Improve[studentID_to_className[fitnessRec.StudentID]].SitUpDegree_total++;
}
// 計算心肺適能各班待加強人數
if (fitnessRec.CardiorespiratoryDegree == "請加強")
{
dic_class_fitness_to_be_Improve[studentID_to_className[fitnessRec.StudentID]].CardiorespiratoryDegree_failed_counter++;
dic_class_fitness_to_be_Improve[studentID_to_className[fitnessRec.StudentID]].CardiorespiratoryDegree_total++;
}
// 用來處理假如該學生沒有常模的狀況，此時不能將之算入不及格或是總母群內，另存錯誤提醒視窗
//2016/7/21 修正，因為實際拿各個學校資料測試，發現其實真實學校資料都缺蠻多的，會造成錯誤提醒視窗一大包
//恩正說學生沒有體適能資料、沒有體適能常模，不是我們的責任，是各個學校應該要自己負責，所以資料不齊者視為"0分"、"缺考"、"不及格"
else if (fitnessRec.CardiorespiratoryDegree == "")
{
Error_List.Add("班級:" + studentID_to_className[fitnessRec.StudentID] + "，" + "學生:" + studentID_to_studentName[fitnessRec.StudentID] + "沒有心肺適能常模資料，將不會納入計算，請確認是否忘記常模計算");
//缺體適能常模資料，就當你不合格要"請加強"
dic_class_fitness_to_be_Improve[studentID_to_className[fitnessRec.StudentID]].CardiorespiratoryDegree_failed_counter++;
dic_class_fitness_to_be_Improve[studentID_to_className[fitnessRec.StudentID]].CardiorespiratoryDegree_total++;
}
else
{
dic_class_fitness_to_be_Improve[studentID_to_className[fitnessRec.StudentID]].CardiorespiratoryDegree_total++;
}
//2016/11/11 光棍節，穎驊新增計算班級四個項目(坐姿體前彎、立定跳遠、仰臥起坐、心肺適能)都通過比例(在金牌、銀牌、銅牌、中等、待加強五個評等中至少拿中等)
// 四大項目都必須要有常模資料，才會進行計算，否則即使只缺一項資料其他項目都通過，也會將之不算四項目都通過
if (fitnessRec.SitAndReachDegree != "" && fitnessRec.StandingLongJumpDegree != "" && fitnessRec.SitUpDegree != "" && fitnessRec.CardiorespiratoryDegree != "")
{
if (fitnessRec.SitAndReachDegree != "請加強" && fitnessRec.StandingLongJumpDegree != "請加強" && fitnessRec.SitUpDegree != "請加強" && fitnessRec.CardiorespiratoryDegree != "請加強")
{
dic_class_fitness_to_be_Improve[studentID_to_className[fitnessRec.StudentID]].Four_Item_All_Pass_counter++;
dic_class_fitness_to_be_Improve[studentID_to_className[fitnessRec.StudentID]].Four_Item_All_Pass_counter_total++;
}
else
{
dic_class_fitness_to_be_Improve[studentID_to_className[fitnessRec.StudentID]].Four_Item_All_Pass_counter_total++;
}
}
else
{
dic_class_fitness_to_be_Improve[studentID_to_className[fitnessRec.StudentID]].Four_Item_All_Pass_counter_total++;
}
// 2016/7/22 上一版已在昨天(7/21)早上出去了，今天再做別的東西時發現ReportProgress有點問題，必須要先轉型有浮點數的類型(decimal 、float)算完後，再轉回int
//否則 1 / studentFitnessRecordList.Count 一除下來的型別int 可能永遠都是零。由於此功能在實際功能其實沒甚麼影響，先把Code改好，下次等有需求再更新。
progress += (int)(((decimal)1 / studentFitnessRecordList.Count) * 60);
BGW.ReportProgress(20 + progress);
}
//蒐集全校正常在學有班級且有"體適能"資料學生之全部ID ，注意此項與studentIDList不一定永遠一樣，因為很有可能有在studentIDList的學生卻沒有體適能資料，
//自然在取得全學生體適能資料studentFitnessRecordList就不會出現，但我們又必須要將沒有資料的人視為"缺考"、"零分"、"待加強"，所以要再出一個studentIDList_fitness
//之後與studentIDList內的ID做比較，找出其班級，把總人數、待加強人數加上去
// 全校身份一般、有班級的學生
foreach (var stuID in studentIDList)
{
//卻沒有體適能資料的話，就在該班把每一個項目總人數、待加強人數給加上去
if (!studentIDList_fitness.Contains(stuID))
{
dic_class_fitness_to_be_Improve[studentID_to_className[stuID]].SitAndReachDegree_failed_counter++;
dic_class_fitness_to_be_Improve[studentID_to_className[stuID]].SitAndReachDegree_total++;
dic_class_fitness_to_be_Improve[studentID_to_className[stuID]].StandingLongJumpDegree_failed_counter++;
dic_class_fitness_to_be_Improve[studentID_to_className[stuID]].StandingLongJumpDegree_total++;
dic_class_fitness_to_be_Improve[studentID_to_className[stuID]].SitUpDegree_failed_counter++;
dic_class_fitness_to_be_Improve[studentID_to_className[stuID]].SitUpDegree_total++;
dic_class_fitness_to_be_Improve[studentID_to_className[stuID]].CardiorespiratoryDegree_failed_counter++;
dic_class_fitness_to_be_Improve[studentID_to_className[stuID]].CardiorespiratoryDegree_total++;
//沒有體適能資料，視為沒通過，在四項全過統計直接加分母總人數就好。
dic_class_fitness_to_be_Improve[studentID_to_className[stuID]].Four_Item_All_Pass_counter_total++;
Error_List.Add("班級:" + studentID_to_className[stuID] + "，" + "學生:" + studentID_to_studentName[stuID] + "沒有體適能資料 請確認是否忘記輸入");
}
}
#endregion
BGW.ReportProgress(80);
#region 報表填值
cs0[0, 0].Value = schoolYear + "學年度中等以上(含中等)各項目百分比統計表";
int RowCounter = 3;
int ColCounter = 0;
int endRow = dic_class_fitness_to_be_Improve.Count + 3;
//看看全資料有沒有錯誤，如果有錯，全校的百分比將不計算，而顯示錯誤
//2016/7/21 因應恩正所說，我們其實不太需要為殘破的資料負責任，所以這個Bool 會暫時用不到
bool DataBroken = false;
Workbook template = new Workbook();
template = new Aspose.Cells.Workbook(new MemoryStream(Properties.Resources.全校體適能中等以上_含中等_各項目百分比統計表_程式用樣版_));
// 固定複製另一份的樣板，最後一行"全校"的那一行，這樣不論班級有多少，最後一行都會是全校
cs0.CopyRow(template.Worksheets[0].Cells, 12, endRow);
foreach (var item in dic_class_fitness_to_be_Improve)
{
cs0.CopyRow(template.Worksheets[0].Cells, 3, RowCounter);
// 第一欄填班級
cs0[RowCounter, ColCounter].Value = item.Key;
// 第二欄坐姿體前彎，且分母總數不可為0
if (item.Value.SitAndReachDegree_total != 0)
{
cs0[RowCounter, ColCounter + 1].Value = Math.Round((100 - (item.Value.SitAndReachDegree_failed_counter / item.Value.SitAndReachDegree_total) * 100), 0, MidpointRounding.AwayFromZero) + "%";
Total_school_to_be_Improve_counter.SitAndReachDegree_failed_counter += item.Value.SitAndReachDegree_failed_counter;
Total_school_to_be_Improve_counter.SitAndReachDegree_total += item.Value.SitAndReachDegree_total;
}
else
{
Error_List.Add("班級:" + item.Key + "全班無同學有坐姿體前彎體適能紀錄常模，將造成計算百分比錯誤，請檢查是否忘記常模計算，或是該班同學都沒有體適能資料");
// 下行為舊的處理方式。
//cs0[RowCounter, ColCounter + 1].Value = "無資料";
//新的處理方式，全班數不到人直接0%
cs0[RowCounter, ColCounter + 1].Value = "0%";
DataBroken = true;
}
// 第三欄填立定跳遠，且分母總數不可為0
if (item.Value.StandingLongJumpDegree_total != 0)
{
cs0[RowCounter, ColCounter + 2].Value = Math.Round((100 - (item.Value.StandingLongJumpDegree_failed_counter / item.Value.StandingLongJumpDegree_total) * 100), 0, MidpointRounding.AwayFromZero) + "%";
Total_school_to_be_Improve_counter.StandingLongJumpDegree_failed_counter += item.Value.StandingLongJumpDegree_failed_counter;
Total_school_to_be_Improve_counter.StandingLongJumpDegree_total += item.Value.StandingLongJumpDegree_total;
}
else
{
Error_List.Add("班級:" + item.Key + "全班無同學有立定跳遠體適能紀錄常模，將造成計算百分比錯誤，請檢查是否忘記常模計算，或是該班同學都沒有體適能資料");
// 下行為舊的處理方式。
//cs0[RowCounter, ColCounter + 2].Value = "無資料";
//新的處理方式，全班數不到人直接0%
cs0[RowCounter, ColCounter + 2].Value = "0%";
DataBroken = true;
}
// 第四欄填仰臥起坐，且分母總數不可為0
if (item.Value.SitUpDegree_total != 0)
{
cs0[RowCounter, ColCounter + 3].Value = Math.Round((100 - (item.Value.SitUpDegree_failed_counter / item.Value.SitUpDegree_total) * 100), 0, MidpointRounding.AwayFromZero) + "%";
Total_school_to_be_Improve_counter.SitUpDegree_failed_counter += item.Value.SitUpDegree_failed_counter;
Total_school_to_be_Improve_counter.SitUpDegree_total += item.Value.SitUpDegree_total;
}
else
{
Error_List.Add("班級:" + item.Key + "全班無同學有仰臥起坐體適能紀錄常模，將造成計算百分比錯誤，請檢查是否忘記常模計算，或是該班同學都沒有體適能資料");
// 下行為舊的處理方式。
//cs0[RowCounter, ColCounter + 3].Value = "無資料";
//新的處理方式，全班數不到人直接0%
cs0[RowCounter, ColCounter + 3].Value = "0%";
DataBroken = true;
}
// 第五欄填心肺適能，且分母總數不可為0
if (item.Value.CardiorespiratoryDegree_total != 0)
{
cs0[RowCounter, ColCounter + 4].Value = Math.Round((100 - (item.Value.CardiorespiratoryDegree_failed_counter / item.Value.CardiorespiratoryDegree_total) * 100), 0, MidpointRounding.AwayFromZero) + "%";
Total_school_to_be_Improve_counter.CardiorespiratoryDegree_failed_counter += item.Value.CardiorespiratoryDegree_failed_counter;
Total_school_to_be_Improve_counter.CardiorespiratoryDegree_total += item.Value.CardiorespiratoryDegree_total;
}
else
{
Error_List.Add("班級:" + item.Key + "全班無同學有心肺體適能紀錄常模，將造成計算百分比錯誤，請檢查是否忘記常模計算，或是該班同學都沒有體適能資料");
// 下行為舊的處理方式。
//cs0[RowCounter, ColCounter + 4].Value = "無資料";
//新的處理方式，全班數不到人直接0%
cs0[RowCounter, ColCounter + 4].Value = "0%";
DataBroken = true;
}
//2016/11/11 光棍節，穎驊新增 第六欄填四項皆通過人數百分比統計，且分母總數不可為0
if (item.Value.Four_Item_All_Pass_counter_total != 0)
{
cs0[RowCounter, ColCounter + 5].Value = Math.Round((item.Value.Four_Item_All_Pass_counter / item.Value.Four_Item_All_Pass_counter_total) * 100, 0, MidpointRounding.AwayFromZero) + "%";
Total_school_to_be_Improve_counter.Four_Item_All_Pass_counter += item.Value.Four_Item_All_Pass_counter;
Total_school_to_be_Improve_counter.Four_Item_All_Pass_counter_total += item.Value.Four_Item_All_Pass_counter_total;
}
RowCounter++;
}
cs0[endRow, ColCounter + 1].Value = Math.Round((100 - (Total_school_to_be_Improve_counter.SitAndReachDegree_failed_counter / Total_school_to_be_Improve_counter.SitAndReachDegree_total) * 100), 0, MidpointRounding.AwayFromZero) + "%";
cs0[endRow, ColCounter + 2].Value = Math.Round((100 - (Total_school_to_be_Improve_counter.StandingLongJumpDegree_failed_counter / Total_school_to_be_Improve_counter.StandingLongJumpDegree_total) * 100), 0, MidpointRounding.AwayFromZero) + "%";
cs0[endRow, ColCounter + 3].Value = Math.Round((100 - (Total_school_to_be_Improve_counter.SitUpDegree_failed_counter / Total_school_to_be_Improve_counter.SitUpDegree_total) * 100), 0, MidpointRounding.AwayFromZero) + "%";
cs0[endRow, ColCounter + 4].Value = Math.Round((100 - (Total_school_to_be_Improve_counter.CardiorespiratoryDegree_failed_counter / Total_school_to_be_Improve_counter.CardiorespiratoryDegree_total) * 100), 0, MidpointRounding.AwayFromZero) + "%";
//新增全校四項通過百分比
cs0[endRow, ColCounter + 5].Value = Math.Round((Total_school_to_be_Improve_counter.Four_Item_All_Pass_counter / Total_school_to_be_Improve_counter.Four_Item_All_Pass_counter_total * 100), 0, MidpointRounding.AwayFromZero) + "%";
// 下行為舊的處理方式。
//if (!DataBroken == true)
//{
// cs0[endRow, ColCounter + 1].Value = Math.Round((100 - (Total_school_to_be_Improve_counter.SitAndReachDegree_failed_counter / Total_school_to_be_Improve_counter.SitAndReachDegree_total) * 100), 0, MidpointRounding.AwayFromZero) + "%";
// cs0[endRow, ColCounter + 2].Value = Math.Round((100 - (Total_school_to_be_Improve_counter.StandingLongJumpDegree_failed_counter / Total_school_to_be_Improve_counter.StandingLongJumpDegree_total) * 100), 0, MidpointRounding.AwayFromZero) + "%";
// cs0[endRow, ColCounter + 3].Value = Math.Round((100 - (Total_school_to_be_Improve_counter.SitUpDegree_failed_counter / Total_school_to_be_Improve_counter.SitUpDegree_total) * 100), 0, MidpointRounding.AwayFromZero) + "%";
// cs0[endRow, ColCounter + 4].Value = Math.Round((100 - (Total_school_to_be_Improve_counter.CardiorespiratoryDegree_failed_counter / Total_school_to_be_Improve_counter.CardiorespiratoryDegree_total) * 100), 0, MidpointRounding.AwayFromZero) + "%";
//}
//else
//{
// cs0[endRow, ColCounter + 1].Value = "無法計算";
// cs0[endRow, ColCounter + 2].Value = "無法計算";
// cs0[endRow, ColCounter + 3].Value = "無法計算";
// cs0[endRow, ColCounter + 4].Value = "無法計算";
//}
#endregion
e.Result = wb;
BGW.ReportProgress(100);
};
#endregion
#region 計算DoWork完成百分比
BGW.ProgressChanged += delegate(object sender, ProgressChangedEventArgs e)
{
FISCA.Presentation.MotherForm.SetStatusBarMessage("班級體適能通知單產生中...", e.ProgressPercentage);
};
#endregion
#region 列印存檔
BGW.RunWorkerCompleted += delegate(object sender, RunWorkerCompletedEventArgs e)
{
//2016/7/21 不再顯示錯誤訊息
// 顯示錯誤訊息
//if (Error_List.Count > 0)
//{
// StringBuilder sb = new StringBuilder();
// foreach (var errorMsg in Error_List)
// {
// sb.AppendLine(errorMsg);
// }
// MsgBox.Show(sb.ToString());
//}
#region RunWorkerCompleted
Workbook workbook = e.Result as Workbook;
if (workbook == null)
{
return;
}
// 以後記得存Excel 都用新版的Xlsx，可以避免ㄧ些不必要的問題(EX: sheet 只能到1023張)
SaveFileDialog save = new SaveFileDialog();
save.Title = "另存新檔";
save.FileName = "全校體適能中等以上(含中等)各項目百分比統計表" + "(" + schoolYear + "學年度" + ")";
save.Filter = "Excel檔案 (*.Xlsx)|*.Xlsx|所有檔案 (*.*)|*.*";
if (save.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
try
{
workbook.Save(save.FileName, Aspose.Cells.SaveFormat.Xlsx);
System.Diagnostics.Process.Start(save.FileName);
}
catch
{
MessageBox.Show("檔案儲存失敗");
}
}
#endregion
};
#endregion
BGW.RunWorkerAsync();
this.Close();
}
