// 對Excel新增資料
private void SetDataDetail(Worksheet sheet, ExcelRowRecord rec, int RowIndex)
{
int columnIndex = 0;
foreach (string columnName in rec._Columns)
{
sheet.Cells[RowIndex, columnIndex++].PutValue(rec.GetColumnValue(columnName));
}
}
/// <summary>
/// 排序:年級/班級序號/班級名稱/學號/姓名
/// </summary>
private int SortData(ExcelRowRecord obj1, ExcelRowRecord obj2)
{
string seatno1 = obj1.GetColumnValue("年級").PadLeft(1, '0'); // 年級
seatno1 += obj1.GetOthersValue("班級序號").PadLeft(3, '0'); // 班級序號
seatno1 += obj1.GetColumnValue("班級名稱").PadLeft(20, '0'); // 班級名稱
seatno1 += obj1.GetColumnValue("學號/座號").PadLeft(15, '0'); // 學號
seatno1 += obj1.GetOthersValue("姓名").PadLeft(10, '0'); // 姓名
string seatno2 = obj2.GetColumnValue("年級").PadLeft(1, '0'); // 年級
seatno2 += obj2.GetOthersValue("班級序號").PadLeft(3, '0'); // 班級序號
seatno2 += obj2.GetColumnValue("班級名稱").PadLeft(20, '0'); // 班級名稱
seatno2 += obj2.GetColumnValue("學號/座號").PadLeft(15, '0'); // 學號
seatno2 += obj2.GetOthersValue("姓名").PadLeft(10, '0'); // 姓名
return(seatno1.CompareTo(seatno2));
}
// 主要邏輯區塊
void BGW_DoWork(object sender, DoWorkEventArgs e)
{
string fileName = (string)((object[])e.Argument)[0];
#region 取得需要的資料
int SchoolYear = integerInput1.Value;
bool ExportDegree = ckExportDegree.Checked;
// 取得選取的學生ID
List <string> studentIDList = K12.Presentation.NLDPanels.Student.SelectedSource;
// 取得學生的基本資料包括班級資料
List <DAO.StudentInfo> studentRecords = DAO.FDQuery.GetStudnetInfoByIDList(studentIDList);
// 取得學生的體適能資料
List <DAO.StudentFitnessRecord> FitnessRecords = DAO.StudentFitness.SelectByStudentIDListAndSchoolYear(studentIDList, SchoolYear);
// Excel的表頭
string[] ExcelColumnNames;
if (ExportDegree == true)
{
ExcelColumnNames = Global._ExcelDataDegreeTitle;
}
else
{
ExcelColumnNames = Global._ExcelDataTitle;
}
#endregion
// 所有資料得集合
List <ExcelRowRecord> excelRowRecords = new List <ExcelRowRecord>();
#region 把資料組合起來
// 學生
foreach (DAO.StudentInfo student in studentRecords)
{
foreach (DAO.StudentFitnessRecord fitnessRecord in FitnessRecords)
{
if (fitnessRecord.StudentID == student.Student_ID)
{
// 設定輸出的欄位
ExcelRowRecord rec = new ExcelRowRecord(ExcelColumnNames);
// 設定每個欄位的值
rec.SetDataForExport(student, fitnessRecord, ExportDegree);
excelRowRecords.Add(rec);
}
}
} // end of foreach (StudentRecord student in studentRecords)
#endregion
// 排序
excelRowRecords.Sort(SortData);
// 開起現有的樣板檔案
Workbook report = new Workbook();
MemoryStream ms = new MemoryStream(Properties.Resources.體適能資料上傳格式);
report.Open(ms);
Worksheet sheet = report.Worksheets[0];
sheet.Name = Global._SheetName;
// 輸出表頭
int RowIndex = _START_ROW - 1;
int colIndex = 0;
foreach (string columnName in ExcelColumnNames)
{
sheet.Cells[RowIndex, colIndex++].PutValue(columnName);
}
//填入資料
RowIndex = _START_ROW;
foreach (ExcelRowRecord excelRowRecord in excelRowRecords)
{
if (RowIndex > _MAX_ROW_COUNT)
{
break;
}
SetDataDetail(sheet, excelRowRecord, RowIndex);
RowIndex++;
}
// 儲存結果
e.Result = new object[] { report, fileName, RowIndex > _MAX_ROW_COUNT };
}
// 主要邏輯區塊
void BGW_DoWork(object sender, DoWorkEventArgs e)
{
string fileName = (string)((object[])e.Argument)[0];
#region 取得需要的資料
int SchoolYear = integerInput1.Value;
bool ExportDegree = ckExportDegree.Checked;
// 取得選取的學生ID
List <string> studentIDList = K12.Presentation.NLDPanels.Student.SelectedSource;
// 取得學生的基本資料包括班級資料
List <DAO.StudentInfo> studentRecords = DAO.FDQuery.GetStudnetInfoByIDList(studentIDList);
// 取得學生的體適能資料
List <DAO.StudentFitnessRecord> FitnessRecords = DAO.StudentFitness.SelectByStudentIDListAndSchoolYear(studentIDList, SchoolYear);
Dictionary <string, DAO.StudentFitnessRecord> FitnessRecordsDic = new Dictionary <string, DAO.StudentFitnessRecord>();
foreach (DAO.StudentFitnessRecord each in FitnessRecords)
{
if (!FitnessRecordsDic.ContainsKey(each.StudentID))
{
FitnessRecordsDic.Add(each.StudentID, each);
}
}
// Excel的表頭
string[] ExcelColumnNames;
if (ExportDegree == true)
{
ExcelColumnNames = Global._ExcelDataDegreeTitle;
}
else
{
ExcelColumnNames = Global._ExcelDataTitle;
}
#endregion
// 所有資料得集合
List <ExcelRowRecord> excelRowRecords = new List <ExcelRowRecord>();
#region 把資料組合起來
// 學生
foreach (DAO.StudentInfo student in studentRecords)
{
if (FitnessRecordsDic.ContainsKey(student.Student_ID))
{
DAO.StudentFitnessRecord fitnessRecord = FitnessRecordsDic[student.Student_ID];
// 設定輸出的欄位
ExcelRowRecord rec = new ExcelRowRecord(ExcelColumnNames);
// 設定每個欄位的值
rec.SetDataForExport(student, fitnessRecord, ExportDegree);
excelRowRecords.Add(rec);
}
else
{
// 設定輸出的欄位
ExcelRowRecord rec = new ExcelRowRecord(ExcelColumnNames);
// 設定每個欄位的值
rec.SetDataForExport(student, null, ExportDegree);
excelRowRecords.Add(rec);
}
} // end of foreach (StudentRecord student in studentRecords)
#endregion
// 排序
excelRowRecords.Sort(SortData);
// 開起現有的樣板檔案
Workbook report = new Workbook();
// 2016/7/19 穎驊修正，因應使用新的Aspose，存檔都建議使用xlsx，如果還是使用舊資源"102學年度體適能上傳資料格式.xls"，
//使用者在存檔的時候，會跳出"存檔類型與副檔名不相同"的錯誤，所以我把舊的檔案複製一份重新存檔成"體適能資料上傳格式_xlsx版_"，以後都會使用這個新檔案
// 目前應該是沒有甚麼問題。
//MemoryStream ms = new MemoryStream(Properties.Resources.體適能資料上傳格式);
MemoryStream ms = new MemoryStream(Properties.Resources.體適能資料上傳格式_xlsx版_);
report.Open(ms);
Worksheet sheet = report.Worksheets[0];
sheet.Name = Global._SheetName;
// 輸出表頭
int RowIndex = _START_ROW - 1;
int colIndex = 0;
foreach (string columnName in ExcelColumnNames)
{
sheet.Cells[RowIndex, colIndex++].PutValue(columnName);
}
//填入資料
RowIndex = _START_ROW;
foreach (ExcelRowRecord excelRowRecord in excelRowRecords)
{
if (RowIndex > _MAX_ROW_COUNT)
{
break;
}
SetDataDetail(sheet, excelRowRecord, RowIndex);
RowIndex++;
}
// 儲存結果
e.Result = new object[] { report, fileName, RowIndex > _MAX_ROW_COUNT };
}
// 主要邏輯區塊
void BGW_DoWork(object sender, DoWorkEventArgs e)
{
string fileName = (string)((object[])e.Argument)[0];
#region 取得需要的資料
int SchoolYear = integerInput1.Value;
bool ExportDegree = ckExportDegree.Checked;
// 取得選取的學生ID
List<string> studentIDList = K12.Presentation.NLDPanels.Student.SelectedSource;
// 取得學生的基本資料包括班級資料
List<DAO.StudentInfo> studentRecords = DAO.FDQuery.GetStudnetInfoByIDList(studentIDList);
// 取得學生的體適能資料
List<DAO.StudentFitnessRecord> FitnessRecords = DAO.StudentFitness.SelectByStudentIDListAndSchoolYear(studentIDList, SchoolYear);
// Excel的表頭
string[] ExcelColumnNames;
if(ExportDegree == true)
ExcelColumnNames = Global._ExcelDataDegreeTitle;
else
ExcelColumnNames = Global._ExcelDataTitle;
#endregion
// 所有資料得集合
List<ExcelRowRecord> excelRowRecords = new List<ExcelRowRecord>();
#region 把資料組合起來
// 學生
foreach (DAO.StudentInfo student in studentRecords)
{
foreach (DAO.StudentFitnessRecord fitnessRecord in FitnessRecords)
{
if (fitnessRecord.StudentID == student.Student_ID)
{
// 設定輸出的欄位
ExcelRowRecord rec = new ExcelRowRecord(ExcelColumnNames);
// 設定每個欄位的值
rec.SetDataForExport(student, fitnessRecord, ExportDegree);
excelRowRecords.Add(rec);
}
}
} // end of foreach (StudentRecord student in studentRecords)
#endregion
// 排序
excelRowRecords.Sort(SortData);
// 開起現有的樣板檔案
Workbook report = new Workbook();
MemoryStream ms = new MemoryStream(Properties.Resources.體適能資料上傳格式);
report.Open(ms);
Worksheet sheet = report.Worksheets[0];
sheet.Name = Global._SheetName;
// 輸出表頭
int RowIndex = _START_ROW - 1;
int colIndex = 0;
foreach (string columnName in ExcelColumnNames)
{
sheet.Cells[RowIndex, colIndex++].PutValue(columnName);
}
//填入資料
RowIndex = _START_ROW;
foreach (ExcelRowRecord excelRowRecord in excelRowRecords)
{
if (RowIndex > _MAX_ROW_COUNT)
{
break;
}
SetDataDetail(sheet, excelRowRecord, RowIndex);
RowIndex++;
}
// 儲存結果
e.Result = new object[] { report, fileName, RowIndex > _MAX_ROW_COUNT };
}
/// <summary>
/// 排序:年級/班級序號/班級名稱/學號/姓名
/// </summary>
private int SortData(ExcelRowRecord obj1, ExcelRowRecord obj2)
{
string seatno1 = obj1.GetColumnValue("年級").PadLeft(1, '0'); // 年級
seatno1 += obj1.GetOthersValue("班級序號").PadLeft(3, '0'); // 班級序號
seatno1 += obj1.GetColumnValue("班級名稱").PadLeft(20, '0'); // 班級名稱
seatno1 += obj1.GetColumnValue("學號/座號").PadLeft(15, '0'); // 學號
seatno1 += obj1.GetOthersValue("姓名").PadLeft(10, '0'); // 姓名
string seatno2 = obj2.GetColumnValue("年級").PadLeft(1, '0'); // 年級
seatno2 += obj2.GetOthersValue("班級序號").PadLeft(3, '0'); // 班級序號
seatno2 += obj2.GetColumnValue("班級名稱").PadLeft(20, '0'); // 班級名稱
seatno2 += obj2.GetColumnValue("學號/座號").PadLeft(15, '0'); // 學號
seatno2 += obj2.GetOthersValue("姓名").PadLeft(10, '0'); // 姓名
return seatno1.CompareTo(seatno2);
}
// 對Excel新增資料
private void SetDataDetail(Worksheet sheet, ExcelRowRecord rec, int RowIndex)
{
int columnIndex = 0;
foreach (string columnName in rec._Columns)
{
sheet.Cells[RowIndex, columnIndex++].PutValue(rec.GetColumnValue(columnName));
}
}
