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