public static bool GetPicturesFromExcelFile(string filepath, out Pictures[] pictures, out string error)
{
error = "";
pictures = null;
try
{
if (File.Exists(filepath) == false)
{
error = "文件不存在";
pictures = null;
return(false);
}
Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook();
workbook.Open(filepath);
pictures = new Pictures[workbook.Worksheets.Count];
for (int i = 0; i < workbook.Worksheets.Count; i++)
{
//pictures.Add();
pictures[i] = workbook.Worksheets[i].Pictures;
}
return(true);
}
catch (System.Exception e)
{
error = e.Message;
return(false);
}
}
/// <summary>
/// 导出数据
/// </summary>
/// <param name="filepath"></param>
/// <param name="datatable"></param>
/// <param name="error"></param>
/// <returns></returns>
public static bool ExcelFileToDataTable(string filepath, out DataTable datatable, out string error)
{
error = "";
datatable = null;
var p = System.Web.HttpContext.Current.Server.MapPath("~/") + filepath;
try
{
if (File.Exists(p) == false)
{
error = "文件不存在";
datatable = null;
return(false);
}
Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook();
workbook.Open(p);
Aspose.Cells.Worksheet worksheet = workbook.Worksheets[0];
datatable = worksheet.Cells.ExportDataTable(0, 0, worksheet.Cells.MaxRow + 1, worksheet.Cells.MaxColumn + 1);
return(true);
}
catch (System.Exception e)
{
error = e.Message;
return(false);
}
}
/// <summary>
/// Excel文件转换为DataTable.
/// </summary>
/// <param name="filepath">Excel文件的全路径</param>
/// <param name="datatable">DataTable:返回值</param>
/// <param name="error">错误信息:返回错误信息，没有错误返回""</param>
/// <returns>true:函数正确执行 false:函数执行错误</returns>
public static bool ExcelFileToDataTable(string filepath, out DataTable datatable, out string error)
{
error = "";
datatable = null;
try
{
if (File.Exists(filepath) == false)
{
error = "文件不存在";
datatable = null;
return(false);
}
Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook();
workbook.Open(filepath);
Aspose.Cells.Worksheet worksheet = workbook.Worksheets[0];
datatable = worksheet.Cells.ExportDataTable(0, 0, worksheet.Cells.MaxRow + 1, worksheet.Cells.MaxColumn + 1, true);
//-------------图片处理-------------
Aspose.Cells.Pictures pictures = worksheet.Pictures;
if (pictures.Count > 0)
{
string error2 = "";
if (InsertPicturesIntoDataTable(pictures, datatable, out datatable, out error2) == false)
{
error = error + error2;
}
}
return(true);
}
catch (System.Exception e)
{
error = e.Message;
return(false);
}
}
/// <summary>
/// Excel文件转换为DataTable.
/// </summary>
/// <param name="filepath">Excel文件的全路径</param>
/// <param name="datatable">DataTable:返回值</param>
/// <param name="error">错误信息:返回错误信息，没有错误返回""</param>
/// <returns>true:函数正确执行 false:函数执行错误</returns>
public static bool ExcelFileToDataSet1(string filepath, out DataSet dataset, out string error)
{
error = "";
dataset = null;
try
{
if (File.Exists(filepath) == false)
{
error = "文件不存在";
dataset = null;
return(false);
}
else
{
dataset = new DataSet();
}
Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook();
workbook.Open(filepath);
int tableCount = workbook.Worksheets.Count;
for (int i = 0; i < tableCount; i++)
{
DataTable datatable = new DataTable();
Aspose.Cells.Worksheet worksheet = workbook.Worksheets[i];
if (worksheet.Cells.Rows.Count > 1)
{
datatable = worksheet.Cells.ExportDataTable(0, 0, worksheet.Cells.MaxRow + 1, worksheet.Cells.MaxColumn + 1);
datatable.TableName = workbook.Worksheets[i].Name;
if (datatable.Rows.Count > 1)
{
for (int k = 0; k < datatable.Columns.Count; k++)
{
datatable.Columns[k].ColumnName = datatable.Rows[0][k].ToString();
}
//datatable.Rows[1].Delete();
datatable.Rows[0].Delete();
datatable.AcceptChanges();
}
dataset.Tables.Add(datatable.Copy());
dataset.Tables[dataset.Tables.Count - 1].TableName = workbook.Worksheets[i].Name;
}
}
return(true);
}
catch (System.Exception e)
{
error = e.Message;
return(false);
}
}
/// <summary>
/// Excel文件转换为DataTable.
/// </summary>
/// <param name="filepath">Excel文件的全路径</param>
/// <param name="datatable">DataTable:返回值</param>
/// <param name="error">错误信息:返回错误信息，没有错误返回""</param>
/// <returns>true:函数正确执行 false:函数执行错误</returns>
public bool ExcelFileToDataSet(string filepath, string guidStr, out DataSet dataset, out string error)
{
error = "";
//dataset = null;
try
{
if (File.Exists(filepath) == false)
{
error = "文件不存在";
dataset = null;
return(false);
}
else
{
dataset = new DataSet();
}
Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook();
workbook.Open(filepath);
int tableCount = workbook.Worksheets.Count;
for (int i = 0; i < tableCount; i++)
{
DataTable datatable = new DataTable();
Aspose.Cells.Worksheet worksheet = workbook.Worksheets[i];
if (worksheet.Cells.Rows.Count > 1)
{
datatable = worksheet.Cells.ExportDataTable(0, 0, worksheet.Cells.MaxRow + 1, worksheet.Cells.MaxColumn + 1);
datatable.TableName = workbook.Worksheets[i].Name;
if (datatable.Rows.Count > 2)
{
for (int k = 0; k < datatable.Columns.Count; k++)
{
datatable.Columns[k].ColumnName = datatable.Rows[1][k].ToString();
}
datatable.Rows[1].Delete();
datatable.Rows[0].Delete();
datatable.AcceptChanges();
}
dataset.Tables.Add(datatable.Copy());
dataset.Tables[dataset.Tables.Count - 1].TableName = workbook.Worksheets[i].Name;
}
}
return(true);
}
catch (System.Exception e)
{
logger.Error(string.Format("{0}:[{1}]:[{2}]:[{3}]", "ExcelFileToDataSet", filepath, "数据保存异常", e.Message + e.StackTrace));
DBLogger.Insert(DBLogger.GetLoggerInfo(filepath, e.Message + e.StackTrace, guidStr, 0));
error = e.Message;
throw e;
return(false);
}
}
/// <summary>
/// 将excel数据存到DataTable
/// </summary>
public DataTable ExcelToDataTable(string file)
{
DataTable dt = new System.Data.DataTable();
Aspose.Cells.Workbook workBook = new Aspose.Cells.Workbook();
workBook.Open(file, FileFormatType.Excel2007Xlsx);
Aspose.Cells.Worksheet sheet = workBook.Worksheets[1];
Aspose.Cells.Cells cells = sheet.Cells;
dt = cells.ExportDataTableAsString(0, 0, cells.MaxDataRow + 1, cells.MaxDataColumn + 1, true);
return(dt);
}
public static DataTable ExcelToDataTable2(string sheetName, string pathName = "")
{
if (pathName == "")
{
pathName = Application.StartupPath + "\\Config\\ACCESS.xlsx";
}
sheetName = sheetName.Substring(0, sheetName.Length - 1);//主要用于剔除在对excelSQL加的$
Aspose.Cells.Workbook workbook1 = new Aspose.Cells.Workbook();
workbook1.Open(pathName);
Aspose.Cells.Worksheet cellSheet1 = workbook1.Worksheets[sheetName];
Cells cells1 = cellSheet1.Cells;
return(cells1.ExportDataTableAsString(0, 0, cells1.MaxDataRow + 1, cells1.MaxDataColumn + 1, true));
}
public static bool ClearExcel(string filePath)
{
try
{
Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook();
workbook.Open(filePath);
workbook.Worksheets.Clear();
workbook.Save(filePath);
return(true);
}
catch (System.Exception e)
{
return(false);
}
}
/// <summary>
/// DataTableToExcel
/// DataTabel转换成Excel文件
///
/// </summary>
/// <param name="datatable">DataTable</param>
/// <param name="filepath">目标文件路径,Excel文件的全路径<</param>
/// <param name="error">错误信息:返回错误信息，没有错误返回""</param>
/// <returns>true:函数正确执行 false:函数执行错误</returns>
public static bool DataTableInsertToExcel(DataTable datatable, ArrayList colNameList, string fromfile, out string error, int beginRow, int beginColumn)
{
error = "";
if (datatable == null)
{
error = "DataTableToExcel:datatable 为空";
return false;
}
Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook();
workbook.Open(fromfile);
Aspose.Cells.Worksheet sheet = workbook.Worksheets[0];
Aspose.Cells.Cells cells = sheet.Cells;
//-------------插入数据-------------
int nRow = 0;
foreach (DataRow row in datatable.Rows)
{
nRow++;
try
{
cells.InsertRow(beginRow);
for (int i = 0; i < colNameList.Count; i++)
{
string colName = colNameList[i].ToString();
for (int j = 0; j < datatable.Columns.Count; j++)
{
if (colName == datatable.Columns[j].ColumnName)
{
object temp = row[datatable.Columns[j].ColumnName];
cells[beginRow, beginColumn + i].PutValue(row[datatable.Columns[j].ColumnName]);
break;
}
}
}
}
catch (System.Exception e)
{
error = error + " DataTableInsertToExcel: " + e.Message;
}
}
//-------------保存-------------
workbook.Save(fromfile);
return true;
}
/// <summary>
/// DataTableToExcel
/// DataTabel转换成Excel文件
///
/// </summary>
/// <param name="datatable">DataTable</param>
/// <param name="filepath">目标文件路径,Excel文件的全路径<</param>
/// <param name="error">错误信息:返回错误信息，没有错误返回""</param>
/// <returns>true:函数正确执行 false:函数执行错误</returns>
public static bool DataTableInsertToExcel(DataTable datatable, ArrayList colNameList, string fromfile, out string error, int beginRow, int beginColumn)
{
error = "";
if (datatable == null)
{
error = "DataTableToExcel:datatable 为空";
return(false);
}
Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook();
workbook.Open(fromfile);
Aspose.Cells.Worksheet sheet = workbook.Worksheets[0];
Aspose.Cells.Cells cells = sheet.Cells;
//-------------插入数据-------------
int nRow = 0;
foreach (DataRow row in datatable.Rows)
{
nRow++;
try
{
cells.InsertRow(beginRow);
for (int i = 0; i < colNameList.Count; i++)
{
string colName = colNameList[i].ToString();
for (int j = 0; j < datatable.Columns.Count; j++)
{
if (colName == datatable.Columns[j].ColumnName)
{
object temp = row[datatable.Columns[j].ColumnName];
cells[beginRow, beginColumn + i].PutValue(row[datatable.Columns[j].ColumnName]);
break;
}
}
}
}
catch (System.Exception e)
{
error = error + " DataTableInsertToExcel: " + e.Message;
}
}
//-------------保存-------------
workbook.Save(fromfile);
return(true);
}
public static bool DataTableToExcel(DataTable datatable, string filepath, out string error)
{
error = "";
try
{
if (datatable == null)
{
error = "DataTableToExcel:datatable 为空";
return(false);
}
Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook();
if (File.Exists(filepath))
{
workbook.Open(filepath);
}
Aspose.Cells.Worksheet sheet = workbook.Worksheets.Add(datatable.TableName);
Aspose.Cells.Cells cells = sheet.Cells;
int nRow = 0;
foreach (DataRow row in datatable.Rows)
{
nRow++;
try
{
for (int i = 0; i < datatable.Columns.Count; i++)
{
cells[nRow, i].PutValue(row[i]);
}
}
catch (System.Exception e)
{
error = error + " DataTableToExcel: " + e.Message;
}
}
workbook.Save(filepath, FileFormatType.Default);
return(true);
}
catch (System.Exception e)
{
error = error + " DataTableToExcel: " + e.Message;
return(false);
}
}
private List <List <CellEntity> > read()
{
String source = txtSourcePath.Text.Trim();
Aspose.Cells.Workbook excel = new Aspose.Cells.Workbook();
excel.Open(source, Aspose.Cells.FileFormatType.Excel2007Xlsx);
Worksheets sheets = excel.Worksheets;
List <List <CellEntity> > contents = new List <List <CellEntity> >();
for (int i = 0; i < sheets.Count; i++)
{
Worksheet sheet = sheets[i];
int startRow = Int32.Parse(startRowSelect.Value.ToString());
if (i == 0)
{
startRow = 0;
}
int endRow = sheet.Cells.MaxDataRow; //结束行,
int colCount = sheet.Cells.Columns.Count;
for (int rowIndex = startRow; rowIndex <= endRow; rowIndex++)
{
List <CellEntity> row = new List <CellEntity>();
for (int colIndex = 0; colIndex < colCount; colIndex++)
{
Cell cell = sheet.Cells[rowIndex, colIndex];
Style style = cell.GetStyle();
String value = cell.HtmlString;
CellEntity cellEntity = new CellEntity();
cellEntity.Style = style;
cellEntity.Value = value;
cellEntity.Cell = cell;
//String content = ConvertObjectToString(sheet.Cells[rowIndex, colIndex].Value);
row.Add(cellEntity);
}
contents.Add(row);
}
}
return(contents);
}
/// <summary>
/// Excel文件转换为DataTable.
/// </summary>
/// <param name="filepath">Excel文件的全路径</param>
/// <param name="datatable">DataTable:返回值</param>
/// <param name="error">错误信息:返回错误信息，没有错误返回""</param>
/// <returns>true:函数正确执行 false:函数执行错误</returns>
public bool ExcelFileToDataTable(string filepath, out DataTable datatable, out string error)
{
error = "";
datatable = null;
try
{
if (File.Exists(filepath) == false)
{
error = "文件不存在";
datatable = null;
return(false);
}
Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook();
workbook.Open(filepath);
Aspose.Cells.Worksheet worksheet = workbook.Worksheets[0];
datatable = worksheet.Cells.ExportDataTable(0, 0, worksheet.Cells.MaxRow + 1, worksheet.Cells.MaxColumn + 1);
return(true);
}
catch (System.Exception e)
{
error = e.Message;
return(false);
}
}
public static bool ExcelFileToDataTable(string filepath, out DataTable datatable, int iFirstRow, int iFirstCol, int rowNum, int colNum, out string error)
{
error = "";
datatable = null;
try
{
if (File.Exists(filepath) == false)
{
error = "文件不存在";
datatable = null;
return(false);
}
Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook();
workbook.Open(filepath);
Aspose.Cells.Worksheet worksheet = workbook.Worksheets[0];
datatable = worksheet.Cells.ExportDataTable(iFirstRow, iFirstCol, rowNum + 1, colNum + 1);
return(true);
}
catch (System.Exception e)
{
error = e.Message;
return(false);
}
}
public static bool GetPicturesFromExcelFile(string filepath, out Pictures[] pictures, out string error)
{
error = "";
pictures = null;
try
{
if (File.Exists(filepath) == false)
{
error = "文件不存在";
pictures = null;
return false;
}
Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook();
workbook.Open(filepath);
pictures = new Pictures[workbook.Worksheets.Count];
for (int i = 0; i < workbook.Worksheets.Count; i++)
{
//pictures.Add();
pictures[i] = workbook.Worksheets[i].Pictures;
}
return true;
}
catch (System.Exception e)
{
error = e.Message;
return false;
}
}
private void buttonX1_Click(object sender, EventArgs e)
{
this.textBoxX2.Text = "";
this.addMsg(" ==== 開始匯入課程 ===");
if (string.IsNullOrWhiteSpace(this.textBoxX1.Text))
return;
//1. 讀取所有科目 (供比對出科目代碼)
this.GetAllSubjects();
//2. 讀取某學年度學期的所有課程 (決定要新增或修改)
this.GetAllCourses();
//3. 開始讀取Excel 上的課程資料，
Workbook wb = new Aspose.Cells.Workbook();
wb.Open(this.textBoxX1.Text);
Worksheet ws = wb.Worksheets[1]; //課程
int rowIndex = 1;
while (ws.Cells[rowIndex, 0].Value != null)
{
string courseCode = GetCellValue(ws.Cells[rowIndex, 1].Value);
string classCode = GetCellValue(ws.Cells[rowIndex, 2].Value);
if (classCode.Length > 2)
classCode = classCode.Substring(1, 2);
string credit = GetCellValue(ws.Cells[rowIndex, 3].Value);
string courseName = GetCellValue(ws.Cells[rowIndex, 4].Value);
string semester = GetCellValue(ws.Cells[rowIndex, 5].Value);
string schoolyear = GetCellValue(ws.Cells[rowIndex, 6].Value);
string subjID = "0";
if (!this.dicSubjects.ContainsKey(courseCode))
this.addMsg( string.Format("匯入課程時找不到 subject ID, code : {0}, class name :{1} , course name : {2}, schoolyear: {3}, semester :{4} ",
courseCode, classCode, courseName, schoolyear, semester));
else
{
subjID = this.dicSubjects[courseCode].UID;
string key = string.Format("{0}_{1}", courseCode, classCode);
//檢查資料庫中是否已有存在？如果存再就修改，否則新增。
bool isCourseExisted = false;
if (this.dicCourseExts.ContainsKey(key))
{
UDT.CourseExt cExt = this.dicCourseExts[key];
if (this.dicCourses.ContainsKey(cExt.CourseID.ToString()))
isCourseExisted = true;
}
if (isCourseExisted)
{
UDT.CourseExt cExt = this.dicCourseExts[key];
cExt.ClassName = classCode;
cExt.SubjectCode = courseCode;
cExt.SubjectID = int.Parse(subjID);
List<ActiveRecord> recs = new List<ActiveRecord>();
recs.Add(cExt);
(new AccessHelper()).UpdateValues(recs);
SHCourseRecord course = this.dicCourses[cExt.CourseID.ToString()];
course.Credit = decimal.Parse(credit);
course.Name = string.Format("{0} {1}", courseName, classCode);
course.SchoolYear = int.Parse(schoolyear);
course.Semester = int.Parse(semester);
SHCourse.Update(course);
}
else
{
SHCourseRecord course = new SHCourseRecord();
course.Credit = decimal.Parse(credit);
course.Name = string.Format("{0} {1}", courseName, classCode);
course.SchoolYear = int.Parse(schoolyear);
course.Semester = int.Parse(semester);
string newID = SHCourse.Insert(course);
UDT.CourseExt cExt = new UDT.CourseExt();
cExt.CourseID = int.Parse(newID);
cExt.ClassName = classCode;
cExt.SubjectCode = courseCode;
cExt.SubjectID = int.Parse(subjID);
List<ActiveRecord> recs = new List<ActiveRecord>();
recs.Add(cExt);
(new AccessHelper()).InsertValues(recs);
}
} //if find subject id
rowIndex += 1;
this.lblStatus.Text = rowIndex.ToString() ;
Application.DoEvents();
} // while loop
//匯入修課學生
this.ImportSCAttendRecords();
}
// 處理新竹相關
private void ProcessHsinChu(XmlElement source, string location)
{
// 資料轉換
Dictionary <string, JHPermrec.UpdateRecord.DAL.StudBatchUpdateRecContentEntity> data = StudBatchUpdateRecEntity.ConvertGetContentData(source);
int tmpY, tmpM;
tmpY = DateTime.Now.Year;
tmpM = DateTime.Now.Month;
string tmpRptY, tmpRptM;
tmpRptY = (tmpY - 1911).ToString();
if (tmpM < 10)
{
tmpRptM = "0" + tmpM.ToString();
}
else
{
tmpRptM = tmpM.ToString();
}
Workbook template = new Workbook();
//從Resources把Template讀出來
template.Open(new MemoryStream(GDResources.JTransfer01ListTemplate_HsinChu), FileFormatType.Excel2003);
//要產生的excel檔
Workbook wb = new Aspose.Cells.Workbook();
wb.Open(new MemoryStream(GDResources.JTransfer01ListTemplate_HsinChu), FileFormatType.Excel2003);
Worksheet ws = wb.Worksheets[0];
//設定預設樣式
wb.DefaultStyle = template.DefaultStyle;
#region 初始變數
int rowj = 1;
int recCount = 0;
int totalRec = data.Count;
#endregion
rowj = 4;
wb.Worksheets[0].Cells[0, 0].PutValue(StudBatchUpdateRecEntity.GetContentSchoolName() + " 學 生 異 動 名 冊");
wb.Worksheets[0].Cells[1, 0].PutValue(StudBatchUpdateRecEntity.GetContentSchoolYear() + "學年度第" + StudBatchUpdateRecEntity.GetContentSemester() + "學期");
wb.Worksheets[0].Cells[1, 8].PutValue(tmpRptY + "年" + tmpRptM + "月填報");
//將xml資料填入至excel
foreach (StudBatchUpdateRecContentEntity sburce in data.Values)
{
recCount++;
wb.Worksheets[0].Cells[rowj, 0].PutValue(sburce.GetStudentNumber());
wb.Worksheets[0].Cells[rowj, 1].PutValue(sburce.GetName());
//wb.Worksheets[0].Cells[rowj, 2].PutValue(sburce.GetClassYear ());// 穎驊更新，檢查 # 6202 客服，發現年級欄位抓錯，不應該找取班級年級，而該用異動年級
wb.Worksheets[0].Cells[rowj, 2].PutValue(sburce.GetGradeYear());
wb.Worksheets[0].Cells[rowj, 3].PutValue(StudBatchUpdateRecEntity.GetContentSemester());
DateTime dt;
// 用西元轉換
if (DateTime.TryParse(sburce.GetUpdateDate(), out dt))
{
wb.Worksheets[0].Cells[rowj, 4].PutValue((dt.Year - 1911) + "");
wb.Worksheets[0].Cells[rowj, 5].PutValue((dt.Month + ""));
}
wb.Worksheets[0].Cells[rowj, 6].PutValue(sburce.GetUpdateCodeType());
wb.Worksheets[0].Cells[rowj, 7].PutValue(sburce.GetUpdateDescription());
wb.Worksheets[0].Cells[rowj, 8].PutValue(sburce.GetImportExportSchool());
rowj++;
//回報進度
ReportProgress((int)(((double)recCount * 100.0) / ((double)totalRec)));
}
// 畫表
Style st2 = wb.Styles[wb.Styles.Add()];
StyleFlag sf2 = new StyleFlag();
sf2.Borders = true;
st2.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
st2.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;
st2.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;
st2.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
int tmpMaxRow = 0, tmpMaxCol = 0;
for (int wbIdx1 = 0; wbIdx1 < wb.Worksheets.Count; wbIdx1++)
{
tmpMaxRow = wb.Worksheets[wbIdx1].Cells.MaxDataRow - 3;
tmpMaxCol = wb.Worksheets[wbIdx1].Cells.MaxDataColumn + 1;
wb.Worksheets[wbIdx1].Cells.CreateRange(4, 0, tmpMaxRow, tmpMaxCol).ApplyStyle(st2, sf2);
}
//儲存
wb.Save(location, FileFormatType.Excel2003);
}
// 處理新竹市樣版
private void ProcessHsinChu(System.Xml.XmlElement source, string location)
{
// 資料轉換
Dictionary <string, JHPermrec.UpdateRecord.DAL.StudBatchUpdateRecContentEntity> data = StudBatchUpdateRecEntity.ConvertGetContentData(source);
#region 建立 Excel
//從 Resources 將新生名冊template讀出來
Workbook template = new Workbook();
template.Open(new MemoryStream(GDResources.JEnrollmentListTemplate_HsinChu), FileFormatType.Excel2003);
//產生 excel
Workbook wb = new Aspose.Cells.Workbook();
wb.Open(new MemoryStream(GDResources.JEnrollmentListTemplate_HsinChu), FileFormatType.Excel2003);
#endregion
//#region 複製樣式-預設樣式、欄寬
//設定預設樣式
wb.DefaultStyle = template.DefaultStyle;
int recCount = 0;
int totalRec = data.Count;
Worksheet wst = wb.Worksheets[0];
wst.Name = "新生名冊";
string SchoolInfoAndSchoolYear = StudBatchUpdateRecEntity.GetContentSchoolName() + " " + StudBatchUpdateRecEntity.GetContentSchoolYear() + wst.Cells[0, 0].StringValue;
wst.Cells[0, 0].PutValue(SchoolInfoAndSchoolYear);
int row = 2;
#region 異動紀錄
//將xml資料填入至excel
foreach (StudBatchUpdateRecContentEntity sburce in data.Values)
{
wst.Cells[row, 0].PutValue(sburce.GetClassName());
wst.Cells[row, 1].PutValue(sburce.GetName());
wst.Cells[row, 2].PutValue(sburce.GetStudentNumber());
wst.Cells[row, 3].PutValue(sburce.GetIDNumber());
DateTime dt;
string strDate = "";
if (DateTime.TryParse(sburce.GetBirthday(), out dt))
{
strDate = "民國" + (dt.Year - 1911) + "年" + dt.Month + "月" + dt.Day + "日";
}
wst.Cells[row, 4].PutValue(strDate);
wst.Cells[row, 5].PutValue(sburce.GetGender());
wst.Cells[row, 6].PutValue(sburce.GetGuardian());
wst.Cells[row, 7].PutValue(sburce.GetAddress());
wst.Cells[row, 8].PutValue(sburce.GetPrimarySchoolName());
wst.Cells[row, 9].PutValue(sburce.GetComment());
row++;
recCount++;
//回報進度
ReportProgress((int)(((double)recCount * 100.0) / ((double)totalRec)));
}
#endregion
// 畫表
Style st2 = wb.Styles[wb.Styles.Add()];
StyleFlag sf2 = new StyleFlag();
sf2.Borders = true;
st2.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
st2.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;
st2.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;
st2.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
int tmpMaxRow = 0, tmpMaxCol = 0;
for (int wbIdx1 = 0; wbIdx1 < wb.Worksheets.Count; wbIdx1++)
{
tmpMaxRow = wb.Worksheets[wbIdx1].Cells.MaxDataRow;
tmpMaxCol = wb.Worksheets[wbIdx1].Cells.MaxDataColumn + 1;
wb.Worksheets[wbIdx1].Cells.CreateRange(1, 0, tmpMaxRow, tmpMaxCol).ApplyStyle(st2, sf2);
}
//儲存 Excel
wb.Save(location, FileFormatType.Excel2003);
}
// 處理高雄市樣版
private void ProcessKaoHsiung(System.Xml.XmlElement source, string location)
{
// 資料轉換
Dictionary <string, JHPermrec.UpdateRecord.DAL.StudBatchUpdateRecContentEntity> data = StudBatchUpdateRecEntity.ConvertGetContentData(source);
#region 建立 Excel
int peoTotalCount = 0; // 總人數
int peoBoyCount = 0; // 男生人數
int peoGirlCount = 0; // 女生人數
int tmpY, tmpM;
tmpY = DateTime.Now.Year;
tmpM = DateTime.Now.Month;
string tmpRptY, tmpRptM;
tmpRptY = (tmpY - 1911).ToString();
if (tmpM < 10)
{
tmpRptM = "0" + tmpM.ToString();
}
else
{
tmpRptM = tmpM.ToString();
}
string strPrintDate = UpdateRecordUtil.ChangeDate1911(DateTime.Now.ToString());
//從 Resources 將新生名冊template讀出來
Workbook template = new Workbook();
//template.Worksheets[0].PageSetup.
template.Open(new MemoryStream(GDResources.JEnrollmentListTemplate), FileFormatType.Excel2003);
//產生 excel
Workbook wb = new Aspose.Cells.Workbook();
wb.Open(new MemoryStream(GDResources.JEnrollmentListTemplate), FileFormatType.Excel2003);
#endregion
//設定預設樣式
wb.DefaultStyle = template.DefaultStyle;
int rowi = 0, rowj = 1;
int recCount = 0;
int totalRec = data.Count;
rowj = 5;
wb.Worksheets[0].Cells[rowi, 4].PutValue(StudBatchUpdateRecEntity.GetContentSchoolName() + " " + StudBatchUpdateRecEntity.GetContentSchoolYear() + "學年度第" + StudBatchUpdateRecEntity.GetContentSemester() + "學期");
wb.Worksheets[0].Cells[rowi, 9].PutValue("列印日期：" + strPrintDate);
wb.Worksheets[0].Cells[rowi + 1, 9].PutValue("列印時間：" + DateTime.Now.ToLongTimeString());
//將xml資料填入至excel
foreach (StudBatchUpdateRecContentEntity sburce in data.Values)
{
recCount++;
//將學生資料填入適當的位置內
wb.Worksheets[0].Cells[rowj, 0].PutValue(sburce.GetStudentNumber());
wb.Worksheets[0].Cells[rowj, 1].PutValue(sburce.GetClassName());
wb.Worksheets[0].Cells[rowj, 3].PutValue(sburce.GetName());
wb.Worksheets[0].Cells[rowj, 4].PutValue(sburce.GetIDNumber());
wb.Worksheets[0].Cells[rowj, 2].PutValue(sburce.GetGender());
if (sburce.GetGender() == "男")
{
peoBoyCount++;
}
if (sburce.GetGender() == "女")
{
peoGirlCount++;
}
if (sburce.GetBirthday() != "")
{
wb.Worksheets[0].Cells[rowj, 5].PutValue(UpdateRecordUtil.ChangeDate1911(sburce.GetBirthday()));
}
if (sburce.GetEnrollmentSchoolYear().Trim() != "")
{
wb.Worksheets[0].Cells[rowj, 6].PutValue(UpdateRecordUtil.getChineseYearStr(sburce.GetEnrollmentSchoolYear()));
wb.Worksheets[0].Cells[rowj, 7].PutValue(UpdateRecordUtil.getMonthStr(sburce.GetEnrollmentSchoolYear(), false));
}
wb.Worksheets[0].Cells[rowj, 8].PutValue(sburce.GetPrimarySchoolName());
wb.Worksheets[0].Cells[rowj, 9].PutValue(sburce.GetPermanentAddress());
wb.Worksheets[0].Cells[rowj, 10].PutValue(sburce.GetComment());
peoTotalCount++;
rowj++;
//回報進度
ReportProgress((int)(((double)recCount * 100.0) / ((double)totalRec)));
}
// 畫表
Style st2 = wb.Styles[wb.Styles.Add()];
StyleFlag sf2 = new StyleFlag();
sf2.Borders = true;
st2.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
st2.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;
st2.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;
st2.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
int tmpMaxRow = 0, tmpMaxCol = 0;
for (int wbIdx1 = 0; wbIdx1 < wb.Worksheets.Count; wbIdx1++)
{
tmpMaxRow = wb.Worksheets[wbIdx1].Cells.MaxDataRow - 4;
tmpMaxCol = wb.Worksheets[wbIdx1].Cells.MaxDataColumn + 1;
wb.Worksheets[wbIdx1].Cells.CreateRange(5, 0, tmpMaxRow, tmpMaxCol).ApplyStyle(st2, sf2);
}
// 統計人數
rowj++;
wb.Worksheets[0].Cells[rowj, 2].PutValue("男：" + peoBoyCount.ToString());
wb.Worksheets[0].Cells[rowj, 4].PutValue("女：" + peoGirlCount.ToString());
wb.Worksheets[0].Cells[rowj, 6].PutValue("總計：" + peoTotalCount.ToString());
// wb.Worksheets[0].Cells[rowj + 1, 0].PutValue("校長 教務主任 註冊組長 核對員");
wb.Worksheets[0].Cells[rowj + 1, 0].PutValue("核對員 註冊組長 教務主任 校長");
// 顯示頁
PageSetup pg = wb.Worksheets[0].PageSetup;
string tmp = "&12 " + tmpRptY + "年" + tmpRptM + "月 填報" + "共&N頁";
pg.SetHeader(2, tmp);
//儲存 Excel
wb.Save(location, FileFormatType.Excel2003);
}
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));
}
}
protected override void Build(XmlElement source, string location)
{
Workbook template = new Workbook();
//�qResources��TemplateŪ�X��
template.Open(new MemoryStream(Properties.Resources.TransferringStudentUpdateRecordListTemplate), FileFormatType.Excel2003);
//�n���ͪ�excel��
Workbook wb = new Aspose.Cells.Workbook();
wb.Open(new MemoryStream(Properties.Resources.TransferringStudentUpdateRecordListTemplate), FileFormatType.Excel2003);
Worksheet ws = wb.Worksheets[0];
//�������j�X��row
int next = 23;
//�������X��col
int col = 14;
//���row�ƥ�
int dataRow = 16;
//����
int index = 0;
//�d���d��
Range tempRange = template.Worksheets[0].Cells.CreateRange(0,23,false);
//�`�@�X�����ʬ���
int count = 0;
int totalRec = source.SelectNodes("�M��/���ʬ���").Count;
foreach (XmlNode list in source.SelectNodes("�M��"))
{
//���ͲM��Ĥ@��
ws.Cells.CreateRange(index, next, false).Copy(tempRange);
//Page
int currentPage = 1;
int totalPage = (list.ChildNodes.Count / dataRow) + 1;
//�g�J�N��
ws.Cells[index, 11].PutValue(source.SelectSingleNode("@�ǮեN��").InnerText + "-" + list.SelectSingleNode("@��O�N��").InnerText);
//�g�J�զW�B�Ǧ~�סB�Ǵ��B��O�B�~��
ws.Cells[index + 2, 1].PutValue(source.SelectSingleNode("@�ǮզW��").InnerText);
ws.Cells[index + 2, 5].PutValue(source.SelectSingleNode("@�Ǧ~��").InnerText + " �Ǧ~�� �� " + source.SelectSingleNode("@�Ǵ�").InnerText + " �Ǵ�");
ws.Cells[index + 2, 8].PutValue(list.SelectSingleNode("@��O").InnerText);
ws.Cells[index + 2, 12].PutValue(list.SelectSingleNode("@�~��").InnerText + "�~��");
//�g�J���
int recCount = 0;
int dataIndex = index + 6;
for (; currentPage <= totalPage; currentPage++)
{
//�ƻs����
if (currentPage + 1 <= totalPage)
{
ws.Cells.CreateRange(index + next, next, false).Copy(tempRange);
}
//��J���
for (int i = 0; i < dataRow && recCount < list.ChildNodes.Count; i++, recCount++)
{
//MsgBox.Show(i.ToString()+" "+recCount.ToString());
XmlNode rec = list.SelectNodes("���ʬ���")[recCount];
if(rec.SelectSingleNode("@�s�Ǹ�")!=null)
if(string.IsNullOrEmpty(rec.SelectSingleNode("@�s�Ǹ�").InnerText))
if(rec.SelectSingleNode("@�Ǹ�")!=null)
ws.Cells[dataIndex, 0].PutValue(rec.SelectSingleNode("@�Ǹ�").InnerText);
else
ws.Cells[dataIndex, 0].PutValue(rec.SelectSingleNode("@�s�Ǹ�").InnerText);
ws.Cells[dataIndex, 1].PutValue(rec.SelectSingleNode("@�m�W").InnerText);
ws.Cells[dataIndex, 2].PutValue(rec.SelectSingleNode("@�����Ҹ�").InnerText.ToString());
ws.Cells[dataIndex, 3].PutValue(rec.SelectSingleNode("@�ʧO�N��").InnerText);
ws.Cells[dataIndex, 4].PutValue(rec.SelectSingleNode("@�ʧO").InnerText);
ws.Cells[dataIndex, 5].PutValue(rec.SelectSingleNode("@�X�ͦ~���").InnerText);
ws.Cells[dataIndex, 6].PutValue(rec.SelectSingleNode("@��J�e�ǥ��_�Ǯ�").InnerText);
ws.Cells[dataIndex, 7].PutValue(rec.SelectSingleNode("@��J�e�ǥ��_�Ǹ�").InnerText + "\n" + rec.SelectSingleNode("@��J�e�ǥ��_��O").InnerText);
ws.Cells[dataIndex, 8].PutValue(BL.Util.ConvertDateStr2(rec.SelectSingleNode("@��J�e�ǥ��_�Ƭd���").InnerText) + "\n" + rec.SelectSingleNode("@��J�e�ǥ��_�Ƭd�帹").InnerText);
ws.Cells[dataIndex, 9].PutValue(rec.SelectSingleNode("@��J�e�ǥ��_�~��").InnerText);
ws.Cells[dataIndex, 10].PutValue(rec.SelectSingleNode("@���ʥN��").InnerText);
ws.Cells[dataIndex, 11].PutValue(rec.SelectSingleNode("@��]�Ψƶ�").InnerText);
ws.Cells[dataIndex, 12].PutValue(BL.Util.ConvertDateStr2(rec.SelectSingleNode("@���ʤ��").InnerText));
//ws.Cells[dataIndex, 13].PutValue(rec.SelectSingleNode("@�Ƶ�").InnerText);
if(rec.SelectSingleNode("@�S������N�X")!=null)
ws.Cells[dataIndex, 13].PutValue(rec.SelectSingleNode("@�S������N�X").InnerText);
dataIndex++;
count++;
//��J�e�ǥ��_�Ǯ�="�|������" ��J�e�ǥ��_�Ǹ�="010101" ��J�e�ǥ��_��O="��T��" ��J�e�ǥ��_�Ƭd���="90/09/09" ��J�e�ǥ��_�Ƭd�帹="�Ф��T�r��09200909090��" ��J�e�ǥ��_�~��="�@�W"
}
//�p��X�p
if (currentPage == totalPage)
{
ws.Cells.CreateRange(dataIndex, 0, 1, 2).Merge();
ws.Cells[dataIndex, 0].PutValue("�X�p " + list.ChildNodes.Count.ToString() + " �W");
}
//����
ws.Cells[index + next -1, 10].PutValue("�� " + currentPage + " ���A�@ " + totalPage + " ��");
ws.HPageBreaks.Add(index + next, col);
//���ޫ��V�U�@��
index += next;
dataIndex = index + 6;
//�^���i��
ReportProgress((int)(((double)count * 100.0) / ((double)totalRec)));
}
}
#region ��J��,�q�l�榡
Worksheet TemplateWb = wb.Worksheets["�q�l�榡�d��"];
Worksheet DyWb = wb.Worksheets[wb.Worksheets.Add()];
DyWb.Name = "�q�l�榡";
Range range_H = TemplateWb.Cells.CreateRange(0, 1, false);
Range range_R = TemplateWb.Cells.CreateRange(1, 1, false);
DyWb.Cells.CreateRange(0, 1, false).Copy(range_H);
int DyWb_index = 0;
DAL.DALTransfer DALTranser = new DAL.DALTransfer();
// �榡�ഫ
List<GovernmentalDocument.Reports.List.rpt_UpdateRecord> _data = DALTranser.ConvertRptUpdateRecord(source);
// �Ƨ� (�� �Z�O�B�~�šB��O�N�X�B�Ǹ�)
_data =(from data in _data orderby data.ClassType,data.GradeYear,data.DeptCode,data.StudentNumber select data).ToList ();
foreach (GovernmentalDocument.Reports.List.rpt_UpdateRecord rec in _data)
{
DyWb_index++;
//�C�W�[�@��,�ƻs�@��
DyWb.Cells.CreateRange(DyWb_index, 1, false).Copy(range_R);
//�Z�O
DyWb.Cells[DyWb_index, 0].PutValue(rec.ClassType);
//��O�N�X
DyWb.Cells[DyWb_index, 1].PutValue(rec.DeptCode);
//�Ǹ�
if(string.IsNullOrEmpty(rec.NewStudNumber))
DyWb.Cells[DyWb_index, 3].PutValue(rec.StudentNumber);
else
DyWb.Cells[DyWb_index, 3].PutValue(rec.NewStudNumber);
//�m�W
DyWb.Cells[DyWb_index, 4].PutValue(rec.Name);
//�����Ҧr��
DyWb.Cells[DyWb_index, 5].PutValue(rec.IDNumber);
//��1
DyWb.Cells[DyWb_index, 6].PutValue(rec.Comment1);
//�ʧO�N�X
DyWb.Cells[DyWb_index, 7].PutValue(rec.GenderCode);
//�X�ͤ��
DyWb.Cells[DyWb_index, 8].PutValue(rec.Birthday);
//�S������N�X
DyWb.Cells[DyWb_index, 9].PutValue(rec.SpecialStatusCode);
//�~��
DyWb.Cells[DyWb_index, 10].PutValue(rec.GradeYear);
//���ʭ�]�N�X
DyWb.Cells[DyWb_index, 11].PutValue(rec.UpdateCode);
//��J���
DyWb.Cells[DyWb_index, 12].PutValue(rec.UpdateDate);
// ��J�����O
DyWb.Cells[DyWb_index, 13].PutValue(rec.TransferStatus);
//��Ƭd���
DyWb.Cells[DyWb_index, 14].PutValue(rec.PreviousSchoolLastADDate);
//��Ƭd��r(*)
DyWb.Cells[DyWb_index, 15].PutValue(rec.PreviousSchoolLastADDoc);
//��Ƭd�帹(*)
DyWb.Cells[DyWb_index, 16].PutValue(rec.PreviousSchoolLastADNum);
//��ǮեN�X(*)
DyWb.Cells[DyWb_index, 17].PutValue(rec.PreviousSchoolCode);
//���O�N�X
DyWb.Cells[DyWb_index, 18].PutValue(rec.PreviousDeptCode);
//��Ǹ�
DyWb.Cells[DyWb_index, 19].PutValue(rec.PreviousStudentNumber);
// ���䴩�µ��c�~�ŻP�Ǵ��O�Τ�r�r��@�W�A�ҥH�o�˼g
//��~��
DyWb.Cells[DyWb_index, 20].PutValue(Getyear(rec.PreviousGradeYear));
//��Ǵ�
DyWb.Cells[DyWb_index, 21].PutValue(Getsemester(rec.PreviousSemester));
//�Ƶ�����
DyWb.Cells[DyWb_index, 22].PutValue(rec.Comment);
}
//foreach (XmlElement Record in source.SelectNodes("�M��/���ʬ���"))
//{
// DyWb_index++;
// //�C�W�[�@��,�ƻs�@��
// DyWb.Cells.CreateRange(DyWb_index, 1, false).Copy(range_R);
// //�Z�O
// DyWb.Cells[DyWb_index, 0].PutValue(Record.GetAttribute("�Z�O"));
// //��O�N�X
// DyWb.Cells[DyWb_index, 1].PutValue((Record.ParentNode as XmlElement).GetAttribute("��O�N��"));
// //�Ǹ�
// DyWb.Cells[DyWb_index, 2].PutValue(Record.GetAttribute("�s�Ǹ�"));
// //�m�W
// DyWb.Cells[DyWb_index, 3].PutValue(Record.GetAttribute("�m�W"));
// //�����Ҧr��
// DyWb.Cells[DyWb_index, 4].PutValue(Record.GetAttribute("�����Ҹ�"));
// //��1
// DyWb.Cells[DyWb_index, 5].PutValue(Record.GetAttribute("��1"));
// //�ʧO�N�X
// DyWb.Cells[DyWb_index, 6].PutValue(Record.GetAttribute("�ʧO�N��"));
// //�X�ͤ��
// DyWb.Cells[DyWb_index, 7].PutValue(GetBirthdateWithoutSlash(Record.GetAttribute("�X�ͦ~���")));
// //�S������N�X
// DyWb.Cells[DyWb_index, 8].PutValue(Record.GetAttribute("�S������N�X"));
// //�~��
// DyWb.Cells[DyWb_index, 9].PutValue((Record.ParentNode as XmlElement).GetAttribute("�~��"));
// //���ʭ�]�N�X
// DyWb.Cells[DyWb_index, 10].PutValue(Record.GetAttribute("���ʥN��"));
// //��J���
// DyWb.Cells[DyWb_index, 11].PutValue(GetBirthdateWithoutSlash(Record.GetAttribute("���ʤ��")));
// //��Ƭd���
// DyWb.Cells[DyWb_index, 12].PutValue(GetBirthdateWithoutSlash(Record.GetAttribute("��J�e�ǥ��_�Ƭd���")));
// //��Ƭd��r(*)
// DyWb.Cells[DyWb_index, 13].PutValue(GetNumAndSrt1(Record.GetAttribute("��J�e�ǥ��_�Ƭd�帹")));
// //��Ƭd�帹(*)
// DyWb.Cells[DyWb_index, 14].PutValue(GetNumAndSrt2(Record.GetAttribute("��J�e�ǥ��_�Ƭd�帹")));
// //��ǮեN�X(*)
// DyWb.Cells[DyWb_index, 15].PutValue(Record.GetAttribute("��J�e�ǥ��_�Ǯ�"));
// //���O�N�X
// DyWb.Cells[DyWb_index, 16].PutValue(Record.GetAttribute("��J�e�ǥ��_��O"));
// //��Ǹ�
// DyWb.Cells[DyWb_index, 17].PutValue(Record.GetAttribute("��J�e�ǥ��_�Ǹ�"));
// //��~��
// DyWb.Cells[DyWb_index, 18].PutValue(Getyear(Record.GetAttribute("��J�e�ǥ��_�~��")));
// //��Ǵ�
// DyWb.Cells[DyWb_index, 19].PutValue(Getsemester(Record.GetAttribute("��J�e�ǥ��_�~��")));
// //�Ƶ�����
// DyWb.Cells[DyWb_index, 20].PutValue(Record.GetAttribute("�Ƶ�"));
//}
DyWb.AutoFitColumns();
wb.Worksheets.RemoveAt("�q�l�榡�d��");
#endregion
wb.Worksheets.ActiveSheetIndex = 0;
//�x�s
wb.Save(location, FileFormatType.Excel2003);
}
protected override void Build(System.Xml.XmlElement source, string location)
{
#region 建立 Excel
//從 Resources 將新生名冊template讀出來
Workbook template = new Workbook();
template.Open(new MemoryStream(Properties.Resources.EnrollmentListTemplate), FileFormatType.Excel2003);
//產生 excel
Workbook wb = new Aspose.Cells.Workbook();
wb.Open(new MemoryStream(Properties.Resources.EnrollmentListTemplate), FileFormatType.Excel2003);
#endregion
#region 複製樣式-預設樣式、欄寬
//設定預設樣式
wb.DefaultStyle = template.DefaultStyle;
//複製樣版中前18個 Column(欄寬)
for (int m = 0; m < 18; m++)
{
/*
* 複製 template的第一個 Sheet的第 m個 Column
* 到 wb的第一個 Sheet的第 m個 Column
*/
wb.Worksheets[0].Cells.CopyColumn(template.Worksheets[0].Cells, m, m);
}
#endregion
#region 初始變數
/******************************
* rowi 填入學校資料用
* rowj 填入學生資料用
* num 計算清單份數
* numcount 計算每份清單頁數
* j 計算所產生清單頁數
* x 判斷個數是否為20被數用
******************************/
int rowi = 0, rowj = 1, num = source.SelectNodes("清單").Count, numcount = 1, j = 0;
bool x = false;
int recCount = 0;
int totalRec = source.SelectNodes("清單/異動紀錄").Count;
#endregion
foreach (XmlNode list in source.SelectNodes("清單"))
{
int i = 0;
#region 找出資料總數及判斷
//找出資料總數方便評估進度
int count = list.SelectNodes("異動紀錄").Count;
//判斷個數是否為20被數
if (count % 20 == 0)
{
x = true;
}
#endregion
#region 異動紀錄
// 所在地代碼對照
Dictionary<string, string> gLocationCodeDict = new Dictionary<string, string>();
foreach (XElement elm in BL.Get.JHSchoolList().Elements("學校"))
{
string code=elm.Attribute("所在地代碼").Value;
if(!gLocationCodeDict.ContainsKey(code))
gLocationCodeDict.Add(code,elm.Attribute("所在地").Value);
}
//將xml資料填入至excel
foreach (XmlNode st in list.SelectNodes("異動紀錄"))
{
recCount++;
if (i % 20 == 0)
{
#region 複製樣式-欄高、範圍
//複製樣版中前27個 Row(欄高)
//for (int m = 0; m < 27; m++)
//{
// /*
// * 複製 template的第一個 Sheet的第m個 Row
// * 到 wb的第一個 Sheet的第(j * 27) + m個 Row
// */
// wb.Worksheets[0].Cells.CopyRow(template.Worksheets[0].Cells, m, (j * 27) + m);
//}
/*
* 複製Style(包含儲存格合併的資訊)
* 先用CreateRange()選取要複製的Range("A1", "R27")
* 再用CopyStyle複製另一個Range中的格式
*/
Range range = template.Worksheets[0].Cells.CreateRange(0, 27, false);
int t = j * 27;
wb.Worksheets[0].Cells.CreateRange(t, 27, false).Copy(range);
#endregion
#region 填入學校資料
//將學校資料填入適當的位置內
wb.Worksheets[0].Cells[rowi, 13].PutValue(source.SelectSingleNode("@學校代號").InnerText);
wb.Worksheets[0].Cells[rowi, 16].PutValue(list.SelectSingleNode("@科別代號").InnerText);
wb.Worksheets[0].Cells[rowi + 2, 2].PutValue(source.SelectSingleNode("@學校名稱").InnerText);
wb.Worksheets[0].Cells[rowi + 2, 6].PutValue(Convert.ToInt32(source.SelectSingleNode("@學年度").InnerText));
wb.Worksheets[0].Cells[rowi + 2, 9].PutValue(Convert.ToInt32(source.SelectSingleNode("@學期").InnerText));
wb.Worksheets[0].Cells[rowi + 2, 12].PutValue(list.SelectSingleNode("@科別").InnerText);
#endregion
if (j > 0)
{
//插入分頁(在 j * 27 跟 (j * 27) +1 中間，R跟S中間)
wb.Worksheets[0].HPageBreaks.Add(j * 27, 18);
rowj += 7;
}
else
{
rowj = 5;
}
rowi += 27;
j++;
#region 顯示頁數
//顯示頁數
if (x != true)
{
wb.Worksheets[0].Cells[(27 * (j - 1)) + 26, 13].PutValue("第" + numcount + "頁，共" + Math.Ceiling((double)count / 20) + "頁");
}
else
{
wb.Worksheets[0].Cells[(27 * (j - 1)) + 26, 13].PutValue("第" + numcount + "頁，共" + (Math.Ceiling((double)count / 20) + 1) + "頁");
}
numcount++;
#endregion
}
#region 填入學生資料
//將學生資料填入適當的位置內
wb.Worksheets[0].Cells[rowj, 1].PutValue(st.SelectSingleNode("@學號").InnerText);
wb.Worksheets[0].Cells[rowj, 3].PutValue(st.SelectSingleNode("@姓名").InnerText);
wb.Worksheets[0].Cells[rowj, 5].PutValue(st.SelectSingleNode("@身分證號").InnerText);
try
{
wb.Worksheets[0].Cells[rowj, 6].PutValue(Convert.ToInt32(st.SelectSingleNode("@性別代號").InnerText));
}
catch (Exception)
{}
wb.Worksheets[0].Cells[rowj, 7].PutValue(st.SelectSingleNode("@性別").InnerText);
wb.Worksheets[0].Cells[rowj, 8].PutValue(st.SelectSingleNode("@出生年月日").InnerText);
string stra1 = "", stra2 = "";
if (st.SelectSingleNode("@畢業國中所在縣市代號") != null)
stra1 = st.SelectSingleNode("@畢業國中所在縣市代號").InnerText;
if (st.SelectSingleNode("@入學資格代號") != null)
stra2 = st.SelectSingleNode("@入學資格代號").InnerText;
wb.Worksheets[0].Cells[rowj, 11].PutValue(stra1 + "\n" + stra2);
string data = "", data1 = "";
string uCode = "";
if (st.SelectSingleNode("@異動代號") == null)
{
if (st.SelectSingleNode("@異動代碼") != null)
uCode = st.SelectSingleNode("@異動代碼").InnerText;
}
else
uCode = st.SelectSingleNode("@異動代號").InnerText;
if (uCode == "001")
data1 = "畢業";
if (uCode == "003")
data1 = "結業";
if (uCode == "004")
data1 = "修滿";
if(st.SelectSingleNode("@畢業國中所在縣市代號")!=null)
if (!string.IsNullOrEmpty(st.SelectSingleNode("@畢業國中所在縣市代號").InnerText))
{
string code=st.SelectSingleNode("@畢業國中所在縣市代號").InnerText;
if (gLocationCodeDict.ContainsKey(code))
{
data = gLocationCodeDict[code];
}
}
wb.Worksheets[0].Cells[rowj, 12].PutValue(data+st.SelectSingleNode("@畢業國中").InnerText+data1);
//wb.Worksheets[0].Cells[rowj, 14].PutValue(st.SelectSingleNode("@備註").InnerText);
if(st.SelectSingleNode("@特殊身份代碼")!=null)
wb.Worksheets[0].Cells[rowj, 14].PutValue(st.SelectSingleNode("@特殊身份代碼").InnerText);
#endregion
i++;
rowj++;
//回報進度
ReportProgress((int)(((double)recCount * 100.0) / ((double)totalRec)));
}
#endregion
#region 若個數為20倍數，處理單一頁面
if (x == true)
{
#region 複製樣式-欄高、範圍
//複製樣版前27個 Row(欄高)
//for (int m = 0; m < 27; m++)
//{
// /*
// * 複製 template的第一個 Sheet的第m個 Row
// * 到 wb的第一個 Sheet的第(j * 27) + m個 Row
// */
// wb.Worksheets[0].Cells.CopyRow(template.Worksheets[0].Cells, m, (j * 27) + m);
//}
/*
* 複製Style(包含儲存格合併的資訊)
* 先用CreateRange()選取要複製的Range("A1", "R27")
* 再用CopyStyle複製另一個Range中的格式
*/
Range range = template.Worksheets[0].Cells.CreateRange(0, 27, false);
int t= j * 27;
wb.Worksheets[0].Cells.CreateRange(t, 27, false).Copy(range);
#endregion
#region 填入學校資料
//將學校資料填入適當的位置內
wb.Worksheets[0].Cells[rowi, 13].PutValue(source.SelectSingleNode("@學校代號").InnerText);
wb.Worksheets[0].Cells[rowi, 16].PutValue(list.SelectSingleNode("@科別代號").InnerText);
wb.Worksheets[0].Cells[rowi + 2, 2].PutValue(source.SelectSingleNode("@學校名稱").InnerText);
wb.Worksheets[0].Cells[rowi + 2, 6].PutValue(Convert.ToInt32(source.SelectSingleNode("@學年度").InnerText));
wb.Worksheets[0].Cells[rowi + 2, 9].PutValue(Convert.ToInt32(source.SelectSingleNode("@學期").InnerText));
wb.Worksheets[0].Cells[rowi + 2, 12].PutValue(list.SelectSingleNode("@科別").InnerText);
#endregion
if (j > 0)
{
//插入分頁(在i跟i+1中間，O跟P中間)
wb.Worksheets[0].HPageBreaks.Add(j * 27, 18);
rowj += 7;
}
rowi += 27;
j++;
#region 顯示頁數
//顯示頁數
wb.Worksheets[0].Cells[(27 * (j - 1)) + 26, 13].PutValue("第" + numcount + "頁，共" + (Math.Ceiling((double)count / 20) + 1) + "頁");
numcount++;
#endregion
}
#endregion
#region 統計人數
//填入統計人數
wb.Worksheets[0].Cells[rowj, 1].PutValue("合 計 ");
wb.Worksheets[0].Cells[rowj, 3].PutValue(count.ToString() + " 名");
#endregion
wb.Worksheets[0].HPageBreaks.Add(j * 27, 18);
#region 設定變數
//調整新清單所使用變數
numcount = 1;
rowj = (27 * j) - 2;
rowi = (27 * j);
x = false;
#endregion
}
#region 97中辦格式
Worksheet mingdao = wb.Worksheets["電子格式99"];
Worksheet mdws = wb.Worksheets[wb.Worksheets.Add()];
mdws.Name = "電子格式";
Range range_header = mingdao.Cells.CreateRange(0, 1, false);
Range range_row = mingdao.Cells.CreateRange(1, 1, false);
mdws.Cells.CreateRange(0, 1, false).Copy(range_header);
int mdws_index = 0;
foreach (XmlElement record in source.SelectNodes("清單/異動紀錄"))
{
mdws_index++;
mdws.Cells.CreateRange(mdws_index, 1, false).Copy(range_row);
mdws.Cells[mdws_index, 0].PutValue(record.GetAttribute("班別"));
mdws.Cells[mdws_index, 1].PutValue((record.ParentNode as XmlElement).GetAttribute("科別代號"));
mdws.Cells[mdws_index, 2].PutValue("");
mdws.Cells[mdws_index, 3].PutValue(record.GetAttribute("學號"));
mdws.Cells[mdws_index, 4].PutValue(record.GetAttribute("姓名"));
mdws.Cells[mdws_index, 5].PutValue(record.GetAttribute("身分證號"));
mdws.Cells[mdws_index, 6].PutValue(record.GetAttribute("註1"));
mdws.Cells[mdws_index, 7].PutValue(record.GetAttribute("性別代號"));
mdws.Cells[mdws_index, 8].PutValue(GetBirthdateWithoutSlash(record.GetAttribute("出生年月日")));
mdws.Cells[mdws_index, 9].PutValue(record.GetAttribute("特殊身份代碼")); //原為抓取註備欄位值
mdws.Cells[mdws_index, 10].PutValue(record.GetAttribute("入學資格代號"));
//mdws.Cells[mdws_index, 11].PutValue(record.GetAttribute("畢業國中所在縣市代號"));
string GradeSchoolCode = record.GetAttribute("畢業國中代碼");
//if (GradeSchoolCode.Length > 3)
// mdws.Cells[mdws_index, 12].PutValue(GradeSchoolCode.Substring(2, 1));
// mdws.Cells[mdws_index, 13].PutValue(record.GetAttribute("畢業國中"));
mdws.Cells[mdws_index, 11].PutValue(GradeSchoolCode);
mdws.Cells[mdws_index, 12].PutValue(record.GetAttribute("入學資格證明文件"));
mdws.Cells[mdws_index, 13].PutValue(record.GetAttribute("備註"));
}
mdws.AutoFitColumns();
mdws.Cells.SetColumnWidth(5, 8.5);
//mdws.Cells.SetColumnWidth(11, 20);
wb.Worksheets.RemoveAt("電子格式99");
wb.Worksheets.ActiveSheetIndex = 0;
#endregion
wb.Worksheets.ActiveSheetIndex = 0;
//儲存 Excel
wb.Save(location, FileFormatType.Excel2003);
}
//导入excel，从流中读取
private void improtExcel(System.IO.Stream excelStream)
{
Aspose.Cells.Workbook wk = new Aspose.Cells.Workbook(excelStream);
wk.Open(excelStream);// 这儿是需要导入的文件
DataTable dt = new DataTable();
DataColumn dc1 = new DataColumn("col1", typeof(string));
DataColumn dc2 = new DataColumn("col2", typeof(string));
DataColumn dc3 = new DataColumn("col3", typeof(string));
DataColumn dc4 = new DataColumn("col4", typeof(string));
DataColumn dc5 = new DataColumn("col5", typeof(string));
dt.Columns.AddRange(new DataColumn[] { dc1, dc2, dc3, dc4, dc5 });
int totalRowCount = wk.Worksheets[0].Cells.Rows.Count;
for (int i = 0; i < totalRowCount; i++)//用于EXCEL数据的等号，可以自行固定如：149，也可以自行去读取它的等号；
{
DataRow dr = dt.NewRow();
dr["col1"] = wk.Worksheets[0].Cells[i, 0].Value;//读取文件里面对应的信息
dr["col2"] = wk.Worksheets[0].Cells[i, 1].Value;
dr["col3"] = wk.Worksheets[0].Cells[i, 2].Value;
dr["col4"] = wk.Worksheets[0].Cells[i, 3].Value;
dr["col5"] = wk.Worksheets[0].Cells[i, 4].Value;
dt.Rows.Add(dr);
}
GridView2.DataSource = dt;
GridView2.DataBind();
}
private void button3_Click(object sender, EventArgs e)
{
OpenFileDialog dialog = new OpenFileDialog();
dialog.Title = "上傳樣板";
dialog.Filter = "Word檔案 (*.doc)|*.doc|Excel檔案 (*.xls)|*.xls|所有檔案 (*.*)|*.*";
if (dialog.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
try
{
if (dialog.FileName.IndexOf(".doc") > -1)
{
TemplateWord = new Aspose.Words.Document(dialog.FileName);
IsWord = true;
}
if (dialog.FileName.IndexOf(".xls") > -1)
{
TemplateExcel = new Aspose.Cells.Workbook();
TemplateExcel.Open(dialog.FileName);
IsWord = false;
}
//Template = new Aspose.Words.Document(dialog.FileName);
string[] fileNames = dialog.FileName.Split('\\');
textBox1.Text = fileNames[fileNames.Length - 1];
}
catch
{
MessageBox.Show("樣板開啟失敗");
}
}
}
public static void XuatDuLieuRaExcel(int iRowPara,
int iColumnPara,
string strSubHeaderPara,
System.Data.DataTable tblBangDuLieuPara,
string strTemplateNamePara)
{
//Đường dẫn file template
string strSourceFilePri = string.Format("{0}{1}{2}",
System.Windows.Forms.Application.StartupPath,
PATH_TEMPLATES, strTemplateNamePara);
SaveFileDialog saveFileDialogPri = new SaveFileDialog();
saveFileDialogPri.Filter = "Excel files (*.xls)|*.xls|All files (*.*)|*.*";
saveFileDialogPri.FilterIndex = 1;
if (saveFileDialogPri.ShowDialog() == DialogResult.OK)
{
FileStream streamTemp = new FileStream(strSourceFilePri, FileMode.Open);
Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook();
workbook.Open(streamTemp);
workbook.Worksheets.Add();
Aspose.Cells.Worksheet worksheet = workbook.Worksheets[0];
//Set cell store subHeader
Aspose.Cells.Cells cellHeader = worksheet.Cells;
//cellHeader.Merge(2, 0, 1, tblBangDuLieuPara.Columns.Count);
worksheet.Cells["A3"].PutValue(strSubHeaderPara);
//worksheet.IsGridlinesVisible = false;
worksheet.Cells.ImportDataTable(tblBangDuLieuPara, false, iRowPara, iColumnPara, tblBangDuLieuPara.Rows.Count, tblBangDuLieuPara.Columns.Count);
//Formatting for cells store database
for (int i = 0; i < tblBangDuLieuPara.Rows.Count; i++)
{
for (int j = 0; j < tblBangDuLieuPara.Columns.Count; j++)
{
Aspose.Cells.Cell cell = worksheet.Cells[iRowPara + i, j];
workbook.Styles.Add();
Aspose.Cells.Style style = cell.GetStyle();
style.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;
style.Borders[BorderType.BottomBorder].Color = Color.Silver;
style.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
style.Borders[BorderType.TopBorder].Color = Color.Silver;
style.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
style.Borders[BorderType.LeftBorder].Color = Color.Silver;
style.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;
style.Borders[BorderType.RightBorder].Color = Color.Silver;
cell.SetStyle(style);
}
}
//worksheet.AutoFitColumns();
//Save excel file
workbook.Save(saveFileDialogPri.FileName, FileFormatType.Default);
MessageBox.Show(WorkingContext.LangManager.GetString("frmRestSheet_ExportExcel_Messa"),
WorkingContext.LangManager.GetString("Message"),
MessageBoxButtons.OK, MessageBoxIcon.Information);
streamTemp.Close();
if (File.Exists(saveFileDialogPri.FileName))
{
Process.Start(saveFileDialogPri.FileName);
}
}
}
protected override void Build(System.Xml.XmlElement source, string location)
{
#region 建立 Excel
//從 Resources 將學籍異動名冊template讀出來
Workbook template = new Workbook();
template.Open(new MemoryStream(Properties.Resources.ExtendingStudentListTemplate), FileFormatType.Excel2003);
//產生 excel
Workbook wb = new Aspose.Cells.Workbook();
wb.Open(new MemoryStream(Properties.Resources.ExtendingStudentListTemplate), FileFormatType.Excel2003);
#endregion
#region 複製樣式-預設樣式、欄寬
//設定預設樣式
wb.DefaultStyle = template.DefaultStyle;
//複製樣版中前18個 Column(欄寬)
for (int m = 0; m < 18; m++)
{
/*
* 複製 template的第一個 Sheet的第 m個 Column
* 到 wb的第一個 Sheet的第 m個 Column
*/
wb.Worksheets[0].Cells.CopyColumn(template.Worksheets[0].Cells, m, m);
}
#endregion
#region 初始變數
/******************************
* rowi 填入學校資料用
* rowj 填入學生資料用
* num 計算清單份數
* numcount 計算每份清單頁數
* j 計算所產生清單頁數
* x 判斷個數是否為20被數用
******************************/
int rowi = 0, rowj = 1, num = source.SelectNodes("清單").Count, numcount = 1, j = 0;
bool x = false;
int recCount = 0;
int totalRec = source.SelectNodes("清單/異動紀錄").Count;
#endregion
foreach (XmlNode list in source.SelectNodes("清單"))
{
int i = 0;
#region 找出資料總數及判斷
//找出資料總數方便評估進度
int count = list.SelectNodes("異動紀錄").Count;
//判斷個數是否為20被數
if (count % 20 == 0)
{
x = true;
}
#endregion
#region 異動紀錄
//將xml資料填入至excel
foreach (XmlNode st in list.SelectNodes("異動紀錄"))
{
recCount++;
if (i % 20 == 0)
{
#region 複製樣式-欄高、範圍
//複製樣版中前287個 Row(欄高)
//for (int m = 0; m < 28; m++)
//{
// /*
// * 複製 template的第一個 Sheet的第m個 Row
// * 到 wb的第一個 Sheet的第(j * 28) + m個 Row
// */
// wb.Worksheets[0].Cells.CopyRow(template.Worksheets[0].Cells, m, (j * 28) + m);
//}
/*
* 複製Style(包含儲存格合併的資訊)
* 先用CreateRange()選取要複製的Range("A1", "R28")
* 再用CopyStyle複製另一個Range中的格式
*/
Range range = template.Worksheets[0].Cells.CreateRange(0, 28, false);
int t = j * 28;
wb.Worksheets[0].Cells.CreateRange(t, 28, false).Copy(range);
#endregion
#region 填入學校資料
//將學校資料填入適當的位置內
wb.Worksheets[0].Cells[rowi, 13].PutValue(source.SelectSingleNode("@學校代號").InnerText);
wb.Worksheets[0].Cells[rowi, 16].PutValue(list.SelectSingleNode("@科別代號").InnerText);
wb.Worksheets[0].Cells[rowi + 2, 2].PutValue(source.SelectSingleNode("@學校名稱").InnerText);
wb.Worksheets[0].Cells[rowi + 2, 7].PutValue(Convert.ToInt32(source.SelectSingleNode("@學年度").InnerText) + " 學年度 第 " + Convert.ToInt32(source.SelectSingleNode("@學期").InnerText) + " 學期");
wb.Worksheets[0].Cells[rowi + 2, 12].PutValue(list.SelectSingleNode("@科別").InnerText);
wb.Worksheets[0].Cells[rowi + 2, 14].PutValue(list.SelectSingleNode("@年級").InnerText);
#endregion
if (j > 0)
{
//插入分頁(在 j * 28 跟 (j * 28) +1 中間，R跟S中間)
wb.Worksheets[0].HPageBreaks.Add(j * 28, 18);
rowj += 8;
}
else
{
rowj = 6;
}
rowi += 28;
j++;
#region 顯示頁數
//顯示頁數
if (x != true)
{
wb.Worksheets[0].Cells[(28 * (j - 1)) + 27, 13].PutValue("第" + numcount + "頁，共" + Math.Ceiling((double)count / 20) + "頁");
}
else
{
wb.Worksheets[0].Cells[(28 * (j - 1)) + 27, 13].PutValue("第" + numcount + "頁，共" + (Math.Ceiling((double)count / 20) + 1) + "頁");
}
numcount++;
#endregion
}
#region 填入學生資料
//將學生資料填入適當的位置內
wb.Worksheets[0].Cells[rowj, 1].PutValue(st.SelectSingleNode("@學號").InnerText);
wb.Worksheets[0].Cells[rowj, 3].PutValue(st.SelectSingleNode("@姓名").InnerText);
wb.Worksheets[0].Cells[rowj, 4].PutValue(st.SelectSingleNode("@身分證號").InnerText);
wb.Worksheets[0].Cells[rowj, 8].PutValue(st.SelectSingleNode("@備查日期").InnerText + "\n" + st.SelectSingleNode("@備查文號").InnerText);
wb.Worksheets[0].Cells[rowj, 11].PutValue(st.SelectSingleNode("@異動代號").InnerText);
wb.Worksheets[0].Cells[rowj, 12].PutValue(st.SelectSingleNode("@原因及事項").InnerText);
if (st.SelectSingleNode("@新學號").InnerText == "")
{
wb.Worksheets[0].Cells[rowj, 13].PutValue(st.SelectSingleNode("@異動日期").InnerText);
}
else
{
wb.Worksheets[0].Cells[rowj, 13].PutValue(st.SelectSingleNode("@新學號").InnerText + "\n" + st.SelectSingleNode("@異動日期").InnerText);
}
wb.Worksheets[0].Cells[rowj, 16].PutValue(st.SelectSingleNode("@備註").InnerText);
#endregion
i++;
rowj++;
//回報進度
ReportProgress((int)(((double)recCount * 100.0) / ((double)totalRec)));
}
#endregion
#region 若個數為20倍數，處理單一頁面
if (x == true)
{
#region 複製樣式-欄高、範圍
//複製樣版前28個 Row(欄高)
//for (int m = 0; m < 28; m++)
//{
// /*
// * 複製 template的第一個 Sheet的第m個 Row
// * 到 wb的第一個 Sheet的第(j * 28) + m個 Row
// */
// wb.Worksheets[0].Cells.CopyRow(template.Worksheets[0].Cells, m, (j * 28) + m);
//}
/*
* 複製Style(包含儲存格合併的資訊)
* 先用CreateRange()選取要複製的Range("A1", "R28")
* 再用CopyStyle複製另一個Range中的格式
*/
Range range = template.Worksheets[0].Cells.CreateRange(0, 28, false);
int t = j * 28;
wb.Worksheets[0].Cells.CreateRange(t, 28, false).Copy(range);
#endregion
#region 填入學校資料
//將學校資料填入適當的位置內
wb.Worksheets[0].Cells[rowi, 13].PutValue(source.SelectSingleNode("@學校代號").InnerText);
wb.Worksheets[0].Cells[rowi, 16].PutValue(list.SelectSingleNode("@科別代號").InnerText);
wb.Worksheets[0].Cells[rowi + 2, 2].PutValue(source.SelectSingleNode("@學校名稱").InnerText);
wb.Worksheets[0].Cells[rowi + 2, 7].PutValue(Convert.ToInt32(source.SelectSingleNode("@學年度").InnerText) + " 學年度 第 " + Convert.ToInt32(source.SelectSingleNode("@學期").InnerText) + " 學期");
wb.Worksheets[0].Cells[rowi + 2, 12].PutValue(list.SelectSingleNode("@科別").InnerText);
#endregion
if (j > 0)
{
//插入分頁(在i跟i+1中間，O跟P中間)
wb.Worksheets[0].HPageBreaks.Add(j * 28, 18);
rowj += 8;
}
rowi += 28;
j++;
#region 顯示頁數
//顯示頁數
wb.Worksheets[0].Cells[(28 * (j - 1)) + 27, 13].PutValue("第" + numcount + "頁，共" + (Math.Ceiling((double)count / 20) + 1) + "頁");
numcount++;
#endregion
}
#endregion
#region 統計人數
//填入統計人數
wb.Worksheets[0].Cells.CreateRange(rowj, 1, 1, 2).UnMerge();
wb.Worksheets[0].Cells.Merge(rowj, 1, 1, 3);
wb.Worksheets[0].Cells[rowj, 1].PutValue("合 計 " + count.ToString() + " 名");
#endregion
wb.Worksheets[0].HPageBreaks.Add(j * 28, 18);
#region 設定變數
//調整新清單所使用變數
numcount = 1;
rowj = (28 * j) - 2;
rowi = (28 * j);
x = false;
#endregion
}
//儲存 Excel
wb.Save(location, FileFormatType.Excel2003);
}
//列印
private void btnPrint_Click(object sender, EventArgs e)
{
//0. 使用者選擇檔名
SaveFileDialog sfd = new SaveFileDialog();
sfd.Title = "另存新檔";
sfd.FileName = "座位表_" + this.layout.ClassRoomName + "_" + this.expandablePanel1.TitleText + ".xls";
sfd.Filter = "Excel 2003 相容檔案 (*.xls)|*.xls|所有檔案 (*.*)|*.*";
DialogResult dr = sfd.ShowDialog();
if (dr != System.Windows.Forms.DialogResult.OK)
return;
//1. Open Excel File
Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook();
// 讀取樣版檔
if (this.layout.GetUDTLayout().ExcelTemplate =="B")
wb.Open(new MemoryStream(EMBACore.Properties.Resources.座位表_100B));
else
wb.Open(new MemoryStream(EMBACore.Properties.Resources.座位表_100A));
// 讀取樣版工作表
Worksheet templateSheet = wb.Worksheets[0];
// 複製樣版
int instanceSheetIndex = wb.Worksheets.AddCopy("範本");
Worksheet instanceSheet = wb.Worksheets[instanceSheetIndex];
instanceSheet.Name = this.expandablePanel1.TitleText;
//填入學生座位
foreach (string studID in this.stud_coords.Keys)
{
//取得學生資料
if (this.students.ContainsKey(studID))
{
SeatTableStudent stud = this.students[studID];
//取得學生位置座標
CellCoordinate coord = this.stud_coords[studID];
//將座標轉換為 Excel 格子位置
int offsetX = coord.X;
int offsetY = (this.layout.YCount - coord.Y -1) * 2 + 1 ;
//放置照片
if (stud.Photo != null)
{
MemoryStream ms = new MemoryStream();
stud.Photo.Save(ms, System.Drawing.Imaging.ImageFormat.Png);
Cell cell = instanceSheet.Cells[offsetY, offsetX];
instanceSheet.Pictures.Add(cell.Row, cell.Column, cell.Row + 1, cell.Column + 1, ms);
}
//寫入姓名
instanceSheet.Cells[offsetY+1, offsetX].PutValue(stud.Name);
}
} //end of foreach loop
try
{
wb.Worksheets.RemoveAt("範本");
wb.Save(sfd.FileName);
if (System.IO.File.Exists(sfd.FileName))
System.Diagnostics.Process.Start(sfd.FileName);
}
catch (Exception ex)
{
Util.ShowMsg(ex.Message,"注意");
}
}
public string ImportInfo(int mode)
{
if (OperatorProvider.Provider.Current().IsSystem)
{
return("超级管理员无此操作权限");
}
var curUser = OperatorProvider.Provider.Current();
string orgId = curUser.OrganizeId;//所属公司
string message = "请选择格式正确的文件(excel数据文件)再导入!";
string falseMessage = "";
int count = HttpContext.Request.Files.Count;
try
{
List <string> listIds = new List <string>();
if (count > 0)
{
HttpPostedFileBase file = HttpContext.Request.Files[0];
string hiddenDirectory = string.Empty;
Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook();
#region 过滤文件
if (string.IsNullOrEmpty(file.FileName))
{
return(message);
}
if (!(file.FileName.ToLower().Substring(file.FileName.ToLower().IndexOf('.')).Contains("xls") || file.FileName.ToLower().Substring(file.FileName.ToLower().IndexOf('.')).Contains("xlsx")))
{
return(message);
}
string fileName1 = DateTime.Now.ToString("yyyyMMddHHmmss") + System.IO.Path.GetExtension(file.FileName);
file.SaveAs(Server.MapPath("~/Resource/temp/" + fileName1));
wb.Open(Server.MapPath("~/Resource/temp/" + fileName1));
#endregion
Worksheet sheets = wb.Worksheets[0];
Aspose.Cells.Cells cells = sheets.Cells;
DataTable dt = cells.ExportDataTable(1, 0, cells.MaxDataRow, cells.MaxColumn + 1, true);
string labledept = mode == 0 ? "部门" : "外协单位";
if (dt.Columns.Contains("部门") && mode > 0)
{
return("当前模板不适用与外协单位违章档案扣分数据导入!");
}
if (dt.Columns.Contains("外协单位") && mode == 0)
{
return("当前模板不适用与生产部门违章档案扣分数据导入!");
}
//记录错误信息
List <string> resultlist = new List <string>();
List <UserEntity> ulist = userbll.GetList().OrderBy(p => p.SortCode).ToList();
List <DepartmentEntity> dlist = departmentBLL.GetList().OrderBy(p => p.SortCode).ToList();
var lllegaltypelist = dataitemdetailbll.GetDataItemListByItemCode("'LllegalType'");
int total = 0;
#region 违章部分
if (sheets.Name.Contains("违章"))//违章扣分信息导入
{
#region 对象装载
List <ImportLllegalPunish> list = new List <ImportLllegalPunish>();
//先获取到职务列表;
for (int i = 0; i < dt.Rows.Count; i++)
{
string resultmessage = "第" + (i + 1).ToString() + "行数据"; //显示错误
bool isadddobj = true;
//姓名
string username = dt.Columns.Contains("姓名") ? dt.Rows[i]["姓名"].ToString().Trim() : string.Empty;
//部门
string deptname = dt.Columns.Contains("部门") ? dt.Rows[i]["部门"].ToString().Trim() : string.Empty;
if (mode > 0)
{
deptname = dt.Columns.Contains("外协单位") ? dt.Rows[i]["外协单位"].ToString().Trim() : string.Empty; //外协单位
}
//专业/班组
string teamname = dt.Columns.Contains("专业/班组") ? dt.Rows[i]["专业/班组"].ToString().Trim() : string.Empty;
//岗位/职务
string dutyname = dt.Columns.Contains("岗位/职务") ? dt.Rows[i]["岗位/职务"].ToString().Trim() : string.Empty;
//违章过程描述
string lllegaldescribe = dt.Columns.Contains("违章过程描述") ? dt.Rows[i]["违章过程描述"].ToString().Trim() : string.Empty;
//违章分类
string lllegaltypename = dt.Columns.Contains("违章分类") ? dt.Rows[i]["违章分类"].ToString().Trim() : string.Empty;
//处罚时间
string punishdate = dt.Columns.Contains("处罚时间") ? dt.Rows[i]["处罚时间"].ToString().Trim() : string.Empty;
//处罚结果(元)
string punishresult = dt.Columns.Contains("处罚结果(元)") ? dt.Rows[i]["处罚结果(元)"].ToString() : string.Empty;
//处罚积分(分)
string punishpoint = dt.Columns.Contains("处罚积分(分)") ? dt.Rows[i]["处罚积分(分)"].ToString() : string.Empty;
string relevanceid = string.Empty;
try
{
#region 对象集合
ImportLllegalPunish entity = new ImportLllegalPunish();
//序号
entity.serialnumber = i + 1; //序号
entity.lllegaldescribe = lllegaldescribe;
entity.lllegaltypename = lllegaltypename;
//部门/承包商层级
if (!string.IsNullOrEmpty(deptname))
{
var deptlist = dlist.Where(p => p.FullName == deptname && p.Nature != "专业" && p.Nature != "班组");
if (deptlist.Count() > 0)
{
var deptentity = deptlist.FirstOrDefault();
entity.deptid = deptentity.DepartmentId;
entity.deptname = deptentity.FullName;
entity.nature = deptentity.Nature;
var parentdeptEntity = dlist.Where(p => p.DepartmentId == deptentity.ParentId).FirstOrDefault();
if (deptentity.Nature == "承包商" && parentdeptEntity.Nature != "部门")
{
var rpdeptEntity = GetRootContractor(deptentity, dlist);
entity.deptid = rpdeptEntity.DepartmentId;
entity.deptname = rpdeptEntity.FullName;
entity.nature = rpdeptEntity.Nature;
entity.teamid = deptentity.DepartmentId;
entity.teamname = deptentity.FullName;
entity.tnature = deptentity.Nature;
}
}
}
//专业/班组
if (!string.IsNullOrEmpty(teamname))
{
var deptlist = dlist.Where(p => p.FullName == teamname && (p.Nature == "专业" || p.Nature == "班组" || p.Nature == "承包商"));
if (deptlist.Count() > 0)
{
string parentcode = string.Empty;
if (!string.IsNullOrEmpty(entity.deptid))
{
parentcode = dlist.Where(p => p.DepartmentId == entity.deptid).FirstOrDefault().EnCode;
var deptentity = deptlist.FirstOrDefault();
//专业和班组、承包商的专业和班组必须来自于对应的部门
if (deptentity.EnCode.StartsWith(parentcode) && deptentity.EnCode != parentcode)
{
entity.teamid = deptentity.DepartmentId;
entity.teamname = deptentity.FullName;
entity.tnature = deptentity.Nature;
}
}
}
}
#region 人员
if (!string.IsNullOrEmpty(username))
{
List <UserEntity> userlist = ulist.Where(p => p.RealName == username.Trim() || p.Account == username.Trim() || p.Mobile == username.Trim() || p.Telephone == username.Trim()).ToList();
if (!string.IsNullOrEmpty(entity.deptid) && !string.IsNullOrEmpty(entity.teamid))
{
userlist = userlist.Where(p => p.DepartmentId == entity.teamid).ToList();
if (userlist.Count() > 0)
{
var checkUserEntity = userlist.FirstOrDefault();
entity.userid = checkUserEntity.UserId;
entity.username = checkUserEntity.RealName;
entity.dutyid = checkUserEntity.DutyId;
entity.dutyname = checkUserEntity.DutyName;
}
}
if (!string.IsNullOrEmpty(entity.deptid) && string.IsNullOrEmpty(entity.teamid))
{
userlist = userlist.Where(p => p.DepartmentId == entity.deptid).ToList();
if (userlist.Count() > 0)
{
var checkUserEntity = userlist.FirstOrDefault();
entity.userid = checkUserEntity.UserId;
entity.username = checkUserEntity.RealName;
entity.dutyid = checkUserEntity.DutyId;
entity.dutyname = checkUserEntity.DutyName;
}
}
}
#endregion
//处罚时间
if (!string.IsNullOrEmpty(punishdate))
{
entity.punishdate = punishdate;
}
else
{
entity.punishdate = DateTime.Now.ToString("yyyy-MM-dd");
}
//违章类型
if (!string.IsNullOrEmpty(lllegaltypename))
{
var checktypeEntity = lllegaltypelist.Where(p => p.ItemName == lllegaltypename.ToString()).FirstOrDefault();
if (null != checktypeEntity)
{
entity.lllegaltypename = checktypeEntity.ItemName;
entity.lllegaltype = checktypeEntity.ItemDetailId;
}
}
#endregion
#region 必填验证
if (!string.IsNullOrEmpty(username))
{
if (!string.IsNullOrEmpty(entity.teamid) && string.IsNullOrEmpty(entity.userid))
{
resultmessage += "人员不存在于专业/班组中、";
isadddobj = false;
}
else if (!string.IsNullOrEmpty(entity.deptid) && string.IsNullOrEmpty(entity.teamid) && string.IsNullOrEmpty(entity.userid))
{
resultmessage += "人员不存在于" + labledept + "中、";
isadddobj = false;
}
else
{
if (string.IsNullOrEmpty(entity.userid))
{
resultmessage += "人员填写错误或不存在、";
isadddobj = false;
}
}
}
if (string.IsNullOrEmpty(deptname))
{
resultmessage += labledept + "为空、";
isadddobj = false;
}
else
{
if (string.IsNullOrEmpty(entity.deptid))
{
resultmessage += labledept + "填写错误或不存在、";
isadddobj = false;
}
}
if (!string.IsNullOrEmpty(teamname))
{
if (!string.IsNullOrEmpty(entity.deptid) && string.IsNullOrEmpty(entity.teamid))
{
resultmessage += "专业/班组不存在于对应的" + labledept + "、";
isadddobj = false;
}
}
if (!string.IsNullOrEmpty(entity.nature))
{
if ((mode == 0 && entity.nature == "承包商"))
{
resultmessage += "生产部门导入模板中不应存在外协单位数据、";
isadddobj = false;
}
if ((mode == 1 && entity.nature != "承包商"))
{
resultmessage += "外协单位导入模板中不应存在生产部门数据、";
isadddobj = false;
}
}
if (string.IsNullOrEmpty(lllegaldescribe))
{
resultmessage += "违章过程描述为空、";
isadddobj = false;
}
if (string.IsNullOrEmpty(lllegaltypename))
{
resultmessage += "违章分类为空、";
isadddobj = false;
}
else
{
if (string.IsNullOrEmpty(entity.lllegaltype))
{
resultmessage += "违章分类不存在、";
isadddobj = false;
}
}
if (string.IsNullOrEmpty(punishresult))
{
resultmessage += "处罚结果(元)为空、";
isadddobj = false;
}
else
{
entity.punishresult = int.Parse(punishresult);
}
if (string.IsNullOrEmpty(punishpoint))
{
resultmessage += "处罚积分(分)为空、";
isadddobj = false;
}
else
{
entity.punishpoint = int.Parse(punishpoint);
}
if (isadddobj)
{
list.Add(entity);
}
else
{
if (!string.IsNullOrEmpty(resultmessage))
{
resultmessage = resultmessage.Substring(0, resultmessage.Length - 1) + ",无法正常导入";
resultlist.Add(resultmessage);
}
}
#endregion
}
catch
{
resultmessage += "出现数据异常,无法正常导入";
resultlist.Add(resultmessage);
}
}
if (resultlist.Count > 0)
{
foreach (string str in resultlist)
{
falseMessage += str + "</br>";
}
}
#endregion
#region 问题数据集合
foreach (ImportLllegalPunish entity in list)
{
string keyValue = string.Empty;
int excuteVal = 0;
//违章档案扣分信息
LllegalDeductMarksEntity baseentity = new LllegalDeductMarksEntity();
//获取已存在的违章问题
if (!string.IsNullOrEmpty(entity.userid))
{
var llist = lllegaldeductmarksbll.GetLllegalRecorList(entity.punishdate, entity.userid, entity.lllegaldescribe, entity.deptid, entity.teamid);
if (llist.Count() > 0)
{
var otherwz = llist.Where(p => p.CREATEUSERID != curUser.UserId);
//其他人创建的
if (otherwz.Count() > 0)
{
falseMessage += "人员为'" + entity.username + "'于" + entity.punishdate + "处罚的数据因已被其他人创建而无法覆盖,不予操作</br>";
excuteVal = -1;
}
else //自己创建
{
baseentity = llist.FirstOrDefault();
//先删除，后新增
lllegaldeductmarksbll.RemoveForm(baseentity.ID);
baseentity = new LllegalDeductMarksEntity();
excuteVal = 1;
}
}
else
{
excuteVal = 1;
}
}
else
{
excuteVal = 1;
}
if (excuteVal > 0)
{
baseentity.APPSIGN = "3"; //标识导入的
baseentity.USERID = entity.userid;
baseentity.USERNAME = entity.username;
baseentity.DEPTID = entity.deptid;
baseentity.DEPTNAME = entity.deptname;
baseentity.TEAMID = entity.teamid;
baseentity.TEAMNAME = entity.teamname;
baseentity.DUTYNAME = entity.dutyname;
baseentity.LLLEGALDESCRIBE = entity.lllegaldescribe;
baseentity.LLLEGALTYPE = entity.lllegaltype;
baseentity.LLLEGALTYPENAME = entity.lllegaltypename;
baseentity.PUNISHDATE = Convert.ToDateTime(entity.punishdate);
baseentity.PUNISHRESULT = entity.punishresult;
baseentity.PUNISHPOINT = entity.punishpoint;
lllegaldeductmarksbll.SaveForm("", baseentity);
total += 1;
}
}
#endregion
}
#endregion
count = dt.Rows.Count;
message = "共有" + count.ToString() + "条记录,成功导入" + total.ToString() + "条,失败" + (count - total).ToString() + "条";
message += "</br>" + falseMessage;
}
}
catch (Exception ex)
{
return(ex.Message);
}
return(message);
}
protected override void Build(XmlElement source, string location)
{
Workbook template = new Workbook();
//從Resources把Template讀出來
template.Open(new MemoryStream(Properties.Resources.ExtendingGraduatingStudentListTemplate), FileFormatType.Excel2003);
//要產生的excel檔
Workbook wb = new Aspose.Cells.Workbook();
wb.Open(new MemoryStream(Properties.Resources.ExtendingGraduatingStudentListTemplate), FileFormatType.Excel2003);
Worksheet ws = wb.Worksheets[0];
//頁面間隔幾個row
int next = 24;
//索引
int index = 0;
//範本範圍
Range tempRange = template.Worksheets[0].Cells.CreateRange(0, 24, false);
//總共幾筆異動紀錄
int count = 0;
int totalRec = source.SelectNodes("清單/異動紀錄").Count;
// 取得名冊內存的最後異動代碼對照
Dictionary <string, string> LastCodeDict = new Dictionary <string, string>();
foreach (XmlNode list in source.SelectNodes("清單"))
{
//產生清單第一頁
//for (int row = 0; row < next; row++)
//{
// ws.Cells.CopyRow(template.Worksheets[0].Cells, row, row + index);
//}
ws.Cells.CreateRange(index, 24, false).Copy(tempRange);
//Page
int currentPage = 1;
int totalPage = (list.ChildNodes.Count / 18) + 1;
//寫入名冊類別
if (source.SelectSingleNode("@類別").InnerText == "延修生畢業名冊")
{
ws.Cells[index, 0].PutValue(ws.Cells[index, 0].StringValue.Replace("□畢業", "■畢業"));
}
else
{
ws.Cells[index, 0].PutValue(ws.Cells[index, 0].StringValue.Replace("□結業", "■結業"));
}
//寫入代號
ws.Cells[index, 6].PutValue("代碼：" + source.SelectSingleNode("@學校代號").InnerText + "-" + list.SelectSingleNode("@科別代號").InnerText);
//寫入校名、學年度、學期、科別
ws.Cells[index + 2, 0].PutValue("校名：" + source.SelectSingleNode("@學校名稱").InnerText);
ws.Cells[index + 2, 4].PutValue(source.SelectSingleNode("@學年度").InnerText + "學年度 第" + source.SelectSingleNode("@學期").InnerText + "學期");
ws.Cells[index + 2, 6].PutValue(list.SelectSingleNode("@科別").InnerText);
//寫入資料
int recCount = 0;
int dataIndex = index + 5;
for (; currentPage <= totalPage; currentPage++)
{
//複製頁面
if (currentPage + 1 <= totalPage)
{
//for (int row = 0; row < next; row++)
//{
// ws.Cells.CopyRow(ws.Cells, row + index, row + index + next);
//}
ws.Cells.CreateRange(index + next, 24, false).Copy(tempRange);
}
//填入資料
for (int i = 0; i < 18 && recCount < list.ChildNodes.Count; i++, recCount++)
{
//MsgBox.Show(i.ToString()+" "+recCount.ToString());
XmlNode rec = list.SelectNodes("異動紀錄")[recCount];
ws.Cells[dataIndex, 0].PutValue(rec.SelectSingleNode("@學號").InnerText + "\n" + rec.SelectSingleNode("@姓名").InnerText);
ws.Cells[dataIndex, 1].PutValue(rec.SelectSingleNode("@性別代號").InnerText.ToString());
ws.Cells[dataIndex, 2].PutValue(rec.SelectSingleNode("@性別").InnerText);
string ssn = rec.SelectSingleNode("@身分證號").InnerText;
if (ssn == "")
{
ssn = rec.SelectSingleNode("@身份證號").InnerText;
}
if (!LastCodeDict.ContainsKey(ssn))
{
LastCodeDict.Add(ssn, rec.SelectSingleNode("@最後異動代號").InnerText.ToString());
}
ws.Cells[dataIndex, 3].PutValue(Util.ConvertDateStr2(rec.SelectSingleNode("@生日").InnerText) + "\n" + ssn);
ws.Cells[dataIndex, 4].PutValue(rec.SelectSingleNode("@最後異動代號").InnerText.ToString());
ws.Cells[dataIndex, 5].PutValue(Util.ConvertDateStr2(rec.SelectSingleNode("@備查日期").InnerText) + "\n" + rec.SelectSingleNode("@備查文號").InnerText);
ws.Cells[dataIndex, 6].PutValue(rec.SelectSingleNode("@畢業證書字號").InnerText);
//ws.Cells[dataIndex, 7].PutValue(rec.SelectSingleNode("@備註").InnerText);
if (rec.SelectSingleNode("@特殊身份代碼") != null)
{
ws.Cells[dataIndex, 7].PutValue(rec.SelectSingleNode("@特殊身份代碼").InnerText);
}
dataIndex++;
count++;
}
//計算合計
if (currentPage == totalPage)
{
ws.Cells[index + 22, 0].PutValue("合計");
ws.Cells[index + 22, 1].PutValue(list.ChildNodes.Count.ToString());
}
//分頁
ws.Cells[index + 23, 6].PutValue("第 " + currentPage + " 頁，共 " + totalPage + " 頁");
ws.HPageBreaks.Add(index + 24, 8);
//索引指向下一頁
index += next;
dataIndex = index + 5;
//回報進度
ReportProgress((int)(((double)count * 100.0) / ((double)totalRec)));
}
}
Worksheet mingdao = wb.Worksheets[1];
Worksheet mdws = wb.Worksheets[1];
mdws.Name = "電子格式";
Range range_header = mingdao.Cells.CreateRange(0, 1, false);
Range range_row = mingdao.Cells.CreateRange(1, 1, false);
mdws.Cells.CreateRange(0, 1, false).Copy(range_header);
int mdws_index = 0;
DAL.DALTransfer DALTranser = new DAL.DALTransfer();
// 格式轉換
List <GovernmentalDocument.Reports.List.rpt_UpdateRecord> _data = DALTranser.ConvertRptUpdateRecord(source);
// 排序 (依 班別、年級、科別代碼、異動代碼)
_data = (from data in _data orderby data.ClassType, data.DeptCode, data.UpdateCode select data).ToList();
foreach (GovernmentalDocument.Reports.List.rpt_UpdateRecord rec in _data)
{
mdws_index++;
//每增加一行,複製一次
mdws.Cells.CreateRange(mdws_index, 1, false).Copy(range_row);
//應畢業學年度
mdws.Cells[mdws_index, 0].PutValue(rec.ExpectGraduateSchoolYear);
//班別
mdws.Cells[mdws_index, 1].PutValue(rec.ClassType);
//科別代碼
mdws.Cells[mdws_index, 2].PutValue(rec.DeptCode);
// 2 放上傳類別，請使用者自填
//學號
mdws.Cells[mdws_index, 4].PutValue(rec.StudentNumber);
//姓名
mdws.Cells[mdws_index, 5].PutValue(rec.Name);
//身分證字號
mdws.Cells[mdws_index, 6].PutValue(rec.IDNumber);
//註1
mdws.Cells[mdws_index, 7].PutValue(rec.Comment1);
//性別代碼
mdws.Cells[mdws_index, 8].PutValue(rec.GenderCode);
//出生日期
mdws.Cells[mdws_index, 9].PutValue(rec.Birthday);
//特殊身份代碼
mdws.Cells[mdws_index, 10].PutValue(rec.SpecialStatusCode);
//異動原因代碼
if (LastCodeDict.ContainsKey(rec.IDNumber))
{
mdws.Cells[mdws_index, 11].PutValue(LastCodeDict[rec.IDNumber]);
}
else
{
mdws.Cells[mdws_index, 11].PutValue(rec.UpdateCode);
}
//備查文字
mdws.Cells[mdws_index, 12].PutValue(rec.LastADDoc);
//備查文號
mdws.Cells[mdws_index, 13].PutValue(rec.LastADNum);
//備查日期
mdws.Cells[mdws_index, 14].PutValue(rec.LastADDate);
//畢業證書字號
mdws.Cells[mdws_index, 15].PutValue(rec.GraduateCertificateNumber);
//備註說明
mdws.Cells[mdws_index, 16].PutValue(rec.Comment);
}
//儲存
wb.Save(location, FileFormatType.Excel2003);
}
// 處理台中縣樣版
private void ProcessTaiChung(System.Xml.XmlElement source, string location)
{
// 資料轉換
Dictionary <string, JHPermrec.UpdateRecord.DAL.StudBatchUpdateRecContentEntity> data = StudBatchUpdateRecEntity.ConvertGetContentData(source);
#region 建立 Excel
int tmpY, tmpM;
tmpY = DateTime.Now.Year;
tmpM = DateTime.Now.Month;
string tmpRptY, tmpRptM;
tmpRptY = (tmpY - 1911).ToString();
if (tmpM < 10)
{
tmpRptM = "0" + tmpM.ToString();
}
else
{
tmpRptM = tmpM.ToString();
}
string strPrintDate = UpdateRecordUtil.ChangeDate1911(DateTime.Now.ToString());
//從 Resources 將新生名冊template讀出來
Workbook template = new Workbook();
//template.Worksheets[0].PageSetup.
template.Open(new MemoryStream(GDResources.JEnrollmentListTemplate_TaiChung), FileFormatType.Excel2003);
//產生 excel
Workbook wb = new Aspose.Cells.Workbook();
wb.Open(new MemoryStream(GDResources.JEnrollmentListTemplate_TaiChung), FileFormatType.Excel2003);
#endregion
#region 複製樣式-預設樣式、欄寬
//設定預設樣式
wb.DefaultStyle = template.DefaultStyle;
#endregion
int rowj = 1;
int recCount = 0;
int totalRec = data.Count;
wb.Worksheets[0].Cells[0, 0].PutValue(StudBatchUpdateRecEntity.GetContentSchoolName() + " 國民中學入學學生名冊");
wb.Worksheets[0].Cells[1, 10].PutValue(tmpRptY + "年" + tmpM + "月填製");
rowj = 4;
wb.Worksheets[0].Cells[1, 0].PutValue(StudBatchUpdateRecEntity.GetContentSchoolYear() + "學年度第" + StudBatchUpdateRecEntity.GetContentSemester() + "學期 1年級");
//將xml資料填入至excel
foreach (StudBatchUpdateRecContentEntity sburce in data.Values)
{
recCount++;
//將學生資料填入適當的位置內
wb.Worksheets[0].Cells[rowj, 0].PutValue(sburce.GetStudentNumber());
wb.Worksheets[0].Cells[rowj, 1].PutValue(sburce.GetName());
wb.Worksheets[0].Cells[rowj, 2].PutValue(sburce.GetGender());
wb.Worksheets[0].Cells[rowj, 3].PutValue(sburce.GetIDNumber());
DateTime dt;
if (DateTime.TryParse(sburce.GetBirthday(), out dt))
{
wb.Worksheets[0].Cells[rowj, 4].PutValue("" + (dt.Year - 1911));
wb.Worksheets[0].Cells[rowj, 5].PutValue("" + dt.Month);
wb.Worksheets[0].Cells[rowj, 6].PutValue("" + dt.Day);
}
if (sburce.GetEnrollmentSchoolYear() != "")
{
wb.Worksheets[0].Cells[rowj, 7].PutValue(UpdateRecordUtil.getChineseYearStr(sburce.GetEnrollmentSchoolYear()));
wb.Worksheets[0].Cells[rowj, 8].PutValue(UpdateRecordUtil.getMonthStr(sburce.GetEnrollmentSchoolYear(), false));
}
wb.Worksheets[0].Cells[rowj, 9].PutValue(sburce.GetPrimarySchoolName());
wb.Worksheets[0].Cells[rowj, 10].PutValue(sburce.GetAddress());
// peoTotalCount++;
rowj++;
//回報進度
ReportProgress((int)(((double)recCount * 100.0) / ((double)totalRec)));
}
// 畫表
Style st2 = wb.Styles[wb.Styles.Add()];
StyleFlag sf2 = new StyleFlag();
sf2.Borders = true;
st2.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
st2.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;
st2.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;
st2.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
int tmpMaxRow = 0, tmpMaxCol = 0;
for (int wbIdx1 = 0; wbIdx1 < wb.Worksheets.Count; wbIdx1++)
{
tmpMaxRow = wb.Worksheets[wbIdx1].Cells.MaxDataRow - 3;
tmpMaxCol = wb.Worksheets[wbIdx1].Cells.MaxDataColumn + 1;
wb.Worksheets[wbIdx1].Cells.CreateRange(4, 0, tmpMaxRow, tmpMaxCol).ApplyStyle(st2, sf2);
}
//合計人數
wb.Worksheets[0].Cells[rowj, 0].Style.HorizontalAlignment = TextAlignmentType.Center;
wb.Worksheets[0].Cells[rowj, 0].PutValue("合計");
wb.Worksheets[0].Cells[rowj, 1].Style.HorizontalAlignment = TextAlignmentType.Center;
wb.Worksheets[0].Cells[rowj, 1].PutValue("" + data.Count + " 名");
wb.Worksheets[0].Cells[rowj, 3].Style.HorizontalAlignment = TextAlignmentType.Center;
wb.Worksheets[0].Cells[rowj, 3].PutValue("以下空白");
//儲存 Excel
wb.Save(location, FileFormatType.Excel2003);
}
// 高雄用
private void ProcessKaoHsiung(System.Xml.XmlElement source, string location)
{
// 資料轉換
Dictionary <string, JHPermrec.UpdateRecord.DAL.StudBatchUpdateRecContentEntity> data = StudBatchUpdateRecEntity.ConvertGetContentData(source);
int peoTotalCount = 0; // 總人數
int peoBoyCount = 0; // 男生人數
int peoGirlCount = 0; // 女生人數
int tmpY, tmpM;
tmpY = DateTime.Now.Year;
tmpM = DateTime.Now.Month;
string tmpRptY, tmpRptM;
tmpRptY = (tmpY - 1911).ToString();
if (tmpM < 10)
{
tmpRptM = "0" + tmpM.ToString();
}
else
{
tmpRptM = tmpM.ToString();
}
#region 建立 Excel
//從 Resources 將死亡異動名冊template讀出來
Workbook template = new Workbook();
template.Open(new MemoryStream(GDResources.JDeadStudentListTemplate), FileFormatType.Excel2003);
//產生 excel
Workbook wb = new Aspose.Cells.Workbook();
wb.Open(new MemoryStream(GDResources.JDeadStudentListTemplate), FileFormatType.Excel2003);
#endregion
#region 複製樣式-預設樣式、欄寬
//設定預設樣式
wb.DefaultStyle = template.DefaultStyle;
#endregion
int rowi = 0, rowj = 1, numcount = 1, j = 0;
int recCount = 0;
int totalRec = data.Count;
rowj = 4;
wb.Worksheets[0].Cells[rowi, 4].PutValue(StudBatchUpdateRecEntity.GetContentSchoolName() + " " + StudBatchUpdateRecEntity.GetContentSchoolYear() + "學年第 " + StudBatchUpdateRecEntity.GetContentSemester() + "學期");
wb.Worksheets[0].Cells[rowi, 8].PutValue("列印日期：" + UpdateRecordUtil.ChangeDate1911(DateTime.Now.ToString()));
wb.Worksheets[0].Cells[rowi + 1, 8].PutValue("列印時間：" + DateTime.Now.ToLongTimeString());
//將xml資料填入至excel
foreach (StudBatchUpdateRecContentEntity sburce in data.Values)
{
recCount++;
#region 填入學生資料
// 班級
wb.Worksheets[0].Cells[rowj, 0].PutValue(sburce.GetClassName());
// 座號
wb.Worksheets[0].Cells[rowj, 1].PutValue(sburce.GetSeatNo());
// 學號
wb.Worksheets[0].Cells[rowj, 2].PutValue(sburce.GetStudentNumber());
// 姓名
wb.Worksheets[0].Cells[rowj, 3].PutValue(sburce.GetName());
// 身分證
wb.Worksheets[0].Cells[rowj, 4].PutValue(sburce.GetIDNumber());
// 出生年月日
if (!string.IsNullOrEmpty(sburce.GetBirthday()))
{
wb.Worksheets[0].Cells[rowj, 5].PutValue(UpdateRecordUtil.ChangeDate1911(sburce.GetBirthday()));
}
// 性別
wb.Worksheets[0].Cells[rowj, 6].PutValue(sburce.GetGender());
// 異動年級
wb.Worksheets[0].Cells[rowj, 7].PutValue(sburce.GetGradeYear());
// 異動日期
if (!string.IsNullOrEmpty(sburce.GetUpdateDate()))
{
wb.Worksheets[0].Cells[rowj, 8].PutValue(UpdateRecordUtil.ChangeDate1911(sburce.GetUpdateDate()));
}
// 學籍最後核准文號
wb.Worksheets[0].Cells[rowj, 9].PutValue(sburce.GetLastADNumber());
if (sburce.GetGender() == "男")
{
peoBoyCount++;
}
if (sburce.GetGender() == "女")
{
peoGirlCount++;
}
peoTotalCount++;
#endregion
rowj++;
//回報進度
ReportProgress((int)(((double)recCount * 100.0) / ((double)totalRec)));
}
Style st2 = wb.Styles[wb.Styles.Add()];
StyleFlag sf2 = new StyleFlag();
sf2.Borders = true;
st2.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
st2.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;
st2.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;
st2.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
int tmpMaxRow = 0, tmpMaxCol = 0;
for (int wbIdx1 = 0; wbIdx1 < wb.Worksheets.Count; wbIdx1++)
{
tmpMaxRow = wb.Worksheets[wbIdx1].Cells.MaxDataRow - 3;
tmpMaxCol = wb.Worksheets[wbIdx1].Cells.MaxDataColumn + 1;
wb.Worksheets[wbIdx1].Cells.CreateRange(4, 0, tmpMaxRow, tmpMaxCol).ApplyStyle(st2, sf2);
}
// 統計人數
rowj++;
wb.Worksheets[0].Cells.CreateRange(rowj, 2, 1, 2).Merge();
wb.Worksheets[0].Cells[rowj, 2].PutValue("男：" + peoBoyCount.ToString());
wb.Worksheets[0].Cells[rowj, 4].PutValue("女：" + peoGirlCount.ToString());
wb.Worksheets[0].Cells[rowj, 8].PutValue("總計：" + peoTotalCount.ToString());
wb.Worksheets[0].Cells.CreateRange(rowj + 1, 0, 1, 10).Merge();
// wb.Worksheets[0].Cells[rowj + 1, 0].PutValue("校長 教務主任 註冊組長 核對員");
wb.Worksheets[0].Cells[rowj + 1, 0].PutValue("核對員 註冊組長 教務主任 校長");
// 顯示頁
PageSetup pg = wb.Worksheets[0].PageSetup;
string tmp = "&12 " + tmpRptY + "年" + tmpRptM + "月 填報" + "共&N頁";
pg.SetHeader(2, tmp);
//儲存 Excel
wb.Save(location, FileFormatType.Excel2003);
}
protected override void Build(System.Xml.XmlElement source, string location)
{
#region 建立 Excel
//從 Resources 將延修生學籍名冊template讀出來
Workbook template = new Workbook();
template.Open(new MemoryStream(Properties.Resources.ExtendingStudentUpdateRecordListTemplate), FileFormatType.Excel2003);
//產生 excel
Workbook wb = new Aspose.Cells.Workbook();
wb.Open(new MemoryStream(Properties.Resources.ExtendingStudentUpdateRecordListTemplate), FileFormatType.Excel2003);
#endregion
#region 複製樣式-預設樣式、欄寬
//設定預設樣式
wb.DefaultStyle = template.DefaultStyle;
//複製樣版中前18個 Column(欄寬)
for (int m = 0; m < 18; m++)
{
/*
* 複製 template的第一個 Sheet的第 m個 Column
* 到 wb的第一個 Sheet的第 m個 Column
*/
wb.Worksheets[0].Cells.CopyColumn(template.Worksheets[0].Cells, m, m);
}
#endregion
#region 初始變數
/******************************
* rowi 填入學校資料用
* rowj 填入學生資料用
* num 計算清單份數
* numcount 計算每份清單頁數
* j 計算所產生清單頁數
* x 判斷個數是否為20被數用
******************************/
int rowi = 0, rowj = 1, num = source.SelectNodes("清單").Count, numcount = 1, j = 0;
bool x = false;
int recCount = 0;
int totalRec = source.SelectNodes("清單/異動紀錄").Count;
#endregion
foreach (XmlNode list in source.SelectNodes("清單"))
{
int i = 0;
#region 找出資料總數及判斷
//找出資料總數方便評估進度
int count = list.SelectNodes("異動紀錄").Count;
//判斷個數是否為20被數
if (count % 20 == 0)
{
x = true;
}
#endregion
#region 異動紀錄
//將xml資料填入至excel
foreach (XmlNode st in list.SelectNodes("異動紀錄"))
{
recCount++;
if (i % 20 == 0)
{
#region 複製樣式-欄高、範圍
//複製樣版中前287個 Row(欄高)
//for (int m = 0; m < 28; m++)
//{
// /*
// * 複製 template的第一個 Sheet的第m個 Row
// * 到 wb的第一個 Sheet的第(j * 28) + m個 Row
// */
// wb.Worksheets[0].Cells.CopyRow(template.Worksheets[0].Cells, m, (j * 28) + m);
//}
/*
* 複製Style(包含儲存格合併的資訊)
* 先用CreateRange()選取要複製的Range("A1", "R28")
* 再用CopyStyle複製另一個Range中的格式
*/
Range range = template.Worksheets[0].Cells.CreateRange(0, 28, false);
int t= j * 28;
wb.Worksheets[0].Cells.CreateRange(t, 28,false).Copy(range);
#endregion
#region 填入學校資料
//將學校資料填入適當的位置內
wb.Worksheets[0].Cells[rowi, 13].PutValue(source.SelectSingleNode("@學校代號").InnerText);
wb.Worksheets[0].Cells[rowi, 16].PutValue(list.SelectSingleNode("@科別代號").InnerText);
wb.Worksheets[0].Cells[rowi + 2, 2].PutValue(source.SelectSingleNode("@學校名稱").InnerText);
wb.Worksheets[0].Cells[rowi + 2, 7].PutValue(Convert.ToInt32(source.SelectSingleNode("@學年度").InnerText) + "學年度第" + Convert.ToInt32(source.SelectSingleNode("@學期").InnerText) +"學期");
wb.Worksheets[0].Cells[rowi + 2, 12].PutValue(list.SelectSingleNode("@科別").InnerText);
#endregion
if (j > 0)
{
//插入分頁(在 j * 28 跟 (j * 28) +1 中間，R跟S中間)
wb.Worksheets[0].HPageBreaks.Add(j * 28, 18);
rowj += 8;
}
else
{
rowj = 6;
}
rowi += 28;
j++;
#region 顯示頁數
//顯示頁數
if (x != true)
{
wb.Worksheets[0].Cells[(28 * (j - 1)) + 27, 13].PutValue("第" + numcount + "頁，共" + Math.Ceiling((double)count / 20) + "頁");
}
else
{
wb.Worksheets[0].Cells[(28 * (j - 1)) + 27, 13].PutValue("第" + numcount + "頁，共" + (Math.Ceiling((double)count / 20) + 1) + "頁");
}
numcount++;
#endregion
}
#region 填入學生資料
//將學生資料填入適當的位置內
wb.Worksheets[0].Cells[rowj, 1].PutValue(st.SelectSingleNode("@學號").InnerText);
wb.Worksheets[0].Cells[rowj, 3].PutValue(st.SelectSingleNode("@姓名").InnerText);
wb.Worksheets[0].Cells[rowj, 4].PutValue(st.SelectSingleNode("@身分證號").InnerText);
wb.Worksheets[0].Cells[rowj, 8].PutValue(Util.ConvertDateStr2(st.SelectSingleNode("@備查日期").InnerText) + "\n" + st.SelectSingleNode("@備查文號").InnerText);
wb.Worksheets[0].Cells[rowj, 11].PutValue(st.SelectSingleNode("@異動代號").InnerText);
wb.Worksheets[0].Cells[rowj, 12].PutValue(st.SelectSingleNode("@原因及事項").InnerText);
if (st.SelectSingleNode("@新學號").InnerText == "")
{
wb.Worksheets[0].Cells[rowj, 13].PutValue(Util.ConvertDateStr2(st.SelectSingleNode("@異動日期").InnerText));
}
else
{
wb.Worksheets[0].Cells[rowj, 13].PutValue(st.SelectSingleNode("@新學號").InnerText + "\n" + Util.ConvertDateStr2(st.SelectSingleNode("@異動日期").InnerText));
}
//wb.Worksheets[0].Cells[rowj, 16].PutValue(st.SelectSingleNode("@備註").InnerText);
if(st.SelectSingleNode("@特殊身份代碼")!=null )
wb.Worksheets[0].Cells[rowj, 16].PutValue(st.SelectSingleNode("@特殊身份代碼").InnerText);
#endregion
i++;
rowj++;
//回報進度
ReportProgress((int)(((double)recCount * 100.0) / ((double)totalRec)));
}
#endregion
#region 若個數為20倍數，處理單一頁面
if (x == true)
{
#region 複製樣式-欄高、範圍
//複製樣版前28個 Row(欄高)
//for (int m = 0; m < 28; m++)
//{
// /*
// * 複製 template的第一個 Sheet的第m個 Row
// * 到 wb的第一個 Sheet的第(j * 28) + m個 Row
// */
// wb.Worksheets[0].Cells.CopyRow(template.Worksheets[0].Cells, m, (j * 28) + m);
//}
/*
* 複製Style(包含儲存格合併的資訊)
* 先用CreateRange()選取要複製的Range("A1", "R28")
* 再用CopyStyle複製另一個Range中的格式
*/
Range range = template.Worksheets[0].Cells.CreateRange(0,28,false);
int t= j * 28;
wb.Worksheets[0].Cells.CreateRange(t, 28, false).Copy(range);
#endregion
#region 填入學校資料
//將學校資料填入適當的位置內
wb.Worksheets[0].Cells[rowi, 13].PutValue(source.SelectSingleNode("@學校代號").InnerText);
wb.Worksheets[0].Cells[rowi, 16].PutValue(list.SelectSingleNode("@科別代號").InnerText);
wb.Worksheets[0].Cells[rowi + 2, 2].PutValue(source.SelectSingleNode("@學校名稱").InnerText);
wb.Worksheets[0].Cells[rowi + 2, 7].PutValue(Convert.ToInt32(source.SelectSingleNode("@學年度").InnerText) + "學年度第" + Convert.ToInt32(source.SelectSingleNode("@學期").InnerText) + "學期");
wb.Worksheets[0].Cells[rowi + 2, 12].PutValue(list.SelectSingleNode("@科別").InnerText);
#endregion
if (j > 0)
{
//插入分頁(在i跟i+1中間，O跟P中間)
wb.Worksheets[0].HPageBreaks.Add(j * 28, 18);
rowj += 8;
}
rowi += 28;
j++;
#region 顯示頁數
//顯示頁數
wb.Worksheets[0].Cells[(28 * (j - 1)) + 27, 13].PutValue("第" + numcount + "頁，共" + (Math.Ceiling((double)count / 20) + 1) + "頁");
numcount++;
#endregion
}
#endregion
#region 統計人數
//填入統計人數
wb.Worksheets[0].Cells.CreateRange(rowj, 1, 1, 2).UnMerge();
wb.Worksheets[0].Cells.Merge(rowj, 1, 1, 3);
wb.Worksheets[0].Cells[rowj, 1].PutValue("合 計 " + count.ToString() + " 名");
#endregion
wb.Worksheets[0].HPageBreaks.Add(j * 28, 18);
#region 設定變數
//調整新清單所使用變數
numcount = 1;
rowj = (28 * j) - 2;
rowi = (28 * j);
x = false;
#endregion
}
Worksheet mingdao = wb.Worksheets[1];
Worksheet mdws = wb.Worksheets[1];
mdws.Name = "電子格式";
Range range_header = mingdao.Cells.CreateRange(0, 1, false);
Range range_row = mingdao.Cells.CreateRange(1, 1, false);
mdws.Cells.CreateRange(0, 1, false).Copy(range_header);
int mdws_index = 0;
DAL.DALTransfer DALTranser = new DAL.DALTransfer();
// 格式轉換
List<GovernmentalDocument.Reports.List.rpt_UpdateRecord> _data = DALTranser.ConvertRptUpdateRecord(source);
// 排序 (依 班別、年級、科別代碼、異動代碼)
_data = (from data in _data orderby data.ClassType, data.DeptCode, data.UpdateCode select data).ToList();
foreach (GovernmentalDocument.Reports.List.rpt_UpdateRecord rec in _data)
{
mdws_index++;
//每增加一行,複製一次
mdws.Cells.CreateRange(mdws_index, 1, false).Copy(range_row);
// 應畢業學年度
mdws.Cells[mdws_index, 0].PutValue(rec.ExpectGraduateSchoolYear);
//班別
mdws.Cells[mdws_index, 1].PutValue(rec.ClassType);
//科別代碼
mdws.Cells[mdws_index, 2].PutValue(rec.DeptCode);
// 2 放上傳類別，請使用者自填
//學號
mdws.Cells[mdws_index, 4].PutValue(rec.StudentNumber);
//姓名
mdws.Cells[mdws_index, 5].PutValue(rec.Name);
//身分證字號
mdws.Cells[mdws_index, 6].PutValue(rec.IDNumber);
//註1
mdws.Cells[mdws_index, 7].PutValue(rec.Comment1);
//性別代碼
mdws.Cells[mdws_index, 8].PutValue(rec.GenderCode);
//出生日期
mdws.Cells[mdws_index, 9].PutValue(rec.Birthday);
//特殊身份代碼
mdws.Cells[mdws_index, 10].PutValue(rec.SpecialStatusCode);
//異動原因代碼
mdws.Cells[mdws_index, 11].PutValue(rec.UpdateCode);
//異動日期
mdws.Cells[mdws_index, 12].PutValue(rec.UpdateDate);
// 異動順序
mdws.Cells[mdws_index, 13].PutValue(rec.Order);
//備查日期
mdws.Cells[mdws_index, 14].PutValue(rec.LastADDate);
//備查文字
mdws.Cells[mdws_index, 15].PutValue(rec.LastADDoc);
//備查文號
mdws.Cells[mdws_index, 16].PutValue(rec.LastADNum);
//更正後資料
string strUpdateData = string.Empty;
//若是更正後資料有值則填入更正後資料
if (!string.IsNullOrEmpty(rec.NewData))
strUpdateData = rec.NewData;
//若是新學號中有值則填入新學號
//判斷strUpdateData是否已有值，若是已有值則加入斷行符號
if (!string.IsNullOrEmpty(rec.NewStudNumber))
strUpdateData += string.IsNullOrEmpty(strUpdateData) ? rec.NewStudNumber : "\n" + rec.NewStudNumber;
mdws.Cells[mdws_index, 17].PutValue(strUpdateData);
// 註2
mdws.Cells[mdws_index, 18].PutValue(rec.Comment2);
//備註說明
mdws.Cells[mdws_index, 19].PutValue(rec.Comment);
}
//foreach (XmlElement record in source.SelectNodes("清單/異動紀錄"))
//{
// mdws_index++;
// mdws.Cells.CreateRange(mdws_index, 1, false).Copy(range_row);
// // 學年度
// string schoolYear = "";
// if (!string.IsNullOrEmpty(record.GetAttribute("學生編號")))
// {
// SHSchool.Data.SHLeaveInfoRecord scl = SHSchool.Data.SHLeaveInfo.SelectByStudentID(record.GetAttribute("學生編號"));
// if (scl.SchoolYear.HasValue)
// schoolYear = scl.SchoolYear.Value.ToString();
// }
// mdws.Cells[mdws_index, 0].PutValue(schoolYear);
// mdws.Cells[mdws_index, 1].PutValue(record.GetAttribute("班別"));
// mdws.Cells[mdws_index, 2].PutValue((record.ParentNode as XmlElement).GetAttribute("科別代號"));
// mdws.Cells[mdws_index, 3].PutValue("");
// mdws.Cells[mdws_index, 4].PutValue(record.GetAttribute("學號"));
// mdws.Cells[mdws_index, 5].PutValue(record.GetAttribute("姓名"));
// mdws.Cells[mdws_index, 6].PutValue(record.GetAttribute("身分證號"));
// mdws.Cells[mdws_index, 7].PutValue(record.GetAttribute("註1"));
// mdws.Cells[mdws_index, 8].PutValue(record.GetAttribute("性別代號"));
// mdws.Cells[mdws_index, 9].PutValue(GetBirthdateWithoutSlash(BL.Util.ConvertDate1(record.GetAttribute("出生年月日"))));
// mdws.Cells[mdws_index, 10].PutValue(record.GetAttribute("特殊身份代碼")); //原為抓取註備欄位值
// mdws.Cells[mdws_index, 11].PutValue(record.GetAttribute("異動代號"));
// mdws.Cells[mdws_index, 12].PutValue(GetBirthdateWithoutSlash(BL.Util.ConvertDate1(record.GetAttribute("異動日期"))));
// mdws.Cells[mdws_index, 13].PutValue(GetBirthdateWithoutSlash(BL.Util.ConvertDate1(record.GetAttribute("備查日期"))));
// mdws.Cells[mdws_index, 14].PutValue(GetADDoc(record.GetAttribute("備查文號")));
// mdws.Cells[mdws_index, 15].PutValue(GetADNo(record.GetAttribute("備查文號")));
// mdws.Cells[mdws_index, 16].PutValue(record.GetAttribute("新學號"));
// mdws.Cells[mdws_index, 17].PutValue(record.GetAttribute("備註"));
//}
mdws.AutoFitColumns();
mdws.Cells.SetColumnWidth(5, 8.5);
mdws.Cells.SetColumnWidth(11, 20);
wb.Worksheets.ActiveSheetIndex = 0;
//儲存 Excel
wb.Save(location, FileFormatType.Excel2003);
}
protected override void Build(System.Xml.XmlElement source, string location)
{
#region 建立 Excel
//從 Resources 將學籍異動名冊template讀出來
Workbook template = new Workbook();
template.Open(new MemoryStream(Properties.Resources.StudentUpdateRecordListTemplate), FileFormatType.Excel2003);
//產生 excel
Workbook wb = new Aspose.Cells.Workbook();
wb.Open(new MemoryStream(Properties.Resources.StudentUpdateRecordListTemplate), FileFormatType.Excel2003);
#endregion
#region 複製樣式-預設樣式、欄寬
//設定預設樣式
wb.DefaultStyle = template.DefaultStyle;
//複製樣版中前18個 Column(欄寬)
for (int m = 0; m < 18; m++)
{
/*
* 複製 template的第一個 Sheet的第 m個 Column
* 到 wb的第一個 Sheet的第 m個 Column
*/
wb.Worksheets[0].Cells.CopyColumn(template.Worksheets[0].Cells, m, m);
}
#endregion
#region 初始變數
/******************************
* rowi 填入學校資料用
* rowj 填入學生資料用
* num 計算清單份數
* numcount 計算每份清單頁數
* j 計算所產生清單頁數
* x 判斷個數是否為20被數用
******************************/
int rowi = 0, rowj = 1, num = source.SelectNodes("清單").Count, numcount = 1, j = 0;
bool x = false;
int recCount = 0;
int totalRec = source.SelectNodes("清單/異動紀錄").Count;
#endregion
foreach (XmlNode list in source.SelectNodes("清單"))
{
int i = 0;
#region 找出資料總數及判斷
//找出資料總數方便評估進度
int count = list.SelectNodes("異動紀錄").Count;
//判斷個數是否為20被數
if (count % 20 == 0)
{
x = true;
}
#endregion
#region 異動紀錄
//將xml資料填入至excel
foreach (XmlElement st in list.SelectNodes("異動紀錄"))
{
recCount++;
if (i % 20 == 0)
{
#region 複製樣式-欄高、範圍
//複製樣版中前287個 Row(欄高)
//for (int m = 0; m < 28; m++)
//{
// /*
// * 複製 template的第一個 Sheet的第m個 Row
// * 到 wb的第一個 Sheet的第(j * 28) + m個 Row
// */
// wb.Worksheets[0].Cells.CopyRow(template.Worksheets[0].Cells, m, (j * 28) + m);
//}
/*
* 複製Style(包含儲存格合併的資訊)
* 先用CreateRange()選取要複製的Range("A1", "R28")
* 再用CopyStyle複製另一個Range中的格式
*/
Range range = template.Worksheets[0].Cells.CreateRange(0, 28, false);
int t = j * 28;
wb.Worksheets[0].Cells.CreateRange(t, 28, false).Copy(range);
#endregion
#region 填入學校資料
//將學校資料填入適當的位置內
wb.Worksheets[0].Cells[rowi, 13].PutValue(source.SelectSingleNode("@學校代號").InnerText);
wb.Worksheets[0].Cells[rowi, 16].PutValue(list.SelectSingleNode("@科別代號").InnerText);
wb.Worksheets[0].Cells[rowi + 2, 2].PutValue(source.SelectSingleNode("@學校名稱").InnerText);
wb.Worksheets[0].Cells[rowi + 2, 7].PutValue(Convert.ToInt32(source.SelectSingleNode("@學年度").InnerText) + " 學年度 第 " + Convert.ToInt32(source.SelectSingleNode("@學期").InnerText) + " 學期");
wb.Worksheets[0].Cells[rowi + 2, 12].PutValue(list.SelectSingleNode("@科別").InnerText);
wb.Worksheets[0].Cells[rowi + 2, 14].PutValue(list.SelectSingleNode("@年級").InnerText);
#endregion
if (j > 0)
{
//插入分頁(在 j * 28 跟 (j * 28) +1 中間，R跟S中間)
wb.Worksheets[0].HPageBreaks.Add(j * 28, 18);
rowj += 8;
}
else
{
rowj = 6;
}
rowi += 28;
j++;
#region 顯示頁數
//顯示頁數
if (x != true)
{
wb.Worksheets[0].Cells[(28 * (j - 1)) + 27, 13].PutValue("第" + numcount + "頁，共" + Math.Ceiling((double)count / 20) + "頁");
}
else
{
wb.Worksheets[0].Cells[(28 * (j - 1)) + 27, 13].PutValue("第" + numcount + "頁，共" + (Math.Ceiling((double)count / 20) + 1) + "頁");
}
numcount++;
#endregion
}
#region 填入學生資料
string updatecode = st.SelectSingleNode("@異動代號").InnerText;
////將學生資料填入適當的位置內
//if (NewStudentNumberCodes.Contains(updatecode))
//{
// string strNum = "";
// if (!string.IsNullOrEmpty(st.SelectSingleNode("@新學號").InnerText))
// strNum = st.SelectSingleNode("@新學號").InnerText;
// else
// {
// string sid = "";
// if (!string.IsNullOrEmpty(st.SelectSingleNode("@學生編號").InnerText))
// sid = st.SelectSingleNode("@學生編號").InnerText;
// List<string> ids = new List<string>();
// ids.Add(sid);
// SHSchool.Data.SHStudent.RemoveByIDs(ids);
// SHSchool.Data.SHStudentRecord stud = SHSchool.Data.SHStudent.SelectByID(sid);
// strNum = stud.StudentNumber;
// }
// wb.Worksheets[0].Cells[rowj, 1].PutValue(strNum);
//}
//else
wb.Worksheets[0].Cells[rowj, 1].PutValue(st.SelectSingleNode("@學號").InnerText);
wb.Worksheets[0].Cells[rowj, 3].PutValue(st.SelectSingleNode("@姓名").InnerText);
wb.Worksheets[0].Cells[rowj, 4].PutValue(st.SelectSingleNode("@身分證號").InnerText);
wb.Worksheets[0].Cells[rowj, 8].PutValue(Util.ConvertDateStr2(st.SelectSingleNode("@備查日期").InnerText) + "\n" + st.SelectSingleNode("@備查文號").InnerText);
wb.Worksheets[0].Cells[rowj, 11].PutValue(st.SelectSingleNode("@異動代號").InnerText);
//wb.Worksheets[0].Cells[rowj, 12].PutValue(st.SelectSingleNode("@原因及事項").InnerText + (string.IsNullOrEmpty(st.GetAttribute("更正後資料")) ? "" : "\n" + st.GetAttribute("更正後資料")));
string UpdateData = "";
if (st.SelectSingleNode("@新資料")!=null)
{
// 更正學號填到另一格
if (updatecode != "401")
UpdateData = st.SelectSingleNode("@新資料").InnerText;
}
wb.Worksheets[0].Cells[rowj, 12].PutValue(st.SelectSingleNode("@原因及事項").InnerText+"\n"+UpdateData);
string strUpdateDate = Util.ConvertDateStr2(st.SelectSingleNode("@異動日期").InnerText);
//假設有異動學生學號的類別才出現新學號字樣
if (st.SelectSingleNode("@新學號")!=null)
if (!string.IsNullOrEmpty(st.SelectSingleNode("@新學號").InnerText))
{
int newNo;
if (int.TryParse(st.SelectSingleNode("@新學號").InnerText, out newNo))
strUpdateDate = newNo + "\n" + strUpdateDate;
}
else
{
// 更正學號
if (updatecode == "401")
{
if (st.SelectSingleNode("@新資料") != null)
if (!string.IsNullOrEmpty(st.SelectSingleNode("@新資料").InnerText))
strUpdateDate = st.SelectSingleNode("@新資料").InnerText + "\n" + strUpdateDate;
}
}
wb.Worksheets[0].Cells[rowj, 13].PutValue(strUpdateDate);
if(st.SelectSingleNode("@特殊身份代碼") !=null )
wb.Worksheets[0].Cells[rowj, 16].PutValue(st.SelectSingleNode("@特殊身份代碼").InnerText);
//wb.Worksheets[0].Cells[rowj, 16].PutValue(st.SelectSingleNode("@備註").InnerText);
#endregion
i++;
rowj++;
//回報進度
ReportProgress((int)(((double)recCount * 100.0) / ((double)totalRec)));
}
#endregion
#region 若個數為20倍數，處理單一頁面
if (x == true)
{
#region 複製樣式-欄高、範圍
//複製樣版前28個 Row(欄高)
//for (int m = 0; m < 28; m++)
//{
// /*
// * 複製 template的第一個 Sheet的第m個 Row
// * 到 wb的第一個 Sheet的第(j * 28) + m個 Row
// */
// wb.Worksheets[0].Cells.CopyRow(template.Worksheets[0].Cells, m, (j * 28) + m);
//}
/*
* 複製Style(包含儲存格合併的資訊)
* 先用CreateRange()選取要複製的Range("A1", "R28")
* 再用CopyStyle複製另一個Range中的格式
*/
Range range = template.Worksheets[0].Cells.CreateRange(0, 28, false);
int t = j * 28;
wb.Worksheets[0].Cells.CreateRange(t, 28, false).Copy(range);
#endregion
#region 填入學校資料
//將學校資料填入適當的位置內
wb.Worksheets[0].Cells[rowi, 13].PutValue(source.SelectSingleNode("@學校代號").InnerText);
wb.Worksheets[0].Cells[rowi, 16].PutValue(list.SelectSingleNode("@科別代號").InnerText);
wb.Worksheets[0].Cells[rowi + 2, 2].PutValue(source.SelectSingleNode("@學校名稱").InnerText);
wb.Worksheets[0].Cells[rowi + 2, 6].PutValue(Convert.ToInt32(source.SelectSingleNode("@學年度").InnerText));
wb.Worksheets[0].Cells[rowi + 2, 9].PutValue(Convert.ToInt32(source.SelectSingleNode("@學期").InnerText));
wb.Worksheets[0].Cells[rowi + 2, 12].PutValue(list.SelectSingleNode("@科別").InnerText);
#endregion
if (j > 0)
{
//插入分頁(在i跟i+1中間，O跟P中間)
wb.Worksheets[0].HPageBreaks.Add(j * 28, 18);
rowj += 8;
}
rowi += 28;
j++;
#region 顯示頁數
//顯示頁數
wb.Worksheets[0].Cells[(28 * (j - 1)) + 27, 13].PutValue("第" + numcount + "頁，共" + (Math.Ceiling((double)count / 20) + 1) + "頁");
numcount++;
#endregion
}
#endregion
#region 統計人數
//填入統計人數
wb.Worksheets[0].Cells.CreateRange(rowj, 1, 1, 2).UnMerge();
wb.Worksheets[0].Cells.Merge(rowj, 1, 1, 3);
wb.Worksheets[0].Cells[rowj, 1].PutValue("合 計 " + count.ToString() + " 名");
#endregion
wb.Worksheets[0].HPageBreaks.Add(j * 28, 18);
#region 設定變數
//調整新清單所使用變數
numcount = 1;
rowj = (28 * j) - 2;
rowi = (28 * j);
x = false;
#endregion
}
// 因2010年格式不同小修改
#region 學籍異動電子格式
//範本
Worksheet TemplateWb = wb.Worksheets["電子格式範本"];
//實做頁面
Worksheet DyWb = wb.Worksheets[wb.Worksheets.Add()];
//名稱
DyWb.Name = "電子格式";
//範圍
Range range_H = TemplateWb.Cells.CreateRange(0, 1, false);
Range range_R = TemplateWb.Cells.CreateRange(1, 1, false);
//拷貝range_H
DyWb.Cells.CreateRange(0, 1, false).Copy(range_H);
int DyWb_index = 0;
// 遇到特殊異動代碼要處理
List<string> spcCode = new List<string>();
spcCode.Add("211");
DAL.DALTransfer DALTranser = new DAL.DALTransfer();
// 格式轉換
List<GovernmentalDocument.Reports.List.rpt_UpdateRecord> _data = DALTranser.ConvertRptUpdateRecord(source);
// 排序 (依 班別、年級、科別代碼、異動代碼)
_data = (from data in _data orderby data.ClassType, GYear(data.GradeYear), data.DeptCode, data.UpdateCode select data).ToList();
foreach(GovernmentalDocument.Reports.List.rpt_UpdateRecord rec in _data )
{
DyWb_index++;
//每增加一行,複製一次
DyWb.Cells.CreateRange(DyWb_index, 1, false).Copy(range_R);
//班別
DyWb.Cells[DyWb_index, 0].PutValue(rec.ClassType);
//科別代碼
DyWb.Cells[DyWb_index, 1].PutValue(rec.DeptCode);
// 2 放上傳類別，請使用者自填
//學號
DyWb.Cells[DyWb_index, 3].PutValue(rec.StudentNumber);
//姓名
DyWb.Cells[DyWb_index, 4].PutValue(rec.Name);
//身分證字號
DyWb.Cells[DyWb_index, 5].PutValue(rec.IDNumber);
//註1
DyWb.Cells[DyWb_index, 6].PutValue(rec.Comment1);
//性別代碼
DyWb.Cells[DyWb_index, 7].PutValue(rec.GenderCode);
//出生日期
DyWb.Cells[DyWb_index, 8].PutValue(rec.Birthday);
//特殊身份代碼
DyWb.Cells[DyWb_index, 9].PutValue(rec.SpecialStatusCode);
//年級
DyWb.Cells[DyWb_index, 10].PutValue(rec.GradeYear);
//異動原因代碼
DyWb.Cells[DyWb_index, 11].PutValue(rec.UpdateCode);
//異動日期
DyWb.Cells[DyWb_index, 12].PutValue(rec.UpdateDate);
// 異動順序
DyWb.Cells[DyWb_index, 13].PutValue(rec.Order);
//備查日期
DyWb.Cells[DyWb_index, 14].PutValue(rec.LastADDate);
//備查文字
DyWb.Cells[DyWb_index, 15].PutValue(rec.LastADDoc);
//備查文號
DyWb.Cells[DyWb_index, 16].PutValue(rec.LastADNum);
//更正後資料
string strUpdateData = string.Empty;
//若是更正後資料有值則填入更正後資料
if (!string.IsNullOrEmpty(rec.NewData))
strUpdateData = rec.NewData;
//若是新學號中有值則填入新學號
//判斷strUpdateData是否已有值，若是已有值則加入斷行符號
if (!string.IsNullOrEmpty(rec.NewStudNumber))
strUpdateData += string.IsNullOrEmpty(strUpdateData) ? rec.NewStudNumber : "\n" + rec.NewStudNumber;
DyWb.Cells[DyWb_index, 17].PutValue(strUpdateData);
// 註2
DyWb.Cells[DyWb_index, 18].PutValue(rec.Comment2);
//備註說明
DyWb.Cells[DyWb_index, 19].PutValue(rec.Comment);
// 2011 新承辦單位修正，轉科讀取新學號
if (NewStudentNumberCodes.Contains(rec.UpdateCode))
{
List<string> ids = new List<string>();
ids.Add(rec.StudentID);
SHSchool.Data.SHStudent.RemoveByIDs(ids);
SHSchool.Data.SHStudentRecord studRec = SHSchool.Data.SHStudent.SelectByID(rec.StudentID);
if(studRec !=null)
DyWb.Cells[DyWb_index, 3].PutValue(studRec.StudentNumber);
DyWb.Cells[DyWb_index, 17].PutValue("");
}
}
//foreach (XmlElement Record in source.SelectNodes("清單/異動紀錄"))
//{
// DyWb_index++;
// //每增加一行,複製一次
// DyWb.Cells.CreateRange(DyWb_index, 1, false).Copy(range_R);
// //班別
// DyWb.Cells[DyWb_index, 0].PutValue(Record.GetAttribute("班別"));
// //科別代碼
// DyWb.Cells[DyWb_index, 1].PutValue((Record.ParentNode as XmlElement).GetAttribute("科別代號"));
// // 2 放上傳類別，請使用者自填
// //學號
// DyWb.Cells[DyWb_index, 3].PutValue(Record.GetAttribute("學號"));
// //姓名
// DyWb.Cells[DyWb_index, 4].PutValue(Record.GetAttribute("姓名"));
// //身分證字號
// DyWb.Cells[DyWb_index, 5].PutValue(Record.GetAttribute("身分證號"));
// //註1
// DyWb.Cells[DyWb_index, 6].PutValue(Record.GetAttribute("註1"));
// //性別代碼
// DyWb.Cells[DyWb_index, 7].PutValue(Record.GetAttribute("性別代號"));
// //出生日期
// DyWb.Cells[DyWb_index, 8].PutValue(GetBirthdateWithoutSlash(Record.GetAttribute("出生年月日")));
// //特殊身份代碼
// DyWb.Cells[DyWb_index, 9].PutValue(Record.GetAttribute("特殊身份代碼")); //原為抓取備註欄位
// //年級
// DyWb.Cells[DyWb_index, 10].PutValue((Record.ParentNode as XmlElement).GetAttribute("年級"));
// //異動原因代碼
// DyWb.Cells[DyWb_index, 11].PutValue(Record.GetAttribute("異動代號"));
// //異動日期
// DyWb.Cells[DyWb_index, 12].PutValue(GetBirthdateWithoutSlash(Record.GetAttribute("異動日期")));
// //原備查日期
// DyWb.Cells[DyWb_index, 13].PutValue(GetBirthdateWithoutSlash(Record.GetAttribute("備查日期")));
// //原備查文字
// DyWb.Cells[DyWb_index, 14].PutValue(GetNumAndSrt1(Record.GetAttribute("備查文號")));
// //原備查文號
// DyWb.Cells[DyWb_index, 15].PutValue(GetNumAndSrt2(Record.GetAttribute("備查文號")));
// // 捨棄
// ////舊班別
// //DyWb.Cells[DyWb_index, 15].PutValue(Record.GetAttribute("舊班別"));
// ////舊科別代碼
// //DyWb.Cells[DyWb_index, 16].PutValue(Record.GetAttribute("舊科別代碼"));
// //更正後資料
// string strUpdateData = string.Empty;
// //若是更正後資料有值則填入更正後資料
// if (!string.IsNullOrEmpty(Record.GetAttribute("更正後資料")))
// strUpdateData = Record.GetAttribute("更正後資料");
// //若是新學號中有值則填入新學號
// //判斷strUpdateData是否已有值，若是已有值則加入斷行符號
// if (!string.IsNullOrEmpty(Record.GetAttribute("新學號")))
// strUpdateData += string.IsNullOrEmpty(strUpdateData) ? Record.GetAttribute("新學號") : "\n" + Record.GetAttribute("新學號");
// DyWb.Cells[DyWb_index, 16].PutValue(strUpdateData);
// //備註說明
// DyWb.Cells[DyWb_index, 17].PutValue(Record.GetAttribute("備註"));
// // 2011 新承辦單位修正
// if(spcCode.Contains(Record.GetAttribute("異動代號")))
// {
// DyWb.Cells[DyWb_index, 3].PutValue(Record.GetAttribute("新學號"));
// DyWb.Cells[DyWb_index, 16].PutValue("");
// }
//}
DyWb.AutoFitColumns();
wb.Worksheets.RemoveAt("電子格式範本");
#endregion
wb.Worksheets.ActiveSheetIndex = 0;
//儲存 Excel
wb.Save(location, FileFormatType.Excel2003);
}
protected override void Build(System.Xml.XmlElement source, string location)
{
#region �إ� Excel
//�q Resources �N���y���ʦW�UtemplateŪ�X��
Workbook template = new Workbook();
template.Open(new MemoryStream(Properties.Resources.ExtendingStudentListTemplate), FileFormatType.Excel2003);
//���� excel
Workbook wb = new Aspose.Cells.Workbook();
wb.Open(new MemoryStream(Properties.Resources.ExtendingStudentListTemplate), FileFormatType.Excel2003);
#endregion
#region �ƻs�˦�-�w�]�˦��B��e
//�]�w�w�]�˦�
wb.DefaultStyle = template.DefaultStyle;
//�ƻs�˪����e18�� Column(��e)
for (int m = 0; m < 18; m++)
{
/*
* �ƻs template���Ĥ@�� Sheet���� m�� Column
* �� wb���Ĥ@�� Sheet���� m�� Column
*/
wb.Worksheets[0].Cells.CopyColumn(template.Worksheets[0].Cells, m, m);
}
#endregion
#region ��l�ܼ�
/******************************
* rowi ��J�Ǯո�ƥ�
* rowj ��J�ǥ�ƥ�
* num �p��M�����
* numcount �p��C���M�歶��
* j �p��Ҳ��ͲM�歶��
* x �P�_�ӼƬO�_��20�Q�ƥ�
******************************/
int rowi = 0, rowj = 1, num = source.SelectNodes("�M��").Count, numcount = 1, j = 0;
bool x = false;
int recCount = 0;
int totalRec = source.SelectNodes("�M��/���ʬ���").Count;
#endregion
foreach (XmlNode list in source.SelectNodes("�M��"))
{
int i = 0;
#region ��X����`�ƤΧP�_
//��X����`�Ƥ�K����i��
int count = list.SelectNodes("���ʬ���").Count;
//�P�_�ӼƬO�_��20�Q��
if (count % 20 == 0)
{
x = true;
}
#endregion
#region ���ʬ���
//�Nxml��ƶ�J��excel
foreach (XmlNode st in list.SelectNodes("���ʬ���"))
{
recCount++;
if (i % 20 == 0)
{
#region �ƻs�˦�-�氪�B�d��
//�ƻs�˪����e287�� Row(�氪)
//for (int m = 0; m < 28; m++)
//{
// /*
// * �ƻs template���Ĥ@�� Sheet����m�� Row
// * �� wb���Ĥ@�� Sheet����(j * 28) + m�� Row
// */
// wb.Worksheets[0].Cells.CopyRow(template.Worksheets[0].Cells, m, (j * 28) + m);
//}
/*
* �ƻsStyle(�]�t�x�s��X�֪���T)
* ����CreateRange()����n�ƻs��Range("A1", "R28")
* �A��CopyStyle�ƻs�t�@��Range�����榡
*/
Range range = template.Worksheets[0].Cells.CreateRange(0, 28, false);
int t= j * 28;
wb.Worksheets[0].Cells.CreateRange(t,28,false).Copy(range);
#endregion
#region ��J�Ǯո��
//�N�Ǯո�ƶ�J�A�����m��
wb.Worksheets[0].Cells[rowi, 13].PutValue(source.SelectSingleNode("@�ǮեN��").InnerText);
wb.Worksheets[0].Cells[rowi, 16].PutValue(list.SelectSingleNode("@��O�N��").InnerText);
wb.Worksheets[0].Cells[rowi + 2, 2].PutValue(source.SelectSingleNode("@�ǮզW��").InnerText);
wb.Worksheets[0].Cells[rowi + 2, 7].PutValue(Convert.ToInt32(source.SelectSingleNode("@�Ǧ~��").InnerText)+" �Ǧ~�� �� "+Convert.ToInt32(source.SelectSingleNode("@�Ǵ�").InnerText)+" �Ǵ�");
wb.Worksheets[0].Cells[rowi + 2, 12].PutValue(list.SelectSingleNode("@��O").InnerText);
wb.Worksheets[0].Cells[rowi + 2, 14].PutValue(list.SelectSingleNode("@�~��").InnerText);
#endregion
if (j > 0)
{
//���J����(�b j * 28 �� (j * 28) +1 �����AR��S����)
wb.Worksheets[0].HPageBreaks.Add(j * 28, 18);
rowj += 8;
}
else
{
rowj = 6;
}
rowi += 28;
j++;
#region ��ܭ���
//��ܭ���
if (x != true)
{
wb.Worksheets[0].Cells[(28 * (j - 1)) + 27, 13].PutValue("��" + numcount + "���A�@" + Math.Ceiling((double)count / 20) + "��");
}
else
{
wb.Worksheets[0].Cells[(28 * (j - 1)) + 27, 13].PutValue("��" + numcount + "���A�@" + (Math.Ceiling((double)count / 20) + 1) + "��");
}
numcount++;
#endregion
}
#region ��J�ǥ��
//�N�ǥ�ƶ�J�A�����m��
wb.Worksheets[0].Cells[rowj, 1].PutValue(st.SelectSingleNode("@�Ǹ�").InnerText);
wb.Worksheets[0].Cells[rowj, 3].PutValue(st.SelectSingleNode("@�m�W").InnerText);
wb.Worksheets[0].Cells[rowj, 4].PutValue(st.SelectSingleNode("@�����Ҹ�").InnerText);
wb.Worksheets[0].Cells[rowj, 8].PutValue(Util.ConvertDateStr2(st.SelectSingleNode("@�Ƭd���").InnerText) + "\n" + st.SelectSingleNode("@�Ƭd�帹").InnerText);
wb.Worksheets[0].Cells[rowj, 11].PutValue(st.SelectSingleNode("@���ʥN��").InnerText);
wb.Worksheets[0].Cells[rowj, 12].PutValue(st.SelectSingleNode("@��]�Ψƶ�").InnerText);
if (st.SelectSingleNode("@�s�Ǹ�").InnerText == "")
{
wb.Worksheets[0].Cells[rowj, 13].PutValue(Util.ConvertDateStr2(st.SelectSingleNode("@���ʤ��").InnerText));
}
else
{
wb.Worksheets[0].Cells[rowj, 13].PutValue(st.SelectSingleNode("@�s�Ǹ�").InnerText + "\n" + Util.ConvertDateStr2(st.SelectSingleNode("@���ʤ��").InnerText));
}
//wb.Worksheets[0].Cells[rowj, 16].PutValue(st.SelectSingleNode("@�Ƶ�").InnerText);
if(st.SelectSingleNode("@�S������N�X")!=null)
wb.Worksheets[0].Cells[rowj, 16].PutValue(st.SelectSingleNode("@�S������N�X").InnerText);
#endregion
i++;
rowj++;
//�^���i��
ReportProgress((int)(((double)recCount * 100.0) / ((double)totalRec)));
}
#endregion
#region �Y�ӼƬ�20���ơA�B�z��@����
if (x == true)
{
#region �ƻs�˦�-�氪�B�d��
//�ƻs�˪��e28�� Row(�氪)
//for (int m = 0; m < 28; m++)
//{
// /*
// * �ƻs template���Ĥ@�� Sheet����m�� Row
// * �� wb���Ĥ@�� Sheet����(j * 28) + m�� Row
// */
// wb.Worksheets[0].Cells.CopyRow(template.Worksheets[0].Cells, m, (j * 28) + m);
//}
/*
* �ƻsStyle(�]�t�x�s��X�֪���T)
* ����CreateRange()����n�ƻs��Range("A1", "R28")
* �A��CopyStyle�ƻs�t�@��Range�����榡
*/
Range range = template.Worksheets[0].Cells.CreateRange(0, 28, false);
int t= j * 28;
wb.Worksheets[0].Cells.CreateRange(t, 28, false).Copy(range);
#endregion
#region ��J�Ǯո��
//�N�Ǯո�ƶ�J�A�����m��
wb.Worksheets[0].Cells[rowi, 13].PutValue(source.SelectSingleNode("@�ǮեN��").InnerText);
wb.Worksheets[0].Cells[rowi, 16].PutValue(list.SelectSingleNode("@��O�N��").InnerText);
wb.Worksheets[0].Cells[rowi + 2, 2].PutValue(source.SelectSingleNode("@�ǮզW��").InnerText);
wb.Worksheets[0].Cells[rowi + 2, 7].PutValue(Convert.ToInt32(source.SelectSingleNode("@�Ǧ~��").InnerText) + " �Ǧ~�� �� " + Convert.ToInt32(source.SelectSingleNode("@�Ǵ�").InnerText) + " �Ǵ�");
wb.Worksheets[0].Cells[rowi + 2, 12].PutValue(list.SelectSingleNode("@��O").InnerText);
#endregion
if (j > 0)
{
//���J����(�bi��i+1�����AO��P����)
wb.Worksheets[0].HPageBreaks.Add(j * 28, 18);
rowj += 8;
}
rowi += 28;
j++;
#region ��ܭ���
//��ܭ���
wb.Worksheets[0].Cells[(28 * (j - 1)) + 27, 13].PutValue("��" + numcount + "���A�@" + (Math.Ceiling((double)count / 20) + 1) + "��");
numcount++;
#endregion
}
#endregion
#region �έp�H��
//��J�έp�H��
wb.Worksheets[0].Cells.CreateRange(rowj, 1, 1, 2).UnMerge();
wb.Worksheets[0].Cells.Merge(rowj, 1, 1, 3);
wb.Worksheets[0].Cells[rowj, 1].PutValue("�X �p " + count.ToString() + " �W");
#endregion
wb.Worksheets[0].HPageBreaks.Add(j * 28, 18);
#region �]�w�ܼ�
//�վ�s�M��Ҩϥ��ܼ�
numcount = 1;
rowj = (28 * j) - 2;
rowi = (28 * j);
x = false;
#endregion
}
Worksheet mdws = wb.Worksheets[1];
mdws.Name = "�q�l�榡";
int mdws_index = 0;
foreach (XmlElement record in source.SelectNodes("�M��/���ʬ���"))
{
mdws_index++;
// �����~�Ǧ~��
mdws.Cells[mdws_index, 0].PutValue(record.GetAttribute("�����~�Ǧ~��"));
mdws.Cells[mdws_index, 1].PutValue(record.GetAttribute("�Z�O"));
mdws.Cells[mdws_index, 2].PutValue((record.ParentNode as XmlElement).GetAttribute("��O�N��"));
mdws.Cells[mdws_index, 3].PutValue("");
mdws.Cells[mdws_index, 4].PutValue(record.GetAttribute("�Ǹ�"));
mdws.Cells[mdws_index, 5].PutValue(record.GetAttribute("�m�W"));
mdws.Cells[mdws_index, 6].PutValue(record.GetAttribute("�����Ҹ�"));
mdws.Cells[mdws_index, 7].PutValue(record.GetAttribute("��1"));
mdws.Cells[mdws_index, 8].PutValue(record.GetAttribute("�ʧO�N��"));
mdws.Cells[mdws_index, 9].PutValue((BL.Util.ConvertDate1(record.GetAttribute("�X�ͦ~���"))));
mdws.Cells[mdws_index, 10].PutValue(record.GetAttribute("�S������N�X")); //�쬰�����������
mdws.Cells[mdws_index, 11].PutValue(record.GetAttribute("���ʥN��"));
mdws.Cells[mdws_index, 12].PutValue(BL.Util.ConvertDate1(record.GetAttribute("���ʤ��")));
mdws.Cells[mdws_index, 13].PutValue(BL.Util.ConvertDate1(record.GetAttribute("�Ƭd���")));
mdws.Cells[mdws_index, 14].PutValue(BL.Util.GetDocNo_Doc(record.GetAttribute("�Ƭd�帹")));
mdws.Cells[mdws_index, 15].PutValue(BL.Util.GetDocNo_No(record.GetAttribute("�Ƭd�帹")));
mdws.Cells[mdws_index, 16].PutValue(record.GetAttribute("�Ƶ�"));
}
mdws.AutoFitColumns();
mdws.Cells.SetColumnWidth(5, 8.5);
mdws.Cells.SetColumnWidth(11, 20);
wb.Worksheets.ActiveSheetIndex = 0;
//�x�s Excel
wb.Save(location, FileFormatType.Excel2003);
}
private void ImportSCAttendRecords()
{
this.addMsg(" ==== 開始匯入修課學生 ===");
/* 取得目前所有的修課紀錄 */
List<UDT.SCAttendExt> attRecs = (new AccessHelper()).Select<UDT.SCAttendExt>();
Dictionary<string, Dictionary<string, UDT.SCAttendExt>> dicAttRecs = new Dictionary<string, Dictionary<string, UDT.SCAttendExt>>();
foreach (UDT.SCAttendExt att in attRecs)
{
if (!dicAttRecs.ContainsKey(att.CourseID.ToString()))
dicAttRecs.Add(att.CourseID.ToString(), new Dictionary<string, UDT.SCAttendExt>());
dicAttRecs[att.CourseID.ToString()].Add(att.StudentID.ToString(), att);
}
/* 取得所有課程 ，以便從課程代碼 及班及名稱，找出 課程系統編號 */
this.GetAllCourses();
List<UDT.CourseExt> allCourses = (new AccessHelper()).Select<UDT.CourseExt>();
Dictionary<string, UDT.CourseExt> dicAllCourses = new Dictionary<string, UDT.CourseExt>();
foreach (UDT.CourseExt course in allCourses)
{
if (this.dicCourses.ContainsKey(course.CourseID.ToString()))
{
string key = string.Format("{0}_{1}", course.SubjectCode, course.ClassName);
dicAllCourses.Add(key, course);
}
}
/* 取得所有學生資料，以便從學號找出學生編號 */
List<K12.Data.StudentRecord> allStudents = K12.Data.Student.SelectAll();
Dictionary<string, K12.Data.StudentRecord> dicAllStudents = new Dictionary<string, K12.Data.StudentRecord>();
foreach (K12.Data.StudentRecord stud in allStudents)
{
if (!string.IsNullOrWhiteSpace(stud.StudentNumber))
dicAllStudents.Add(stud.StudentNumber, stud);
}
/* 讀取 Excel 資料 */
Workbook wb = new Aspose.Cells.Workbook();
wb.Open(this.textBoxX1.Text);
Worksheet ws = wb.Worksheets[0]; //修課紀錄
int rowIndex = 1;
while (ws.Cells[rowIndex, 3].Value != null)
{
string studNo = GetCellValue(ws.Cells[rowIndex, 3].Value);
if (!dicAllStudents.ContainsKey(studNo))
{
this.addMsg(string.Format("找不到學生，學號：{0}, rowNo: {1} ", studNo, rowIndex.ToString()));
}
else
{
string studID = dicAllStudents[studNo].ID ;
string courseCode = GetCellValue(ws.Cells[rowIndex, 7].Value);
string classCode = GetCellValue(ws.Cells[rowIndex, 8].Value);
if (classCode.Length > 2)
classCode = classCode.Substring(1, 2);
string key = string.Format("{0}_{1}", courseCode, classCode);
if (!dicAllCourses.ContainsKey(key))
{
this.addMsg(string.Format("找不到課程，課號：{0}, 班及：{1}, rowNo: {2} ", courseCode, classCode, rowIndex.ToString()));
}
else
{
string courseID = dicAllCourses[key].CourseID.ToString();
//判斷該生是否已經修課，若是，則 skip ，否則新增 !
if (dicAttRecs.ContainsKey(courseID) && dicAttRecs[courseID].ContainsKey(studID))
{
//do nothing
string msg = string.Format("學號：{0} 已修課程： 課號 = {1}, 班號 = {2}, rowindex ={3}，故忽略不匯入！", studNo, courseCode, classCode, rowIndex.ToString());
this.addMsg(msg);
}
else
{
// 新增修課紀錄
UDT.SCAttendExt attRec = new UDT.SCAttendExt();
attRec.StudentID = int.Parse(studID);
attRec.CourseID = int.Parse(courseID);
List<ActiveRecord> recs = new List<ActiveRecord>();
recs.Add(attRec);
(new AccessHelper()).InsertValues( recs);
}
}
}
rowIndex += 1;
this.lblStatus.Text = rowIndex.ToString();
Application.DoEvents();
}
}
public string ImportStandard(string refid, string refname, string deptcode)
{
try
{
if (OperatorProvider.Provider.Current().IsSystem)
{
return("超级管理员无此操作权限");
}
string orgId = OperatorProvider.Provider.Current().OrganizeId;//所属公司
int error = 0;
int success = 0;
string message = "请选择文件格式正确的文件再导入!";
string falseMessage = "";
int count = HttpContext.Request.Files.Count;
if (count > 0)
{
if (HttpContext.Request.Files.Count != 2)
{
return("请按正确的方式导入两个文件.");
}
HttpPostedFileBase file = HttpContext.Request.Files[0];
HttpPostedFileBase file2 = HttpContext.Request.Files[1];
if (string.IsNullOrEmpty(file.FileName) || string.IsNullOrEmpty(file2.FileName))
{
return(message);
}
Boolean isZip1 = file.FileName.Substring(file.FileName.IndexOf('.')).Contains("zip"); //第一个文件是否为Zip格式
Boolean isZip2 = file2.FileName.Substring(file2.FileName.IndexOf('.')).Contains("zip"); //第二个文件是否为Zip格式
if ((isZip1 || isZip2) == false || (isZip1 && isZip2) == true)
{
return(message);
}
string fileName1 = DateTime.Now.ToString("yyyyMMddHHmmss") + System.IO.Path.GetExtension(file.FileName);
file.SaveAs(Server.MapPath("~/Resource/temp/" + fileName1));
string fileName2 = DateTime.Now.ToString("yyyyMMddHHmmss") + System.IO.Path.GetExtension(file2.FileName);
file2.SaveAs(Server.MapPath("~/Resource/temp/" + fileName2));
string decompressionDirectory = Server.MapPath("~/Resource/decompression/") + DateTime.Now.ToString("yyyyMMddhhmmssfff") + "\\";
Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook();
if (isZip1)
{
UnZip(Server.MapPath("~/Resource/temp/" + fileName1), decompressionDirectory, "", true);
wb.Open(Server.MapPath("~/Resource/temp/" + fileName2));
}
else
{
UnZip(Server.MapPath("~/Resource/temp/" + fileName2), decompressionDirectory, "", true);
wb.Open(Server.MapPath("~/Resource/temp/" + fileName1));
}
Aspose.Cells.Cells cells = wb.Worksheets[0].Cells;
DataTable dt = cells.ExportDataTable(1, 0, cells.MaxDataRow, cells.MaxColumn, false);
for (int i = 0; i < dt.Rows.Count; i++)
{
StdsysFilesEntity standard = new StdsysFilesEntity();
standard.ID = Guid.NewGuid().ToString();
//文件名称
string filename = dt.Rows[i][0].ToString();
//文件编号
string fileno = dt.Rows[i][1].ToString();
//---****值存在空验证*****--
if (string.IsNullOrEmpty(filename) || string.IsNullOrEmpty(dt.Rows[i][2].ToString()))
{
falseMessage += "</br>" + "第" + (i + 1) + "行值存在空,未能导入.";
error++;
continue;
}
bool conbool = false;
//文件路径
string[] filepaths = dt.Rows[i][2].ToString().Split(';');
var filepath = "";
for (int j = 0; j < filepaths.Length; j++)
{
filepath = filepaths[j];
if (string.IsNullOrEmpty(filepath))
{
continue;
}
//---****文件格式验证*****--
if (!(filepath.Substring(filepath.IndexOf('.')).Contains("doc") || filepath.Substring(filepath.IndexOf('.')).Contains("docx") || filepath.Substring(filepath.IndexOf('.')).Contains("pdf")))
{
falseMessage += "</br>" + "第" + (i + 1) + "行指定附件格式不正确,未能导入.";
error++;
conbool = true;
continue;
}
//---****文件是否存在验证*****--
if (!System.IO.File.Exists(decompressionDirectory + filepath))
{
falseMessage += "</br>" + "第" + (i + 1) + "行指定附件不存在,未能导入.";
error++;
conbool = true;
continue;
}
var fileinfo = new FileInfo(decompressionDirectory + filepath);
FileInfoEntity fileInfoEntity = new FileInfoEntity();
string fileguid = Guid.NewGuid().ToString();
fileInfoEntity.Create();
fileInfoEntity.RecId = standard.ID; //关联ID
fileInfoEntity.FileName = filepath;
fileInfoEntity.FilePath = "~/Resource/StandardSystem/" + fileguid + fileinfo.Extension;
fileInfoEntity.FileSize = (Math.Round(decimal.Parse(fileinfo.Length.ToString()) / decimal.Parse("1024"), 2)).ToString();//文件大小（kb）
fileInfoEntity.FileExtensions = fileinfo.Extension;
fileInfoEntity.FileType = fileinfo.Extension.Replace(".", "");
TransportRemoteToServer(Server.MapPath("~/Resource/StandardSystem/"), decompressionDirectory + filepath, fileguid + fileinfo.Extension);
fileinfobll.SaveForm("", fileInfoEntity);
}
if (conbool)
{
continue;
}
standard.FileName = filename;
standard.FileNo = fileno;
standard.RefId = refid;
standard.RefName = refname;
DepartmentEntity deptEntity = deptBll.GetEntityByCode(deptcode);
if (deptEntity != null)
{
standard.PubDepartId = deptEntity.DepartmentId;
standard.PubDepartName = deptEntity.FullName;
}
else
{
standard.PubDepartId = OperatorProvider.Provider.Current().DeptId;
standard.PubDepartName = OperatorProvider.Provider.Current().DeptName;
}
if (!string.IsNullOrEmpty(dt.Rows[i][3].ToString()))
{
standard.PubDate = Convert.ToDateTime(dt.Rows[i][3].ToString());
}
if (!string.IsNullOrEmpty(dt.Rows[i][4].ToString()))
{
standard.ReviseDate = Convert.ToDateTime(dt.Rows[i][4].ToString());
}
if (!string.IsNullOrEmpty(dt.Rows[i][5].ToString()))
{
standard.UseDate = Convert.ToDateTime(dt.Rows[i][5].ToString());
}
standard.Remark = !string.IsNullOrEmpty(dt.Rows[i][6].ToString()) ? dt.Rows[i][6].ToString() : "";
try
{
stdsysfilesbll.SaveForm(standard.ID, standard);
success++;
}
catch
{
error++;
}
}
message = "共有" + dt.Rows.Count + "条记录,成功导入" + success + "条，失败" + error + "条";
message += "</br>" + falseMessage;
}
return(message);
}
catch (Exception e)
{
return("导入的Excel数据格式不正确，请下载标准模板重新填写！");
}
}
// 處理台中樣版
private void ProcessTaiChung(System.Xml.XmlElement source, string location)
{
// 資料轉換
Dictionary <string, JHPermrec.UpdateRecord.DAL.StudBatchUpdateRecContentEntity> data = StudBatchUpdateRecEntity.ConvertGetContentData(source);
#region 建立 Excel
int tmpY, tmpM;
tmpY = DateTime.Now.Year;
tmpM = DateTime.Now.Month;
string tmpRptY, tmpRptM;
tmpRptY = (tmpY - 1911).ToString();
if (tmpM < 10)
{
tmpRptM = "0" + tmpM.ToString();
}
else
{
tmpRptM = tmpM.ToString();
}
string strPrintDate = UpdateRecordUtil.ChangeDate1911(DateTime.Now.ToString());
// 台中轉入與新生樣式相同
//從 Resources 將新生名冊template讀出來
Workbook template = new Workbook();
//template.Worksheets[0].PageSetup.
//template.Open(new MemoryStream(GDResources.JEnrollmentListTemplate_TaiChung), FileFormatType.Excel2003);
template.Open(new MemoryStream(GDResources.JTransferListTemplate_TaiChung), FileFormatType.Excel2003);
//產生 excel
Workbook wb = new Aspose.Cells.Workbook();
//wb.Open(new MemoryStream(GDResources.JEnrollmentListTemplate_TaiChung), FileFormatType.Excel2003);
wb.Open(new MemoryStream(GDResources.JTransferListTemplate_TaiChung), FileFormatType.Excel2003);
#endregion
#region 複製樣式-預設樣式、欄寬
//設定預設樣式
wb.DefaultStyle = template.DefaultStyle;
#endregion
#region 初始變數
int rowj = 1;
int recCount = 0;
int totalRec = data.Count;
#endregion
wb.Worksheets[0].Cells[0, 0].PutValue(StudBatchUpdateRecEntity.GetContentSchoolName() + " 轉入學生名冊");
wb.Worksheets[0].Cells[1, 6].PutValue(tmpRptY + "年" + tmpRptM + "月填報");
//wb.Worksheets[0].Cells[0, 0].PutValue(StudBatchUpdateRecEntity.GetContentSchoolName () + " 國民中學入學學生名冊");
//wb.Worksheets[0].Cells[1, 10].PutValue(tmpRptY + "年" + tmpM + "月填製");
Range templateRow = template.Worksheets[0].Cells.CreateRange(4, 7, false);
//string strGradeYear="";
rowj = 4;
//將xml資料填入至excel
foreach (StudBatchUpdateRecContentEntity sburce in data.Values)
{
//填入前先複製格式
wb.Worksheets[0].Cells.CreateRange(rowj, 7, false).Copy(templateRow);
//if (rowj == 4)
//strGradeYear = sburce.GetClassYear ();
recCount++;
//將學生資料填入適當的位置內
wb.Worksheets[0].Cells[rowj, 0].PutValue(sburce.GetStudentNumber());
wb.Worksheets[0].Cells[rowj, 1].PutValue(sburce.GetName());
wb.Worksheets[0].Cells[rowj, 2].PutValue(sburce.GetGradeYear());
wb.Worksheets[0].Cells[rowj, 3].PutValue(StudBatchUpdateRecEntity.GetContentSemester());
//異動年月
DateTime dt;
if (DateTime.TryParse(sburce.GetUpdateDate(), out dt))
{
wb.Worksheets[0].Cells[rowj, 4].PutValue("" + (dt.Year - 1911));
wb.Worksheets[0].Cells[rowj, 5].PutValue("" + dt.Month);
}
//異動情形
wb.Worksheets[0].Cells[rowj, 6].PutValue(sburce.GetImportExportSchool());
//原因
wb.Worksheets[0].Cells[rowj, 7].PutValue(sburce.GetUpdateDescription());
rowj++;
//回報進度
ReportProgress((int)(((double)recCount * 100.0) / ((double)totalRec)));
}
// Title
//wb.Worksheets[0].Cells[1, 0].PutValue(StudBatchUpdateRecEntity.GetContentSchoolYear() + "學年度第" + StudBatchUpdateRecEntity.GetContentSemester () + "學期 "+strGradeYear +"年級");
wb.Worksheets[0].Cells[1, 0].PutValue(StudBatchUpdateRecEntity.GetContentSchoolYear() + "學年度第" + StudBatchUpdateRecEntity.GetContentSemester() + "學期 異動：轉入");
//合計人數
wb.Worksheets[0].Cells[rowj, 0].Style.HorizontalAlignment = TextAlignmentType.Center;
wb.Worksheets[0].Cells[rowj, 0].PutValue("合計");
wb.Worksheets[0].Cells[rowj, 1].Style.HorizontalAlignment = TextAlignmentType.Center;
wb.Worksheets[0].Cells[rowj, 1].PutValue("" + data.Count + " 名");
wb.Worksheets[0].Cells[rowj, 3].Style.HorizontalAlignment = TextAlignmentType.Center;
wb.Worksheets[0].Cells[rowj, 3].PutValue("以下空白");
// 畫表
//Style st2 = wb.Styles[wb.Styles.Add()];
//StyleFlag sf2 = new StyleFlag();
//sf2.Borders = true;
//st2.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
//st2.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;
//st2.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;
//st2.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
//int tmpMaxRow = 0, tmpMaxCol = 0;
//for (int wbIdx1 = 0; wbIdx1 < wb.Worksheets.Count; wbIdx1++)
//{
// tmpMaxRow = wb.Worksheets[wbIdx1].Cells.MaxDataRow - 3;
// tmpMaxCol = wb.Worksheets[wbIdx1].Cells.MaxDataColumn + 1;
// wb.Worksheets[wbIdx1].Cells.CreateRange(4, 0, tmpMaxRow, tmpMaxCol).ApplyStyle(st2, sf2);
//}
//儲存 Excel
wb.Save(location, FileFormatType.Excel2003);
}
public static void XuatDuLieuRaExcel(int iRowPara,
int iColumnPara,
string strSubHeaderPara,
System.Data.DataTable tblBangDuLieuPara,
string strTemplateNamePara)
{
//Đường dẫn file template
string strSourceFilePri = string.Format("{0}{1}{2}",
System.Windows.Forms.Application.StartupPath,
PATH_TEMPLATES, strTemplateNamePara);
SaveFileDialog saveFileDialogPri = new SaveFileDialog();
saveFileDialogPri.Filter = "Excel files (*.xls)|*.xls|All files (*.*)|*.*";
saveFileDialogPri.FilterIndex = 1;
if (saveFileDialogPri.ShowDialog() == DialogResult.OK)
{
FileStream streamTemp = new FileStream(strSourceFilePri, FileMode.Open);
Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook();
workbook.Open(streamTemp);
workbook.Worksheets.Add();
Aspose.Cells.Worksheet worksheet = workbook.Worksheets[0];
//Set cell store subHeader
Aspose.Cells.Cells cellHeader = worksheet.Cells;
//cellHeader.Merge(2, 0, 1, tblBangDuLieuPara.Columns.Count);
worksheet.Cells["A3"].PutValue(strSubHeaderPara);
//worksheet.IsGridlinesVisible = false;
worksheet.Cells.ImportDataTable(tblBangDuLieuPara, false, iRowPara, iColumnPara, tblBangDuLieuPara.Rows.Count, tblBangDuLieuPara.Columns.Count);
//Formatting for cells store database
for (int i = 0; i < tblBangDuLieuPara.Rows.Count; i++)
{
for (int j = 0; j < tblBangDuLieuPara.Columns.Count; j++)
{
Aspose.Cells.Cell cell = worksheet.Cells[iRowPara + i, j];
workbook.Styles.Add();
Aspose.Cells.Style style = cell.GetStyle();
style.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;
style.Borders[BorderType.BottomBorder].Color = Color.Silver;
style.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
style.Borders[BorderType.TopBorder].Color = Color.Silver;
style.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
style.Borders[BorderType.LeftBorder].Color = Color.Silver;
style.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;
style.Borders[BorderType.RightBorder].Color = Color.Silver;
cell.SetStyle(style);
}
}
//worksheet.AutoFitColumns();
//Save excel file
workbook.Save(saveFileDialogPri.FileName, FileFormatType.Default);
MessageBox.Show(WorkingContext.LangManager.GetString("frmRestSheet_ExportExcel_Messa"),
WorkingContext.LangManager.GetString("Message"),
MessageBoxButtons.OK, MessageBoxIcon.Information);
streamTemp.Close();
if (File.Exists(saveFileDialogPri.FileName))
Process.Start(saveFileDialogPri.FileName);
}
}
protected override void Build(System.Xml.XmlElement source, string location)
{
#region 建立 Excel
//從 Resources 將延修生學籍名冊template讀出來
Workbook template = new Workbook();
template.Open(new MemoryStream(Properties.Resources.ExtendingStudentUpdateRecordListTemplate), FileFormatType.Excel2003);
//產生 excel
Workbook wb = new Aspose.Cells.Workbook();
wb.Open(new MemoryStream(Properties.Resources.ExtendingStudentUpdateRecordListTemplate), FileFormatType.Excel2003);
#endregion
#region 複製樣式-預設樣式、欄寬
//設定預設樣式
wb.DefaultStyle = template.DefaultStyle;
//複製樣版中前18個 Column(欄寬)
for (int m = 0; m < 18; m++)
{
/*
* 複製 template的第一個 Sheet的第 m個 Column
* 到 wb的第一個 Sheet的第 m個 Column
*/
wb.Worksheets[0].Cells.CopyColumn(template.Worksheets[0].Cells, m, m);
}
#endregion
#region 初始變數
/******************************
* rowi 填入學校資料用
* rowj 填入學生資料用
* num 計算清單份數
* numcount 計算每份清單頁數
* j 計算所產生清單頁數
* x 判斷個數是否為20被數用
******************************/
int rowi = 0, rowj = 1, num = source.SelectNodes("清單").Count, numcount = 1, j = 0;
bool x = false;
int recCount = 0;
int totalRec = source.SelectNodes("清單/異動紀錄").Count;
#endregion
foreach (XmlNode list in source.SelectNodes("清單"))
{
int i = 0;
#region 找出資料總數及判斷
//找出資料總數方便評估進度
int count = list.SelectNodes("異動紀錄").Count;
//判斷個數是否為20被數
if (count % 20 == 0)
{
x = true;
}
#endregion
#region 異動紀錄
//將xml資料填入至excel
foreach (XmlNode st in list.SelectNodes("異動紀錄"))
{
recCount++;
if (i % 20 == 0)
{
#region 複製樣式-欄高、範圍
//複製樣版中前287個 Row(欄高)
//for (int m = 0; m < 28; m++)
//{
// /*
// * 複製 template的第一個 Sheet的第m個 Row
// * 到 wb的第一個 Sheet的第(j * 28) + m個 Row
// */
// wb.Worksheets[0].Cells.CopyRow(template.Worksheets[0].Cells, m, (j * 28) + m);
//}
/*
* 複製Style(包含儲存格合併的資訊)
* 先用CreateRange()選取要複製的Range("A1", "R28")
* 再用CopyStyle複製另一個Range中的格式
*/
Range range = template.Worksheets[0].Cells.CreateRange(0, 28, false);
int t = j * 28;
wb.Worksheets[0].Cells.CreateRange(t, 28, false).Copy(range);
#endregion
#region 填入學校資料
//將學校資料填入適當的位置內
wb.Worksheets[0].Cells[rowi, 13].PutValue(source.SelectSingleNode("@學校代號").InnerText);
wb.Worksheets[0].Cells[rowi, 16].PutValue(list.SelectSingleNode("@科別代號").InnerText);
wb.Worksheets[0].Cells[rowi + 2, 2].PutValue(source.SelectSingleNode("@學校名稱").InnerText);
wb.Worksheets[0].Cells[rowi + 2, 7].PutValue(Convert.ToInt32(source.SelectSingleNode("@學年度").InnerText) + "學年度第" + Convert.ToInt32(source.SelectSingleNode("@學期").InnerText) + "學期");
wb.Worksheets[0].Cells[rowi + 2, 12].PutValue(list.SelectSingleNode("@科別").InnerText);
#endregion
if (j > 0)
{
//插入分頁(在 j * 28 跟 (j * 28) +1 中間，R跟S中間)
wb.Worksheets[0].HPageBreaks.Add(j * 28, 18);
rowj += 8;
}
else
{
rowj = 6;
}
rowi += 28;
j++;
#region 顯示頁數
//顯示頁數
if (x != true)
{
wb.Worksheets[0].Cells[(28 * (j - 1)) + 27, 13].PutValue("第" + numcount + "頁，共" + Math.Ceiling((double)count / 20) + "頁");
}
else
{
wb.Worksheets[0].Cells[(28 * (j - 1)) + 27, 13].PutValue("第" + numcount + "頁，共" + (Math.Ceiling((double)count / 20) + 1) + "頁");
}
numcount++;
#endregion
}
#region 填入學生資料
//將學生資料填入適當的位置內
wb.Worksheets[0].Cells[rowj, 1].PutValue(st.SelectSingleNode("@學號").InnerText);
wb.Worksheets[0].Cells[rowj, 3].PutValue(st.SelectSingleNode("@姓名").InnerText);
wb.Worksheets[0].Cells[rowj, 4].PutValue(st.SelectSingleNode("@身分證號").InnerText);
wb.Worksheets[0].Cells[rowj, 8].PutValue(Util.ConvertDateStr2(st.SelectSingleNode("@備查日期").InnerText) + "\n" + st.SelectSingleNode("@備查文號").InnerText);
wb.Worksheets[0].Cells[rowj, 11].PutValue(st.SelectSingleNode("@異動代號").InnerText);
wb.Worksheets[0].Cells[rowj, 12].PutValue(st.SelectSingleNode("@原因及事項").InnerText);
if (st.SelectSingleNode("@新學號").InnerText == "")
{
wb.Worksheets[0].Cells[rowj, 13].PutValue(Util.ConvertDateStr2(st.SelectSingleNode("@異動日期").InnerText));
}
else
{
wb.Worksheets[0].Cells[rowj, 13].PutValue(st.SelectSingleNode("@新學號").InnerText + "\n" + Util.ConvertDateStr2(st.SelectSingleNode("@異動日期").InnerText));
}
//wb.Worksheets[0].Cells[rowj, 16].PutValue(st.SelectSingleNode("@備註").InnerText);
if (st.SelectSingleNode("@特殊身份代碼") != null)
{
wb.Worksheets[0].Cells[rowj, 16].PutValue(st.SelectSingleNode("@特殊身份代碼").InnerText);
}
#endregion
i++;
rowj++;
//回報進度
ReportProgress((int)(((double)recCount * 100.0) / ((double)totalRec)));
}
#endregion
#region 若個數為20倍數，處理單一頁面
if (x == true)
{
#region 複製樣式-欄高、範圍
//複製樣版前28個 Row(欄高)
//for (int m = 0; m < 28; m++)
//{
// /*
// * 複製 template的第一個 Sheet的第m個 Row
// * 到 wb的第一個 Sheet的第(j * 28) + m個 Row
// */
// wb.Worksheets[0].Cells.CopyRow(template.Worksheets[0].Cells, m, (j * 28) + m);
//}
/*
* 複製Style(包含儲存格合併的資訊)
* 先用CreateRange()選取要複製的Range("A1", "R28")
* 再用CopyStyle複製另一個Range中的格式
*/
Range range = template.Worksheets[0].Cells.CreateRange(0, 28, false);
int t = j * 28;
wb.Worksheets[0].Cells.CreateRange(t, 28, false).Copy(range);
#endregion
#region 填入學校資料
//將學校資料填入適當的位置內
wb.Worksheets[0].Cells[rowi, 13].PutValue(source.SelectSingleNode("@學校代號").InnerText);
wb.Worksheets[0].Cells[rowi, 16].PutValue(list.SelectSingleNode("@科別代號").InnerText);
wb.Worksheets[0].Cells[rowi + 2, 2].PutValue(source.SelectSingleNode("@學校名稱").InnerText);
wb.Worksheets[0].Cells[rowi + 2, 7].PutValue(Convert.ToInt32(source.SelectSingleNode("@學年度").InnerText) + "學年度第" + Convert.ToInt32(source.SelectSingleNode("@學期").InnerText) + "學期");
wb.Worksheets[0].Cells[rowi + 2, 12].PutValue(list.SelectSingleNode("@科別").InnerText);
#endregion
if (j > 0)
{
//插入分頁(在i跟i+1中間，O跟P中間)
wb.Worksheets[0].HPageBreaks.Add(j * 28, 18);
rowj += 8;
}
rowi += 28;
j++;
#region 顯示頁數
//顯示頁數
wb.Worksheets[0].Cells[(28 * (j - 1)) + 27, 13].PutValue("第" + numcount + "頁，共" + (Math.Ceiling((double)count / 20) + 1) + "頁");
numcount++;
#endregion
}
#endregion
#region 統計人數
//填入統計人數
wb.Worksheets[0].Cells.CreateRange(rowj, 1, 1, 2).UnMerge();
wb.Worksheets[0].Cells.Merge(rowj, 1, 1, 3);
wb.Worksheets[0].Cells[rowj, 1].PutValue("合 計 " + count.ToString() + " 名");
#endregion
wb.Worksheets[0].HPageBreaks.Add(j * 28, 18);
#region 設定變數
//調整新清單所使用變數
numcount = 1;
rowj = (28 * j) - 2;
rowi = (28 * j);
x = false;
#endregion
}
Worksheet mingdao = wb.Worksheets[1];
Worksheet mdws = wb.Worksheets[1];
mdws.Name = "電子格式";
Range range_header = mingdao.Cells.CreateRange(0, 1, false);
Range range_row = mingdao.Cells.CreateRange(1, 1, false);
mdws.Cells.CreateRange(0, 1, false).Copy(range_header);
int mdws_index = 0;
DAL.DALTransfer DALTranser = new DAL.DALTransfer();
// 格式轉換
List <GovernmentalDocument.Reports.List.rpt_UpdateRecord> _data = DALTranser.ConvertRptUpdateRecord(source);
// 排序 (依 班別、年級、科別代碼、異動代碼)
_data = (from data in _data orderby data.ClassType, data.DeptCode, data.UpdateCode select data).ToList();
foreach (GovernmentalDocument.Reports.List.rpt_UpdateRecord rec in _data)
{
mdws_index++;
//每增加一行,複製一次
mdws.Cells.CreateRange(mdws_index, 1, false).Copy(range_row);
// 應畢業學年度
mdws.Cells[mdws_index, 0].PutValue(rec.ExpectGraduateSchoolYear);
//班別
mdws.Cells[mdws_index, 1].PutValue(rec.ClassType);
//科別代碼
mdws.Cells[mdws_index, 2].PutValue(rec.DeptCode);
// 2 放上傳類別，請使用者自填
//學號
mdws.Cells[mdws_index, 4].PutValue(rec.StudentNumber);
//姓名
mdws.Cells[mdws_index, 5].PutValue(rec.Name);
//身分證字號
mdws.Cells[mdws_index, 6].PutValue(rec.IDNumber);
//註1
mdws.Cells[mdws_index, 7].PutValue(rec.Comment1);
//性別代碼
mdws.Cells[mdws_index, 8].PutValue(rec.GenderCode);
//出生日期
mdws.Cells[mdws_index, 9].PutValue(rec.Birthday);
//特殊身份代碼
mdws.Cells[mdws_index, 10].PutValue(rec.SpecialStatusCode);
//異動原因代碼
mdws.Cells[mdws_index, 11].PutValue(rec.UpdateCode);
//異動日期
mdws.Cells[mdws_index, 12].PutValue(rec.UpdateDate);
// 異動順序
mdws.Cells[mdws_index, 13].PutValue(rec.Order);
//備查日期
mdws.Cells[mdws_index, 14].PutValue(rec.LastADDate);
//備查文字
mdws.Cells[mdws_index, 15].PutValue(rec.LastADDoc);
//備查文號
mdws.Cells[mdws_index, 16].PutValue(rec.LastADNum);
//更正後資料
string strUpdateData = string.Empty;
//若是更正後資料有值則填入更正後資料
if (!string.IsNullOrEmpty(rec.NewData))
{
strUpdateData = rec.NewData;
}
//若是新學號中有值則填入新學號
//判斷strUpdateData是否已有值，若是已有值則加入斷行符號
if (!string.IsNullOrEmpty(rec.NewStudNumber))
{
strUpdateData += string.IsNullOrEmpty(strUpdateData) ? rec.NewStudNumber : "\n" + rec.NewStudNumber;
}
mdws.Cells[mdws_index, 17].PutValue(strUpdateData);
// 註2
mdws.Cells[mdws_index, 18].PutValue(rec.Comment2);
//備註說明
mdws.Cells[mdws_index, 19].PutValue(rec.Comment);
}
//foreach (XmlElement record in source.SelectNodes("清單/異動紀錄"))
//{
// mdws_index++;
// mdws.Cells.CreateRange(mdws_index, 1, false).Copy(range_row);
// // 學年度
// string schoolYear = "";
// if (!string.IsNullOrEmpty(record.GetAttribute("學生編號")))
// {
// SHSchool.Data.SHLeaveInfoRecord scl = SHSchool.Data.SHLeaveInfo.SelectByStudentID(record.GetAttribute("學生編號"));
// if (scl.SchoolYear.HasValue)
// schoolYear = scl.SchoolYear.Value.ToString();
// }
// mdws.Cells[mdws_index, 0].PutValue(schoolYear);
// mdws.Cells[mdws_index, 1].PutValue(record.GetAttribute("班別"));
// mdws.Cells[mdws_index, 2].PutValue((record.ParentNode as XmlElement).GetAttribute("科別代號"));
// mdws.Cells[mdws_index, 3].PutValue("");
// mdws.Cells[mdws_index, 4].PutValue(record.GetAttribute("學號"));
// mdws.Cells[mdws_index, 5].PutValue(record.GetAttribute("姓名"));
// mdws.Cells[mdws_index, 6].PutValue(record.GetAttribute("身分證號"));
// mdws.Cells[mdws_index, 7].PutValue(record.GetAttribute("註1"));
// mdws.Cells[mdws_index, 8].PutValue(record.GetAttribute("性別代號"));
// mdws.Cells[mdws_index, 9].PutValue(GetBirthdateWithoutSlash(BL.Util.ConvertDate1(record.GetAttribute("出生年月日"))));
// mdws.Cells[mdws_index, 10].PutValue(record.GetAttribute("特殊身份代碼")); //原為抓取註備欄位值
// mdws.Cells[mdws_index, 11].PutValue(record.GetAttribute("異動代號"));
// mdws.Cells[mdws_index, 12].PutValue(GetBirthdateWithoutSlash(BL.Util.ConvertDate1(record.GetAttribute("異動日期"))));
// mdws.Cells[mdws_index, 13].PutValue(GetBirthdateWithoutSlash(BL.Util.ConvertDate1(record.GetAttribute("備查日期"))));
// mdws.Cells[mdws_index, 14].PutValue(GetADDoc(record.GetAttribute("備查文號")));
// mdws.Cells[mdws_index, 15].PutValue(GetADNo(record.GetAttribute("備查文號")));
// mdws.Cells[mdws_index, 16].PutValue(record.GetAttribute("新學號"));
// mdws.Cells[mdws_index, 17].PutValue(record.GetAttribute("備註"));
//}
mdws.AutoFitColumns();
mdws.Cells.SetColumnWidth(5, 8.5);
mdws.Cells.SetColumnWidth(11, 20);
wb.Worksheets.ActiveSheetIndex = 0;
//儲存 Excel
wb.Save(location, FileFormatType.Excel2003);
}
/// <summary>
/// 處理新竹
/// </summary>
/// <param name="source"></param>
/// <param name="location"></param>
private void ProcessHsinChu(XmlElement source, string location)
{
// 資料轉換
Dictionary <string, JHPermrec.UpdateRecord.DAL.StudBatchUpdateRecContentEntity> data = StudBatchUpdateRecEntity.ConvertGetContentData(source);
int tmpY, tmpM, tmpD;
tmpY = DateTime.Now.Year;
tmpM = DateTime.Now.Month;
tmpD = DateTime.Now.Day;
string tmpRptY, tmpRptM;
string strPrintDate = (tmpY - 1911).ToString() + "/";
tmpRptY = (tmpY - 1911).ToString();
if (tmpM < 10)
{
strPrintDate += "0" + tmpM.ToString() + "/";
tmpRptM = "0" + tmpM.ToString();
}
else
{
strPrintDate += tmpM.ToString() + "/";
tmpRptM = tmpM.ToString();
}
if (tmpD < 10)
{
strPrintDate += "0" + tmpD.ToString();
}
else
{
strPrintDate += tmpD.ToString();
}
Workbook template = new Workbook();
//從Resources把Template讀出來
template.Open(new MemoryStream(GDResources.JUpdateStudentPermrecTemplate), FileFormatType.Excel2003);
//要產生的excel檔
Workbook wb = new Aspose.Cells.Workbook();
wb.Open(new MemoryStream(GDResources.JUpdateStudentPermrecTemplate), FileFormatType.Excel2003);
Worksheet ws = wb.Worksheets[0];
//設定預設樣式
wb.DefaultStyle = template.DefaultStyle;
int rowj = 5;
int recCount = 0;
int totalRec = data.Count;
string SchoolNameTitle = StudBatchUpdateRecEntity.GetContentSchoolName() + "更 正 學 籍 名 冊";
string SchoolYearSemesterTitle = StudBatchUpdateRecEntity.GetContentSchoolYear() + "學年度 第" + StudBatchUpdateRecEntity.GetContentSemester() + "學期";
wb.Worksheets[0].Cells[0, 0].PutValue(SchoolNameTitle);
wb.Worksheets[0].Cells[1, 0].PutValue(SchoolYearSemesterTitle);
#region 異動紀錄
//將xml資料填入至excel
foreach (StudBatchUpdateRecContentEntity sburce in data.Values)
{
recCount++;
#region 填入學生資料
//將學生資料填入適當的位置內
wb.Worksheets[0].Cells[rowj, 0].PutValue(sburce.GetStudentNumber());
wb.Worksheets[0].Cells[rowj, 1].PutValue(sburce.GetName());
wb.Worksheets[0].Cells[rowj, 2].PutValue(sburce.GetGender());
DateTime orinialBirtday, LastUpdateDate, Birthday;
if (DateTime.TryParse(sburce.GetBirthday(), out orinialBirtday))
{
wb.Worksheets[0].Cells[rowj, 3].PutValue((orinialBirtday.Year - 1911).ToString());
wb.Worksheets[0].Cells[rowj, 4].PutValue(orinialBirtday.Month.ToString());
wb.Worksheets[0].Cells[rowj, 5].PutValue(orinialBirtday.Day.ToString());
}
string LastUpdateInfo = "";
if (DateTime.TryParse(sburce.GetLastADDate(), out LastUpdateDate) == true)
{
LastUpdateInfo = (LastUpdateDate.Year - 1911) + "/" + LastUpdateDate.Month + "/" + LastUpdateDate.Day;
}
LastUpdateInfo += sburce.GetLastADNumber();
wb.Worksheets[0].Cells[rowj, 6].PutValue(LastUpdateInfo);
if (!string.IsNullOrEmpty(sburce.GetNewBirthday()))
{
if (DateTime.TryParse(sburce.GetNewBirthday(), out Birthday))
{
wb.Worksheets[0].Cells[rowj, 11].PutValue((Birthday.Year - 1911).ToString());
wb.Worksheets[0].Cells[rowj, 12].PutValue(Birthday.Month.ToString());
wb.Worksheets[0].Cells[rowj, 13].PutValue(Birthday.Day.ToString());
}
}
if (!string.IsNullOrEmpty(sburce.GetNewName()))
{
wb.Worksheets[0].Cells[rowj, 9].PutValue(sburce.GetNewName());
}
if (!string.IsNullOrEmpty(sburce.GetNewGender()))
{
wb.Worksheets[0].Cells[rowj, 10].PutValue(sburce.GetNewGender());
}
if (!string.IsNullOrEmpty(sburce.GetNewIDNumber()))
{
wb.Worksheets[0].Cells[rowj, 14].PutValue(sburce.GetNewIDNumber());
}
wb.Worksheets[0].Cells[rowj, 15].PutValue(sburce.GetComment());
#endregion
rowj++;
//回報進度
ReportProgress((int)(((double)recCount * 100.0) / ((double)totalRec)));
}
#endregion
// 畫表
Style st2 = wb.Styles[wb.Styles.Add()];
StyleFlag sf2 = new StyleFlag();
sf2.Borders = true;
st2.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
st2.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;
st2.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;
st2.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
int tmpMaxRow = 0, tmpMaxCol = 0;
for (int wbIdx1 = 0; wbIdx1 < wb.Worksheets.Count; wbIdx1++)
{
tmpMaxRow = wb.Worksheets[wbIdx1].Cells.MaxDataRow;
tmpMaxCol = wb.Worksheets[wbIdx1].Cells.MaxDataColumn + 1;
wb.Worksheets[wbIdx1].Cells.CreateRange(1, 0, tmpMaxRow, tmpMaxCol).ApplyStyle(st2, sf2);
}
//儲存
wb.Save(location, FileFormatType.Excel2003);
}
public ActionResult ExportExamineData(string queryJson)
{
try
{
Operator user = OperatorProvider.Provider.Current();
Pagination pagination = new Pagination();
pagination.page = 1;
pagination.rows = 1000000000;
var watch = CommonHelper.TimerStart();
pagination.p_kid = "t.id";
var table = @"(select
examinetodeptid,wm_concat(id) id,
examinetodept,max(createdate) createdate,
wm_concat(distinct(examineperson)) examineperson,
to_char(min(examinetime),'yyyy-MM-dd')||'~'|| to_char(max(examinetime),'yyyy-MM-dd') examinetime,
sum(examinemoney) examinemoney,
wm_concat(examinetype) examinetype,createuserorgcode
from epg_dailyexamine t where 1=1 {0} group by examinetodeptid,examinetodept,createuserorgcode) t";
var strWhere = string.Empty;
pagination.p_fields = @" t.examinetodeptid,
t.examinetodept,
t.examineperson,
t.examinetime,
t.examinemoney,
t.examinetype";
pagination.conditionJson = "1=1";
pagination.sidx = "t.createdate";
pagination.sord = "desc";
if (!user.IsSystem)
{
pagination.conditionJson += " and t.createuserorgcode='" + user.OrganizeCode + "'";
}
if (!string.IsNullOrEmpty(queryJson))
{
var queryParam = queryJson.ToJObject();
if (!queryParam["examinetodeptid"].IsEmpty())
{
strWhere += " and t.examinetodeptid ='" + queryParam["examinetodeptid"].ToString() + "'";
}
if (!queryParam["examinetype"].IsEmpty())
{
strWhere += " and t.examinetype='" + queryParam["examinetype"].ToString() + "'";
}
if (!queryParam["examinecontent"].IsEmpty())
{
strWhere += " and t.examinecontent like '%" + queryParam["examinecontent"].ToString() + "%'";
}
//开始时间
if (!queryParam["sTime"].IsEmpty())
{
strWhere += string.Format(@" and t.examinetime >= to_date('{0}','yyyy-mm-dd hh24:mi:ss')", queryParam["sTime"].ToString());
}
//结束时间
if (!queryParam["eTime"].IsEmpty())
{
strWhere += string.Format(@" and t.examinetime < to_date('{0}','yyyy-mm-dd hh24:mi:ss')", Convert.ToDateTime(queryParam["eTime"].ToString()).AddDays(1).ToString("yyyy-MM-dd"));
}
}
table = string.Format(table, strWhere);
pagination.p_tablename = table;
var data = dailyexaminebll.GetExportExamineCollent(pagination, queryJson);
Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook();
string fName = "日常考核汇总_" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
wb.Open(Server.MapPath("~/Resource/ExcelTemplate/tmp.xls"));
var num = wb.Worksheets[0].Cells.Columns.Count;
Aspose.Cells.Worksheet sheet = wb.Worksheets[0] as Aspose.Cells.Worksheet;
Aspose.Cells.Cell cell = sheet.Cells[0, 0];
cell.PutValue("考核汇总表"); //标题
cell.Style.Pattern = BackgroundType.Solid;
cell.Style.Font.Size = 16;
cell.Style.Font.Color = Color.Black;
List <string> colList = new List <string>()
{
"被考核单位", "考核金额", "考核类型", "考核人", "考核时间"
};
List <string> colList1 = new List <string>()
{
"examinetodept", "examinemoney", "examinetype", "examineperson", "examinetime"
};
for (int i = 0; i < colList.Count; i++)
{
//序号列
Aspose.Cells.Cell serialcell = sheet.Cells[1, 0];
serialcell.PutValue(" ");
for (int j = 0; j < colList.Count; j++)
{
Aspose.Cells.Cell curcell = sheet.Cells[1, j + 1];
sheet.Cells.SetColumnWidth(j + 1, 40);
curcell.Style.Pattern = BackgroundType.Solid;
curcell.Style.Font.Size = 12;
curcell.Style.Font.Color = Color.Black;
curcell.PutValue(colList[j].ToString()); //列头
}
Aspose.Cells.Cells cells = sheet.Cells;
cells.Merge(0, 0, 1, colList.Count + 1);
}
for (int i = 0; i < data.Rows.Count; i++)
{
//序列号
Aspose.Cells.Cell serialcell = sheet.Cells[i + 2, 0];
if (string.IsNullOrWhiteSpace(data.Rows[i]["parent"].ToString()))
{
serialcell.PutValue("合计");
}
//内容填充
for (int j = 0; j < colList1.Count; j++)
{
Aspose.Cells.Cell curcell = sheet.Cells[i + 2, j + 1];
curcell.PutValue(data.Rows[i][colList1[j]].ToString());
}
}
HttpResponse resp = System.Web.HttpContext.Current.Response;
wb.Save(Server.MapPath("~/Resource/Temp/" + fName));
return(Success("导出成功。", fName));
}
catch (Exception)
{
throw;
}
}
protected override void Build(System.Xml.XmlElement source, string location)
{
#region 建立 Excel
//從 Resources 將新生名冊template讀出來
Workbook template = new Workbook();
template.Open(new MemoryStream(Properties.Resources.RetaintoStudentListTemplate), FileFormatType.Excel2003);
//產生 excel
Workbook wb = new Aspose.Cells.Workbook();
wb.Open(new MemoryStream(Properties.Resources.RetaintoStudentListTemplate), FileFormatType.Excel2003);
#endregion
#region 複製樣式-預設樣式、欄寬
//設定預設樣式
wb.DefaultStyle = template.DefaultStyle;
//複製樣版中前18個 Column(欄寬)
for (int m = 0; m < 18; m++)
{
/*
* 複製 template的第一個 Sheet的第 m個 Column
* 到 wb的第一個 Sheet的第 m個 Column
*/
wb.Worksheets[0].Cells.CopyColumn(template.Worksheets[0].Cells, m, m);
}
#endregion
#region 初始變數
/******************************
* rowi 填入學校資料用
* rowj 填入學生資料用
* num 計算清單份數
* numcount 計算每份清單頁數
* j 計算所產生清單頁數
* x 判斷個數是否為20被數用
******************************/
int rowi = 0, rowj = 1, num = source.SelectNodes("清單").Count, numcount = 1, j = 0;
bool x = false;
int recCount = 0;
int totalRec = source.SelectNodes("清單/異動紀錄").Count;
#endregion
foreach (XmlNode list in source.SelectNodes("清單"))
{
int i = 0;
#region 找出資料總數及判斷
//找出資料總數方便評估進度
int count = list.SelectNodes("異動紀錄").Count;
//判斷個數是否為20被數
if (count % 20 == 0)
{
x = true;
}
#endregion
#region 異動紀錄
// 所在地代碼對照
Dictionary <string, string> gLocationCodeDict = new Dictionary <string, string>();
//將xml資料填入至excel
foreach (XmlNode st in list.SelectNodes("異動紀錄"))
{
recCount++;
if (i % 20 == 0)
{
#region 複製樣式-欄高、範圍
//複製樣版中前28個 Row(欄高)
//for (int m = 0; m < 28; m++)
//{
// /*
// * 複製 template的第一個 Sheet的第m個 Row
// * 到 wb的第一個 Sheet的第(j * 28) + m個 Row
// */
// wb.Worksheets[0].Cells.CopyRow(template.Worksheets[0].Cells, m, (j * 28) + m);
//}
/*
* 複製Style(包含儲存格合併的資訊)
* 先用CreateRange()選取要複製的Range("A1", "R28")
* 再用CopyStyle複製另一個Range中的格式
*/
Range range = template.Worksheets[0].Cells.CreateRange(0, 28, false);
int t = j * 28;
wb.Worksheets[0].Cells.CreateRange(t, 28, false).Copy(range);
#endregion
#region 填入學校資料
//將學校資料填入適當的位置內
wb.Worksheets[0].Cells[rowi, 13].PutValue(source.SelectSingleNode("@學校代號").InnerText);
wb.Worksheets[0].Cells[rowi, 16].PutValue(list.SelectSingleNode("@科別代號").InnerText);
wb.Worksheets[0].Cells[rowi + 2, 2].PutValue(source.SelectSingleNode("@學校名稱").InnerText);
wb.Worksheets[0].Cells[rowi + 2, 6].PutValue(Convert.ToInt32(source.SelectSingleNode("@學年度").InnerText));
wb.Worksheets[0].Cells[rowi + 2, 9].PutValue(Convert.ToInt32(source.SelectSingleNode("@學期").InnerText));
wb.Worksheets[0].Cells[rowi + 2, 12].PutValue(list.SelectSingleNode("@科別").InnerText);
#endregion
if (j > 0)
{
//插入分頁(在 j * 28 跟 (j * 28) +1 中間，R跟S中間)
wb.Worksheets[0].HPageBreaks.Add(j * 28, 18);
rowj += 8;
}
else
{
rowj = 6;
}
rowi += 28;
j++;
#region 顯示頁數
//顯示頁數
if (x != true)
{
wb.Worksheets[0].Cells[(28 * (j - 1)) + 26, 13].PutValue("第" + numcount + "頁，共" + Math.Ceiling((double)count / 20) + "頁");
}
else
{
wb.Worksheets[0].Cells[(28 * (j - 1)) + 26, 13].PutValue("第" + numcount + "頁，共" + (Math.Ceiling((double)count / 20) + 1) + "頁");
}
numcount++;
#endregion
}
#region 填入學生資料
//將學生資料填入適當的位置內
wb.Worksheets[0].Cells[rowj, 1].PutValue(st.SelectSingleNode("@姓名").InnerText);
string ss = st.SelectSingleNode("@出生年月日").InnerText + "\n" + st.SelectSingleNode("@身分證號").InnerText;
wb.Worksheets[0].Cells[rowj, 3].PutValue(ss);
try
{
wb.Worksheets[0].Cells[rowj, 6].PutValue(Convert.ToInt32(st.SelectSingleNode("@性別代號").InnerText));
}
catch (Exception)
{ }
wb.Worksheets[0].Cells[rowj, 7].PutValue(st.SelectSingleNode("@性別").InnerText);
wb.Worksheets[0].Cells[rowj, 8].PutValue(st.SelectSingleNode("@出生年月日").InnerText);
if (st.SelectSingleNode("@特殊身份代碼") != null)
{
wb.Worksheets[0].Cells[rowj, 8].PutValue(st.SelectSingleNode("@特殊身份代碼").InnerText);
}
string uCode = "";
if (st.SelectSingleNode("@異動代號") == null)
{
if (st.SelectSingleNode("@異動代碼") != null)
{
uCode = st.SelectSingleNode("@異動代碼").InnerText;
}
}
else
{
uCode = st.SelectSingleNode("@異動代號").InnerText;
}
wb.Worksheets[0].Cells[rowj, 9].PutValue(uCode);
wb.Worksheets[0].Cells[rowj, 12].PutValue(st.SelectSingleNode("@原因及事項").InnerText);
wb.Worksheets[0].Cells[rowj, 13].PutValue(st.SelectSingleNode("@備註").InnerText);
#endregion
i++;
rowj++;
//回報進度
ReportProgress((int)(((double)recCount * 100.0) / ((double)totalRec)));
}
#endregion
#region 若個數為20倍數，處理單一頁面
if (x == true)
{
#region 複製樣式-欄高、範圍
//複製樣版前28個 Row(欄高)
//for (int m = 0; m < 28; m++)
//{
// /*
// * 複製 template的第一個 Sheet的第m個 Row
// * 到 wb的第一個 Sheet的第(j * 28) + m個 Row
// */
// wb.Worksheets[0].Cells.CopyRow(template.Worksheets[0].Cells, m, (j * 28) + m);
//}
/*
* 複製Style(包含儲存格合併的資訊)
* 先用CreateRange()選取要複製的Range("A1", "R28")
* 再用CopyStyle複製另一個Range中的格式
*/
Range range = template.Worksheets[0].Cells.CreateRange(0, 28, false);
int t = j * 28;
wb.Worksheets[0].Cells.CreateRange(t, 28, false).Copy(range);
#endregion
#region 填入學校資料
//將學校資料填入適當的位置內
wb.Worksheets[0].Cells[rowi, 13].PutValue(source.SelectSingleNode("@學校代號").InnerText);
wb.Worksheets[0].Cells[rowi, 16].PutValue(list.SelectSingleNode("@科別代號").InnerText);
wb.Worksheets[0].Cells[rowi + 2, 2].PutValue(source.SelectSingleNode("@學校名稱").InnerText);
wb.Worksheets[0].Cells[rowi + 2, 6].PutValue(Convert.ToInt32(source.SelectSingleNode("@學年度").InnerText));
wb.Worksheets[0].Cells[rowi + 2, 9].PutValue(Convert.ToInt32(source.SelectSingleNode("@學期").InnerText));
wb.Worksheets[0].Cells[rowi + 2, 12].PutValue(list.SelectSingleNode("@科別").InnerText);
#endregion
if (j > 0)
{
//插入分頁(在i跟i+1中間，O跟P中間)
wb.Worksheets[0].HPageBreaks.Add(j * 28, 18);
rowj += 8;
}
rowi += 28;
j++;
#region 顯示頁數
//顯示頁數
wb.Worksheets[0].Cells[(28 * (j - 1)) + 26, 13].PutValue("第" + numcount + "頁，共" + (Math.Ceiling((double)count / 20) + 1) + "頁");
numcount++;
#endregion
}
#endregion
#region 統計人數
//填入統計人數
wb.Worksheets[0].Cells[rowj, 1].PutValue("合 計 ");
wb.Worksheets[0].Cells[rowj, 3].PutValue(count.ToString() + " 名");
#endregion
wb.Worksheets[0].HPageBreaks.Add(j * 28, 18);
#region 設定變數
//調整新清單所使用變數
numcount = 1;
rowj = (28 * j) - 2;
rowi = (28 * j);
x = false;
#endregion
}
#region 97中辦格式
Worksheet mingdao = wb.Worksheets["電子格式103"];
Worksheet mdws = wb.Worksheets[wb.Worksheets.Add()];
mdws.Name = "電子格式";
Range range_header = mingdao.Cells.CreateRange(0, 1, false);
Range range_row = mingdao.Cells.CreateRange(1, 1, false);
mdws.Cells.CreateRange(0, 1, false).Copy(range_header);
int mdws_index = 0;
foreach (XmlElement record in source.SelectNodes("清單/異動紀錄"))
{
mdws_index++;
mdws.Cells.CreateRange(mdws_index, 1, false).Copy(range_row);
mdws.Cells[mdws_index, 0].PutValue(record.GetAttribute("班別"));
mdws.Cells[mdws_index, 1].PutValue((record.ParentNode as XmlElement).GetAttribute("科別代號"));
mdws.Cells[mdws_index, 2].PutValue("");
mdws.Cells[mdws_index, 3].PutValue(record.GetAttribute("姓名"));
mdws.Cells[mdws_index, 4].PutValue(record.GetAttribute("身分證號"));
mdws.Cells[mdws_index, 5].PutValue(record.GetAttribute("註1"));
mdws.Cells[mdws_index, 6].PutValue(record.GetAttribute("性別代號"));
mdws.Cells[mdws_index, 7].PutValue(GetBirthdateWithoutSlash(record.GetAttribute("出生年月日")));
mdws.Cells[mdws_index, 8].PutValue(record.GetAttribute("特殊身份代碼"));
mdws.Cells[mdws_index, 9].PutValue(record.GetAttribute("異動代碼"));
mdws.Cells[mdws_index, 10].PutValue(record.GetAttribute("備註"));
}
mdws.AutoFitColumns();
wb.Worksheets.RemoveAt("電子格式103");
wb.Worksheets.ActiveSheetIndex = 0;
#endregion
wb.Worksheets.ActiveSheetIndex = 0;
//儲存 Excel
wb.Save(location, FileFormatType.Excel2003);
}
public static bool ExcelFileToDataTable(string filepath, out DataTable datatable, int iFirstRow, int iFirstCol, int rowNum, int colNum, out string error)
{
error = "";
datatable = null;
try
{
if (File.Exists(filepath) == false)
{
error = "文件不存在";
datatable = null;
return false;
}
Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook();
workbook.Open(filepath);
Aspose.Cells.Worksheet worksheet = workbook.Worksheets[0];
datatable = worksheet.Cells.ExportDataTable(iFirstRow, iFirstCol, rowNum + 1, colNum + 1);
return true;
}
catch (System.Exception e)
{
error = e.Message;
return false;
}
}
protected override void Build(System.Xml.XmlElement source, string location)
{
#region 建立 Excel
//從 Resources 將新生名冊template讀出來
Workbook template = new Workbook();
template.Open(new MemoryStream(Properties.Resources.TemporaryStudentListTemplate), FileFormatType.Excel2003);
//產生 excel
Workbook wb = new Aspose.Cells.Workbook();
wb.Open(new MemoryStream(Properties.Resources.TemporaryStudentListTemplate), FileFormatType.Excel2003);
#endregion
#region 複製樣式-預設樣式、欄寬
//設定預設樣式
wb.DefaultStyle = template.DefaultStyle;
//複製樣版中前18個 Column(欄寬)
for (int m = 0; m < 18; m++)
{
/*
* 複製 template的第一個 Sheet的第 m個 Column
* 到 wb的第一個 Sheet的第 m個 Column
*/
wb.Worksheets[0].Cells.CopyColumn(template.Worksheets[0].Cells, m, m);
}
#endregion
#region 初始變數
/******************************
* rowi 填入學校資料用
* rowj 填入學生資料用
* num 計算清單份數
* numcount 計算每份清單頁數
* j 計算所產生清單頁數
* x 判斷個數是否為20被數用
******************************/
int rowi = 0, rowj = 1, num = source.SelectNodes("清單").Count, numcount = 1, j = 0;
bool x = false;
int recCount = 0;
int totalRec = source.SelectNodes("清單/異動紀錄").Count;
#endregion
foreach (XmlNode list in source.SelectNodes("清單"))
{
int i = 0;
#region 找出資料總數及判斷
//找出資料總數方便評估進度
int count = list.SelectNodes("異動紀錄").Count;
//判斷個數是否為20被數
if (count % 20 == 0)
{
x = true;
}
#endregion
#region 異動紀錄
// 所在地代碼對照
Dictionary <string, string> gLocationCodeDict = new Dictionary <string, string>();
//將xml資料填入至excel
foreach (XmlNode st in list.SelectNodes("異動紀錄"))
{
recCount++;
if (i % 20 == 0)
{
#region 複製樣式-欄高、範圍
//複製樣版中前28個 Row(欄高)
//for (int m = 0; m < 28; m++)
//{
// /*
// * 複製 template的第一個 Sheet的第m個 Row
// * 到 wb的第一個 Sheet的第(j * 28) + m個 Row
// */
// wb.Worksheets[0].Cells.CopyRow(template.Worksheets[0].Cells, m, (j * 28) + m);
//}
/*
* 複製Style(包含儲存格合併的資訊)
* 先用CreateRange()選取要複製的Range("A1", "R28")
* 再用CopyStyle複製另一個Range中的格式
*/
Range range = template.Worksheets[0].Cells.CreateRange(0, 28, false);
int t = j * 28;
wb.Worksheets[0].Cells.CreateRange(t, 28, false).Copy(range);
#endregion
#region 填入學校資料
//將學校資料填入適當的位置內
wb.Worksheets[0].Cells[rowi, 13].PutValue(source.SelectSingleNode("@學校代號").InnerText);
wb.Worksheets[0].Cells[rowi, 16].PutValue(list.SelectSingleNode("@科別代號").InnerText);
wb.Worksheets[0].Cells[rowi + 2, 2].PutValue(source.SelectSingleNode("@學校名稱").InnerText);
wb.Worksheets[0].Cells[rowi + 2, 6].PutValue(Convert.ToInt32(source.SelectSingleNode("@學年度").InnerText));
wb.Worksheets[0].Cells[rowi + 2, 9].PutValue(Convert.ToInt32(source.SelectSingleNode("@學期").InnerText));
wb.Worksheets[0].Cells[rowi + 2, 12].PutValue(list.SelectSingleNode("@科別").InnerText);
#endregion
if (j > 0)
{
//插入分頁(在 j * 28 跟 (j * 28) +1 中間，R跟S中間)
wb.Worksheets[0].HPageBreaks.Add(j * 28, 18);
rowj += 8;
}
else
{
rowj = 6;
}
rowi += 28;
j++;
#region 顯示頁數
//顯示頁數
if (x != true)
{
wb.Worksheets[0].Cells[(28 * (j - 1)) + 26, 13].PutValue("第" + numcount + "頁，共" + Math.Ceiling((double)count / 20) + "頁");
}
else
{
wb.Worksheets[0].Cells[(28 * (j - 1)) + 26, 13].PutValue("第" + numcount + "頁，共" + (Math.Ceiling((double)count / 20) + 1) + "頁");
}
numcount++;
#endregion
}
#region 填入學生資料
//將學生資料填入適當的位置內
wb.Worksheets[0].Cells[rowj, 1].PutValue(st.SelectSingleNode("@學號").InnerText);
wb.Worksheets[0].Cells[rowj, 3].PutValue(st.SelectSingleNode("@姓名").InnerText);
string ss = st.SelectSingleNode("@出生年月日").InnerText + "\n" + st.SelectSingleNode("@身分證號").InnerText;
wb.Worksheets[0].Cells[rowj, 4].PutValue(ss);
try
{
wb.Worksheets[0].Cells[rowj, 7].PutValue(Convert.ToInt32(st.SelectSingleNode("@性別代號").InnerText));
}
catch (Exception)
{ }
wb.Worksheets[0].Cells[rowj, 8].PutValue(st.SelectSingleNode("@性別").InnerText);
if (st.SelectSingleNode("@特殊身份代碼") != null)
{
wb.Worksheets[0].Cells[rowj, 9].PutValue(st.SelectSingleNode("@特殊身份代碼").InnerText);
}
// 借讀學校代碼
string LSCode = "";
if (st.SelectSingleNode("@借讀學校代碼") != null)
{
LSCode = st.SelectSingleNode("@借讀學校代碼").InnerText;
}
wb.Worksheets[0].Cells[rowj, 10].PutValue(LSCode);
// 借讀科別代碼
string LDCode = "";
if (st.SelectSingleNode("@借讀科別代碼") != null)
{
LDCode = st.SelectSingleNode("@借讀科別代碼").InnerText;
}
wb.Worksheets[0].Cells[rowj, 11].PutValue(LDCode);
string uCode = "";
if (st.SelectSingleNode("@異動代號") == null)
{
if (st.SelectSingleNode("@異動代碼") != null)
{
uCode = st.SelectSingleNode("@異動代碼").InnerText;
}
}
else
{
uCode = st.SelectSingleNode("@異動代號").InnerText;
}
wb.Worksheets[0].Cells[rowj, 12].PutValue(uCode);
wb.Worksheets[0].Cells[rowj, 13].PutValue(st.SelectSingleNode("@原因及事項").InnerText);
//申請日期
string sD1 = "";
if (st.SelectSingleNode("@申請開始日期") != null)
{
sD1 += ParseCDate1(st.SelectSingleNode("@申請開始日期").InnerText);
}
if (st.SelectSingleNode("@申請結束日期") != null)
{
sD1 += "\n" + ParseCDate1(st.SelectSingleNode("@申請結束日期").InnerText);
}
wb.Worksheets[0].Cells[rowj, 14].PutValue(sD1);
//實際日期
string sD2 = "";
if (st.SelectSingleNode("@實際開始日期") != null)
{
sD2 += ParseCDate1(st.SelectSingleNode("@實際開始日期").InnerText);
}
if (st.SelectSingleNode("@實際結束日期") != null)
{
sD2 += "\n" + ParseCDate1(st.SelectSingleNode("@實際結束日期").InnerText);
}
wb.Worksheets[0].Cells[rowj, 17].PutValue(sD2);
wb.Worksheets[0].Cells[rowj, 18].PutValue(st.SelectSingleNode("@備註").InnerText);
#endregion
i++;
rowj++;
//回報進度
ReportProgress((int)(((double)recCount * 100.0) / ((double)totalRec)));
}
#endregion
#region 若個數為20倍數，處理單一頁面
if (x == true)
{
#region 複製樣式-欄高、範圍
//複製樣版前28個 Row(欄高)
//for (int m = 0; m < 28; m++)
//{
// /*
// * 複製 template的第一個 Sheet的第m個 Row
// * 到 wb的第一個 Sheet的第(j * 28) + m個 Row
// */
// wb.Worksheets[0].Cells.CopyRow(template.Worksheets[0].Cells, m, (j * 28) + m);
//}
/*
* 複製Style(包含儲存格合併的資訊)
* 先用CreateRange()選取要複製的Range("A1", "R28")
* 再用CopyStyle複製另一個Range中的格式
*/
Range range = template.Worksheets[0].Cells.CreateRange(0, 28, false);
int t = j * 28;
wb.Worksheets[0].Cells.CreateRange(t, 28, false).Copy(range);
#endregion
#region 填入學校資料
//將學校資料填入適當的位置內
wb.Worksheets[0].Cells[rowi, 13].PutValue(source.SelectSingleNode("@學校代號").InnerText);
wb.Worksheets[0].Cells[rowi, 16].PutValue(list.SelectSingleNode("@科別代號").InnerText);
wb.Worksheets[0].Cells[rowi + 2, 2].PutValue(source.SelectSingleNode("@學校名稱").InnerText);
wb.Worksheets[0].Cells[rowi + 2, 6].PutValue(Convert.ToInt32(source.SelectSingleNode("@學年度").InnerText));
wb.Worksheets[0].Cells[rowi + 2, 9].PutValue(Convert.ToInt32(source.SelectSingleNode("@學期").InnerText));
wb.Worksheets[0].Cells[rowi + 2, 12].PutValue(list.SelectSingleNode("@科別").InnerText);
#endregion
if (j > 0)
{
//插入分頁(在i跟i+1中間，O跟P中間)
wb.Worksheets[0].HPageBreaks.Add(j * 28, 18);
rowj += 8;
}
rowi += 28;
j++;
#region 顯示頁數
//顯示頁數
wb.Worksheets[0].Cells[(28 * (j - 1)) + 26, 13].PutValue("第" + numcount + "頁，共" + (Math.Ceiling((double)count / 20) + 1) + "頁");
numcount++;
#endregion
}
#endregion
#region 統計人數
//填入統計人數
wb.Worksheets[0].Cells[rowj, 1].PutValue("合 計 ");
wb.Worksheets[0].Cells[rowj, 3].PutValue(count.ToString() + " 名");
#endregion
wb.Worksheets[0].HPageBreaks.Add(j * 28, 18);
#region 設定變數
//調整新清單所使用變數
numcount = 1;
rowj = (28 * j) - 2;
rowi = (28 * j);
x = false;
#endregion
}
#region 97中辦格式
Worksheet mingdao = wb.Worksheets["電子格式範本"];
Worksheet mdws = wb.Worksheets[wb.Worksheets.Add()];
mdws.Name = "借讀學生名冊";
Range range_header = mingdao.Cells.CreateRange(0, 1, false);
Range range_row = mingdao.Cells.CreateRange(1, 1, false);
// 107新格式 結束行要 有End 字樣
Range range_R_EndRow = mingdao.Cells.CreateRange(2, 1, false);
mdws.Cells.CreateRange(0, 1, false).Copy(range_header);
int mdws_index = 0;
foreach (XmlElement record in source.SelectNodes("清單/異動紀錄"))
{
mdws_index++;
mdws.Cells.CreateRange(mdws_index, 1, false).Copy(range_row);
mdws.Cells[mdws_index, 0].PutValue(record.GetAttribute("班別"));
mdws.Cells[mdws_index, 1].PutValue((record.ParentNode as XmlElement).GetAttribute("科別代號"));
mdws.Cells[mdws_index, 2].PutValue("");
mdws.Cells[mdws_index, 3].PutValue(record.GetAttribute("學號"));
mdws.Cells[mdws_index, 4].PutValue(record.GetAttribute("姓名"));
mdws.Cells[mdws_index, 5].PutValue(record.GetAttribute("身分證號"));
mdws.Cells[mdws_index, 6].PutValue(record.GetAttribute("註1"));
mdws.Cells[mdws_index, 7].PutValue(record.GetAttribute("性別代號"));
mdws.Cells[mdws_index, 8].PutValue(GetBirthdateWithoutSlash(record.GetAttribute("出生年月日")));
mdws.Cells[mdws_index, 9].PutValue(record.GetAttribute("特殊身份代碼"));
mdws.Cells[mdws_index, 10].PutValue(record.GetAttribute("年級"));
mdws.Cells[mdws_index, 11].PutValue(record.GetAttribute("借讀學校代碼"));
mdws.Cells[mdws_index, 12].PutValue(record.GetAttribute("借讀科別代碼"));
mdws.Cells[mdws_index, 13].PutValue(record.GetAttribute("異動代碼"));
mdws.Cells[mdws_index, 14].PutValue(ParseCDate2(record.GetAttribute("申請開始日期")));
mdws.Cells[mdws_index, 15].PutValue(ParseCDate2(record.GetAttribute("申請結束日期")));
mdws.Cells[mdws_index, 16].PutValue(record.GetAttribute("備註"));
}
// 資料末底 加End
mdws.Cells.CreateRange(mdws_index + 1, 1, false).Copy(range_R_EndRow);
mdws.AutoFitColumns();
wb.Worksheets.RemoveAt("電子格式範本");
//範本
Worksheet TemplateWb_Cover = wb.Worksheets["借讀學生名冊封面範本"];
//實做頁面
Worksheet cover = wb.Worksheets[wb.Worksheets.Add()];
//名稱
cover.Name = "借讀學生名冊封面";
string school_code = source.SelectSingleNode("@學校代號").InnerText;
string school_year = source.SelectSingleNode("@學年度").InnerText;
string school_semester = source.SelectSingleNode("@學期").InnerText;
//範圍
Range range_H_Cover = TemplateWb_Cover.Cells.CreateRange(0, 1, false);
//range_H_Cover
cover.Cells.CreateRange(0, 1, false).Copy(range_H_Cover);
Range range_R_cover = TemplateWb_Cover.Cells.CreateRange(1, 1, false);
// 107新格式 結束行要 有End 字樣
Range range_R_cover_EndRow = TemplateWb_Cover.Cells.CreateRange(2, 1, false);
int cover_row_counter = 1;
//2018/2/2 穎驊註解 ，下面是新的封面產生方式
foreach (XmlNode list in source.SelectNodes("清單"))
{
//每增加一行,複製一次
cover.Cells.CreateRange(cover_row_counter, 1, false).Copy(range_R_cover);
string gradeYear = list.SelectSingleNode("@年級").InnerText;
string deptCode = list.SelectSingleNode("@科別代碼").InnerText;
//學校代碼
cover.Cells[cover_row_counter, 0].PutValue(school_code);
//學年度
cover.Cells[cover_row_counter, 1].PutValue(school_year);
//學期
cover.Cells[cover_row_counter, 2].PutValue(school_semester);
//年級
cover.Cells[cover_row_counter, 3].PutValue(gradeYear);
//科別代碼
cover.Cells[cover_row_counter, 6].PutValue(deptCode);
foreach (XmlElement st in list.SelectNodes("異動名冊封面"))
{
string reportType = st.SelectSingleNode("@名冊別").InnerText;
string classType = st.SelectSingleNode("@班別").InnerText;
string updateType = st.SelectSingleNode("@上傳類別").InnerText;
string disasterStudentCount = st.SelectSingleNode("@因災害申請借讀學生數").InnerText;
string maladapStudentCount = st.SelectSingleNode("@因適應不良申請借讀學生數").InnerText;
string playerTrainingStudentCount = st.SelectSingleNode("@因參加國家代表隊選手培集訓申請借讀學生數").InnerText;
string remarksContent = st.SelectSingleNode("@備註說明").InnerText;
//名冊別
cover.Cells[cover_row_counter, 4].PutValue(reportType);
//班別
cover.Cells[cover_row_counter, 5].PutValue(classType);
//上傳類別
cover.Cells[cover_row_counter, 7].PutValue(updateType);
//因災害申請借讀學生數
cover.Cells[cover_row_counter, 8].PutValue(disasterStudentCount);
//因適應不良申請借讀學生數
cover.Cells[cover_row_counter, 9].PutValue(maladapStudentCount);
//因參加國家代表隊選手培集訓申請借讀學生數
cover.Cells[cover_row_counter, 10].PutValue(playerTrainingStudentCount);
//備註說明
cover.Cells[cover_row_counter, 11].PutValue(remarksContent);
}
cover_row_counter++;
}
// 資料末底 加End
cover.Cells.CreateRange(cover_row_counter, 1, false).Copy(range_R_cover_EndRow);
#endregion
wb.Worksheets.RemoveAt("借讀學生名冊封面範本");
wb.Worksheets.ActiveSheetIndex = 0;
//儲存 Excel
wb.Save(location, FileFormatType.Excel2003);
}
public static bool ExcelFileToDataTables(string filepath, out DataTable[] datatables, out string error)
{
error = "";
datatables = null;
int nSheetsCount = 0;
try
{
if (File.Exists(filepath) == false)
{
error = "文件不存在";
datatables = null;
return false;
}
Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook();
workbook.Open(filepath);
nSheetsCount = workbook.Worksheets.Count;
datatables = new DataTable[nSheetsCount];
for (int i = 0; i < nSheetsCount; i++)
{
Aspose.Cells.Worksheet worksheet = workbook.Worksheets[i];
datatables[i] = worksheet.Cells.ExportDataTable(0, 0, worksheet.Cells.MaxRow + 1, worksheet.Cells.MaxColumn + 1);
//-------------图片处理-------------
Aspose.Cells.Pictures pictures = worksheet.Pictures;
if (pictures.Count > 0)
{
string error2 = "";
if (InsertPicturesIntoDataTable(pictures, datatables[i], out datatables[i], out error2) == false)
{
error = error + error2;
}
}
}
return true;
}
catch (System.Exception e)
{
error = e.Message;
return false;
}
}
protected override void Build(XmlElement source, string location)
{
Workbook template = new Workbook();
//�qResources��TemplateŪ�X��
template.Open(new MemoryStream(Properties.Resources.ExtendingGraduatingStudentListTemplate), FileFormatType.Excel2003);
//�n���ͪ�excel��
Workbook wb = new Aspose.Cells.Workbook();
wb.Open(new MemoryStream(Properties.Resources.ExtendingGraduatingStudentListTemplate), FileFormatType.Excel2003);
Worksheet ws = wb.Worksheets[0];
//�������j�X��row
int next = 24;
//����
int index = 0;
//�d���d��
Range tempRange = template.Worksheets[0].Cells.CreateRange(0, 24, false);
//�`�@�X�����ʬ���
int count = 0;
int totalRec = source.SelectNodes("�M��/���ʬ���").Count;
// ���o�W�U���s���̫Ყ�ʥN�X���
Dictionary<string,string> LastCodeDict = new Dictionary<string,string>();
foreach (XmlNode list in source.SelectNodes("�M��"))
{
//���ͲM��Ĥ@��
//for (int row = 0; row < next; row++)
//{
// ws.Cells.CopyRow(template.Worksheets[0].Cells, row, row + index);
//}
ws.Cells.CreateRange(index, 24, false).Copy(tempRange);
//Page
int currentPage = 1;
int totalPage = (list.ChildNodes.Count / 18) + 1;
//�g�J�W�U���O
if (source.SelectSingleNode("@���O").InnerText == "���ץͲ��~�W�U")
ws.Cells[index, 0].PutValue(ws.Cells[index, 0].StringValue.Replace("�����~", "�����~"));
else
ws.Cells[index, 0].PutValue(ws.Cells[index, 0].StringValue.Replace("�����~", "�����~"));
//�g�J�N��
ws.Cells[index, 6].PutValue("�N�X�G" + source.SelectSingleNode("@�ǮեN��").InnerText + "-" + list.SelectSingleNode("@��O�N��").InnerText);
//�g�J�զW�B�Ǧ~�סB�Ǵ��B��O
ws.Cells[index + 2, 0].PutValue("�զW�G" + source.SelectSingleNode("@�ǮզW��").InnerText);
ws.Cells[index + 2, 4].PutValue(source.SelectSingleNode("@�Ǧ~��").InnerText + "�Ǧ~�� ��" + source.SelectSingleNode("@�Ǵ�").InnerText + "�Ǵ�");
ws.Cells[index + 2, 6].PutValue(list.SelectSingleNode("@��O").InnerText);
//�g�J���
int recCount = 0;
int dataIndex = index + 5;
for (; currentPage <= totalPage; currentPage++)
{
//�ƻs����
if (currentPage + 1 <= totalPage)
{
//for (int row = 0; row < next; row++)
//{
// ws.Cells.CopyRow(ws.Cells, row + index, row + index + next);
//}
ws.Cells.CreateRange(index + next, 24, false).Copy(tempRange);
}
//��J���
for (int i = 0; i < 18 && recCount < list.ChildNodes.Count; i++, recCount++)
{
//MsgBox.Show(i.ToString()+" "+recCount.ToString());
XmlNode rec = list.SelectNodes("���ʬ���")[recCount];
ws.Cells[dataIndex, 0].PutValue(rec.SelectSingleNode("@�Ǹ�").InnerText + "\n" + rec.SelectSingleNode("@�m�W").InnerText);
ws.Cells[dataIndex, 1].PutValue(rec.SelectSingleNode("@�ʧO�N��").InnerText.ToString());
ws.Cells[dataIndex, 2].PutValue(rec.SelectSingleNode("@�ʧO").InnerText);
string ssn = rec.SelectSingleNode("@�����Ҹ�").InnerText;
if (ssn == "")
ssn = rec.SelectSingleNode("@�����Ҹ�").InnerText;
if(!LastCodeDict.ContainsKey(ssn))
LastCodeDict.Add(ssn,rec.SelectSingleNode("@�̫Ყ�ʥN��").InnerText.ToString());
ws.Cells[dataIndex, 3].PutValue(Util.ConvertDateStr2(rec.SelectSingleNode("@�ͤ�").InnerText) + "\n" + ssn);
ws.Cells[dataIndex, 4].PutValue(rec.SelectSingleNode("@�̫Ყ�ʥN��").InnerText.ToString());
ws.Cells[dataIndex, 5].PutValue(Util.ConvertDateStr2(rec.SelectSingleNode("@�Ƭd���").InnerText) + "\n" + rec.SelectSingleNode("@�Ƭd�帹").InnerText);
ws.Cells[dataIndex, 6].PutValue(rec.SelectSingleNode("@���~�ҮѦr��").InnerText);
//ws.Cells[dataIndex, 7].PutValue(rec.SelectSingleNode("@�Ƶ�").InnerText);
if(rec.SelectSingleNode("@�S������N�X")!=null)
ws.Cells[dataIndex, 7].PutValue(rec.SelectSingleNode("@�S������N�X").InnerText);
dataIndex++;
count++;
}
//�p��X�p
if (currentPage == totalPage)
{
ws.Cells[index + 22, 0].PutValue("�X�p");
ws.Cells[index + 22, 1].PutValue(list.ChildNodes.Count.ToString());
}
//����
ws.Cells[index + 23, 6].PutValue("�� " + currentPage + " ���A�@ " + totalPage + " ��");
ws.HPageBreaks.Add(index + 24, 8);
//���ޫ��V�U�@��
index += next;
dataIndex = index + 5;
//�^���i��
ReportProgress((int)(((double)count * 100.0) / ((double)totalRec)));
}
}
Worksheet mingdao = wb.Worksheets[1];
Worksheet mdws = wb.Worksheets[1];
mdws.Name = "�q�l�榡";
Range range_header = mingdao.Cells.CreateRange(0, 1, false);
Range range_row = mingdao.Cells.CreateRange(1, 1, false);
mdws.Cells.CreateRange(0, 1, false).Copy(range_header);
int mdws_index = 0;
DAL.DALTransfer DALTranser = new DAL.DALTransfer();
// �榡�ഫ
List<GovernmentalDocument.Reports.List.rpt_UpdateRecord> _data = DALTranser.ConvertRptUpdateRecord(source);
// �Ƨ� (�� �Z�O�B�~�šB��O�N�X�B���ʥN�X)
_data = (from data in _data orderby data.ClassType, data.DeptCode, data.UpdateCode select data).ToList();
foreach (GovernmentalDocument.Reports.List.rpt_UpdateRecord rec in _data)
{
mdws_index++;
//�C�W�[�@��,�ƻs�@��
mdws.Cells.CreateRange(mdws_index, 1, false).Copy(range_row);
//�����~�Ǧ~��
mdws.Cells[mdws_index, 0].PutValue(rec.ExpectGraduateSchoolYear);
//�Z�O
mdws.Cells[mdws_index, 1].PutValue(rec.ClassType);
//��O�N�X
mdws.Cells[mdws_index, 2].PutValue(rec.DeptCode);
// 2 ��W�����O�A�ШϥΪ̦۶�
//�Ǹ�
mdws.Cells[mdws_index, 4].PutValue(rec.StudentNumber);
//�m�W
mdws.Cells[mdws_index, 5].PutValue(rec.Name);
//�����Ҧr��
mdws.Cells[mdws_index, 6].PutValue(rec.IDNumber);
//��1
mdws.Cells[mdws_index, 7].PutValue(rec.Comment1);
//�ʧO�N�X
mdws.Cells[mdws_index, 8].PutValue(rec.GenderCode);
//�X�ͤ��
mdws.Cells[mdws_index, 9].PutValue(rec.Birthday);
//�S������N�X
mdws.Cells[mdws_index, 10].PutValue(rec.SpecialStatusCode);
//���ʭ�]�N�X
if(LastCodeDict.ContainsKey(rec.IDNumber))
mdws.Cells[mdws_index, 11].PutValue(LastCodeDict[rec.IDNumber]);
else
mdws.Cells[mdws_index, 11].PutValue(rec.UpdateCode);
//�Ƭd��r
mdws.Cells[mdws_index, 12].PutValue(rec.LastADDoc);
//�Ƭd�帹
mdws.Cells[mdws_index, 13].PutValue(rec.LastADNum);
//�Ƭd���
mdws.Cells[mdws_index, 14].PutValue(rec.LastADDate);
//���~�ҮѦr��
mdws.Cells[mdws_index, 15].PutValue(rec.GraduateCertificateNumber);
//�Ƶ�����
mdws.Cells[mdws_index, 16].PutValue(rec.Comment);
}
//�x�s
wb.Save(location, FileFormatType.Excel2003);
}
protected override void Build(System.Xml.XmlElement source, string location)
{
Workbook template = new Workbook();
//從Resources把Template讀出來
template.Open(new MemoryStream(Properties.Resources.GraduatingStudentListTemplate), FileFormatType.Excel2003);
//要產生的excel檔
Workbook wb = new Aspose.Cells.Workbook();
wb.Open(new MemoryStream(Properties.Resources.GraduatingStudentListTemplate), FileFormatType.Excel2003);
Worksheet ws = wb.Worksheets[0];
//頁面間隔幾個row
int next = 24;
//索引
int index = 0;
//範本範圍
Range tempRange = template.Worksheets[0].Cells.CreateRange(0,24,false);
//總共幾筆異動紀錄
int count = 0;
int totalRec = source.SelectNodes("清單/異動紀錄").Count;
foreach (XmlNode list in source.SelectNodes("清單"))
{
//產生清單第一頁
//for (int row = 0; row < next; row++)
//{
// ws.Cells.CopyRow(template.Worksheets[0].Cells, row, row + index);
//}
ws.Cells.CreateRange(index, next, false).Copy(tempRange);
//Page
int currentPage = 1;
int totalPage = (list.ChildNodes.Count / 18) + 1;
//寫入名冊類別
if (source.SelectSingleNode("@類別").InnerText == "畢業名冊")
ws.Cells[index, 0].PutValue(ws.Cells[index, 0].StringValue.Replace("□畢業", "■畢業"));
else
ws.Cells[index, 0].PutValue(ws.Cells[index, 0].StringValue.Replace("□結業", "■結業"));
////寫入代號
//ws.Cells[index,6].PutValue("代碼："+source.SelectSingleNode("@學校代號").InnerText+"-"+list.SelectSingleNode("@科別代號").InnerText);
////寫入校名、學年度、學期、科別
//ws.Cells[index+2, 0].PutValue("校名：" + source.SelectSingleNode("@學校名稱").InnerText);
//ws.Cells[index+2, 4].PutValue(source.SelectSingleNode("@學年度").InnerText + "學年度 第" + source.SelectSingleNode("@學期").InnerText + "學期");
//ws.Cells[index+2, 6].PutValue(list.SelectSingleNode("@科別").InnerText);
//寫入資料
int recCount = 0;
int dataIndex = index + 5;
for (; currentPage <= totalPage; currentPage++)
{
//寫入代號
ws.Cells[index, 6].PutValue("代碼：" + source.SelectSingleNode("@學校代號").InnerText + "-" + list.SelectSingleNode("@科別代號").InnerText);
//寫入校名、學年度、學期、科別
ws.Cells[index + 2, 0].PutValue("校名：" + source.SelectSingleNode("@學校名稱").InnerText);
ws.Cells[index + 2, 4].PutValue(source.SelectSingleNode("@學年度").InnerText + "學年度 第" + source.SelectSingleNode("@學期").InnerText + "學期");
ws.Cells[index + 2, 6].PutValue(list.SelectSingleNode("@科別").InnerText);
//複製頁面
if (currentPage+1 <= totalPage)
{
ws.Cells.CreateRange(index + next, next, false).Copy(tempRange);
//寫入名冊類別
if (source.SelectSingleNode("@類別").InnerText == "畢業名冊")
ws.Cells[index + next, 0].PutValue(ws.Cells[index + next, 0].StringValue.Replace("□畢業", "■畢業"));
else
ws.Cells[index + next, 0].PutValue(ws.Cells[index + next, 0].StringValue.Replace("□結業", "■結業"));
}
//填入資料
for (int i = 0; i < 18 && recCount < list.ChildNodes.Count; i++, recCount++)
{
//MsgBox.Show(i.ToString()+" "+recCount.ToString());
XmlNode rec = list.SelectNodes("異動紀錄")[recCount];
ws.Cells[dataIndex, 0].PutValue(rec.SelectSingleNode("@學號").InnerText + "\n" + rec.SelectSingleNode("@姓名").InnerText);
ws.Cells[dataIndex, 1].PutValue(rec.SelectSingleNode("@性別代號").InnerText.ToString());
ws.Cells[dataIndex, 2].PutValue(rec.SelectSingleNode("@性別").InnerText);
string ssn = "";
if(rec.SelectSingleNode("@身分證號")!=null)
ssn=rec.SelectSingleNode("@身分證號").InnerText;
if (ssn == "")
if(rec.SelectSingleNode("@身份證號")!=null)
ssn = rec.SelectSingleNode("@身份證號").InnerText;
ws.Cells[dataIndex, 3].PutValue(Util.ConvertDateStr2(rec.SelectSingleNode("@生日").InnerText) + "\n" + ssn);
if(rec.SelectSingleNode("@最後異動代號")!=null )
ws.Cells[dataIndex, 4].PutValue(rec.SelectSingleNode("@最後異動代號").InnerText.ToString());
ws.Cells[dataIndex, 5].PutValue(Util.ConvertDateStr2(rec.SelectSingleNode("@備查日期").InnerText) + "\n" +rec.SelectSingleNode("@備查文號").InnerText);
ws.Cells[dataIndex, 6].PutValue(rec.SelectSingleNode("@畢業證書字號").InnerText);
//ws.Cells[dataIndex, 7].PutValue(rec.SelectSingleNode("@備註").InnerText);
if(rec.SelectSingleNode("@特殊身份代碼")!=null)
ws.Cells[dataIndex, 7].PutValue(rec.SelectSingleNode("@特殊身份代碼").InnerText);
dataIndex++;
count++;
}
//計算合計
if (currentPage == totalPage)
{
ws.Cells[dataIndex, 0].PutValue("合計");
ws.Cells[dataIndex, 1].PutValue(list.ChildNodes.Count.ToString());
//ws.Cells[index + 22, 0].PutValue("合計");
//ws.Cells[index + 22, 1].PutValue(list.ChildNodes.Count.ToString());
}
//分頁
ws.Cells[index + 23, 6].PutValue("第 " + currentPage + " 頁，共 " + totalPage + " 頁");
ws.HPageBreaks.Add(index+24, 8);
//索引指向下一頁
index += next;
dataIndex = index + 5;
//回報進度
ReportProgress((int)(((double)count * 100.0) / ((double)totalRec)));
}
}
#region 畢業異動,電子格式
//範本
Worksheet TemplateWb = wb.Worksheets["電子格式範本"];
//實做頁面
Worksheet DyWb = wb.Worksheets[wb.Worksheets.Add()];
//名稱
DyWb.Name = "電子格式";
//範圍
Range range_H = TemplateWb.Cells.CreateRange(0, 1, false);
Range range_R = TemplateWb.Cells.CreateRange(1, 1, false);
//拷貝range_H
DyWb.Cells.CreateRange(0, 1, false).Copy(range_H);
int DyWb_index = 0;
foreach(XmlElement Record in source.SelectNodes("清單/異動紀錄"))
{
DyWb_index++;
//每增加一行,複製一次
DyWb.Cells.CreateRange(DyWb_index,1,false).Copy(range_R);
//班別
DyWb.Cells[DyWb_index, 0].PutValue(Record.GetAttribute("班別"));
//科別代碼
DyWb.Cells[DyWb_index, 1].PutValue((Record.ParentNode as XmlElement).GetAttribute("科別代號"));
// 上傳類別
//學號
DyWb.Cells[DyWb_index, 3].PutValue(Record.GetAttribute("學號"));
//姓名
DyWb.Cells[DyWb_index, 4].PutValue(Record.GetAttribute("姓名"));
//身分證字號
if(Record.GetAttribute("身分證號")=="")
DyWb.Cells[DyWb_index, 5].PutValue(Record.GetAttribute("身份證號"));
else
DyWb.Cells[DyWb_index, 5].PutValue(Record.GetAttribute("身分證號"));
//註1
DyWb.Cells[DyWb_index,6].PutValue(Record.GetAttribute("註1"));
//性別代碼
DyWb.Cells[DyWb_index, 7].PutValue(Record.GetAttribute("性別代號"));
//出生日期
if(!string.IsNullOrEmpty(Record.GetAttribute("生日")))
DyWb.Cells[DyWb_index, 8].PutValue(GetBirthdateWithoutSlash(Record.GetAttribute("生日")));
else
DyWb.Cells[DyWb_index, 8].PutValue(GetBirthdateWithoutSlash(Record.GetAttribute("生日1")));
// 特殊身分代碼
DyWb.Cells[DyWb_index, 9].PutValue(Record.GetAttribute("特殊身分代碼"));
// 年級
DyWb.Cells[DyWb_index, 10].PutValue(Record.GetAttribute("年級"));
// 學籍異動代碼
DyWb.Cells[DyWb_index, 11].PutValue(Record.GetAttribute("最後異動代號"));
//學籍異動文字
DyWb.Cells[DyWb_index, 12].PutValue(Util.GetDocNo_Doc(Record.GetAttribute("備查文號")));
//學籍異動文號
DyWb.Cells[DyWb_index, 13].PutValue(Util.GetDocNo_No(Record.GetAttribute("備查文號")));
// 學籍異動核准日期
DyWb.Cells[DyWb_index, 14].PutValue(Util.ConvertDate1(Record.GetAttribute("備查日期")));
//畢業證書字號
DyWb.Cells[DyWb_index, 15].PutValue(Record.GetAttribute("畢業證書字號"));
//備註說明
DyWb.Cells[DyWb_index, 16].PutValue(Record.GetAttribute("備註"));
}
DyWb.AutoFitColumns();
wb.Worksheets.RemoveAt("電子格式範本");
#endregion
wb.Worksheets.ActiveSheetIndex = 0;
//儲存
wb.Save(location, FileFormatType.Excel2003);
}