private void SaveFile(MemoryStream stream, string filename, string format) { if (format == "xls") { Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook(); wb.Open(stream); // 2017/8/22 穎驊依據高雄小組專案 [03-05][04+] EXCEL匯入格式可否修正為xlsx也可匯入? 更改為新版 Aspose.Cells_201402 寫法 //wb.Save(filename, Aspose.Cells.FileFormatType.Excel2003); wb.Save(filename, Aspose.Cells.SaveFormat.Excel97To2003); } else if (format == "xlsx") { Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook(); wb.Open(stream); wb.Save(filename, Aspose.Cells.FileFormatType.Excel2007Xlsx); } else if (format == "doc") { Aspose.Words.Document doc = new Aspose.Words.Document(stream); doc.Save(filename, Aspose.Words.SaveFormat.Doc); } else if (format == "docx") { Aspose.Words.Document doc = new Aspose.Words.Document(stream); doc.Save(filename, Aspose.Words.SaveFormat.Docx); } }
/// <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); return(true); } catch (System.Exception e) { error = e.Message; return(false); } }
// 上傳模組 private void UploadTemplate(string fileName, ref bool uploadIndex, ref string uploadData) { FileStream fs = new FileStream(fileName, FileMode.Open); byte[] tempBuffer = new byte[fs.Length]; fs.Read(tempBuffer, 0, tempBuffer.Length); MemoryStream ms = new MemoryStream(tempBuffer); try { Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook(); wb.Open(ms, Aspose.Cells.FileFormatType.Excel2003); wb = null; } catch { throw new Exception("此版學籍表範本限用相容於 Excel 2003 檔案。"); } try { uploadData = Convert.ToBase64String(tempBuffer); uploadIndex = true; fs.Close(); } catch (Exception ex) { throw new Exception(ex.Message); } }
private void SaveFile(MemoryStream stream, string filename, string format) { if (format == "xls") { Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook(); wb.Open(stream); wb.Save(filename, Aspose.Cells.FileFormatType.Excel2003); } else if (format == "xlsx") { Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook(); wb.Open(stream); wb.Save(filename, Aspose.Cells.FileFormatType.Excel2007Xlsx); } else if (format == "doc") { Aspose.Words.Document doc = new Aspose.Words.Document(stream); doc.Save(filename, Aspose.Words.SaveFormat.Doc); } else if (format == "docx") { Aspose.Words.Document doc = new Aspose.Words.Document(stream); doc.Save(filename, Aspose.Words.SaveFormat.Docx); } }
/// <summary> /// 导出风险管控列表 /// </summary> /// <param name="queryJson"></param> /// <returns></returns> public ActionResult ExportExcel(string queryJson) { try { HttpResponse resp = System.Web.HttpContext.Current.Response; Operator user = ERCHTMS.Code.OperatorProvider.Provider.Current(); Pagination pagination = new Pagination(); pagination.p_kid = ""; pagination.p_fields = "'' num1,post,name,activitystep,case when isconventional=0 then '常规' else '非常规' end as isconventional,others as others1,b.fullname as dept1,to_char(a.createdate,'yyyy-MM-dd') as date1,'' num2,name as equname,areaname,case when isspecialequ=0 then '是' else '否' end as isspecialequ,others as others2,b.fullname as dept2,to_char(a.createdate,'yyyy-MM-dd') as date2,type"; pagination.p_tablename = "bis_baselisting a left join base_department b on a.createuserdeptcode=b.encode"; pagination.conditionJson = "1=1"; pagination.page = 1; pagination.rows = 100000; if (user.RoleName.Contains("公司级") || user.RoleName.Contains("厂级")) { pagination.conditionJson += " and a.CREATEUSERORGCODE ='" + user.OrganizeCode + "'"; } else { pagination.conditionJson += " and a.createuserdeptcode ='" + user.DeptCode + "'"; } var data = baselistingbll.GetPageListJson(pagination, queryJson); Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook(); wb.Open(Server.MapPath("~/Resource/ExcelTemplate/作业活动及设备设施清单导出模板.xls")); if (data.Rows.Count > 0) { DataTable dt1 = data.Select("type=0").Count() > 0 ? data.Select("type=0").CopyToDataTable() : new DataTable(); if (dt1.Rows.Count > 0) { for (int i = 0; i < dt1.Rows.Count; i++) { dt1.Rows[i]["num1"] = i + 1; } wb.Worksheets[0].Cells.ImportDataTable(dt1, false, 1, 0, data.Rows.Count, 7); } DataTable dt2 = data.Select("type=1").Count() > 0 ? data.Select("type=1").CopyToDataTable() : new DataTable(); if (dt2.Rows.Count > 0) { dt2.Columns.Remove("post"); dt2.Columns.Remove("name"); dt2.Columns.Remove("activitystep"); dt2.Columns.Remove("isconventional"); dt2.Columns.Remove("others1"); dt2.Columns.Remove("dept1"); dt2.Columns.Remove("date1"); dt2.Columns.Remove("num1"); for (int i = 0; i < dt2.Rows.Count; i++) { dt2.Rows[i]["num2"] = i + 1; } wb.Worksheets[1].Cells.ImportDataTable(dt2, false, 1, 0, data.Rows.Count, 7); } } wb.Save(Server.UrlEncode("作业活动及设备设施清单.xls"), Aspose.Cells.FileFormatType.Excel2003, Aspose.Cells.SaveType.OpenInBrowser, resp); return(Success("导出成功。")); } catch (Exception ex) { return(Error(ex.ToString())); } }
private void Event_ImportFromExcelFile(string filename) { try { Aspose.Cells.Workbook wbk = new Aspose.Cells.Workbook(); this.AsposeWkb = wbk; wbk.Open(filename); if (workbook_idx == -1) { this.lb_workbooks.Items.Clear(); for (int i = 0; i < wbk.Worksheets.Count; i++) { this.lb_workbooks.Items.Add(wbk.Worksheets[i].Name); } } } finally { } }
public static DataTable ImportExcelToGrid(string pstrFilename, int iColEnd, int iRowNumber) { string mstr_FileName = pstrFilename; string mstr_PathFileName = mstr_FileName; Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook(); wb.Open(mstr_FileName); Aspose.Cells.Worksheet ws = wb.Worksheets[0]; DataTable dt = new DataTable(); int iRowEnd = GetEndRow(ws, iRowNumber + 1); try { for (int j = 0; j < iColEnd; j++) { string strDataColumn = ws.Cells[iRowNumber - 1, j].Value.ToString().Trim(); dt.Columns.Add(new DataColumn(strDataColumn, typeof(string))); } int i = iRowNumber; for (; i < iRowEnd; i++) { DataRow dr = dt.NewRow(); for (int j = 0; j < iColEnd; j++) { string strValue = ws.Cells[i, j].Value == null ? "" : ws.Cells[i, j].Value.ToString(); dr[j] = strValue; } dt.Rows.Add(dr); } return(dt); } catch (Exception exp) { } return(dt); }
private string TransferFileToBase64String(string fileName) { FileStream fs = new FileStream(fileName, FileMode.Open); byte[] tempBuffer = new byte[fs.Length]; fs.Read(tempBuffer, 0, tempBuffer.Length); fs.Close(); MemoryStream ms = new MemoryStream(tempBuffer); try { Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook(); wb.Open(ms, Aspose.Cells.FileFormatType.Excel2003); } catch { throw new Exception("教學意見表樣版限用相容於 Excel 2003 檔案。"); } try { return Convert.ToBase64String(tempBuffer); } catch (Exception ex) { throw new Exception(ex.Message); } }
public string ImportLabor() { LaborprotectionBLL laborprotectionbll = new LaborprotectionBLL(); PostCache postCache = new PostCache(); PostBLL postBLL = new PostBLL(); DepartmentBLL departmentBLL = new DepartmentBLL(); //获取到已选数据 List <LaborprotectionEntity> laborlist = laborprotectionbll.GetLaborList(); var currUser = OperatorProvider.Provider.Current(); string orgId = OperatorProvider.Provider.Current().OrganizeId;//所属公司 int error = 0; string message = "请选择格式正确的文件再导入!"; string falseMessage = ""; int count = HttpContext.Request.Files.Count; if (count > 0) { HttpPostedFileBase file = HttpContext.Request.Files[0]; if (string.IsNullOrEmpty(file.FileName)) { return(message); } if (!(file.FileName.Substring(file.FileName.IndexOf('.')).Contains("xls") || file.FileName.Substring(file.FileName.IndexOf('.')).Contains("xlsx"))) { return(message); } string fileName = DateTime.Now.ToString("yyyyMMddHHmmss") + System.IO.Path.GetExtension(file.FileName); file.SaveAs(Server.MapPath("~/Resource/temp/" + fileName)); Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook(); wb.Open(Server.MapPath("~/Resource/temp/" + fileName)); Aspose.Cells.Cells cells = wb.Worksheets[0].Cells; if (cells.MaxDataRow == 0) { message = "没有数据,请选择先填写模板在进行导入!"; return(message); } DataTable dt = cells.ExportDataTable(0, 0, cells.MaxDataRow + 1, cells.MaxColumn + 1, true); int order = 1; IList <LaborprotectionEntity> LaborList = new List <LaborprotectionEntity>(); IEnumerable <DepartmentEntity> deptlist = new DepartmentBLL().GetList(); OrganizeBLL orgbll = new OrganizeBLL(); //先获取到原始的一个编号 string no = laborprotectionbll.GetNo(); int ysno = Convert.ToInt32(no); DataItemDetailBLL dataItemDetailBLL = new DataItemDetailBLL(); var dataitem = dataItemDetailBLL.GetDataItemListByItemCode("'LaborName'").ToList(); List <LaborprotectionEntity> insertpro = new List <LaborprotectionEntity>(); List <LaborinfoEntity> insertinfo = new List <LaborinfoEntity>(); //先获取人员 List <UserEntity> userlist = new UserBLL().GetListForCon(it => it.IsPresence == "1" && it.Account != "System").ToList(); List <LaborequipmentinfoEntity> eqlist = new List <LaborequipmentinfoEntity>(); for (int i = 0; i < dt.Rows.Count; i++) { order = i; string Name = dt.Rows[i]["名称"].ToString(); string Model = dt.Rows[i]["型号"].ToString(); string Type = dt.Rows[i]["类型"].ToString(); string DeptName = dt.Rows[i]["使用部门"].ToString(); string OrgName = dt.Rows[i]["使用单位"].ToString(); string PostName = dt.Rows[i]["使用岗位"].ToString().Trim(); string Unit = dt.Rows[i]["劳动防护用品单位"].ToString().Trim(); string Time = dt.Rows[i]["使用期限"].ToString().Trim(); string TimeType = dt.Rows[i]["使用期限单位"].ToString().Trim(); string deptId = "", deptCode = "", PostId = ""; //---****值存在空验证*****-- if (string.IsNullOrEmpty(Name) || string.IsNullOrEmpty(Unit) || string.IsNullOrEmpty(DeptName) || string.IsNullOrEmpty(OrgName) || string.IsNullOrEmpty(PostName)) { falseMessage += "</br>" + "第" + (i + 2) + "行值存在空,未能导入."; error++; continue; } //验证机构是不是和自己一个机构 DepartmentEntity org = deptlist.Where(it => it.FullName == OrgName).FirstOrDefault(); if (org == null) { falseMessage += "</br>" + "第" + (i + 2) + "行使用单位名称不存在,未能导入."; error++; continue; } //如果导入的机构id和本人的机构id不一致 if (org.DepartmentId != currUser.OrganizeId) { falseMessage += "</br>" + "第" + (i + 2) + "行使用单位不是导入者的单位,未能导入."; error++; continue; } //验证所填部门是否存在 var deptFlag = false; var entity1 = deptlist.Where(x => x.OrganizeId == currUser.OrganizeId && x.FullName == DeptName).FirstOrDefault(); if (entity1 == null) { falseMessage += "</br>" + "第" + (i + 2) + "行使用部门不存在,未能导入."; error++; deptFlag = true; break; } else { deptId = entity1.DepartmentId; deptCode = entity1.EnCode; } //var deptFlag = false; //var array = DeptName.Split('/'); //for (int j = 0; j < array.Length; j++) //{ // if (j == 0) // { // if (currUser.RoleName.Contains("省级")) // { // var entity1 = deptlist.Where(x => x.OrganizeId == currUser.OrganizeId && x.FullName == array[j].ToString()).FirstOrDefault(); // if (entity1 == null) // { // falseMessage += "</br>" + "第" + (i + 2) + "行部门不存在,未能导入."; // error++; // deptFlag = true; // break; // } // else // { // deptId = entity1.DepartmentId; // deptCode = entity1.EnCode; // } // } // else // { // var entity = deptlist.Where(x => x.OrganizeId == currUser.OrganizeId && x.Nature == "厂级" && x.FullName == array[j].ToString()).FirstOrDefault(); // if (entity == null) // { // entity = deptlist.Where(x => x.OrganizeId == currUser.OrganizeId && x.Nature == "部门" && x.FullName == array[j].ToString()).FirstOrDefault(); // if (entity == null) // { // entity = deptlist.Where(x => x.OrganizeId == currUser.OrganizeId && x.Nature == "承包商" && x.FullName == array[j].ToString()).FirstOrDefault(); // if (entity == null) // { // falseMessage += "</br>" + "第" + (i + 2) + "行部门不存在,未能导入."; // error++; // deptFlag = true; // break; // } // else // { // deptId = entity.DepartmentId; // deptCode = entity.EnCode; // } // } // else // { // deptId = entity.DepartmentId; // deptCode = entity.EnCode; // } // } // else // { // deptId = entity.DepartmentId; // deptCode = entity.EnCode; // } // } // } // else if (j == 1) // { // var entity1 = deptlist.Where(x => x.OrganizeId == currUser.OrganizeId && x.Nature == "专业" && x.FullName == array[j].ToString()).FirstOrDefault(); // if (entity1 == null) // { // entity1 = deptlist.Where(x => x.OrganizeId == currUser.OrganizeId && x.Nature == "班组" && x.FullName == array[j].ToString()).FirstOrDefault(); // if (entity1 == null) // { // falseMessage += "</br>" + "第" + (i + 2) + "行专业/班组不存在,未能导入."; // error++; // deptFlag = true; // break; // } // else // { // deptId = entity1.DepartmentId; // deptCode = entity1.EnCode; // } // } // else // { // deptId = entity1.DepartmentId; // deptCode = entity1.EnCode; // } // } // else // { // var entity1 = deptlist.Where(x => x.OrganizeId == currUser.OrganizeId && x.Nature == "班组" && x.FullName == array[j].ToString()).FirstOrDefault(); // if (entity1 == null) // { // falseMessage += "</br>" + "第" + (i + 2) + "行班组不存在,未能导入."; // error++; // deptFlag = true; // break; // } // else // { // deptId = entity1.DepartmentId; // deptCode = entity1.EnCode; // } // } //} if (deptFlag) { continue; } //检验所填岗位是否属于其公司或者部门 if (string.IsNullOrEmpty(deptId) || deptId == "undefined") { //所属公司 RoleEntity data = postCache.GetList(orgId, "true").OrderBy(x => x.SortCode).Where(a => a.FullName == PostName).FirstOrDefault(); if (data == null) { falseMessage += "</br>" + "第" + (i + 2) + "行岗位不属于该公司,未能导入."; error++; continue; } } else { //所属部门 //所属公司 RoleEntity data = postCache.GetList(orgId, deptId).OrderBy(x => x.SortCode).Where(a => a.FullName == PostName).FirstOrDefault(); if (data == null) { falseMessage += "</br>" + "第" + (i + 2) + "行岗位不属于该部门,未能导入."; error++; continue; } } //--**验证岗位是否存在**-- RoleEntity re = postBLL.GetList().Where(a => a.FullName == PostName && a.OrganizeId == orgId).FirstOrDefault(); if (!(string.IsNullOrEmpty(deptId) || deptId == "undefined")) { re = postBLL.GetList().Where(a => a.FullName == PostName && a.OrganizeId == orgId && a.DeptId == deptId).FirstOrDefault(); if (re == null) { re = postBLL.GetList().Where(a => a.FullName == PostName && a.OrganizeId == orgId && a.Nature == departmentBLL.GetEntity(deptId).Nature).FirstOrDefault(); } } if (re == null) { falseMessage += "</br>" + "第" + (i + 2) + "行岗位有误,未能导入."; error++; continue; } else { PostId = re.RoleId; } LaborinfoEntity linfo = new LaborinfoEntity(); linfo.PostId = PostId; linfo.DeptCode = deptCode; linfo.DeptId = deptId; linfo.DeptName = DeptName; linfo.LaboroPerationTime = DateTime.Now; linfo.LaboroPerationUserName = currUser.UserName; linfo.Model = Model; linfo.Name = Name; linfo.OrgCode = currUser.OrganizeCode; linfo.OrgId = currUser.OrganizeId; linfo.OrgName = currUser.OrganizeName; linfo.Type = Type; if (Time == "" || !isInt(Time)) { linfo.TimeNum = null; } else { linfo.TimeNum = Convert.ToInt32(Time); linfo.TimeType = TimeType; } linfo.PostName = PostName; linfo.Unit = Unit; linfo.Create(); //如果已存在物品库中 LaborprotectionEntity lp = laborlist.Where(it => it.Name == Name).FirstOrDefault(); if (lp != null) { linfo.No = lp.No; linfo.LId = lp.ID; //如果库里有值 则使用库里的值 linfo.Type = linfo.Type; linfo.TimeNum = lp.TimeNum; linfo.TimeType = lp.TimeType; } else { LaborprotectionEntity newlp = new LaborprotectionEntity(); newlp.Create(); newlp.Name = Name; newlp.No = ysno.ToString(); newlp.LaborOperationTime = DateTime.Now; newlp.LaborOperationUserName = currUser.UserName; newlp.Model = Model; newlp.Type = Type; newlp.Unit = Unit; newlp.TimeNum = linfo.TimeNum; newlp.TimeType = TimeType; linfo.No = ysno.ToString(); linfo.LId = newlp.ID; ysno++; insertpro.Add(newlp); } int num = 0; List <UserEntity> ulist = userlist.Where(it => it.DepartmentId == deptId && it.DutyId == PostId).ToList(); for (int j = 0; j < ulist.Count; j++) { //添加岗位关联人员 LaborequipmentinfoEntity eq = new LaborequipmentinfoEntity(); eq.UserName = ulist[j].RealName; eq.AssId = linfo.ID; eq.LaborType = 0; eq.ShouldNum = 1; num++; eq.UserId = ulist[j].UserId; if (linfo.Type == "衣服") { eq.Size = "L"; } else if (linfo.Type == "鞋子") { eq.Size = "40"; } else { eq.Size = ""; } eq.Create(); eqlist.Add(eq); } linfo.ShouldNum = num; insertinfo.Add(linfo); } laborinfobll.ImportSaveForm(insertinfo, insertpro, eqlist); count = dt.Rows.Count; message = "共有" + count + "条记录,成功导入" + (count - error) + "条,失败" + error + "条"; message += "</br>" + falseMessage; } return(message); }
public static void Main() { //FISCA.Permission.Catalog cat = FISCA.Permission.RoleAclSource.Instance["教務作業"]["功能按鈕"]; //cat.Add(new FISCA.Permission.RibbonFeature("SHSchool.SHStaticRank2.Data", "計算固定排名(測試版)")); var button = FISCA.Presentation.MotherForm.RibbonBarItems["教務作業", "批次作業/檢視"]["成績作業"]["計算固定排名"]["計算多學期成績固定排名(104學年度技職繁星)"]; button.Enable = FISCA.Permission.UserAcl.Current["SHSchool.SHStaticRank2.Data"].Executable; button.Click += delegate { var conf = new StarTechnical(); conf.ShowDialog(); if (conf.DialogResult == System.Windows.Forms.DialogResult.OK) { CalcMutilSemeSubjectRank.OneClassCompleted += delegate { #region delegate if (string.IsNullOrWhiteSpace(conf.Configure.Rank1Tag)) { return; } #region 取得設定值 , MapRecord string rank1tag = conf.Configure.Rank1Tag.Replace("[", "").Replace("]", ""); int subjectLimit = Math.Max(conf.Configure.useSubjectPrintList.Count, conf.Configure.SubjectLimit); string path = Path.Combine(System.Windows.Forms.Application.StartupPath + "\\Reports", CalcMutilSemeSubjectRank.FolderName); string path_prefix = "E_群別"; string path_suffix = "報名表.xls"; FISCA.UDT.AccessHelper _AccessHelper = new FISCA.UDT.AccessHelper(); Dictionary <string, MapRecord> dmr; try { dmr = _AccessHelper.Select <MapRecord>() .FindAll(delegate(MapRecord mr) { return(!string.IsNullOrWhiteSpace(mr.student_tag)); }) .ToDictionary(x => x.student_tag, x => x); } catch (Exception) { return; } int seq; Dictionary <string, Aspose.Cells.Workbook> wbs = new Dictionary <string, Aspose.Cells.Workbook>(); Dictionary <string, Aspose.Cells.Workbook> wbCheck = new Dictionary <string, Aspose.Cells.Workbook>(); Aspose.Cells.Workbook wb; Aspose.Cells.Worksheet ws; string fulltag; string group_code; string full_doc_path; #endregion foreach (DataRow student in CalcMutilSemeSubjectRank._table.Rows) { fulltag = student.Table.Columns.Contains("類別一分類") ? rank1tag + ":" + student["類別一分類"] : ""; //只處理有設定群別的類別 if (dmr.ContainsKey(fulltag)) { #region 處理上傳表 group_code = dmr[fulltag].code1; full_doc_path = Path.Combine(path, path_prefix + group_code + path_suffix); try { #region 開啟xls檔案加入Dictionary,或新增 if (wbs.ContainsKey(group_code)) { wb = wbs[group_code]; } else if (File.Exists(full_doc_path)) { wb = new Aspose.Cells.Workbook(); wb.Open(full_doc_path); wbs.Add(group_code, wb); } else { wb = new Aspose.Cells.Workbook(); ws = wb.Worksheets[0]; #region 報表header初始化 ws.Cells[0, 0].PutValue("序號"); ws.Cells[0, 1].PutValue("學號"); ws.Cells[0, 2].PutValue("學生姓名"); ws.Cells[0, 3].PutValue("群別代碼"); ws.Cells[0, 4].PutValue("學制代碼"); ws.Cells[0, 5].PutValue("科(組)、學程名稱"); ws.Cells[0, 6].PutValue("班級名稱"); ws.Cells[0, 7].PutValue("學業平均成績科(組)、學程名次"); ws.Cells[0, 8].PutValue("學業平均成績群名次"); ws.Cells[0, 9].PutValue("專業及實習科目平均成績群名次"); ws.Cells[0, 10].PutValue("英文平均成績群名次"); ws.Cells[0, 11].PutValue("國文平均成績群名次"); ws.Cells[0, 12].PutValue("數學平均成績群名次"); #endregion wbs.Add(group_code, wb); } #endregion ws = wb.Worksheets[0]; seq = 1;//序號 while (!string.IsNullOrWhiteSpace("" + ws.Cells[seq, 0].Value)) { seq++; } #region 填入資料 ws.Cells[seq, 0].PutValue("" + seq); //1.序號 ws.Cells[seq, 1].PutValue("" + student["學號"]); //2.學號 ws.Cells[seq, 2].PutValue("" + student["姓名"]); //3.學生姓名 ws.Cells[seq, 3].PutValue(group_code); //4.群別代碼 ws.Cells[seq, 4].PutValue(dmr[fulltag].code2); //5.學制代碼 ws.Cells[seq, 5].PutValue("" + student["科別"]); //6.科(組),學程名稱 ws.Cells[seq, 6].PutValue("" + student["班級"]); //7.班級名稱 ws.Cells[seq, 7].PutValue("" + student["學業原始平均科排名"]); //8.平均科排名 ws.Cells[seq, 8].PutValue("" + student["學業原始平均類別一排名"]); //9.學業平均成績群名次 ws.Cells[seq, 9].PutValue("" + student["篩選科目原始成績加權平均平均類別二排名"]); //10.專業及實習平均成績群名次 for (int i = 1; i <= subjectLimit; i++) { switch ("" + student["科目名稱" + i]) { case "英文": ws.Cells[seq, 10].PutValue("" + student["科目平均類別一排名" + i]); //11.英文平均成績群名次 break; case "國文": ws.Cells[seq, 11].PutValue("" + student["科目平均類別一排名" + i]); //12.國文平均成績群名次 break; case "數學": ws.Cells[seq, 12].PutValue("" + student["科目平均類別一排名" + i]); //13.數學平均群名次 break; default: break; } } #endregion wb = null; } catch (Exception) { #region save all opened excel foreach (KeyValuePair <string, Aspose.Cells.Workbook> item in wbs) { item.Value.Save(Path.Combine(path, path_prefix + item.Key + path_suffix), Aspose.Cells.FileFormatType.Excel97To2003); } #endregion throw; } #endregion #region 處理驗算表 group_code = dmr[fulltag].code1; full_doc_path = Path.Combine(Path.Combine(path, path_prefix + "成績明細" + group_code + ".xls")); try { #region 開啟xls檔案加入Dictionary,或新增 if (wbCheck.ContainsKey(group_code)) { wb = wbCheck[group_code]; } else if (File.Exists(full_doc_path)) { wb = new Aspose.Cells.Workbook(); wb.Open(full_doc_path); wbCheck.Add(group_code, wb); } else { wb = new Aspose.Cells.Workbook(); ws = wb.Worksheets[0]; #region 報表header初始化 ws.Cells[0, 0].PutValue("序號"); ws.Cells[0, 1].PutValue("學號"); ws.Cells[0, 2].PutValue("學生姓名"); ws.Cells[0, 3].PutValue("群別代碼"); ws.Cells[0, 4].PutValue("學制代碼"); ws.Cells[0, 5].PutValue("科(組)、學程名稱"); ws.Cells[0, 6].PutValue("班級名稱"); ws.Cells[0, 7].PutValue("類別一分類"); ws.Cells[0, 8].PutValue("類別二分類"); ws.Cells[0, 9].PutValue("學業原始平均成績"); ws.Cells[0, 10].PutValue("學業平均成績科(組)、學程名次"); ws.Cells[0, 11].PutValue("學業平均成績群名次"); ws.Cells[0, 12].PutValue("專業及實習科目平均成績"); ws.Cells[0, 13].PutValue("專業及實習科目平均成績群名次"); ws.Cells[0, 14].PutValue("英文科平均成績"); ws.Cells[0, 15].PutValue("英文平均成績群名次"); ws.Cells[0, 16].PutValue("國文科平均成績"); ws.Cells[0, 17].PutValue("國文平均成績群名次"); ws.Cells[0, 18].PutValue("數學科平均成績"); ws.Cells[0, 19].PutValue("數學平均成績群名次"); #endregion wbCheck.Add(group_code, wb); } #endregion ws = wb.Worksheets[0]; seq = 1;//序號 while (!string.IsNullOrWhiteSpace("" + ws.Cells[seq, 0].Value)) { seq++; } #region 填入資料 ws.Cells[seq, 0].PutValue("" + seq); //1.序號 ws.Cells[seq, 1].PutValue("" + student["學號"]); //2.學號 ws.Cells[seq, 2].PutValue("" + student["姓名"]); //3.學生姓名 ws.Cells[seq, 3].PutValue(group_code); //4.群別代碼 ws.Cells[seq, 4].PutValue(dmr[fulltag].code2); //5.學制代碼 ws.Cells[seq, 5].PutValue("" + student["科別"]); //6.科(組),學程名稱 ws.Cells[seq, 6].PutValue("" + student["班級"]); //7.班級名稱 ws.Cells[seq, 7].PutValue("" + student["類別一分類"]); ws.Cells[seq, 8].PutValue("" + student["類別二分類"]); ws.Cells[seq, 9].PutValue("" + student["學業原始平均"]); //學業原始平均成績 ws.Cells[seq, 10].PutValue("" + student["學業原始平均科排名"]); //8.平均科排名 ws.Cells[seq, 11].PutValue("" + student["學業原始平均類別一排名"]); //9.學業平均成績群名次 ws.Cells[seq, 12].PutValue("" + student["篩選科目原始成績加權平均平均類別二"]); //專業及實習平均成績 ws.Cells[seq, 13].PutValue("" + student["篩選科目原始成績加權平均平均類別二排名"]); //10.專業及實習平均成績群名次 for (int i = 1; i <= subjectLimit; i++) { switch ("" + student["科目名稱" + i]) { case "英文": ws.Cells[seq, 14].PutValue("" + student["科目平均" + i]); //英文平均成績 ws.Cells[seq, 15].PutValue("" + student["科目平均類別一排名" + i]); //11.英文平均成績群名次 break; case "國文": ws.Cells[seq, 16].PutValue("" + student["科目平均" + i]); //國文平均成績 ws.Cells[seq, 17].PutValue("" + student["科目平均類別一排名" + i]); //12.國文平均成績群名次 break; case "數學": ws.Cells[seq, 18].PutValue("" + student["科目平均" + i]); //數學平均成績 ws.Cells[seq, 19].PutValue("" + student["科目平均類別一排名" + i]); //13.數學平均群名次 break; default: break; } } #endregion wb = null; } catch (Exception) { #region save all opened excel foreach (KeyValuePair <string, Aspose.Cells.Workbook> item in wbCheck) { item.Value.Save(Path.Combine(path, path_prefix + "成績明細" + item.Key + ".xls"), Aspose.Cells.FileFormatType.Excel97To2003); } #endregion throw; } #endregion } } #region save all opened excel foreach (KeyValuePair <string, Aspose.Cells.Workbook> item in wbs) { item.Value.Save(Path.Combine(path, path_prefix + item.Key + path_suffix), Aspose.Cells.FileFormatType.Excel97To2003); } #endregion #region save all opened excel wbCheck foreach (KeyValuePair <string, Aspose.Cells.Workbook> item in wbCheck) { item.Value.Save(Path.Combine(path, path_prefix + "成績明細" + item.Key + ".xls"), Aspose.Cells.FileFormatType.Excel97To2003); } #endregion wbs = null; #endregion }; CalcMutilSemeSubjectRank.Setup(conf.Configure); } }; }
public string ImportQuestion() { var user = OperatorProvider.Provider.Current(); if (OperatorProvider.Provider.Current().IsSystem) { return("超级管理员无此操作权限"); } string orgId = OperatorProvider.Provider.Current().OrganizeId;//所属公司 int error = 0; string message = "请选择格式正确的文件再导入!"; string falseMessage = ""; int count = HttpContext.Request.Files.Count; if (count > 0) { HttpPostedFileBase file = HttpContext.Request.Files[0]; if (string.IsNullOrEmpty(file.FileName)) { return(message); } if (!(file.FileName.Substring(file.FileName.IndexOf('.')).Contains("xls") || file.FileName.Substring(file.FileName.IndexOf('.')).Contains("xlsx"))) { return(message); } string fileName = DateTime.Now.ToString("yyyyMMddHHmmss") + System.IO.Path.GetExtension(file.FileName); file.SaveAs(Server.MapPath("~/Resource/temp/" + fileName)); Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook(); wb.Open(Server.MapPath("~/Resource/temp/" + fileName)); Aspose.Cells.Cells cells = wb.Worksheets[0].Cells; string where = ""; if (user.RoleName.Contains("厂级")) { where += " a.ISROLE = '0' and a.CREATEUSERDEPTCODE ='" + user.DeptCode + "'"; } else { where += " a.ISROLE = '1' and a.CREATEUSERDEPTCODE ='" + user.DeptCode + "'"; } DataTable dtcontent = matrixsafecheckbll.GetInfoBySql("select ID, CODE,CONTENT from BIS_MATRIXCONTENT a where " + where); if (dtcontent.Rows.Count == 0) { return("请先设置检查内容"); } DataTable dtdept = matrixsafecheckbll.GetInfoBySql("select ID, code,dept,deptname,deptcode from BIS_MATRIXDEPT a where " + where); if (dtdept.Rows.Count == 0) { return("请先设置检查部门"); } DataTable dt = cells.ExportDataTable(1, 0, cells.MaxDataRow, cells.MaxColumn + 1, false); dt.Rows.RemoveAt(0); for (int i = 1; i < dt.Rows.Count; i++) { //检查日期 string checktime = dt.Rows[i][0].ToString(); //检查内容 string checkcontent = dt.Rows[i][1].ToString(); //检查部门 string checkdept = dt.Rows[i][2].ToString(); //检查人员 string checkuser = dt.Rows[i][3].ToString(); //---****值存在空验证*****-- if (string.IsNullOrEmpty(checktime) && string.IsNullOrEmpty(checkcontent) && string.IsNullOrEmpty(checkdept) && string.IsNullOrEmpty(checkuser)) { continue; } if (string.IsNullOrEmpty(checktime) || string.IsNullOrEmpty(checkcontent) || string.IsNullOrEmpty(checkdept)) { falseMessage += "</br>" + "第" + (i + 2) + "行值存在空,未能导入."; error++; continue; } MatrixsafecheckEntity entir = new MatrixsafecheckEntity(); entir.ISOVER = 1; // 检查时间 try { if (!string.IsNullOrEmpty(checktime)) { // 检查时间 DataTable dtcount = matrixsafecheckbll.GetInfoBySql("select id from bis_matrixsafecheck where checktime = to_date('" + DateTime.Parse(checktime).ToString("yyyy-MM-dd") + "', 'yyyy-MM-dd HH24:mi:ss') "); if (dtcount.Rows.Count > 0) { falseMessage += "</br>" + "第" + (i + 2) + "行检查时间已存在,未能导入."; error++; continue; } else { entir.CHECKTIME = DateTime.Parse(DateTime.Parse(checktime).ToString("yyyy-MM-dd")); } } } catch { falseMessage += "</br>" + "第" + (i + 2) + "行检查时间有误,未能导入."; error++; continue; } // 检查内容 entir.CONTENT = checkcontent; if (!string.IsNullOrEmpty(checkcontent)) { var conarr = checkcontent.Split(','); int contentresult = 0; foreach (string continfo in conarr) { foreach (DataRow condr in dtcontent.Rows) { if (continfo == condr["CONTENT"].ToString()) { if (entir.CONTENTID == "" || entir.CONTENTID == null) { entir.CONTENTID = condr["ID"].ToString(); entir.CONTENTNUM = condr["CODE"].ToString(); } else { entir.CONTENTID += "," + condr["ID"].ToString(); entir.CONTENTNUM += "," + condr["CODE"].ToString(); } contentresult++; break; } } } if (contentresult != conarr.Length) { falseMessage += "</br>" + "第" + (i + 2) + "行检查内容有误,未能导入."; error++; continue; } } // 检查部门 entir.CHECKDEPTNAME = checkdept; if (!string.IsNullOrEmpty(checkdept)) { var deptarr = checkdept.Split(','); int checkdeptresult = 0; foreach (string deptinfo in deptarr) { foreach (DataRow deptdr in dtdept.Rows) { if (deptinfo == deptdr["DEPTNAME"].ToString()) { if (entir.CHECKDEPTSEL == "" || entir.CHECKDEPTSEL == null) { entir.CHECKDEPTSEL = deptdr["ID"].ToString(); entir.CHECKDEPT = deptdr["DEPT"].ToString(); entir.CHECKDEPTCODE = deptdr["DEPTCODE"].ToString(); entir.CHECKDEPTNUM = deptdr["CODE"].ToString(); } else { entir.CHECKDEPTSEL += "," + deptdr["ID"].ToString(); entir.CHECKDEPT += "," + deptdr["DEPT"].ToString(); entir.CHECKDEPTCODE += "," + deptdr["DEPTCODE"].ToString(); entir.CHECKDEPTNUM += "," + deptdr["CODE"].ToString(); } checkdeptresult++; break; } } } if (checkdeptresult != deptarr.Length) { falseMessage += "</br>" + "第" + (i + 2) + "行检查部门有误,未能导入."; error++; continue; } } // 检查人员 entir.CHECKUSERNAME = checkuser; if (!string.IsNullOrEmpty(checkuser)) { var userarr = checkuser.Split(','); int checkuserresult = 0; foreach (string userinfo in userarr) { DataTable userdt = matrixsafecheckbll.GetInfoBySql(" SELECT USERID,ACCOUNT,DEPARTMENTCODE,REALNAME FROM BASE_USER WHERE REALNAME = '" + userinfo + "' "); if (userdt.Rows.Count == 0) { break; } else { if (entir.CHECKUSER == "" || entir.CHECKUSER == null) { entir.CHECKUSER = userdt.Rows[0]["USERID"].ToString(); entir.CHECKUSERCODE = userdt.Rows[0]["ACCOUNT"].ToString(); entir.CHECKUSERDEPT = userdt.Rows[0]["DEPARTMENTCODE"].ToString(); } else { entir.CHECKUSER += "," + userdt.Rows[0]["USERID"].ToString(); entir.CHECKUSERCODE += "," + userdt.Rows[0]["ACCOUNT"].ToString(); entir.CHECKUSERDEPT = userdt.Rows[0]["DEPARTMENTCODE"].ToString(); } checkuserresult++; } } if (checkuserresult != userarr.Length) { falseMessage += "</br>" + "第" + (i + 2) + "行检查人员有误,未能导入."; error++; continue; } } try { entir.ID = Guid.NewGuid().ToString(); matrixsafecheckbll.SaveForm(entir.ID, entir); } catch { falseMessage += "</br>" + "第" + (i + 2) + "行保存失败,未能导入."; error++; continue; } } count = dt.Rows.Count - 1; message = "共有" + count + "条记录,成功导入" + (count - error) + "条,失败" + error + "条"; message += "</br>" + falseMessage; } return(message); }
public string ImportStandard(string treeId, string treeName, string treeCode) { 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++) { SafeInstitutionEntity entity = new SafeInstitutionEntity(); entity.Id = Guid.NewGuid().ToString(); entity.FilesId = Guid.NewGuid().ToString(); //文件名称 string filename = dt.Rows[i][0].ToString(); //文件编号 string filecode = dt.Rows[i][1].ToString(); //发布单位 string issuedept = dt.Rows[i][4].ToString(); //发布时间 string releasedate = dt.Rows[i][5].ToString(); //修订时间 string revisedate = dt.Rows[i][6].ToString(); //实施时间 string carrydate = dt.Rows[i][7].ToString(); //备注 string Remark = dt.Rows[i][8].ToString(); //---****值存在空验证*****-- if (string.IsNullOrEmpty(filename) || string.IsNullOrEmpty(filecode)) { 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; } string strPath = filepath.Substring(filepath.IndexOf('.')); //---****文件格式验证*****-- if (!(strPath.Contains("doc") || strPath.Contains("docx") || strPath.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 = entity.FilesId; //关联ID fileInfoEntity.FileName = filepath; fileInfoEntity.FilePath = "~/Resource/InstitutionSystem/" + 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/InstitutionSystem/"), decompressionDirectory + filepath, fileguid + fileinfo.Extension); fileinfobll.SaveForm("", fileInfoEntity); } if (conbool) { continue; } //正文附件路径 filepaths = dt.Rows[i][3].ToString().Split(';'); filepath = ""; for (int j = 0; j < filepaths.Length; j++) { filepath = filepaths[j]; if (string.IsNullOrEmpty(filepath)) { continue; } string strPath = filepath.Substring(filepath.IndexOf('.')); //---****文件格式验证*****-- if (!(strPath.Contains("doc") || strPath.Contains("docx") || strPath.Contains("pdf") || strPath.Contains("ppt") || strPath.Contains("xlsx") || strPath.Contains("xls") || strPath.Contains("png") || strPath.Contains("jpg") || strPath.Contains("jpeg"))) { 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 = entity.Id; //关联ID fileInfoEntity.FileName = filepath; fileInfoEntity.FilePath = "~/Resource/InstitutionSystem/" + 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/InstitutionSystem/"), decompressionDirectory + filepath, fileguid + fileinfo.Extension); fileinfobll.SaveForm("", fileInfoEntity); } entity.FileName = filename; entity.FileCode = filecode; entity.IssueDept = issuedept; entity.LawTypeId = treeId; entity.LawTypeName = treeName; entity.LawTypeCode = treeCode; if (!string.IsNullOrEmpty(releasedate)) { entity.ReleaseDate = Convert.ToDateTime(releasedate); } if (!string.IsNullOrEmpty(revisedate)) { entity.ReviseDate = Convert.ToDateTime(revisedate); } if (!string.IsNullOrEmpty(carrydate)) { entity.CarryDate = Convert.ToDateTime(carrydate); } entity.Remark = !string.IsNullOrEmpty(Remark) ? Remark : ""; try { safeinstitutionbll.SaveForm(entity.Id, entity); success++; } catch { error++; } } message = "共有" + dt.Rows.Count + "条记录,成功导入" + success + "条,失败" + error + "条"; message += "</br>" + falseMessage; } return(message); } catch (Exception e) { return("导入的Excel数据格式不正确,请下载标准模板重新填写!"); } }
public string ImportUsers(string applyId) { if (OperatorProvider.Provider.Current().IsSystem) { return("超级管理员无法操作此功能"); } var currUser = OperatorProvider.Provider.Current(); int error = 0; string message = "请选择格式正确的文件再导入!"; string falseMessage = ""; int count = HttpContext.Request.Files.Count; if (count > 0) { HttpPostedFileBase file = HttpContext.Request.Files[0]; if (string.IsNullOrEmpty(file.FileName)) { return(message); } if (!(file.FileName.Substring(file.FileName.IndexOf('.')).Contains("xls") || file.FileName.Substring(file.FileName.IndexOf('.')).Contains("xlsx"))) { return(message); } string fileName = DateTime.Now.ToString("yyyyMMddHHmmss") + System.IO.Path.GetExtension(file.FileName); file.SaveAs(Server.MapPath("~/Resource/temp/" + fileName)); Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook(); wb.Open(Server.MapPath("~/Resource/temp/" + fileName)); Aspose.Cells.Cells cells = wb.Worksheets[0].Cells; DataTable dt = cells.ExportDataTable(1, 0, cells.MaxDataRow, cells.MaxColumn + 1, true); StringBuilder sb = new StringBuilder("begin \r\n"); IList <ThreePeopleInfoEntity> list = new List <ThreePeopleInfoEntity>(); for (int i = 0; i < dt.Rows.Count; i++) { DataRow dr = dt.Rows[i]; string userName = dr[0].ToString().Trim(); string idCard = dr[1].ToString().Trim(); string userType = dr[2].ToString().Trim(); if (string.IsNullOrEmpty(userName) || string.IsNullOrEmpty(idCard) || string.IsNullOrEmpty(userType)) { falseMessage += "</br>" + "第" + (i + 3) + "行值存在空,未能导入."; error++; continue; } //---****身份证正确验证*****-- if (!Regex.IsMatch(idCard, @"^(^d{15}$|^\d{18}$|^\d{17}(\d|X|x))$", RegexOptions.IgnoreCase)) { falseMessage += "</br>" + "第" + (i + 3) + "行身份证号格式有误,未能导入."; error++; continue; } if (list.Count(t => t.IdCard == idCard) > 0) { falseMessage += "</br>" + "第" + (i + 3) + "行人员身份证信息已存在,未能导入."; error++; continue; } else { list.Add(new ThreePeopleInfoEntity { UserName = userName, IdCard = idCard, ApplyId = applyId, OrgCode = currUser.OrganizeCode, TicketType = userType }); } } //sb.Append("end \r\n commit;"); if (dt.Rows.Count > 0) { CacheFactory.Cache().WriteCache(list, applyId, DateTime.Now.AddMinutes(30)); // new DepartmentBLL().ExecuteSql(sb.ToString()); } count = dt.Rows.Count; message = "共有" + count + "条记录,成功导入" + (count - error) + "条,失败" + error + "条"; message += "</br>" + falseMessage; } return(message); }
/// <summary> /// 导出到Excel /// </summary> /// <param name="queryJson"></param> /// <returns></returns> public ActionResult ExportData(string queryJson) { Pagination pagination = new Pagination(); pagination.page = 1; pagination.rows = 100000000; pagination.p_kid = "a.Id as workid"; pagination.p_fields = "supervisestate,taskworktype,handtype,to_char(TaskWorkStartTime,'yyyy-mm-dd hh24:mi') || ' - ' || to_char(TaskWorkStartTime,'yyyy-mm-dd hh24:mi'),'' as taskdept,'' as taskworkplace,b.fullname,taskusername,'-' as timelong"; pagination.p_tablename = "bis_supervisetask a left join Base_Department b on a.steamid=b.departmentid"; pagination.conditionJson = "1=1"; pagination.sidx = "supervisestate asc,a.createdate"; pagination.sord = "desc"; Operator user = ERCHTMS.Code.OperatorProvider.Provider.Current(); if (!user.IsSystem) { string authType = new AuthorizeBLL().GetOperAuthorzeType(user, HttpContext.Request.Cookies["currentmoduleId"].Value, "search"); if (!string.IsNullOrEmpty(authType)) { switch (authType) { case "1": pagination.conditionJson += " and a.createuserid='" + user.UserId + "'"; break; case "2": pagination.conditionJson += " and a.createuserdeptcode='" + user.DeptCode + "'"; break; case "3": //本子部门 pagination.conditionJson += string.Format(" and ((a.steamid in(select departmentid from base_department where encode like '{0}%' or senddeptid='{1}') and supervisestate!='1') or a.createuserid='{2}')", user.DeptCode, user.DeptId, user.UserId); break; case "4": pagination.conditionJson += " and ((a.createuserorgcode='" + user.OrganizeCode + "' and supervisestate!='1') or(a.createuserid='" + user.UserId + "'))"; break; } } else { pagination.conditionJson += " and 0=1"; } } DataTable exportTable = supervisetaskbll.GetPageDataTable(pagination, queryJson); foreach (DataRow item in exportTable.Rows) { if (item["supervisestate"].ToString() == "1") { item["supervisestate"] = "创建监督"; } else if (item["supervisestate"].ToString() == "2") { item["supervisestate"] = "未监督"; } else { item["supervisestate"] = "已监督"; } var type = ""; if (!string.IsNullOrEmpty(item["taskworktype"].ToString()) && !string.IsNullOrEmpty(item["handtype"].ToString())) { type = item["taskworktype"].ToString() + "," + item["handtype"].ToString(); } else { if (!string.IsNullOrEmpty(item["taskworktype"].ToString())) { type = item["taskworktype"].ToString(); } if (!string.IsNullOrEmpty(item["handtype"].ToString())) { type = item["handtype"].ToString(); } } item["taskworktype"] = type; var workdata = superviseworkinfobll.GetList(item["workid"].ToString()); string place = "", deptname = ""; foreach (var work in workdata) { place += work.WorkPlace + ","; deptname += work.WorkDeptName + ","; } if (!string.IsNullOrEmpty(place)) { item["taskworkplace"] = place.TrimEnd(','); } if (!string.IsNullOrEmpty(deptname)) { item["taskdept"] = deptname.TrimEnd(','); } } exportTable.Columns.Remove("workid"); exportTable.Columns.Remove("handtype"); exportTable.Columns.Remove("r"); // 确定导出文件名 string fileName = "高风险作业旁站监督信息"; HttpResponse resp = System.Web.HttpContext.Current.Response; // 详细列表内容 string fielname = fileName + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls"; Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook(); wb.Open(Server.MapPath("~/Resource/ExcelTemplate/高风险作业旁站监督信息.xlsx")); Aspose.Cells.Worksheet sheet = wb.Worksheets[0] as Aspose.Cells.Worksheet; Aspose.Cells.Cell cell = sheet.Cells[1, 1]; sheet.Cells.ImportDataTable(exportTable, false, 1, 0); wb.Save(Server.UrlEncode(fielname), Aspose.Cells.FileFormatType.Excel2003, Aspose.Cells.SaveType.OpenInBrowser, resp); return(Success("导出成功!")); }
public string ImportEle() { UserBLL userbll = new UserBLL(); NosaeleBLL nosaelebll = new NosaeleBLL(); int error = 0; int sussceed = 0; string message = "请选择格式正确的文件再导入!"; string falseMessage = ""; int count = HttpContext.Request.Files.Count; if (count > 0) { HttpPostedFileBase file = HttpContext.Request.Files[0]; if (string.IsNullOrEmpty(file.FileName)) { return(message); } if (!(file.FileName.Substring(file.FileName.IndexOf('.')).Contains("xls") || file.FileName.Substring(file.FileName.IndexOf('.')).Contains("xlsx"))) { return(message); } string fileName = DateTime.Now.ToString("yyyyMMddHHmmss") + System.IO.Path.GetExtension(file.FileName); string filePath = Server.MapPath("~/Resource/temp/" + fileName); file.SaveAs(filePath); Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook(); wb.Open(Server.MapPath("~/Resource/temp/" + fileName)); Aspose.Cells.Cells cells = wb.Worksheets[0].Cells; if ((cells.MaxDataRow - 3) == 0) { message = "没有数据,请选择先填写模板在进行导入!"; return(message); } DataTable dt = cells.ExportDataTable(3, 0, (cells.MaxDataRow - 2), cells.MaxColumn + 1, true); //DataTable dt = ExcelHelper.ExcelImport(filePath); for (int i = 0; i < dt.Rows.Count; i++) { object[] vals = dt.Rows[i].ItemArray; if (IsEndRow(vals) == true) { break; } var msg = ""; if (Validate(i, vals, userbll, nosaelebll, out msg) == true) { var entity = GenEntity(vals, userbll, nosaelebll); nosaareabll.SaveForm(entity.ID, entity); sussceed++; } else { falseMessage += "第" + (i + 1) + "行" + msg + "<br/>"; error++; } } count = dt.Rows.Count; message = "共有" + count + "条记录,成功导入" + sussceed + "条,失败" + error + "条"; message += "<br/>" + falseMessage; //删除临时文件 System.IO.File.Delete(filePath); } return(message); }
// 上傳模組 private void UploadTemplate(string fileName, ref bool uploadIndex, ref string uploadData) { FileStream fs = new FileStream(fileName, FileMode.Open); byte[] tempBuffer = new byte[fs.Length]; fs.Read(tempBuffer, 0, tempBuffer.Length); MemoryStream ms = new MemoryStream(tempBuffer); try { Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook(); wb.Open(ms, Aspose.Cells.FileFormatType.Excel2003); wb = null; } catch { throw new Exception("此版學籍表範本限用相容於 Excel 2003 檔案。"); } try { uploadData = Convert.ToBase64String(tempBuffer); uploadIndex = true; fs.Close(); } catch (Exception ex) { throw new Exception(ex.Message); } }
public string ImportQuestion(string keyvalue) { if (OperatorProvider.Provider.Current().IsSystem) { return("超级管理员无此操作权限"); } string orgId = OperatorProvider.Provider.Current().OrganizeId;//所属公司 int error = 0; string message = "请选择格式正确的文件再导入!"; string falseMessage = ""; int count = HttpContext.Request.Files.Count; if (count > 0) { HttpPostedFileBase file = HttpContext.Request.Files[0]; if (string.IsNullOrEmpty(file.FileName)) { return(message); } if (!(file.FileName.Substring(file.FileName.IndexOf('.')).Contains("xls") || file.FileName.Substring(file.FileName.IndexOf('.')).Contains("xlsx"))) { return(message); } string fileName = DateTime.Now.ToString("yyyyMMddHHmmss") + System.IO.Path.GetExtension(file.FileName); file.SaveAs(Server.MapPath("~/Resource/temp/" + fileName)); Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook(); wb.Open(Server.MapPath("~/Resource/temp/" + fileName)); Aspose.Cells.Cells cells = wb.Worksheets[0].Cells; DataTable dt = cells.ExportDataTable(1, 0, cells.MaxDataRow, cells.MaxColumn + 1, false); for (int i = 1; i < dt.Rows.Count; i++) { //序号 string sortnum = dt.Rows[i][0].ToString(); //发现问题 string findquestion = dt.Rows[i][1].ToString(); //整改措施 string actioncontent = dt.Rows[i][2].ToString(); //责任部门 string dutydept = dt.Rows[i][3].ToString(); //责任人 string dutyusername = dt.Rows[i][4].ToString(); //要求完成时间 string finishdate = dt.Rows[i][5].ToString(); //验收人 string acceptuser = dt.Rows[i][6].ToString(); //整改完成情况 string actionresult = dt.Rows[i][7].ToString(); //实际完成时间 string actualdate = dt.Rows[i][8].ToString(); //备注 string beizhu = dt.Rows[i][9].ToString(); //---****值存在空验证*****-- if (string.IsNullOrEmpty(findquestion) || string.IsNullOrEmpty(actioncontent) || string.IsNullOrEmpty(dutydept) || string.IsNullOrEmpty(dutyusername) || string.IsNullOrEmpty(finishdate) || string.IsNullOrEmpty(acceptuser)) { falseMessage += "</br>" + "第" + (i + 2) + "行值存在空,未能导入."; error++; continue; } if (actionresult == "已完成" && string.IsNullOrEmpty(actualdate)) { falseMessage += "</br>" + "第" + (i + 2) + "行值,整改完成情况为已完成时必须填实际完成时间."; error++; continue; } FivesafetycheckauditEntity pe = new FivesafetycheckauditEntity(); pe.ID = Guid.NewGuid().ToString(); pe.FINDQUESTION = findquestion; pe.ACTIONCONTENT = actioncontent; pe.CHECKPASS = "******"; pe.CHECKID = keyvalue; pe.DUTYDEPT = dutydept; if (!string.IsNullOrEmpty(dutydept)) { string[] ar = dutydept.Split('/'); //int resultdept = 0; DataTable deptentity = null; string deptid = ""; foreach (string arstr in ar) { if (deptid == "") { deptentity = fivesafetycheckbll.GetInfoBySql("select departmentid,encode from base_department where fullname = '" + arstr + "' "); } else { deptentity = fivesafetycheckbll.GetInfoBySql("select departmentid,encode from base_department where PARENTID = '" + deptid + "' and fullname = '" + arstr + "' "); } if (deptentity.Rows.Count == 0) { //resultdept = 1; break; } else { deptid = deptentity.Rows[0]["departmentid"].ToString(); } } //var deptentity = fivesafetycheckbll.GetInfoBySql("select departmentid,encode from base_department where fullname = '" + dutydept + "' "); if (deptentity.Rows.Count > 0) { pe.DUTYDEPTCODE = deptentity.Rows[0]["encode"].ToString(); pe.DUTYDEPTID = deptentity.Rows[0]["departmentid"].ToString(); } else { // 如果查询不到部门,智能提醒检查 刘畅 string stdept = fivesafetycheckbll.GetDeptByName(dutydept); if (stdept != "" && stdept != null) { falseMessage += "</br>" + "第" + (i + 2) + "行责任部门未在系统中查到,智能识别判断您输入的可能是<" + stdept + ">."; } else { falseMessage += "</br>" + "第" + (i + 2) + "行责任部门未在系统中查到,未能导入."; } error++; continue; } } pe.DUTYUSERNAME = dutyusername; if (!string.IsNullOrEmpty(dutyusername)) { if (dutyusername.IndexOf('/') > -1) { var deptentity = fivesafetycheckbll.GetInfoBySql("select userid,mobile from base_user where realname = '" + dutyusername.Split('/')[0] + "' "); if (deptentity.Rows.Count == 1) { pe.DUTYUSERID = deptentity.Rows[0]["userid"].ToString(); } else if (deptentity.Rows.Count > 1) { deptentity = fivesafetycheckbll.GetInfoBySql("select userid,mobile from base_user where realname = '" + dutyusername.Split('/')[0] + "' and mobile='" + dutyusername.Split('/')[1] + "' "); if (deptentity.Rows[0]["mobile"].ToString() == dutyusername.Split('/')[1]) { pe.DUTYUSERID = deptentity.Rows[0]["userid"].ToString(); } else { falseMessage += "</br>" + "第" + (i + 2) + "行责任人未在系统中查到,未能导入."; error++; continue; } } else { falseMessage += "</br>" + "第" + (i + 2) + "行责任人未在系统中查到,未能导入."; error++; continue; } } else { var deptentity = fivesafetycheckbll.GetInfoBySql("select userid from base_user where realname = '" + dutyusername + "' "); if (deptentity.Rows.Count > 0) { pe.DUTYUSERID = deptentity.Rows[0]["userid"].ToString(); } else { falseMessage += "</br>" + "第" + (i + 2) + "行责任人未在系统中查到,未能导入."; error++; continue; } } } pe.ACCEPTUSER = acceptuser; if (!string.IsNullOrEmpty(acceptuser)) { if (acceptuser.IndexOf('/') > -1) { var deptentity = fivesafetycheckbll.GetInfoBySql("select userid,mobile from base_user where realname = '" + acceptuser.Split('/')[0] + "' "); if (deptentity.Rows.Count == 1) { pe.ACCEPTUSERID = deptentity.Rows[0]["userid"].ToString(); } else if (deptentity.Rows.Count > 1) { deptentity = fivesafetycheckbll.GetInfoBySql("select userid,mobile from base_user where realname = '" + acceptuser.Split('/')[0] + "' and mobile='" + acceptuser.Split('/')[1] + "' "); if (deptentity.Rows[0]["mobile"].ToString() == acceptuser.Split('/')[1]) { pe.ACCEPTUSERID = deptentity.Rows[0]["userid"].ToString(); } else { falseMessage += "</br>" + "第" + (i + 2) + "行验收人未在系统中查到,未能导入."; error++; continue; } } else { falseMessage += "</br>" + "第" + (i + 2) + "行验收人未在系统中查到,未能导入."; error++; continue; } } else { var deptentity = fivesafetycheckbll.GetInfoBySql("select userid from base_user where realname = '" + acceptuser + "' "); if (deptentity.Rows.Count > 0) { pe.ACCEPTUSERID = deptentity.Rows[0]["userid"].ToString(); } else { falseMessage += "</br>" + "第" + (i + 2) + "行验收人未在系统中查到,未能导入."; error++; continue; } } } try { if (!string.IsNullOrEmpty(finishdate)) { pe.FINISHDATE = DateTime.Parse(DateTime.Parse(finishdate).ToString("yyyy-MM-dd")); } } catch { falseMessage += "</br>" + "第" + (i + 2) + "行要求完成时间有误,未能导入."; error++; continue; } try { if (!string.IsNullOrEmpty(actualdate)) { pe.ACTUALDATE = DateTime.Parse(DateTime.Parse(finishdate).ToString("yyyy-MM-dd")); } } catch { falseMessage += "</br>" + "第" + (i + 2) + "行实际完成时间有误,未能导入."; error++; continue; } //完成情况 if (!string.IsNullOrEmpty(actionresult)) { if (actionresult == "已完成") { pe.ACTIONRESULT = "0"; pe.CHECKPASS = "******"; pe.ACCEPTREUSLT = "0"; } else if (actionresult == "未完成") { pe.ACTIONRESULT = "1"; pe.ACTUALDATE = null; } else { falseMessage += "</br>" + "第" + (i + 2) + "行整改完成情况有误,未能导入."; error++; continue; } } //备注 if (beizhu.Length > 2000) { falseMessage += "</br>" + "第" + (i + 2) + "行备注文本过长,未能导入."; error++; continue; } else { pe.BEIZHU = beizhu; } try { fivesafetycheckauditbll.SaveForm(pe.ID, pe); } catch { falseMessage += "</br>" + "第" + (i + 2) + "行保存失败,未能导入."; error++; continue; } } count = dt.Rows.Count - 1; message = "共有" + count + "条记录,成功导入" + (count - error) + "条,失败" + error + "条"; message += "</br>" + falseMessage; } return(message); }
public static void main() { FISCA.Presentation.RibbonBarItem item1 = FISCA.Presentation.MotherForm.RibbonBarItems["教務作業", "資料統計"]; item1["報表"].Size = FISCA.Presentation.RibbonBarButton.MenuButtonSize.Large; item1["報表"]["高中職學校班級及學生概況"].Enable = UserAcl.Current["SH.School.SchoolStatistics"].Executable; item1["報表"]["高中職學校班級及學生概況"].Click += delegate { //運用Aspose元件來新增活頁簿 Aspose.Cells.Workbook ScoreWorkBook = new Aspose.Cells.Workbook(); System.Windows.Forms.SaveFileDialog saveFileDialog = new System.Windows.Forms.SaveFileDialog(); saveFileDialog.FileName = "高中職學校班級及學生概況.xls"; saveFileDialog.AddExtension = true; saveFileDialog.DefaultExt = "xls"; string filename = (saveFileDialog.ShowDialog() == System.Windows.Forms.DialogResult.OK) ? saveFileDialog.FileName : ""; // ScoreWorkBook.Open(System.Windows.Forms.Application.StartupPath + "\\Customize\\高中職學校班級及學生概況.xlt"); ScoreWorkBook.Open(new System.IO.MemoryStream(Properties.Resources.高中職學校班級及學生概況)); Program.ErrorList.Clear(); #region 普通科 ClassStatistics ClassSat = new ClassStatistics(); ClassSat.StartStatistics(0); ScoreWorkBook.Worksheets[0].Name = "高中職學校班級及學生概況-普通科"; ScoreWorkBook.Worksheets[0].Cells[4, 0].PutValue(SmartSchool.Customization.Data.SystemInformation.SchoolCode); //一年級班級數總計 ScoreWorkBook.Worksheets[0].Cells[7, 5].PutValue(ClassSat.Level1Count24); ScoreWorkBook.Worksheets[0].Cells[7, 7].PutValue(ClassSat.Level1Count34); ScoreWorkBook.Worksheets[0].Cells[7, 9].PutValue(ClassSat.Level1Count44); ScoreWorkBook.Worksheets[0].Cells[7, 11].PutValue(ClassSat.Level1Count54); ScoreWorkBook.Worksheets[0].Cells[7, 13].PutValue(ClassSat.Level1Count55); //二年級班級數總計 ScoreWorkBook.Worksheets[0].Cells[8, 5].PutValue(ClassSat.Level2Count24); ScoreWorkBook.Worksheets[0].Cells[8, 7].PutValue(ClassSat.Level2Count34); ScoreWorkBook.Worksheets[0].Cells[8, 9].PutValue(ClassSat.Level2Count44); ScoreWorkBook.Worksheets[0].Cells[8, 11].PutValue(ClassSat.Level2Count54); ScoreWorkBook.Worksheets[0].Cells[8, 13].PutValue(ClassSat.Level2Count55); //三年級班級數總計 ScoreWorkBook.Worksheets[0].Cells[9, 5].PutValue(ClassSat.Level3Count24); ScoreWorkBook.Worksheets[0].Cells[9, 7].PutValue(ClassSat.Level3Count34); ScoreWorkBook.Worksheets[0].Cells[9, 9].PutValue(ClassSat.Level3Count44); ScoreWorkBook.Worksheets[0].Cells[9, 11].PutValue(ClassSat.Level3Count54); ScoreWorkBook.Worksheets[0].Cells[9, 13].PutValue(ClassSat.Level3Count55); //四年級班級數總計 ScoreWorkBook.Worksheets[0].Cells[10, 5].PutValue(ClassSat.Level4Count24); ScoreWorkBook.Worksheets[0].Cells[10, 7].PutValue(ClassSat.Level4Count34); ScoreWorkBook.Worksheets[0].Cells[10, 9].PutValue(ClassSat.Level4Count44); ScoreWorkBook.Worksheets[0].Cells[10, 11].PutValue(ClassSat.Level4Count54); ScoreWorkBook.Worksheets[0].Cells[10, 13].PutValue(ClassSat.Level4Count55); //一年級學生數總計 ScoreWorkBook.Worksheets[0].Cells[15, 5].PutValue(ClassSat.SR15C5); ScoreWorkBook.Worksheets[0].Cells[15, 6].PutValue(ClassSat.SR15C6); ScoreWorkBook.Worksheets[0].Cells[15, 7].PutValue(ClassSat.SR15C7); ScoreWorkBook.Worksheets[0].Cells[15, 8].PutValue(ClassSat.SR15C8); ScoreWorkBook.Worksheets[0].Cells[15, 9].PutValue(ClassSat.SR15C9); ScoreWorkBook.Worksheets[0].Cells[15, 10].PutValue(ClassSat.SR15C10); ScoreWorkBook.Worksheets[0].Cells[15, 11].PutValue(ClassSat.SR15C11); ScoreWorkBook.Worksheets[0].Cells[15, 12].PutValue(ClassSat.SR15C12); ScoreWorkBook.Worksheets[0].Cells[15, 13].PutValue(ClassSat.SR15C13); ScoreWorkBook.Worksheets[0].Cells[15, 14].PutValue(ClassSat.SR15C14); ScoreWorkBook.Worksheets[0].Cells[16, 5].PutValue(ClassSat.SR16C5); ScoreWorkBook.Worksheets[0].Cells[16, 6].PutValue(ClassSat.SR16C6); ScoreWorkBook.Worksheets[0].Cells[16, 7].PutValue(ClassSat.SR16C7); ScoreWorkBook.Worksheets[0].Cells[16, 8].PutValue(ClassSat.SR16C8); ScoreWorkBook.Worksheets[0].Cells[16, 9].PutValue(ClassSat.SR16C9); ScoreWorkBook.Worksheets[0].Cells[16, 10].PutValue(ClassSat.SR16C10); ScoreWorkBook.Worksheets[0].Cells[16, 11].PutValue(ClassSat.SR16C11); ScoreWorkBook.Worksheets[0].Cells[16, 12].PutValue(ClassSat.SR16C12); ScoreWorkBook.Worksheets[0].Cells[16, 13].PutValue(ClassSat.SR16C13); ScoreWorkBook.Worksheets[0].Cells[16, 14].PutValue(ClassSat.SR16C14); //二年級學生數總計 ScoreWorkBook.Worksheets[0].Cells[17, 5].PutValue(ClassSat.SR17C5); ScoreWorkBook.Worksheets[0].Cells[17, 6].PutValue(ClassSat.SR17C6); ScoreWorkBook.Worksheets[0].Cells[17, 7].PutValue(ClassSat.SR17C7); ScoreWorkBook.Worksheets[0].Cells[17, 8].PutValue(ClassSat.SR17C8); ScoreWorkBook.Worksheets[0].Cells[17, 9].PutValue(ClassSat.SR17C9); ScoreWorkBook.Worksheets[0].Cells[17, 10].PutValue(ClassSat.SR17C10); ScoreWorkBook.Worksheets[0].Cells[17, 11].PutValue(ClassSat.SR17C11); ScoreWorkBook.Worksheets[0].Cells[17, 12].PutValue(ClassSat.SR17C12); ScoreWorkBook.Worksheets[0].Cells[17, 13].PutValue(ClassSat.SR17C13); ScoreWorkBook.Worksheets[0].Cells[17, 14].PutValue(ClassSat.SR17C14); ScoreWorkBook.Worksheets[0].Cells[18, 5].PutValue(ClassSat.SR18C5); ScoreWorkBook.Worksheets[0].Cells[18, 6].PutValue(ClassSat.SR18C6); ScoreWorkBook.Worksheets[0].Cells[18, 7].PutValue(ClassSat.SR18C7); ScoreWorkBook.Worksheets[0].Cells[18, 8].PutValue(ClassSat.SR18C8); ScoreWorkBook.Worksheets[0].Cells[18, 9].PutValue(ClassSat.SR18C9); ScoreWorkBook.Worksheets[0].Cells[18, 10].PutValue(ClassSat.SR18C10); ScoreWorkBook.Worksheets[0].Cells[18, 11].PutValue(ClassSat.SR18C11); ScoreWorkBook.Worksheets[0].Cells[18, 12].PutValue(ClassSat.SR18C12); ScoreWorkBook.Worksheets[0].Cells[18, 13].PutValue(ClassSat.SR18C13); ScoreWorkBook.Worksheets[0].Cells[18, 14].PutValue(ClassSat.SR18C14); //三年級學生數總計 ScoreWorkBook.Worksheets[0].Cells[19, 5].PutValue(ClassSat.SR19C5); ScoreWorkBook.Worksheets[0].Cells[19, 6].PutValue(ClassSat.SR19C6); ScoreWorkBook.Worksheets[0].Cells[19, 7].PutValue(ClassSat.SR19C7); ScoreWorkBook.Worksheets[0].Cells[19, 8].PutValue(ClassSat.SR19C8); ScoreWorkBook.Worksheets[0].Cells[19, 9].PutValue(ClassSat.SR19C9); ScoreWorkBook.Worksheets[0].Cells[19, 10].PutValue(ClassSat.SR19C10); ScoreWorkBook.Worksheets[0].Cells[19, 11].PutValue(ClassSat.SR19C11); ScoreWorkBook.Worksheets[0].Cells[19, 12].PutValue(ClassSat.SR19C12); ScoreWorkBook.Worksheets[0].Cells[19, 13].PutValue(ClassSat.SR19C13); ScoreWorkBook.Worksheets[0].Cells[19, 14].PutValue(ClassSat.SR19C14); ScoreWorkBook.Worksheets[0].Cells[20, 5].PutValue(ClassSat.SR20C5); ScoreWorkBook.Worksheets[0].Cells[20, 6].PutValue(ClassSat.SR20C6); ScoreWorkBook.Worksheets[0].Cells[20, 7].PutValue(ClassSat.SR20C7); ScoreWorkBook.Worksheets[0].Cells[20, 8].PutValue(ClassSat.SR20C8); ScoreWorkBook.Worksheets[0].Cells[20, 9].PutValue(ClassSat.SR20C9); ScoreWorkBook.Worksheets[0].Cells[20, 10].PutValue(ClassSat.SR20C10); ScoreWorkBook.Worksheets[0].Cells[20, 11].PutValue(ClassSat.SR20C11); ScoreWorkBook.Worksheets[0].Cells[20, 12].PutValue(ClassSat.SR20C12); ScoreWorkBook.Worksheets[0].Cells[20, 13].PutValue(ClassSat.SR20C13); ScoreWorkBook.Worksheets[0].Cells[20, 14].PutValue(ClassSat.SR20C14); //四年級學生數總計 ScoreWorkBook.Worksheets[0].Cells[21, 5].PutValue(ClassSat.SR21C5); ScoreWorkBook.Worksheets[0].Cells[21, 6].PutValue(ClassSat.SR21C6); ScoreWorkBook.Worksheets[0].Cells[21, 7].PutValue(ClassSat.SR21C7); ScoreWorkBook.Worksheets[0].Cells[21, 8].PutValue(ClassSat.SR21C8); ScoreWorkBook.Worksheets[0].Cells[21, 9].PutValue(ClassSat.SR21C9); ScoreWorkBook.Worksheets[0].Cells[21, 10].PutValue(ClassSat.SR21C10); ScoreWorkBook.Worksheets[0].Cells[21, 11].PutValue(ClassSat.SR21C11); ScoreWorkBook.Worksheets[0].Cells[21, 12].PutValue(ClassSat.SR21C12); ScoreWorkBook.Worksheets[0].Cells[21, 13].PutValue(ClassSat.SR21C13); ScoreWorkBook.Worksheets[0].Cells[21, 14].PutValue(ClassSat.SR21C14); ScoreWorkBook.Worksheets[0].Cells[22, 5].PutValue(ClassSat.SR22C5); ScoreWorkBook.Worksheets[0].Cells[22, 6].PutValue(ClassSat.SR22C6); ScoreWorkBook.Worksheets[0].Cells[22, 7].PutValue(ClassSat.SR22C7); ScoreWorkBook.Worksheets[0].Cells[22, 8].PutValue(ClassSat.SR22C8); ScoreWorkBook.Worksheets[0].Cells[22, 9].PutValue(ClassSat.SR22C9); ScoreWorkBook.Worksheets[0].Cells[22, 10].PutValue(ClassSat.SR22C10); ScoreWorkBook.Worksheets[0].Cells[22, 11].PutValue(ClassSat.SR22C11); ScoreWorkBook.Worksheets[0].Cells[22, 12].PutValue(ClassSat.SR22C12); ScoreWorkBook.Worksheets[0].Cells[22, 13].PutValue(ClassSat.SR22C13); ScoreWorkBook.Worksheets[0].Cells[22, 14].PutValue(ClassSat.SR22C14); //延修生學生數總計 ScoreWorkBook.Worksheets[0].Cells[23, 5].PutValue(ClassSat.SR23C5); ScoreWorkBook.Worksheets[0].Cells[23, 6].PutValue(ClassSat.SR23C6); ScoreWorkBook.Worksheets[0].Cells[23, 7].PutValue(ClassSat.SR23C7); ScoreWorkBook.Worksheets[0].Cells[23, 8].PutValue(ClassSat.SR23C8); ScoreWorkBook.Worksheets[0].Cells[23, 9].PutValue(ClassSat.SR23C9); ScoreWorkBook.Worksheets[0].Cells[23, 10].PutValue(ClassSat.SR23C10); ScoreWorkBook.Worksheets[0].Cells[23, 11].PutValue(ClassSat.SR23C11); ScoreWorkBook.Worksheets[0].Cells[23, 12].PutValue(ClassSat.SR23C12); ScoreWorkBook.Worksheets[0].Cells[23, 13].PutValue(ClassSat.SR23C13); ScoreWorkBook.Worksheets[0].Cells[23, 14].PutValue(ClassSat.SR23C14); ScoreWorkBook.Worksheets[0].Cells[24, 5].PutValue(ClassSat.SR24C5); ScoreWorkBook.Worksheets[0].Cells[24, 6].PutValue(ClassSat.SR24C6); ScoreWorkBook.Worksheets[0].Cells[24, 7].PutValue(ClassSat.SR24C7); ScoreWorkBook.Worksheets[0].Cells[24, 8].PutValue(ClassSat.SR24C8); ScoreWorkBook.Worksheets[0].Cells[24, 9].PutValue(ClassSat.SR24C9); ScoreWorkBook.Worksheets[0].Cells[24, 10].PutValue(ClassSat.SR24C10); ScoreWorkBook.Worksheets[0].Cells[24, 11].PutValue(ClassSat.SR24C11); ScoreWorkBook.Worksheets[0].Cells[24, 12].PutValue(ClassSat.SR24C12); ScoreWorkBook.Worksheets[0].Cells[24, 13].PutValue(ClassSat.SR24C13); ScoreWorkBook.Worksheets[0].Cells[24, 14].PutValue(ClassSat.SR24C14); #endregion #region 職業科 ClassStatistics ClassSat1 = new ClassStatistics(); ClassSat1.StartStatistics(1); ScoreWorkBook.Worksheets[1].Name = "高中職學校班級及學生概況-職業科"; ScoreWorkBook.Worksheets[1].Cells[4, 0].PutValue(SmartSchool.Customization.Data.SystemInformation.SchoolCode); //一年級班級數總計 ScoreWorkBook.Worksheets[1].Cells[7, 5].PutValue(ClassSat1.Level1Count24); ScoreWorkBook.Worksheets[1].Cells[7, 7].PutValue(ClassSat1.Level1Count34); ScoreWorkBook.Worksheets[1].Cells[7, 9].PutValue(ClassSat1.Level1Count44); ScoreWorkBook.Worksheets[1].Cells[7, 11].PutValue(ClassSat1.Level1Count54); ScoreWorkBook.Worksheets[1].Cells[7, 13].PutValue(ClassSat1.Level1Count55); //二年級班級數總計 ScoreWorkBook.Worksheets[1].Cells[8, 5].PutValue(ClassSat1.Level2Count24); ScoreWorkBook.Worksheets[1].Cells[8, 7].PutValue(ClassSat1.Level2Count34); ScoreWorkBook.Worksheets[1].Cells[8, 9].PutValue(ClassSat1.Level2Count44); ScoreWorkBook.Worksheets[1].Cells[8, 11].PutValue(ClassSat1.Level2Count54); ScoreWorkBook.Worksheets[1].Cells[8, 13].PutValue(ClassSat1.Level2Count55); //三年級班級數總計 ScoreWorkBook.Worksheets[1].Cells[9, 5].PutValue(ClassSat1.Level3Count24); ScoreWorkBook.Worksheets[1].Cells[9, 7].PutValue(ClassSat1.Level3Count34); ScoreWorkBook.Worksheets[1].Cells[9, 9].PutValue(ClassSat1.Level3Count44); ScoreWorkBook.Worksheets[1].Cells[9, 11].PutValue(ClassSat1.Level3Count54); ScoreWorkBook.Worksheets[1].Cells[9, 13].PutValue(ClassSat1.Level3Count55); //四年級班級數總計 ScoreWorkBook.Worksheets[1].Cells[10, 5].PutValue(ClassSat1.Level4Count24); ScoreWorkBook.Worksheets[1].Cells[10, 7].PutValue(ClassSat1.Level4Count34); ScoreWorkBook.Worksheets[1].Cells[10, 9].PutValue(ClassSat1.Level4Count44); ScoreWorkBook.Worksheets[1].Cells[10, 11].PutValue(ClassSat1.Level4Count54); ScoreWorkBook.Worksheets[1].Cells[10, 13].PutValue(ClassSat1.Level4Count55); //一年級學生數總計 ScoreWorkBook.Worksheets[1].Cells[15, 5].PutValue(ClassSat1.SR15C5); ScoreWorkBook.Worksheets[1].Cells[15, 6].PutValue(ClassSat1.SR15C6); ScoreWorkBook.Worksheets[1].Cells[15, 7].PutValue(ClassSat1.SR15C7); ScoreWorkBook.Worksheets[1].Cells[15, 8].PutValue(ClassSat1.SR15C8); ScoreWorkBook.Worksheets[1].Cells[15, 9].PutValue(ClassSat1.SR15C9); ScoreWorkBook.Worksheets[1].Cells[15, 10].PutValue(ClassSat1.SR15C10); ScoreWorkBook.Worksheets[1].Cells[15, 11].PutValue(ClassSat1.SR15C11); ScoreWorkBook.Worksheets[1].Cells[15, 12].PutValue(ClassSat1.SR15C12); ScoreWorkBook.Worksheets[1].Cells[15, 13].PutValue(ClassSat1.SR15C13); ScoreWorkBook.Worksheets[1].Cells[15, 14].PutValue(ClassSat1.SR15C14); ScoreWorkBook.Worksheets[1].Cells[16, 5].PutValue(ClassSat1.SR16C5); ScoreWorkBook.Worksheets[1].Cells[16, 6].PutValue(ClassSat1.SR16C6); ScoreWorkBook.Worksheets[1].Cells[16, 7].PutValue(ClassSat1.SR16C7); ScoreWorkBook.Worksheets[1].Cells[16, 8].PutValue(ClassSat1.SR16C8); ScoreWorkBook.Worksheets[1].Cells[16, 9].PutValue(ClassSat1.SR16C9); ScoreWorkBook.Worksheets[1].Cells[16, 10].PutValue(ClassSat1.SR16C10); ScoreWorkBook.Worksheets[1].Cells[16, 11].PutValue(ClassSat1.SR16C11); ScoreWorkBook.Worksheets[1].Cells[16, 12].PutValue(ClassSat1.SR16C12); ScoreWorkBook.Worksheets[1].Cells[16, 13].PutValue(ClassSat1.SR16C13); ScoreWorkBook.Worksheets[1].Cells[16, 14].PutValue(ClassSat1.SR16C14); //二年級學生數總計 ScoreWorkBook.Worksheets[1].Cells[17, 5].PutValue(ClassSat1.SR17C5); ScoreWorkBook.Worksheets[1].Cells[17, 6].PutValue(ClassSat1.SR17C6); ScoreWorkBook.Worksheets[1].Cells[17, 7].PutValue(ClassSat1.SR17C7); ScoreWorkBook.Worksheets[1].Cells[17, 8].PutValue(ClassSat1.SR17C8); ScoreWorkBook.Worksheets[1].Cells[17, 9].PutValue(ClassSat1.SR17C9); ScoreWorkBook.Worksheets[1].Cells[17, 10].PutValue(ClassSat1.SR17C10); ScoreWorkBook.Worksheets[1].Cells[17, 11].PutValue(ClassSat1.SR17C11); ScoreWorkBook.Worksheets[1].Cells[17, 12].PutValue(ClassSat1.SR17C12); ScoreWorkBook.Worksheets[1].Cells[17, 13].PutValue(ClassSat1.SR17C13); ScoreWorkBook.Worksheets[1].Cells[17, 14].PutValue(ClassSat1.SR17C14); ScoreWorkBook.Worksheets[1].Cells[18, 5].PutValue(ClassSat1.SR18C5); ScoreWorkBook.Worksheets[1].Cells[18, 6].PutValue(ClassSat1.SR18C6); ScoreWorkBook.Worksheets[1].Cells[18, 7].PutValue(ClassSat1.SR18C7); ScoreWorkBook.Worksheets[1].Cells[18, 8].PutValue(ClassSat1.SR18C8); ScoreWorkBook.Worksheets[1].Cells[18, 9].PutValue(ClassSat1.SR18C9); ScoreWorkBook.Worksheets[1].Cells[18, 10].PutValue(ClassSat1.SR18C10); ScoreWorkBook.Worksheets[1].Cells[18, 11].PutValue(ClassSat1.SR18C11); ScoreWorkBook.Worksheets[1].Cells[18, 12].PutValue(ClassSat1.SR18C12); ScoreWorkBook.Worksheets[1].Cells[18, 13].PutValue(ClassSat1.SR18C13); ScoreWorkBook.Worksheets[1].Cells[18, 14].PutValue(ClassSat1.SR18C14); //三年級學生數總計 ScoreWorkBook.Worksheets[1].Cells[19, 5].PutValue(ClassSat1.SR19C5); ScoreWorkBook.Worksheets[1].Cells[19, 6].PutValue(ClassSat1.SR19C6); ScoreWorkBook.Worksheets[1].Cells[19, 7].PutValue(ClassSat1.SR19C7); ScoreWorkBook.Worksheets[1].Cells[19, 8].PutValue(ClassSat1.SR19C8); ScoreWorkBook.Worksheets[1].Cells[19, 9].PutValue(ClassSat1.SR19C9); ScoreWorkBook.Worksheets[1].Cells[19, 10].PutValue(ClassSat1.SR19C10); ScoreWorkBook.Worksheets[1].Cells[19, 11].PutValue(ClassSat1.SR19C11); ScoreWorkBook.Worksheets[1].Cells[19, 12].PutValue(ClassSat1.SR19C12); ScoreWorkBook.Worksheets[1].Cells[19, 13].PutValue(ClassSat1.SR19C13); ScoreWorkBook.Worksheets[1].Cells[19, 14].PutValue(ClassSat1.SR19C14); ScoreWorkBook.Worksheets[1].Cells[20, 5].PutValue(ClassSat1.SR20C5); ScoreWorkBook.Worksheets[1].Cells[20, 6].PutValue(ClassSat1.SR20C6); ScoreWorkBook.Worksheets[1].Cells[20, 7].PutValue(ClassSat1.SR20C7); ScoreWorkBook.Worksheets[1].Cells[20, 8].PutValue(ClassSat1.SR20C8); ScoreWorkBook.Worksheets[1].Cells[20, 9].PutValue(ClassSat1.SR20C9); ScoreWorkBook.Worksheets[1].Cells[20, 10].PutValue(ClassSat1.SR20C10); ScoreWorkBook.Worksheets[1].Cells[20, 11].PutValue(ClassSat1.SR20C11); ScoreWorkBook.Worksheets[1].Cells[20, 12].PutValue(ClassSat1.SR20C12); ScoreWorkBook.Worksheets[1].Cells[20, 13].PutValue(ClassSat1.SR20C13); ScoreWorkBook.Worksheets[1].Cells[20, 14].PutValue(ClassSat1.SR20C14); //四年級學生數總計 ScoreWorkBook.Worksheets[1].Cells[21, 5].PutValue(ClassSat1.SR21C5); ScoreWorkBook.Worksheets[1].Cells[21, 6].PutValue(ClassSat1.SR21C6); ScoreWorkBook.Worksheets[1].Cells[21, 7].PutValue(ClassSat1.SR21C7); ScoreWorkBook.Worksheets[1].Cells[21, 8].PutValue(ClassSat1.SR21C8); ScoreWorkBook.Worksheets[1].Cells[21, 9].PutValue(ClassSat1.SR21C9); ScoreWorkBook.Worksheets[1].Cells[21, 10].PutValue(ClassSat1.SR21C10); ScoreWorkBook.Worksheets[1].Cells[21, 11].PutValue(ClassSat1.SR21C11); ScoreWorkBook.Worksheets[1].Cells[21, 12].PutValue(ClassSat1.SR21C12); ScoreWorkBook.Worksheets[1].Cells[21, 13].PutValue(ClassSat1.SR21C13); ScoreWorkBook.Worksheets[1].Cells[21, 14].PutValue(ClassSat1.SR21C14); ScoreWorkBook.Worksheets[1].Cells[22, 5].PutValue(ClassSat1.SR22C5); ScoreWorkBook.Worksheets[1].Cells[22, 6].PutValue(ClassSat1.SR22C6); ScoreWorkBook.Worksheets[1].Cells[22, 7].PutValue(ClassSat1.SR22C7); ScoreWorkBook.Worksheets[1].Cells[22, 8].PutValue(ClassSat1.SR22C8); ScoreWorkBook.Worksheets[1].Cells[22, 9].PutValue(ClassSat1.SR22C9); ScoreWorkBook.Worksheets[1].Cells[22, 10].PutValue(ClassSat1.SR22C10); ScoreWorkBook.Worksheets[1].Cells[22, 11].PutValue(ClassSat1.SR22C11); ScoreWorkBook.Worksheets[1].Cells[22, 12].PutValue(ClassSat1.SR22C12); ScoreWorkBook.Worksheets[1].Cells[22, 13].PutValue(ClassSat1.SR22C13); ScoreWorkBook.Worksheets[1].Cells[22, 14].PutValue(ClassSat1.SR22C14); //延修生學生數總計 ScoreWorkBook.Worksheets[1].Cells[23, 5].PutValue(ClassSat1.SR23C5); ScoreWorkBook.Worksheets[1].Cells[23, 6].PutValue(ClassSat1.SR23C6); ScoreWorkBook.Worksheets[1].Cells[23, 7].PutValue(ClassSat1.SR23C7); ScoreWorkBook.Worksheets[1].Cells[23, 8].PutValue(ClassSat1.SR23C8); ScoreWorkBook.Worksheets[1].Cells[23, 9].PutValue(ClassSat1.SR23C9); ScoreWorkBook.Worksheets[1].Cells[23, 10].PutValue(ClassSat1.SR23C10); ScoreWorkBook.Worksheets[1].Cells[23, 11].PutValue(ClassSat1.SR23C11); ScoreWorkBook.Worksheets[1].Cells[23, 12].PutValue(ClassSat1.SR23C12); ScoreWorkBook.Worksheets[1].Cells[23, 13].PutValue(ClassSat1.SR23C13); ScoreWorkBook.Worksheets[1].Cells[23, 14].PutValue(ClassSat1.SR23C14); ScoreWorkBook.Worksheets[1].Cells[24, 5].PutValue(ClassSat1.SR24C5); ScoreWorkBook.Worksheets[1].Cells[24, 6].PutValue(ClassSat1.SR24C6); ScoreWorkBook.Worksheets[1].Cells[24, 7].PutValue(ClassSat1.SR24C7); ScoreWorkBook.Worksheets[1].Cells[24, 8].PutValue(ClassSat1.SR24C8); ScoreWorkBook.Worksheets[1].Cells[24, 9].PutValue(ClassSat1.SR24C9); ScoreWorkBook.Worksheets[1].Cells[24, 10].PutValue(ClassSat1.SR24C10); ScoreWorkBook.Worksheets[1].Cells[24, 11].PutValue(ClassSat1.SR24C11); ScoreWorkBook.Worksheets[1].Cells[24, 12].PutValue(ClassSat1.SR24C12); ScoreWorkBook.Worksheets[1].Cells[24, 13].PutValue(ClassSat1.SR24C13); ScoreWorkBook.Worksheets[1].Cells[24, 14].PutValue(ClassSat1.SR24C14); #endregion #region 綜合高中 ClassStatistics ClassSat2 = new ClassStatistics(); ClassSat2.StartStatistics(2); ScoreWorkBook.Worksheets[2].Name = "高中職學校班級及學生概況-綜合高中"; ScoreWorkBook.Worksheets[2].Cells[4, 0].PutValue(SmartSchool.Customization.Data.SystemInformation.SchoolCode); //一年級班級數總計 ScoreWorkBook.Worksheets[2].Cells[7, 5].PutValue(ClassSat2.Level1Count24); ScoreWorkBook.Worksheets[2].Cells[7, 7].PutValue(ClassSat2.Level1Count34); ScoreWorkBook.Worksheets[2].Cells[7, 9].PutValue(ClassSat2.Level1Count44); ScoreWorkBook.Worksheets[2].Cells[7, 11].PutValue(ClassSat2.Level1Count54); ScoreWorkBook.Worksheets[2].Cells[7, 13].PutValue(ClassSat2.Level1Count55); //二年級班級數總計 ScoreWorkBook.Worksheets[2].Cells[8, 5].PutValue(ClassSat2.Level2Count24); ScoreWorkBook.Worksheets[2].Cells[8, 7].PutValue(ClassSat2.Level2Count34); ScoreWorkBook.Worksheets[2].Cells[8, 9].PutValue(ClassSat2.Level2Count44); ScoreWorkBook.Worksheets[2].Cells[8, 11].PutValue(ClassSat2.Level2Count54); ScoreWorkBook.Worksheets[2].Cells[8, 13].PutValue(ClassSat2.Level2Count55); //三年級班級數總計 ScoreWorkBook.Worksheets[2].Cells[9, 5].PutValue(ClassSat2.Level3Count24); ScoreWorkBook.Worksheets[2].Cells[9, 7].PutValue(ClassSat2.Level3Count34); ScoreWorkBook.Worksheets[2].Cells[9, 9].PutValue(ClassSat2.Level3Count44); ScoreWorkBook.Worksheets[2].Cells[9, 11].PutValue(ClassSat2.Level3Count54); ScoreWorkBook.Worksheets[2].Cells[9, 13].PutValue(ClassSat2.Level3Count55); //四年級班級數總計 ScoreWorkBook.Worksheets[2].Cells[10, 5].PutValue(ClassSat2.Level4Count24); ScoreWorkBook.Worksheets[2].Cells[10, 7].PutValue(ClassSat2.Level4Count34); ScoreWorkBook.Worksheets[2].Cells[10, 9].PutValue(ClassSat2.Level4Count44); ScoreWorkBook.Worksheets[2].Cells[10, 11].PutValue(ClassSat2.Level4Count54); ScoreWorkBook.Worksheets[2].Cells[10, 13].PutValue(ClassSat2.Level4Count55); //一年級學生數總計 ScoreWorkBook.Worksheets[2].Cells[15, 5].PutValue(ClassSat2.SR15C5); ScoreWorkBook.Worksheets[2].Cells[15, 6].PutValue(ClassSat2.SR15C6); ScoreWorkBook.Worksheets[2].Cells[15, 7].PutValue(ClassSat2.SR15C7); ScoreWorkBook.Worksheets[2].Cells[15, 8].PutValue(ClassSat2.SR15C8); ScoreWorkBook.Worksheets[2].Cells[15, 9].PutValue(ClassSat2.SR15C9); ScoreWorkBook.Worksheets[2].Cells[15, 10].PutValue(ClassSat2.SR15C10); ScoreWorkBook.Worksheets[2].Cells[15, 11].PutValue(ClassSat2.SR15C11); ScoreWorkBook.Worksheets[2].Cells[15, 12].PutValue(ClassSat2.SR15C12); ScoreWorkBook.Worksheets[2].Cells[15, 13].PutValue(ClassSat2.SR15C13); ScoreWorkBook.Worksheets[2].Cells[15, 14].PutValue(ClassSat2.SR15C14); ScoreWorkBook.Worksheets[2].Cells[16, 5].PutValue(ClassSat2.SR16C5); ScoreWorkBook.Worksheets[2].Cells[16, 6].PutValue(ClassSat2.SR16C6); ScoreWorkBook.Worksheets[2].Cells[16, 7].PutValue(ClassSat2.SR16C7); ScoreWorkBook.Worksheets[2].Cells[16, 8].PutValue(ClassSat2.SR16C8); ScoreWorkBook.Worksheets[2].Cells[16, 9].PutValue(ClassSat2.SR16C9); ScoreWorkBook.Worksheets[2].Cells[16, 10].PutValue(ClassSat2.SR16C10); ScoreWorkBook.Worksheets[2].Cells[16, 11].PutValue(ClassSat2.SR16C11); ScoreWorkBook.Worksheets[2].Cells[16, 12].PutValue(ClassSat2.SR16C12); ScoreWorkBook.Worksheets[2].Cells[16, 13].PutValue(ClassSat2.SR16C13); ScoreWorkBook.Worksheets[2].Cells[16, 14].PutValue(ClassSat2.SR16C14); //二年級學生數總計 ScoreWorkBook.Worksheets[2].Cells[17, 5].PutValue(ClassSat2.SR17C5); ScoreWorkBook.Worksheets[2].Cells[17, 6].PutValue(ClassSat2.SR17C6); ScoreWorkBook.Worksheets[2].Cells[17, 7].PutValue(ClassSat2.SR17C7); ScoreWorkBook.Worksheets[2].Cells[17, 8].PutValue(ClassSat2.SR17C8); ScoreWorkBook.Worksheets[2].Cells[17, 9].PutValue(ClassSat2.SR17C9); ScoreWorkBook.Worksheets[2].Cells[17, 10].PutValue(ClassSat2.SR17C10); ScoreWorkBook.Worksheets[2].Cells[17, 11].PutValue(ClassSat2.SR17C11); ScoreWorkBook.Worksheets[2].Cells[17, 12].PutValue(ClassSat2.SR17C12); ScoreWorkBook.Worksheets[2].Cells[17, 13].PutValue(ClassSat2.SR17C13); ScoreWorkBook.Worksheets[2].Cells[17, 14].PutValue(ClassSat2.SR17C14); ScoreWorkBook.Worksheets[2].Cells[18, 5].PutValue(ClassSat2.SR18C5); ScoreWorkBook.Worksheets[2].Cells[18, 6].PutValue(ClassSat2.SR18C6); ScoreWorkBook.Worksheets[2].Cells[18, 7].PutValue(ClassSat2.SR18C7); ScoreWorkBook.Worksheets[2].Cells[18, 8].PutValue(ClassSat2.SR18C8); ScoreWorkBook.Worksheets[2].Cells[18, 9].PutValue(ClassSat2.SR18C9); ScoreWorkBook.Worksheets[2].Cells[18, 10].PutValue(ClassSat2.SR18C10); ScoreWorkBook.Worksheets[2].Cells[18, 11].PutValue(ClassSat2.SR18C11); ScoreWorkBook.Worksheets[2].Cells[18, 12].PutValue(ClassSat2.SR18C12); ScoreWorkBook.Worksheets[2].Cells[18, 13].PutValue(ClassSat2.SR18C13); ScoreWorkBook.Worksheets[2].Cells[18, 14].PutValue(ClassSat2.SR18C14); //三年級學生數總計 ScoreWorkBook.Worksheets[2].Cells[19, 5].PutValue(ClassSat2.SR19C5); ScoreWorkBook.Worksheets[2].Cells[19, 6].PutValue(ClassSat2.SR19C6); ScoreWorkBook.Worksheets[2].Cells[19, 7].PutValue(ClassSat2.SR19C7); ScoreWorkBook.Worksheets[2].Cells[19, 8].PutValue(ClassSat2.SR19C8); ScoreWorkBook.Worksheets[2].Cells[19, 9].PutValue(ClassSat2.SR19C9); ScoreWorkBook.Worksheets[2].Cells[19, 10].PutValue(ClassSat2.SR19C10); ScoreWorkBook.Worksheets[2].Cells[19, 11].PutValue(ClassSat2.SR19C11); ScoreWorkBook.Worksheets[2].Cells[19, 12].PutValue(ClassSat2.SR19C12); ScoreWorkBook.Worksheets[2].Cells[19, 13].PutValue(ClassSat2.SR19C13); ScoreWorkBook.Worksheets[2].Cells[19, 14].PutValue(ClassSat2.SR19C14); ScoreWorkBook.Worksheets[2].Cells[20, 5].PutValue(ClassSat2.SR20C5); ScoreWorkBook.Worksheets[2].Cells[20, 6].PutValue(ClassSat2.SR20C6); ScoreWorkBook.Worksheets[2].Cells[20, 7].PutValue(ClassSat2.SR20C7); ScoreWorkBook.Worksheets[2].Cells[20, 8].PutValue(ClassSat2.SR20C8); ScoreWorkBook.Worksheets[2].Cells[20, 9].PutValue(ClassSat2.SR20C9); ScoreWorkBook.Worksheets[2].Cells[20, 10].PutValue(ClassSat2.SR20C10); ScoreWorkBook.Worksheets[2].Cells[20, 11].PutValue(ClassSat2.SR20C11); ScoreWorkBook.Worksheets[2].Cells[20, 12].PutValue(ClassSat2.SR20C12); ScoreWorkBook.Worksheets[2].Cells[20, 13].PutValue(ClassSat2.SR20C13); ScoreWorkBook.Worksheets[2].Cells[20, 14].PutValue(ClassSat2.SR20C14); //四年級學生數總計 ScoreWorkBook.Worksheets[2].Cells[21, 5].PutValue(ClassSat2.SR21C5); ScoreWorkBook.Worksheets[2].Cells[21, 6].PutValue(ClassSat2.SR21C6); ScoreWorkBook.Worksheets[2].Cells[21, 7].PutValue(ClassSat2.SR21C7); ScoreWorkBook.Worksheets[2].Cells[21, 8].PutValue(ClassSat2.SR21C8); ScoreWorkBook.Worksheets[2].Cells[21, 9].PutValue(ClassSat2.SR21C9); ScoreWorkBook.Worksheets[2].Cells[21, 10].PutValue(ClassSat2.SR21C10); ScoreWorkBook.Worksheets[2].Cells[21, 11].PutValue(ClassSat2.SR21C11); ScoreWorkBook.Worksheets[2].Cells[21, 12].PutValue(ClassSat2.SR21C12); ScoreWorkBook.Worksheets[2].Cells[21, 13].PutValue(ClassSat2.SR21C13); ScoreWorkBook.Worksheets[2].Cells[21, 14].PutValue(ClassSat2.SR21C14); ScoreWorkBook.Worksheets[2].Cells[22, 5].PutValue(ClassSat2.SR22C5); ScoreWorkBook.Worksheets[2].Cells[22, 6].PutValue(ClassSat2.SR22C6); ScoreWorkBook.Worksheets[2].Cells[22, 7].PutValue(ClassSat2.SR22C7); ScoreWorkBook.Worksheets[2].Cells[22, 8].PutValue(ClassSat2.SR22C8); ScoreWorkBook.Worksheets[2].Cells[22, 9].PutValue(ClassSat2.SR22C9); ScoreWorkBook.Worksheets[2].Cells[22, 10].PutValue(ClassSat2.SR22C10); ScoreWorkBook.Worksheets[2].Cells[22, 11].PutValue(ClassSat2.SR22C11); ScoreWorkBook.Worksheets[2].Cells[22, 12].PutValue(ClassSat2.SR22C12); ScoreWorkBook.Worksheets[2].Cells[22, 13].PutValue(ClassSat2.SR22C13); ScoreWorkBook.Worksheets[2].Cells[22, 14].PutValue(ClassSat2.SR22C14); //延修生學生數總計 ScoreWorkBook.Worksheets[2].Cells[23, 5].PutValue(ClassSat2.SR23C5); ScoreWorkBook.Worksheets[2].Cells[23, 6].PutValue(ClassSat2.SR23C6); ScoreWorkBook.Worksheets[2].Cells[23, 7].PutValue(ClassSat2.SR23C7); ScoreWorkBook.Worksheets[2].Cells[23, 8].PutValue(ClassSat2.SR23C8); ScoreWorkBook.Worksheets[2].Cells[23, 9].PutValue(ClassSat2.SR23C9); ScoreWorkBook.Worksheets[2].Cells[23, 10].PutValue(ClassSat2.SR23C10); ScoreWorkBook.Worksheets[2].Cells[23, 11].PutValue(ClassSat2.SR23C11); ScoreWorkBook.Worksheets[2].Cells[23, 12].PutValue(ClassSat2.SR23C12); ScoreWorkBook.Worksheets[2].Cells[23, 13].PutValue(ClassSat2.SR23C13); ScoreWorkBook.Worksheets[2].Cells[23, 14].PutValue(ClassSat2.SR23C14); ScoreWorkBook.Worksheets[2].Cells[24, 5].PutValue(ClassSat2.SR24C5); ScoreWorkBook.Worksheets[2].Cells[24, 6].PutValue(ClassSat2.SR24C6); ScoreWorkBook.Worksheets[2].Cells[24, 7].PutValue(ClassSat2.SR24C7); ScoreWorkBook.Worksheets[2].Cells[24, 8].PutValue(ClassSat2.SR24C8); ScoreWorkBook.Worksheets[2].Cells[24, 9].PutValue(ClassSat2.SR24C9); ScoreWorkBook.Worksheets[2].Cells[24, 10].PutValue(ClassSat2.SR24C10); ScoreWorkBook.Worksheets[2].Cells[24, 11].PutValue(ClassSat2.SR24C11); ScoreWorkBook.Worksheets[2].Cells[24, 12].PutValue(ClassSat2.SR24C12); ScoreWorkBook.Worksheets[2].Cells[24, 13].PutValue(ClassSat2.SR24C13); ScoreWorkBook.Worksheets[2].Cells[24, 14].PutValue(ClassSat2.SR24C14); #endregion try { if (Program.ErrorList.Count > 0) { int rowIdx = 1; foreach (string str in Program.ErrorList) { ScoreWorkBook.Worksheets[3].Cells[rowIdx, 0].PutValue(str); rowIdx++; } System.Windows.Forms.MessageBox.Show("產生過程有發生問題,請到工作表Error檢視。"); } if (Program.ErrorList.Count == 0) { ScoreWorkBook.Worksheets.RemoveAt(3); } ScoreWorkBook.Save(filename); System.Diagnostics.Process.Start(filename); } catch { System.Windows.Forms.MessageBox.Show("指定路徑無法存取。", "建立檔案失敗", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error); } }; //權限設定 Catalog permission = RoleAclSource.Instance["教務作業"]["功能按鈕"]; permission.Add(new RibbonFeature("SH.School.SchoolStatistics", "高中職學校班級及學生概況")); }
public string ImportData() { try { if (OperatorProvider.Provider.Current().IsSystem) { return("超级管理员无此操作权限"); } var currUser = OperatorProvider.Provider.Current(); string orgId = OperatorProvider.Provider.Current().OrganizeId;//所属公司 int error = 0; string message = "请选择格式正确的文件再导入!"; string falseMessage = ""; int count = HttpContext.Request.Files.Count; if (count > 0) { HttpPostedFileBase file = HttpContext.Request.Files[0]; if (string.IsNullOrEmpty(file.FileName)) { return(message); } if (!(file.FileName.Substring(file.FileName.IndexOf('.')).Contains("xls") || file.FileName.Substring(file.FileName.IndexOf('.')).Contains("xlsx"))) { return(message); } string fileName = DateTime.Now.ToString("yyyyMMddHHmmss") + System.IO.Path.GetExtension(file.FileName); file.SaveAs(Server.MapPath("~/Resource/temp/" + fileName)); Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook(); wb.Open(Server.MapPath("~/Resource/temp/" + fileName)); Aspose.Cells.Cells cells = wb.Worksheets[0].Cells; DataTable dt = new DataTable(); if (cells.MaxDataRow > 1) { dt = cells.ExportDataTable(1, 0, cells.MaxDataRow, cells.MaxColumn + 1, true); #region 作业活动类 for (int i = 0; i < dt.Rows.Count; i++) { //用于数据验证填报部门 string deptlist = dt.Rows[i]["填报单位"].ToString().Trim(); string controlDept = currUser.DeptName; //管控部门 string controlDeptId = currUser.DeptId; //管控部门 string controlDeptCode = currUser.DeptCode; //管控部门 //岗位(工种) string Post = dt.Rows[i]["岗位(工种)"].ToString().Trim(); string PostId = string.Empty; //作业活动 string Name = dt.Rows[i]["作业活动"].ToString().Trim(); //活动步骤 string ActivityStep = dt.Rows[i]["活动步骤"].ToString().Trim(); //常规/非常规 string IsConventional = dt.Rows[i]["常规/非常规"].ToString().Trim(); //其他 string Others = dt.Rows[i]["其他"].ToString().Trim(); //---****值存在空验证*****-- if (string.IsNullOrEmpty(Name) || string.IsNullOrEmpty(ActivityStep) || string.IsNullOrEmpty(IsConventional) || string.IsNullOrWhiteSpace(Post)) { falseMessage += "</br>" + "第" + (i + 2) + "行值存在空,未能导入."; error++; continue; } var p1 = string.Empty; var p2 = string.Empty; bool isSkip = false; //验证所填部门是否存在 if (!string.IsNullOrWhiteSpace(deptlist)) { var array = deptlist.Split('/'); for (int j = 0; j < array.Length; j++) { if (j == 0) { var entity = departmentBLL.GetList().Where(x => x.OrganizeId == currUser.OrganizeId && x.Nature == "厂级" && x.FullName == array[j].ToString()).FirstOrDefault(); if (entity == null) { entity = departmentBLL.GetList().Where(x => x.OrganizeId == currUser.OrganizeId && x.Nature == "部门" && x.FullName == array[j].ToString()).FirstOrDefault(); if (entity == null) { entity = departmentBLL.GetList().Where(x => x.OrganizeId == currUser.OrganizeId && x.Nature == "承包商" && x.FullName == array[j].ToString()).FirstOrDefault(); if (entity == null) { falseMessage += "</br>" + "第" + (i + 2) + "行部门信息不存在,未能导入."; error++; isSkip = true; break; } else { controlDept = entity.FullName; controlDeptId = entity.DepartmentId; controlDeptCode = entity.EnCode; p1 = entity.DepartmentId; } } else { controlDept = entity.FullName; controlDeptId = entity.DepartmentId; controlDeptCode = entity.EnCode; p1 = entity.DepartmentId; } } else { controlDept = entity.FullName; controlDeptId = entity.DepartmentId; controlDeptCode = entity.EnCode; p1 = entity.DepartmentId; } } else if (j == 1) { var entity1 = departmentBLL.GetList().Where(x => x.OrganizeId == currUser.OrganizeId && (x.Nature == "专业" || x.Nature == "承包商" || x.Nature == "分包商") && x.FullName == array[j].ToString() && x.ParentId == p1).FirstOrDefault(); if (entity1 == null) { entity1 = departmentBLL.GetList().Where(x => x.OrganizeId == currUser.OrganizeId && x.Nature == "班组" && x.FullName == array[j].ToString() && x.ParentId == p1).FirstOrDefault(); if (entity1 == null) { falseMessage += "</br>" + "第" + (i + 2) + "行部门信息不存在,未能导入."; error++; isSkip = true; break; } else { controlDept = entity1.FullName; controlDeptId = entity1.DepartmentId; controlDeptCode = entity1.EnCode; p2 = entity1.DepartmentId; } } else { controlDept = entity1.FullName; controlDeptId = entity1.DepartmentId; controlDeptCode = entity1.EnCode; p2 = entity1.DepartmentId; } } else { var entity1 = departmentBLL.GetList().Where(x => x.OrganizeId == currUser.OrganizeId && (x.Nature == "班组" || x.Nature == "承包商" || x.Nature == "分包商") && x.FullName == array[j].ToString() && x.ParentId == p2).FirstOrDefault(); if (entity1 == null) { falseMessage += "</br>" + "第" + (i + 2) + "行部门信息不存在,未能导入."; error++; isSkip = true; break; } else { controlDept = entity1.FullName; controlDeptId = entity1.DepartmentId; controlDeptCode = entity1.EnCode; } } } } if (isSkip) { continue; } //验证岗位是不是在部门里面 var controldept = departmentBLL.GetEntity(controlDeptId); RoleEntity re = new RoleEntity(); if (controldept.Nature == "厂级") { re = postBLL.GetList().Where(a => (a.FullName == Post && a.OrganizeId == orgId)).FirstOrDefault(); } else { re = postBLL.GetList().Where(a => (a.FullName == Post && a.OrganizeId == orgId && a.DeptId == controlDeptId)).FirstOrDefault(); } if (re == null) { falseMessage += "</br>" + "第" + (i + 2) + "行岗位有误,未能导入."; error++; continue; } else { PostId = re.RoleId; } //---****判断系统是否已经存在该作业活动、活动步骤的数据*****-- Expression <Func <BaseListingEntity, bool> > condition = t => t.Name == Name && t.ActivityStep == ActivityStep && t.Type == 0 && t.PostId == PostId; if (baselistingbll.GetList(condition).Count() > 0) { falseMessage += "</br>" + "第" + (i + 2) + "行数据已经存在于系统中,无需添加."; error++; continue; } BaseListingEntity Listingentity = new BaseListingEntity(); Listingentity.Name = Name; Listingentity.ActivityStep = ActivityStep; Listingentity.IsConventional = IsConventional == "常规" ? 0 : 1; Listingentity.Others = Others; Listingentity.CreateUserDeptCode = string.IsNullOrWhiteSpace(deptlist) ? currUser.DeptCode : controlDeptCode; Listingentity.ControlsDept = controlDept; Listingentity.ControlsDeptId = controlDeptId; Listingentity.ControlsDeptCode = controlDeptCode; Listingentity.Type = 0; Listingentity.Post = Post; Listingentity.PostId = PostId; condition = t => t.Name == Name && !(t.AreaName == null || t.AreaName.Trim() == ""); var defualt = baselistingbll.GetList(condition).ToList().FirstOrDefault(); Listingentity.AreaName = defualt == null ? "" : defualt.AreaName; Listingentity.AreaId = defualt == null ? "" : defualt.AreaId; Listingentity.AreaCode = defualt == null ? "" : defualt.AreaCode; Listingentity.CreateDate = DateTime.Now.AddSeconds(i); baselistingbll.SaveForm("", Listingentity); } count = dt.Rows.Count; message = "作业活动类共有" + count + "条记录,成功导入" + (count - error) + "条,失败" + error + "条"; message += "</br>" + falseMessage + "</br>"; #endregion } else { message = "作业活动类没有数据。</br>"; } error = 0; falseMessage = ""; cells = wb.Worksheets[1].Cells; if (cells.MaxDataRow > 1) { #region 设备设施类 dt = cells.ExportDataTable(1, 0, cells.MaxDataRow, cells.MaxColumn + 1, true); for (int i = 0; i < dt.Rows.Count; i++) { //用于数据验证填报部门 string deptlist = dt.Rows[i]["填报单位"].ToString().Trim(); string controlDept = currUser.DeptName; //管控部门 string controlDeptId = currUser.DeptId; //管控部门 string controlDeptCode = currUser.DeptCode; //管控部门 //设备名称 string Name = dt.Rows[i]["设备名称"].ToString().Trim(); //所在地点 string arealist = dt.Rows[i]["所在地点"].ToString().Trim(); //是否特种设备 string IsSpecialEqu = dt.Rows[i]["是否特种设备"].ToString().Trim(); //其他 string Others = dt.Rows[i]["其他"].ToString().Trim(); string AreaName = string.Empty; //所在地点名称 string AreaId = string.Empty; //所在地点Id string AreaCode = string.Empty; //所在地点Code //---****值存在空验证*****-- if (string.IsNullOrEmpty(Name) || string.IsNullOrEmpty(arealist) || string.IsNullOrEmpty(IsSpecialEqu)) { falseMessage += "</br>" + "第" + (i + 2) + "行值存在空,未能导入."; error++; continue; } var p1 = string.Empty; var p2 = string.Empty; bool isSkip = false; //验证所填部门是否存在 if (!string.IsNullOrWhiteSpace(deptlist)) { var array = deptlist.Split('/'); for (int j = 0; j < array.Length; j++) { if (j == 0) { var entity = departmentBLL.GetList().Where(x => x.OrganizeId == currUser.OrganizeId && x.Nature == "厂级" && x.FullName == array[j].ToString()).FirstOrDefault(); if (entity == null) { entity = departmentBLL.GetList().Where(x => x.OrganizeId == currUser.OrganizeId && x.Nature == "部门" && x.FullName == array[j].ToString()).FirstOrDefault(); if (entity == null) { entity = departmentBLL.GetList().Where(x => x.OrganizeId == currUser.OrganizeId && x.Nature == "承包商" && x.FullName == array[j].ToString()).FirstOrDefault(); if (entity == null) { falseMessage += "</br>" + "第" + (i + 2) + "行部门信息不存在,未能导入."; error++; isSkip = true; break; } else { controlDept = entity.FullName; controlDeptId = entity.DepartmentId; controlDeptCode = entity.EnCode; p1 = entity.DepartmentId; } } else { controlDept = entity.FullName; controlDeptId = entity.DepartmentId; controlDeptCode = entity.EnCode; p1 = entity.DepartmentId; } } else { controlDept = entity.FullName; controlDeptId = entity.DepartmentId; controlDeptCode = entity.EnCode; p1 = entity.DepartmentId; } } else if (j == 1) { var entity1 = departmentBLL.GetList().Where(x => x.OrganizeId == currUser.OrganizeId && (x.Nature == "专业" || x.Nature == "承包商" || x.Nature == "分包商") && x.FullName == array[j].ToString() && x.ParentId == p1).FirstOrDefault(); if (entity1 == null) { entity1 = departmentBLL.GetList().Where(x => x.OrganizeId == currUser.OrganizeId && x.Nature == "班组" && x.FullName == array[j].ToString() && x.ParentId == p1).FirstOrDefault(); if (entity1 == null) { falseMessage += "</br>" + "第" + (i + 2) + "行部门信息不存在,未能导入."; error++; isSkip = true; break; } else { controlDept = entity1.FullName; controlDeptId = entity1.DepartmentId; controlDeptCode = entity1.EnCode; p2 = entity1.DepartmentId; } } else { controlDept = entity1.FullName; controlDeptId = entity1.DepartmentId; controlDeptCode = entity1.EnCode; p2 = entity1.DepartmentId; } } else { var entity1 = departmentBLL.GetList().Where(x => x.OrganizeId == currUser.OrganizeId && (x.Nature == "班组" || x.Nature == "承包商" || x.Nature == "分包商") && x.FullName == array[j].ToString() && x.ParentId == p2).FirstOrDefault(); if (entity1 == null) { falseMessage += "</br>" + "第" + (i + 2) + "行部门信息不存在,未能导入."; error++; isSkip = true; break; } else { controlDept = entity1.FullName; controlDeptId = entity1.DepartmentId; controlDeptCode = entity1.EnCode; } } } } if (isSkip) { continue; } //验证所在地点(区域) var disItem = new DistrictBLL().GetListForCon(x => x.DistrictName == arealist && x.OrganizeId == currUser.OrganizeId).FirstOrDefault(); if (disItem != null) { AreaId = disItem.DistrictID; AreaCode = disItem.DistrictCode; AreaName = disItem.DistrictName; } else { falseMessage += "</br>" + "第" + (i + 2) + "行所在地点信息与系统内置的区域不一致,未能导入."; error++; continue; } //---****判断系统是否已经存在该设备名称、所在地点的数据*****-- Expression <Func <BaseListingEntity, bool> > condition = t => t.Name == Name && t.AreaId == AreaId && t.Type == 1; if (baselistingbll.GetList(condition).Count() > 0) { falseMessage += "</br>" + "第" + (i + 2) + "行数据已经存在于系统中,无需添加."; error++; continue; } BaseListingEntity Listingentity = new BaseListingEntity(); Listingentity.Name = Name; Listingentity.AreaCode = AreaCode; Listingentity.AreaId = AreaId; Listingentity.AreaName = AreaName; Listingentity.IsSpecialEqu = IsSpecialEqu == "是" ? 0 : 1; Listingentity.Others = Others; Listingentity.CreateUserDeptCode = string.IsNullOrWhiteSpace(deptlist) ? currUser.DeptCode : controlDeptCode; Listingentity.ControlsDept = controlDept; Listingentity.ControlsDeptId = controlDeptId; Listingentity.ControlsDeptCode = controlDeptCode; Listingentity.Type = 1; Listingentity.CreateDate = DateTime.Now.AddSeconds(i); baselistingbll.SaveForm("", Listingentity); } count = dt.Rows.Count; message += "设备设施类共有" + count + "条记录,成功导入" + (count - error) + "条,失败" + error + "条"; message += "</br>" + falseMessage; #endregion } else { message += "设备设施类没有数据。</br>"; } } return(message); } catch (Exception ex) { return(ex.ToString()); } }
/// <summary> /// 预知风险训练库导出详情 /// </summary> /// <param name="keyValue"></param> /// <returns></returns> public ActionResult ExportData(string queryJson, string fileName) { Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook(); //string path = "~/Resource/Temp"; string fName = "作业安全分析库" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls"; wb.Open(Server.MapPath("~/Resource/ExcelTemplate/危险预知训练数据库导入模版.xlsx")); var queryParam = queryJson.ToJObject(); //var riskType = queryParam["riskType"].ToString(); Operator user = ERCHTMS.Code.OperatorProvider.Provider.Current(); Pagination pagination = new Pagination(); pagination.page = 1; pagination.rows = 10000000; pagination.sidx = "t.createdate,t.id"; pagination.sord = "desc"; pagination.p_tablename = "bis_risktrainlib t left join bis_risktrainlibdetail d on d.workid = t.id"; pagination.p_kid = "t.id"; pagination.p_fields = @"t.worktask,t.risklevel,t.worktype,t.workpost,t.workdes, t.resources,t.workarea,d.process,d.atrisk,d.controls"; pagination.conditionJson = "1=1"; if (!user.IsSystem) { //根据当前用户对模块的权限获取记录 string where = new AuthorizeBLL().GetModuleDataAuthority(ERCHTMS.Code.OperatorProvider.Provider.Current(), HttpContext.Request.Cookies["currentmoduleId"].Value, "t.createuserdeptcode", "t.createuserorgcode"); if (!string.IsNullOrEmpty(where)) { pagination.conditionJson += " and " + where; } } var data = risktrainlibbll.GetPageListJson(pagination, queryJson); var cells = wb.Worksheets[0].Cells; int Colnum = data.Columns.Count; int Rownum = data.Rows.Count; for (int i = 0; i < Rownum; i++) { for (int k = 0; k < Colnum - 1; k++) { if (k == 0) { cells[2 + i, k].PutValue(i + 1); } else { cells[2 + i, k].PutValue(data.Rows[i][k].ToString()); } } } int q = 0; int RowOrder = 0; int m = 1; for (int i = 0; i < data.Rows.Count; i = q) { RowOrder = data.Select(string.Format("id='{0}'", data.Rows[i]["id"].ToString())).ToList().Count; cells.Merge(2 + q, 0, RowOrder, 1); cells.Merge(2 + q, 1, RowOrder, 1); cells.Merge(2 + q, 2, RowOrder, 1); cells.Merge(2 + q, 3, RowOrder, 1); cells.Merge(2 + q, 4, RowOrder, 1); cells.Merge(2 + q, 5, RowOrder, 1); cells.Merge(2 + q, 6, RowOrder, 1); cells.Merge(2 + q, 7, RowOrder, 1); cells[2 + q, 0].PutValue(m); m++; q += RowOrder; } HttpResponse resp = System.Web.HttpContext.Current.Response; System.Threading.Thread.Sleep(400); wb.Save(Server.MapPath("~/Resource/Temp/" + fName)); return(Success("导出成功。", fName)); }
public string ImportTrainLib() { try { if (OperatorProvider.Provider.Current().IsSystem) { return("超级管理员无此操作权限"); } string orgId = OperatorProvider.Provider.Current().OrganizeId; int error = 0; string message = "请选择格式正确的文件再导入!"; string falseMessage = ""; int count = HttpContext.Request.Files.Count; if (count > 0) { HttpPostedFileBase file = HttpContext.Request.Files[0]; if (string.IsNullOrEmpty(file.FileName)) { return(message); } if (!(file.FileName.Substring(file.FileName.IndexOf('.')).Contains("xls") || file.FileName.Substring(file.FileName.IndexOf('.')).Contains("xlsx"))) { return(message); } string fileName = DateTime.Now.ToString("yyyyMMddHHmmss") + System.IO.Path.GetExtension(file.FileName); file.SaveAs(Server.MapPath("~/Resource/temp/" + fileName)); Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook(); wb.Open(Server.MapPath("~/Resource/temp/" + fileName), file.FileName.Substring(file.FileName.IndexOf('.')).Contains("xlsx")?Aspose.Cells.FileFormatType.Excel2007Xlsx:Aspose.Cells.FileFormatType.Excel2003); var sheet = wb.Worksheets[0]; if (sheet.Cells[1, 1].StringValue != "工作任务" || sheet.Cells[1, 2].StringValue != "风险等级" || sheet.Cells[1, 3].StringValue != "作业类型" || sheet.Cells[1, 4].StringValue != "作业岗位" || sheet.Cells[1, 5].StringValue != "任务描述" || sheet.Cells[1, 6].StringValue != "资源准备" || sheet.Cells[1, 7].StringValue != "作业区域" || sheet.Cells[1, 8].StringValue != "工序" || sheet.Cells[1, 9].StringValue != "潜在危险" || sheet.Cells[1, 10].StringValue != "防范措施") { return(message); } var RiskLibList = new List <RisktrainlibEntity>(); var RiskDetailList = new List <RisktrainlibdetailEntity>(); var date = DateTime.Now; for (int i = 2; i <= sheet.Cells.MaxDataRow; i++) { var entity = new RisktrainlibEntity(); entity.Create(); entity.WorkTask = sheet.Cells[i, 1].StringValue; entity.RiskLevel = sheet.Cells[i, 2].StringValue; switch (entity.RiskLevel) { case "重大风险": entity.RiskLevelVal = "1"; break; case "较大风险": entity.RiskLevelVal = "2"; break; case "一般风险": entity.RiskLevelVal = "3"; break; case "低风险": entity.RiskLevelVal = "4"; break; default: break; } entity.WorkType = sheet.Cells[i, 3].StringValue; entity.WorkPost = sheet.Cells[i, 4].StringValue; entity.WorkDes = sheet.Cells[i, 5].StringValue; entity.Resources = sheet.Cells[i, 6].StringValue; entity.WorkArea = sheet.Cells[i, 7].StringValue; entity.DataSources = "2"; if (string.IsNullOrEmpty(sheet.Cells[i, 1].StringValue)) { entity.ID = RiskLibList[i - 1 - 2].ID; } RiskLibList.Add(entity); } for (int i = 2; i <= sheet.Cells.MaxDataRow; i++) { var dentity = new RisktrainlibdetailEntity(); if (sheet.Cells[i, 8].StringValue.Length > 1000) { falseMessage += "</br>" + "第" + (i + 1) + "行工序字符长度超长,未能导入."; error++; continue; } if (sheet.Cells[i, 9].StringValue.Length > 1000) { falseMessage += "</br>" + "第" + (i + 1) + "行存在风险字符长度超长,未能导入."; error++; continue; } if (sheet.Cells[i, 10].StringValue.Length > 1000) { falseMessage += "</br>" + "第" + (i + 1) + "行管理措施字符长度超长,未能导入."; error++; continue; } dentity.Process = sheet.Cells[i, 8].StringValue; dentity.AtRisk = sheet.Cells[i, 9].StringValue; dentity.Controls = sheet.Cells[i, 10].StringValue; dentity.Create(); dentity.WorkId = RiskLibList[i - 2].ID; RiskDetailList.Add(dentity); } RiskLibList = RiskLibList.Where(x => x.WorkTask != "").ToList(); for (int i = 0; i < RiskLibList.Count; i++) { if (!string.IsNullOrWhiteSpace(RiskLibList[i].WorkType)) { var data = new DataItemDetailBLL().GetDataItemListByItemCode("'StatisticsType'"); string worktype = string.Empty; var list = RiskLibList[i].WorkType.Split(','); for (int k = 0; k < list.Length; k++) { var entity = data.Where(x => x.ItemName == list[k].Trim()).FirstOrDefault(); if (entity != null) { if (string.IsNullOrWhiteSpace(worktype)) { RiskLibList[i].WorkTypeCode += entity.ItemValue + ","; worktype += entity.ItemName + ","; } else { if (!worktype.Contains(entity.ItemName)) { RiskLibList[i].WorkTypeCode += entity.ItemValue + ","; worktype += entity.ItemName + ","; } } } //if (entity != null) //{ // RiskLibList[i].WorkTypeCode += entity.ItemValue + ","; //} //else //{ // RiskLibList[i].WorkType.Replace(list[k], ""); //} } if (!string.IsNullOrWhiteSpace(RiskLibList[i].WorkTypeCode)) { RiskLibList[i].WorkTypeCode = RiskLibList[i].WorkTypeCode.Substring(0, RiskLibList[i].WorkTypeCode.Length - 1); RiskLibList[i].WorkType = worktype.Substring(0, worktype.Length - 1); } } if (string.IsNullOrWhiteSpace(RiskLibList[i].WorkArea)) { //falseMessage += "</br>" + "第" + (i+1) + "行区域为空,未能导入."; //error++; //continue; } else { DistrictEntity disEntity = districtBLL.GetDistrict(orgId, RiskLibList[i].WorkArea); if (disEntity == null) { //电厂没有该区域则不赋值 RiskLibList[i].WorkArea = ""; } else { RiskLibList[i].WorkAreaId = disEntity.DistrictID; } } } risktrainlibbll.InsertImportData(RiskLibList, RiskDetailList); //risktrainlibbll.InsertRiskTrainLib(RiskLibList); //risktrainlibdetailbll.InsertRiskTrainDetailLib(RiskDetailList); count = RiskDetailList.Count; message = "共有" + count + "条记录,成功导入" + (count - error) + "条,失败" + error + "条"; message += "</br>" + falseMessage; //DataTable dt = cells.ExportDataTable(0, 0, cells.MaxDataRow, cells.MaxColumn + 1, true); } return(message); } catch (Exception ex) { throw new Exception(ex.Message); } //return null; }
/// <summary> /// 风险清单导出(重大风险) /// </summary> /// <param name="queryJson"></param> /// <param name="fileName"></param> /// <returns></returns> public ActionResult ExportExcel(string queryJson, string fileName) { Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook(); //string path = "~/Resource/Temp"; string fName = "安全风险清单" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls"; var IsGdxy = new DataItemDetailBLL().GetDataItemListByItemCode("'VManager'").ToList(); if (IsGdxy.Count > 0) { wb.Open(Server.MapPath("~/Resource/ExcelTemplate/风险措施库导出模板.xls")); } else { wb.Open(Server.MapPath("~/Resource/ExcelTemplate/风险措施库通用导出模板.xls")); } var queryParam = queryJson.ToJObject(); var riskType = queryParam["riskType"].ToString(); var IndexState = queryParam["IndexState"].ToString(); Operator user = ERCHTMS.Code.OperatorProvider.Provider.Current(); string authType = new AuthorizeBLL().GetOperAuthorzeType(user, HttpContext.Request.Cookies["currentmoduleId"].Value, "search"); if (string.IsNullOrWhiteSpace(riskType)) { DataSet ds = new DataSet(); List <string> typeList = new List <string>() { "作业", "设备", "区域", "管理", "岗位", "工器具及危化品" }; for (int i = 0; i < typeList.Count; i++) { DataTable exportTable = new DataTable(); exportTable = riskbll.GetPageExportList(queryJson, typeList[i], authType, IndexState); exportTable.TableName = i.ToString(); ds.Tables.Add(exportTable); for (int j = 0; j < wb.Worksheets.Count; j++) { if (wb.Worksheets[j].Name == typeList[i]) { wb.Worksheets[j].Cells.ImportDataTable(exportTable, false, 2, 0); } } } //AsposeExcelHelper.ExecuteResultX(ds, path, typeList, fName); } else { DataTable exportTable = riskbll.GetPageExportList(queryJson, riskType, authType, IndexState); for (int j = 0; j < wb.Worksheets.Count; j++) { if (wb.Worksheets[j].Name == riskType) { wb.Worksheets[j].Cells.ImportDataTable(exportTable, false, 2, 0); } } } HttpResponse resp = System.Web.HttpContext.Current.Response; System.Threading.Thread.Sleep(400); wb.Save(Server.MapPath("~/Resource/Temp/" + fName)); //wb.Save(Server.UrlEncode(fName), Aspose.Cells.FileFormatType.Excel2003, Aspose.Cells.SaveType.OpenInBrowser, resp); return(Success("导出成功。", fName)); }
public ActionResult ExportQD(string keyValue) { //获取会议基本信息 ConferenceEntity cr = conferencebll.GetEntity(keyValue); ////获取会议签到人员信息 //List<Object> data = conferenceuserbll.GetSignTable(keyValue); ////签到 //List<Object> dataQD = data[0] as List<Object>; ////未签到 //List<Object> dataWQD = data[1] as List<Object>; ////请假 //List<Object> dataQ = data[2] as List<Object>; string path = Request.PhysicalApplicationPath; int index = 0; string fileName = HttpUtility.UrlDecode("安全会议签到") + DateTime.Now.ToString("yyyyMMddHHmm") + ".xls"; //定义导出文件名,一定记得编码 try { Aspose.Cells.Workbook wk = new Aspose.Cells.Workbook(); wk.Open(Server.MapPath("~/Resource/ExcelTemplate/安全会议签到表模板.xlsx")); //加载模板 Aspose.Cells.Worksheet sheet = wk.Worksheets[0]; Aspose.Cells.Style style = wk.Styles[wk.Styles.Add()]; style.HorizontalAlignment = Aspose.Cells.TextAlignmentType.Center; //文字居中 style.IsTextWrapped = true; //自动换行 style.Borders[Aspose.Cells.BorderType.TopBorder].LineStyle = Aspose.Cells.CellBorderType.Thin; style.Borders[Aspose.Cells.BorderType.RightBorder].LineStyle = Aspose.Cells.CellBorderType.Thin; style.Borders[Aspose.Cells.BorderType.BottomBorder].LineStyle = Aspose.Cells.CellBorderType.Thin; style.Borders[Aspose.Cells.BorderType.LeftBorder].LineStyle = Aspose.Cells.CellBorderType.Thin; string sql = string.Format(" select t.USERNAME,o.DEPTNAME,t.PHOTOURL,t.issign,t.REVIEWSTATE from BIS_ConferenceUser t left join v_userinfo o on t.userid=o.userid where t.ConferenceID='{0}'", keyValue); DataTable dt = specialequipmentbll.SelectData(sql); //参会人员集合 var listC = dt; //签到人员集合 var SdUser = listC.Select(" issign='0'"); //未签到人员集合 var YdUser = listC.Select(" issign<>'0' and REVIEWSTATE<>'2'"); //请假人数 var QUser = listC.Select(" REVIEWSTATE='2'"); sheet.Cells[1, 2].PutValue(cr.ConferenceName); //会议名称 sheet.Cells[1, 4].PutValue(cr.Locale); //会议地点 sheet.Cells[2, 2].PutValue(cr.CompereDept); //召开部门 sheet.Cells[2, 4].PutValue(cr.Compere); //主持人 sheet.Cells[3, 2].PutValue(cr.Content); //主要议题 if (SdUser.Length == 0) { sheet.Cells[5, 1].PutValue("无"); } else { sheet.Cells[5 + SdUser.Length, 0].PutValue("已签到人数"); sheet.Cells.Merge(5, 0, SdUser.Length, 1); foreach (DataRow item in SdUser) { index++; sheet.Cells[4 + index, 1].PutValue(index); sheet.Cells[4 + index, 2].PutValue(item[0].ToString()); //参会人员 sheet.Cells[4 + index, 4].PutValue(item[1].ToString()); //所属部门 if (!string.IsNullOrEmpty(item[2].ToString())) { string[] u = item[2].ToString().Split('/'); string url = path + "/" + u[1] + "/" + u[2] + "/" + u[3] + "/" + u[4] + "/" + u[5];//签名 FileStream fs = new FileStream(url, FileMode.Open); int imgIndex = sheet.Pictures.Add(4 + index, 3, fs, 120, 30); fs.Close(); sheet.Cells.SetColumnWidth(5, 20); sheet.Cells.SetRowHeight(4 + index, 44); sheet.Pictures[imgIndex].Width = 139; sheet.Pictures[imgIndex].Height = 50; sheet.Pictures[imgIndex].Top = 3; sheet.Pictures[imgIndex].Left = 6; } } } int num = SdUser.Length == 0 ? 1 : SdUser.Length; int s = SdUser.Length == 0 ? 1 : SdUser.Length; if (index > 0) { s = 0; } if (YdUser.Length == 0) { sheet.Cells[5 + num, 0].PutValue("未签到人数"); sheet.Cells[5 + num, 1].PutValue("无"); } else { sheet.Cells[5 + num, 0].PutValue("未签到人数"); sheet.Cells.Merge(5 + num, 0, YdUser.Length, 1); foreach (DataRow item in YdUser) { index++; sheet.Cells[4 + s + index, 1].PutValue(index); sheet.Cells[4 + s + index, 2].PutValue(item[0].ToString()); sheet.Cells[4 + s + index, 4].PutValue(item[1].ToString()); sheet.Cells[4 + s + index, 3].PutValue(""); } } s = YdUser.Length == 0 ? 1 : YdUser.Length; int qj = s + SdUser.Length == 0 ? 1 : s + SdUser.Length; qj = SdUser.Length == 0 ? qj + 1 : qj; int qjNum = 0; if (QUser.Length > 0) { sheet.Cells[5 + qj, 0].PutValue("请假人数"); sheet.Cells.Merge(5 + qj, 0, QUser.Length, 1); foreach (DataRow dr in QUser) { sheet.Cells[5 + qj + qjNum, 1].PutValue(index++ + 1); sheet.Cells[5 + qj + qjNum, 2].PutValue(dr[0].ToString()); sheet.Cells[5 + qj + qjNum, 4].PutValue(dr[1].ToString()); sheet.Cells[5 + qj + qjNum, 3].PutValue(""); qjNum++; } } else { qjNum += 1; sheet.Cells[5 + qj, 0].PutValue("请假人数"); sheet.Cells[5 + qj, 1].PutValue("无"); } Aspose.Cells.Range r = sheet.Cells.CreateRange(5, 0, qj + qjNum, 5); r.Style = style; HttpResponse resp = System.Web.HttpContext.Current.Response; wk.Save(HttpUtility.UrlEncode(fileName), Aspose.Cells.FileFormatType.Excel2003, Aspose.Cells.SaveType.OpenInExcel, resp); return(ToJsonResult("0")); } catch (Exception ex) { string env = Config.GetValue("Environment"); string msg = env == "dev" ? ex.Message : "对不起,系统出错了"; return(ToJsonResult("" + msg + "")); } }
public string ImportData(string OrganizeId, string orgName) { try { int error = 0; string orgId = OperatorProvider.Provider.Current().OrganizeId; if (!string.IsNullOrEmpty(OrganizeId)) { orgId = OrganizeId; } string message = "请选择格式正确的文件再导入!"; string falseMessage = ""; int count = HttpContext.Request.Files.Count; if (count > 0) { HttpPostedFileBase file = HttpContext.Request.Files[0]; if (string.IsNullOrEmpty(file.FileName)) { return(message); } if (!(file.FileName.Substring(file.FileName.IndexOf('.')).Contains("xls") || file.FileName.Substring(file.FileName.IndexOf('.')).Contains("xlsx"))) { return(message); } string fileName = DateTime.Now.ToString("yyyyMMddHHmmss") + System.IO.Path.GetExtension(file.FileName); file.SaveAs(Server.MapPath("~/Resource/temp/" + fileName)); Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook(); wb.Open(Server.MapPath("~/Resource/temp/" + fileName)); Aspose.Cells.Cells cells = wb.Worksheets[0].Cells; DataTable dt = cells.ExportDataTable(0, 0, cells.MaxDataRow + 1, cells.MaxColumn + 1, true); //DataTable dt = ExcelHelper.ExcelImport(Server.MapPath("~/Resource/temp/" + fileName)); int order = 1; var deptBll = new DepartmentBLL(); for (int i = 1; i < dt.Rows.Count; i++) { order = i; //区域名称 string areaName = dt.Rows[i][0].ToString().Trim(); if (string.IsNullOrWhiteSpace(areaName)) { falseMessage += "</br>" + "第" + (i + 2) + "行区域名称为空,未能导入."; error++; continue; } //上级区域名称 string parentName = dt.Rows[i][1].ToString().Trim(); //管控部门名称 string deptName = dt.Rows[i][2].ToString().Trim(); if (string.IsNullOrWhiteSpace(deptName)) { falseMessage += "</br>" + "第" + (i + 2) + "行管控部门名称为空,未能导入."; error++; continue; } //区域负责人 string dutyUser = dt.Rows[i][3].ToString().Trim(); if (string.IsNullOrWhiteSpace(dutyUser)) { falseMessage += "</br>" + "第" + (i + 2) + "行区域负责人名称为空,未能导入."; error++; continue; } DistrictEntity area = new DistrictEntity(); area.DistrictName = areaName; area.ChargeDept = deptName; area.DisreictChargePerson = dutyUser; area.OrganizeId = OrganizeId; area.BelongCompany = orgName; //验证上级区域名称并获取相关信息 if (!string.IsNullOrWhiteSpace(parentName)) { DataTable dtArea = deptBll.GetDataTable(string.Format("select DistrictId from BIS_DISTRICT where DistrictName='{0}' and organizeid='{1}'", parentName, OrganizeId)); if (dtArea.Rows.Count > 0) { area.ParentID = dtArea.Rows[0][0].ToString(); dtArea = deptBll.GetDataTable(string.Format("select count(1) from BIS_DISTRICT where DistrictName='{0}' and organizeid='{1}' and parentid='{2}'", areaName, OrganizeId, area.ParentID)); if (dtArea.Rows[0][0].ToString() != "0") { falseMessage += "</br>" + "第" + (i + 2) + "行区域信息已经存在,未能导入.已存在区域信息:" + areaName; error++; continue; } } else { falseMessage += "</br>" + "第" + (i + 2) + "行上级区域名称与系统区域不匹配,未能导入.错误的区域信息:" + parentName; error++; continue; } } else { area.ParentID = "0"; DataTable dtArea = deptBll.GetDataTable(string.Format("select count(1) from BIS_DISTRICT where DistrictName='{0}' and organizeid='{1}' and parentid='0'", areaName, OrganizeId)); if (dtArea.Rows[0][0].ToString() != "0") { falseMessage += "</br>" + "第" + (i + 2) + "行区域信息已经存在,未能导入.错误的区域信息:" + areaName; error++; continue; } } //验证管控部门并获取相关信息 if (!string.IsNullOrWhiteSpace(deptName)) { DataTable dtDept = deptBll.GetDataTable(string.Format("select encode,departmentid,MANAGER,MANAGERID,OuterPhone from BASE_DEPARTMENT where fullname='{0}' and organizeid='{1}'", deptName, OrganizeId)); if (dtDept.Rows.Count > 0) { area.ChargeDeptCode = dtDept.Rows[0][0].ToString(); area.ChargeDeptID = dtDept.Rows[0][1].ToString(); area.DeptChargePerson = dtDept.Rows[0][2].ToString(); area.DeptChargePersonID = dtDept.Rows[0][3].ToString(); area.LinkTel = dtDept.Rows[0][4].ToString(); } else { falseMessage += "</br>" + "第" + (i + 2) + "行管控部门名称与系统部门名称不匹配,错误的部门信息:" + deptName; error++; continue; } } //验证区域负责人并获取相关信息 if (!string.IsNullOrWhiteSpace(dutyUser)) { DataTable dtUsers = deptBll.GetDataTable(string.Format("select account from BASE_user where organizeid='{1}' and realname in('{0}') ", dutyUser.Replace(",", "','"), OrganizeId)); StringBuilder sb = new StringBuilder(); foreach (DataRow dr in dtUsers.Rows) { sb.AppendFormat("{0},", dr[0].ToString()); } string users = sb.ToString().TrimEnd(','); area.DisreictChargePersonID = sb.ToString().TrimEnd(','); if (dutyUser.Split(',').Length != users.Split(',').Length) { falseMessage += "</br>" + "第" + (i + 2) + "行区域负责人信息与系统中人员不匹配,错误信息:" + dutyUser; error++; } } try { bis_districtbll.SaveForm("", area); } catch (Exception ex) { falseMessage += "</br>" + "第" + (i + 2) + "行数据插入出现异常,错误信息:" + Newtonsoft.Json.JsonConvert.SerializeObject(ex); error++; } } count = dt.Rows.Count - 1; message = "共有" + count + "条记录,成功导入" + (count - error) + "条,失败" + error + "条"; message += "</br>" + falseMessage; } return(message); } catch (Exception ex) { return(Newtonsoft.Json.JsonConvert.SerializeObject(ex)); } }
public static DataTable importRosterExcel2(string Filename, int iColNumber, int iRowNumber) { string mes1 = ""; string mes2 = ""; string mstr_FileName = Filename; string mstr_PathFileName = mstr_FileName; Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook(); wb.Open(mstr_FileName); Aspose.Cells.Worksheet ws = wb.Worksheets[0]; CultureInfo ci = CultureInfo.CreateSpecificCulture("en-US"); string DTformat = ci.DateTimeFormat.ShortDatePattern; //MessageBox.Show(ci.DateTimeFormat.ShortDatePattern); DataTable dt = new DataTable(); dt.TableName = ws.Name; int iColEnd = GetEndCol(ws); int iRowEnd = GetEndRow(ws, iRowNumber + 4); try { for (int j = 0; j < iColEnd; j++) { if (ws.Cells[iRowNumber, j].Value == null) { dt.Columns.Add(new DataColumn()); } else { object cName = ws.Cells[iRowNumber - 1, j].Value; object value = ws.Cells[iRowNumber, j].Value; if (value.GetType() == typeof(DateTime)) { dt.Columns.Add(new DataColumn(cName.ToString().Replace("\r\n", "").Replace("\n", "").Trim(), typeof(DateTime))); } else if (value.GetType() == typeof(double)) { dt.Columns.Add(new DataColumn(cName.ToString().Replace("\r\n", "").Replace("\n", "").Trim(), typeof(decimal))); } else { dt.Columns.Add(new DataColumn(cName.ToString().Replace("\r\n", "").Replace("\n", "").Trim(), typeof(string))); } } } int i = iRowNumber + 3; for (; i < iRowEnd; i++) { DataRow dr = dt.NewRow(); for (int j = 0; j < iColEnd; j++) { object value = ws.Cells[i, j].Value; if (value == null) { dr[j] = DBNull.Value; } else { string[] s = value.ToString().Trim().Split(new string[] { " _ " }, StringSplitOptions.None); string str = value.ToString().Contains(" _ ") ? s[s.Length - 1] : value.ToString().Trim(); mes1 = dt.Columns[j].ColumnName; mes2 = (i + 1).ToString(); if (dt.Columns[j].DataType == typeof(DateTime)) { try { dr[j] = DateTime.ParseExact(value.ToString(), "dd/MM/yyyy", CultureInfo.InvariantCulture).ToString(DTformat, CultureInfo.InvariantCulture); } catch (Exception) { try { DateTime creationDate; string[] sDate = value.ToString().Split('/'); string sDateTime = sDate[1] + '/' + sDate[0] + '/' + sDate[2]; creationDate = Convert.ToDateTime(sDateTime); dr[j] = creationDate; } catch (Exception ex) { dr[j] = Convert.ToDateTime(value.ToString()); } } //DateTime creationDate; //if (DateTime.TryParseExact(value.ToString(), DTformat, // CultureInfo.InvariantCulture, DateTimeStyles.None, // out creationDate)) //{ // dr[j] = creationDate; //} //else //{ // string[] sDate = value.ToString().Split('/'); // string sDateTime = sDate[1] + '/' + sDate[0] + '/' + sDate[2]; // creationDate = Convert.ToDateTime(sDateTime); // dr[j] = creationDate; //} //dr[j] = string.Format("{0:" + DTformat + "}", value); //dr[j] = DateTime.ParseExact(value.ToString(), "dd/MM/yyyy", CultureInfo.InvariantCulture).ToString(DTformat, CultureInfo.InvariantCulture); //DateTime.ParseExact(value.ToString(), DTformat, null); } else if (dt.Columns[j].DataType == typeof(decimal)) { dr[j] = Convert.ToInt32(str); } else { dr[j] = str; } } } dt.Rows.Add(dr); } return(dt); } catch (Exception ex) { ex.HelpLink = "Sai ở cột [" + mes1 + "], dòng [" + mes2 + "]"; //MessageBox.Show(ex.Message); //Util.Log.Instance.WriteExceptionLog(ex, "importExcel"); throw (ex); } return(null); }
private DataTable BuildDataTableFromFileExcel(System.IO.Stream dataStream) { var infoTb = BuildNewtable(); try { Aspose.Cells.License license = new Aspose.Cells.License(); license.SetLicense("Aspose.Cells.lic"); Aspose.Cells.Workbook document = new Aspose.Cells.Workbook(); document.Open(dataStream); if (document.Worksheets[0].Cells.Rows.Count <= 1) return infoTb; for (var i = 1; i < document.Worksheets[0].Cells.Rows.Count; i++) { if (string.IsNullOrEmpty(document.Worksheets[0].Cells[i, 0].Value.ToString())) continue; var row = infoTb.NewRow(); row["WorkingAccId"] = document.Worksheets[0].Cells[i, 3].Value; row["PaymentMethod"] = document.Worksheets[0].Cells[i, 0].Value; row["CreditAmount"] = String.Format(CultureInfo.InvariantCulture, "{0:0,0}", document.Worksheets[0].Cells[i, 5].Value); row["CreditAccount"] = string.Format("{0}-{1}-{2}", document.Worksheets[0].Cells[i, 4].Value, document.Worksheets[0].Cells[i, 3].Value, document.Worksheets[0].Cells[i, 2].Value); row["Exist"] = string.Empty; infoTb.Rows.Add(row); } UpdateDataInfo(infoTb); } catch (Exception ex) { labelNoResults.Text = "Wrong Selected Company Payment file"; } return infoTb; }
private void btnXuatDanhSach_Click(object sender, EventArgs e) { if (grvSinhVien.DataRowCount > 0) { CreateWaitDialog("Đang xuất dữ liệu, xin vui lòng chờ.", "Xuất dữ liệu"); try { MemoryStream ms = new MemoryStream(Resources.DanhSachSinhVien); var book = new Aspose.Cells.Workbook(); book.Open(ms); var sheet = book.Worksheets[0]; var cells = sheet.Cells; var fields = new string[] { "MaSinhVien", "HoVaTen", "NgaySinh", "NoiSinh", "ThuongTru", "KhenThuong", "KyLuat" }; cells["A2"].PutValue("Danh sách sinh viên " + pDM_LopInfo.TenLop + (cboDanhSachHienThi.SelectedIndex == 1 ? " (đã bị xóa tên)" : "")); var rIndex = 5; for (var i = 0; i < dtSinhVien.Rows.Count; i++) { if (i < dtSinhVien.Rows.Count - 2) { cells.InsertRow(rIndex + i + 1); } cells["A" + (rIndex + i)].PutValue(i + 1); for (var j = 0; j < fields.Length; j++) { cells[rIndex + i - 1, j + 1].PutValue(dtSinhVien.Rows[i][fields[j]]); } } SaveFileDialog sDlg = new SaveFileDialog(); sDlg.Title = "Lưu file kết xuất"; sDlg.InitialDirectory = Application.StartupPath; sDlg.FileName = "DanhSachSinhVien_" + DateTime.Now.ToString("yyyyMMddhhmm") + ".xls"; if (sDlg.ShowDialog() != DialogResult.Cancel) { book.Save(sDlg.FileName, Aspose.Cells.FileFormatType.Excel2003); Process.Start(sDlg.FileName); } } catch (Exception ex) { ThongBaoLoi("Có lỗi khi xuất dữ liệu. " + ex.Message); } finally { CloseWaitDialog(); } } else { ThongBao("Chưa có danh sách sinh viên."); } }
public string ImportLabor() { var currUser = OperatorProvider.Provider.Current(); string orgId = OperatorProvider.Provider.Current().OrganizeId;//所属公司 int error = 0; string message = "请选择格式正确的文件再导入!"; string falseMessage = ""; int count = HttpContext.Request.Files.Count; if (count > 0) { HttpPostedFileBase file = HttpContext.Request.Files[0]; if (string.IsNullOrEmpty(file.FileName)) { return(message); } if (!(file.FileName.Substring(file.FileName.IndexOf('.')).Contains("xls") || file.FileName.Substring(file.FileName.IndexOf('.')).Contains("xlsx"))) { return(message); } string fileName = DateTime.Now.ToString("yyyyMMddHHmmss") + System.IO.Path.GetExtension(file.FileName); file.SaveAs(Server.MapPath("~/Resource/temp/" + fileName)); Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook(); wb.Open(Server.MapPath("~/Resource/temp/" + fileName)); Aspose.Cells.Cells cells = wb.Worksheets[0].Cells; if (cells.MaxDataRow == 0) { message = "没有数据,请选择先填写模板在进行导入!"; return(message); } DataTable dt = cells.ExportDataTable(0, 0, cells.MaxDataRow + 1, cells.MaxColumn + 1, true); int order = 1; IList <LaborprotectionEntity> LaborList = new List <LaborprotectionEntity>(); //先获取到原始的一个编号 string no = laborprotectionbll.GetNo(); int ysno = Convert.ToInt32(no); for (int i = 0; i < dt.Rows.Count; i++) { order = i; string Name = dt.Rows[i]["劳动防护用品名称"].ToString(); string Unit = dt.Rows[i]["劳动防护用品单位"].ToString(); string Model = dt.Rows[i]["型号"].ToString(); string Type = dt.Rows[i]["类型"].ToString(); string TimeNum = dt.Rows[i]["使用期限时间"].ToString(); string TimeType = dt.Rows[i]["使用期限单位"].ToString(); string Note = dt.Rows[i]["使用说明"].ToString().Trim(); //---****值存在空验证*****-- if (string.IsNullOrEmpty(Name) || string.IsNullOrEmpty(Unit)) { falseMessage += "</br>" + "第" + (i + 2) + "行值存在空,未能导入."; error++; continue; } LaborprotectionEntity ue = new LaborprotectionEntity(); ue.Name = Name; ue.Unit = Unit; ue.Model = Model; ue.Type = Type; if (TimeNum != "") { ue.TimeNum = Convert.ToInt32(TimeNum);//工号 } ue.TimeType = TimeType; ue.Note = Note; ue.No = ysno.ToString(); //下一条编号增加 ysno++; ue.LaborOperationUserName = currUser.UserName; ue.LaborOperationTime = DateTime.Now; try { laborprotectionbll.SaveForm("", ue); } catch { error++; } } count = dt.Rows.Count; message = "共有" + count + "条记录,成功导入" + (count - error) + "条,失败" + error + "条"; message += "</br>" + falseMessage; } return(message); }
public string ImportSuppliesData() { int error = 0; string message = "请选择格式正确的文件再导入!"; string falseMessage = ""; int count = HttpContext.Request.Files.Count; if (count > 0) { count = 0; HttpPostedFileBase file = HttpContext.Request.Files[0]; if (string.IsNullOrEmpty(file.FileName)) { return(message); } if (!(file.FileName.Substring(file.FileName.IndexOf('.')).Contains("xls") || file.FileName.Substring(file.FileName.IndexOf('.')).Contains("xlsx"))) { return(message); } string fileName = DateTime.Now.ToString("yyyyMMddHHmmss") + System.IO.Path.GetExtension(file.FileName); file.SaveAs(Server.MapPath("~/Resource/temp/" + fileName)); Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook(); wb.Open(Server.MapPath("~/Resource/temp/" + fileName), file.FileName.Substring(file.FileName.IndexOf('.')).Contains("xlsx") ? Aspose.Cells.FileFormatType.Excel2007Xlsx : Aspose.Cells.FileFormatType.Excel2003); Operator user = ERCHTMS.Code.OperatorProvider.Provider.Current(); //判断表头是否正确,以免使用错误模板 var sheet = wb.Worksheets[0]; if (sheet.Cells[2, 0].StringValue != "物资类型" || sheet.Cells[2, 1].StringValue != "物资名称" || sheet.Cells[2, 2].StringValue != "规格型号" || sheet.Cells[2, 3].StringValue != "数量" || sheet.Cells[2, 4].StringValue != "单位" || sheet.Cells[2, 5].StringValue != "存放区域" || sheet.Cells[2, 6].StringValue != "存放地点" || sheet.Cells[2, 7].StringValue != "主要功能") { return(message); } //List<SuppliesEntity> slist = new List<SuppliesEntity>(); for (int i = 3; i <= sheet.Cells.MaxDataRow; i++) { count++; SuppliesEntity entity = new SuppliesEntity(); if (string.IsNullOrWhiteSpace(sheet.Cells[i, 1].StringValue)) { falseMessage += "</br>" + "第" + (i + 1) + "行物资名称不能为空,未能导入."; error++; continue; } if (string.IsNullOrWhiteSpace(sheet.Cells[i, 3].StringValue)) { falseMessage += "</br>" + "第" + (i + 1) + "行数量不能为空,未能导入."; error++; continue; } entity.SUPPLIESTYPENAME = sheet.Cells[i, 0].StringValue; var item = new DataItemDetailBLL().GetDataItemListByItemCode("'MAE_SUPPLIESTYPE'").Where(x => x.ItemName == entity.SUPPLIESTYPENAME).ToList().FirstOrDefault(); if (item != null) { entity.SUPPLIESTYPE = item.ItemValue; } entity.SUPPLIESNAME = sheet.Cells[i, 1].StringValue; entity.Models = sheet.Cells[i, 2].StringValue; entity.NUM = Convert.ToInt32(sheet.Cells[i, 3].StringValue); entity.SUPPLIESUNTILNAME = sheet.Cells[i, 4].StringValue; var itemUnit = new DataItemDetailBLL().GetDataItemListByItemCode("'MAE_SUPPLIESUNTIL'").Where(x => x.ItemName == entity.SUPPLIESUNTILNAME).ToList().FirstOrDefault(); if (itemUnit != null) { entity.SUPPLIESUNTIL = itemUnit.ItemValue; } if (!string.IsNullOrWhiteSpace(sheet.Cells[i, 5].StringValue)) { DistrictEntity district = districtbll.GetDistrict(user.OrganizeId, sheet.Cells[i, 5].StringValue); if (district == null) { falseMessage += "</br>" + "第" + (i + 1) + "行区域不是系统内区域,未能导入."; error++; continue; } else { entity.WorkAreaCode = district.DistrictCode; entity.WorkAreaName = district.DistrictName; } } entity.STORAGEPLACE = sheet.Cells[i, 6].StringValue; entity.MAINFUN = sheet.Cells[i, 7].StringValue; entity.CREATEDATE = DateTime.Now; entity.DEPARTID = user.DeptId; entity.DEPARTNAME = user.DeptName; entity.USERID = user.UserId; entity.USERNAME = user.UserName; entity.SUPPLIESCODE = suppliesbll.GetMaxCode(); //entity.Create(); suppliesbll.SaveForm(entity.ID, entity); var entityInorOut = new InoroutrecordEntity { USERID = entity.USERID, USERNAME = entity.USERNAME, DEPARTID = entity.DEPARTID, DEPARTNAME = entity.DEPARTNAME, INOROUTTIME = DateTime.Now, SUPPLIESCODE = entity.SUPPLIESCODE, SUPPLIESTYPE = entity.SUPPLIESTYPE, SUPPLIESTYPENAME = entity.SUPPLIESTYPENAME, SUPPLIESNAME = entity.SUPPLIESNAME, SUPPLIESUNTIL = entity.SUPPLIESUNTIL, SUPPLIESUNTILNAME = entity.SUPPLIESUNTILNAME, NUM = entity.NUM, STORAGEPLACE = entity.STORAGEPLACE, MOBILE = entity.MOBILE, SUPPLIESID = entity.ID, STATUS = 0 }; var inoroutrecordbll = new InoroutrecordBLL(); inoroutrecordbll.SaveForm("", entityInorOut); //slist.Add(entity); } message = "共有" + count + "条记录,成功导入" + (count - error) + "条,失败" + error + "条。"; if (error > 0) { message += "</br>" + falseMessage; } } return(message); }
/// <summary> /// 导出到Excel /// </summary> /// <param name="queryJson"></param> /// <returns></returns> public ActionResult ExportData(string queryJson) { Pagination pagination = new Pagination(); pagination.page = 1; pagination.rows = 100000000; pagination.p_kid = "a.Id"; pagination.p_fields = "c.itemname as applystatename,b.itemname as worktype,workplace,workcontent,to_char(workstarttime,'yyyy-mm-dd hh24:mi') || ' - '||to_char(workendtime,'yyyy-mm-dd hh24:mi'),applyusername,applydeptname,to_char(a.createdate,'yyyy-mm-dd hh24:mi')"; pagination.p_tablename = " bis_highriskapply a left join base_dataitemdetail b on a.worktype=b.itemvalue and b.itemid =(select itemid from base_dataitem where itemcode='WorkType') left join base_dataitemdetail c on a.applystate=c.itemvalue and c.itemid =(select itemid from base_dataitem where itemcode='WorkStatus')"; pagination.conditionJson = "1=1"; pagination.sidx = "a.createdate"; pagination.sord = "desc"; Operator user = ERCHTMS.Code.OperatorProvider.Provider.Current(); if (!user.IsSystem) { string authType = new AuthorizeBLL().GetOperAuthorzeType(user, HttpContext.Request.Cookies["currentmoduleId"].Value, "search"); if (!string.IsNullOrEmpty(authType)) { switch (authType) { case "1": pagination.conditionJson += " and applyuserid='" + user.UserId + "'"; break; case "2": pagination.conditionJson += " and ApplyDeptCode='" + user.DeptCode + "'"; break; case "3": //本子部门 pagination.conditionJson += string.Format(" and ApplyDeptCode in(select encode from base_department where encode like '{0}%' or senddeptid='{1}')", user.DeptCode, user.DeptId); break; case "4": pagination.conditionJson += " and a.createuserorgcode='" + user.OrganizeCode + "'"; break; } } else { pagination.conditionJson += " and 0=1"; } } DataTable exportTable = highriskapplybll.GetPageDataTable(pagination, queryJson); exportTable.Columns.Remove("id"); exportTable.Columns.Remove("r"); // 确定导出文件名 string fileName = "高风险作业许可申请信息"; HttpResponse resp = System.Web.HttpContext.Current.Response; // 详细列表内容 string fielname = fileName + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls"; Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook(); wb.Open(Server.MapPath("~/Resource/ExcelTemplate/高风险作业许可申请信息.xlsx")); Aspose.Cells.Worksheet sheet = wb.Worksheets[0] as Aspose.Cells.Worksheet; Aspose.Cells.Cell cell = sheet.Cells[1, 1]; sheet.Cells.ImportDataTable(exportTable, false, 1, 0); wb.Save(Server.UrlEncode(fielname), Aspose.Cells.FileFormatType.Excel2003, Aspose.Cells.SaveType.OpenInBrowser, resp); return(Success("导出成功!")); }
public string ImportStandard(string standardtype, string categorycode) { if (OperatorProvider.Provider.Current().IsSystem) { return("超级管理员无此操作权限"); } string orgId = OperatorProvider.Provider.Current().OrganizeId;//所属公司 int error = 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(2, 0, cells.MaxDataRow - 1, cells.MaxColumn + 1, false); for (int i = 1; i < dt.Rows.Count; i++) { //文件名称 string filename = dt.Rows[i][0].ToString(); //文件路径 string filepath = dt.Rows[i][1].ToString(); //相应元素 string relevantelement = ""; string relevantelementname = ""; string relevantelementid = ""; //实施日期 string carrydate = ""; if (standardtype == "1" || standardtype == "2" || standardtype == "3" || standardtype == "4" || standardtype == "5" || standardtype == "6") { relevantelement = dt.Rows[i][2].ToString(); carrydate = dt.Rows[i][3].ToString(); } //文学字号 string dispatchcode = ""; //颁布部门 string publishdept = ""; if (standardtype == "6") { dispatchcode = dt.Rows[i][4].ToString(); publishdept = dt.Rows[i][5].ToString(); } string dutyid = ""; string dutyName = ""; //---****值存在空验证*****-- if (string.IsNullOrEmpty(filename) || string.IsNullOrEmpty(filepath)) { falseMessage += "</br>" + "第" + (i + 3) + "行值存在空,未能导入."; error++; continue; } //---****文件格式验证*****-- if (!(filepath.Substring(filepath.IndexOf('.')).Contains("doc") || filepath.Substring(filepath.IndexOf('.')).Contains("docx") || filepath.Substring(filepath.IndexOf('.')).Contains("pdf"))) { falseMessage += "</br>" + "第" + (i + 3) + "行附件格式不正确,未能导入."; error++; continue; } //---****文件是否存在验证*****-- if (!System.IO.File.Exists(decompressionDirectory + filepath)) { falseMessage += "</br>" + "第" + (i + 3) + "行附件不存在,未能导入."; error++; continue; } //--**验证岗位是否存在 * *-- int startnum = 4; if (standardtype == "1" || standardtype == "2" || standardtype == "3" || standardtype == "4" || standardtype == "5") { startnum = 4; } else if (standardtype == "6") { startnum = 6; } else if (standardtype == "7" || standardtype == "8" || standardtype == "9") { startnum = 2; } for (int j = startnum; j < dt.Columns.Count; j++) { if (!dt.Rows[i][j].IsEmpty()) { foreach (var item in dt.Rows[i][j].ToString().Split(',')) { DepartmentEntity dept = DepartmentBLL.GetList().Where(t => t.OrganizeId == orgId && t.FullName == dt.Rows[0][j].ToString()).FirstOrDefault(); if (dept == null) { continue; } RoleEntity re = postBLL.GetList().Where(a => a.FullName == item.ToString() && a.OrganizeId == orgId && a.DeleteMark == 0 && a.EnabledMark == 1 && a.DeptId == dept.DepartmentId).FirstOrDefault(); if (re == null) { //falseMessage += "</br>" + "第" + (i + 3) + "行岗位有误,未能导入."; //error++; continue; } else { dutyid += re.RoleId + ","; dutyName += re.FullName + ","; } } } } dutyid = dutyid.Length > 0 ? dutyid.Substring(0, dutyid.Length - 1) : ""; dutyName = dutyName.Length > 0 ? dutyName.Substring(0, dutyName.Length - 1) : ""; StandardsystemEntity standard = new StandardsystemEntity(); try { if (!string.IsNullOrEmpty(carrydate)) { standard.CARRYDATE = DateTime.Parse(DateTime.Parse(carrydate).ToString("yyyy-MM-dd")); } } catch { falseMessage += "</br>" + "第" + (i + 3) + "行时间有误,未能导入."; error++; continue; } if (!string.IsNullOrEmpty(relevantelement)) { foreach (var item in relevantelement.Split(',')) { ElementEntity re = elementBLL.GetList("").Where(a => a.NAME == item.ToString()).FirstOrDefault(); if (re == null) { //falseMessage += "</br>" + "第" + (i + 2) + "行相应元素有误,未能导入."; //error++; continue; } else { relevantelementname += re.NAME + ","; relevantelementid += re.ID + ","; } } } relevantelementname = string.IsNullOrEmpty(relevantelementname) ? "" : relevantelementname.Substring(0, relevantelementname.Length - 1); relevantelementid = string.IsNullOrEmpty(relevantelementid) ? "" : relevantelementid.Substring(0, relevantelementid.Length - 1); standard.FILENAME = filename; standard.STATIONID = dutyid; standard.STATIONNAME = dutyName; standard.RELEVANTELEMENTNAME = relevantelementname; standard.RELEVANTELEMENTID = relevantelementid; standard.DISPATCHCODE = dispatchcode; standard.PUBLISHDEPT = publishdept; standard.STANDARDTYPE = standardtype; standard.CATEGORYCODE = categorycode; standard.CONSULTNUM = 0; standard.ID = Guid.NewGuid().ToString(); 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); try { standardsystembll.SaveForm(standard.ID, standard); } catch { error++; } } count = dt.Rows.Count - 1; message = "共有" + count + "条记录,成功导入" + (count - error) + "条,失败" + error + "条"; message += "</br>" + falseMessage; } return(message); }
/// <summary> /// 產生匯入檔 /// </summary> private void ExportInputFile() { List<ReportStudentData> ExportData = new List<ReportStudentData>(); List<string> SIDList = (from x in Students select x.StudentID).ToList(); // 父母資料 List<JHParentRecord> ParentRecList = JHParent.SelectByStudentIDs(SIDList); // 地址 List<JHAddressRecord> AddressRecList = JHAddress.SelectByStudentIDs(SIDList); // 電話 List<JHPhoneRecord> PhoneRecList = JHPhone.SelectByStudentIDs(SIDList); List<SLearningDomainParser> allsems = new List<SLearningDomainParser>(); allsems.Add(new SLearningDomainParser(1, 1)); allsems.Add(new SLearningDomainParser(1, 2)); allsems.Add(new SLearningDomainParser(2, 1)); allsems.Add(new SLearningDomainParser(2, 2)); allsems.Add(new SLearningDomainParser(3, 1)); allsems.Add(new SLearningDomainParser(3, 2)); allsems.Add(new SLearningDomainParser(7, 1)); allsems.Add(new SLearningDomainParser(7, 2)); allsems.Add(new SLearningDomainParser(8, 1)); allsems.Add(new SLearningDomainParser(8, 2)); allsems.Add(new SLearningDomainParser(9, 1)); allsems.Add(new SLearningDomainParser(9, 2)); //// 取得 UDT 對照 //List<JointAdmissionModule.DAL.UserDefData_StudTypeWeight> udtList = DAL.UDTTransfer.GetDataFromUDT_StudTypeWeight().Where(x => x.SchoolType == "高中").ToList(); //// 建立特種身分代碼對照表 //Dictionary<string, string> specCodeDict = new Dictionary<string, string>(); //foreach (JointAdmissionModule.DAL.UserDefData_StudTypeWeight dd in udtList) // if (!specCodeDict.ContainsKey(dd.JoinStudType)) // specCodeDict.Add(dd.JoinStudType, dd.JoinStudTypeCode); // 資料轉換 foreach (ReportStudent stud in Students) { // 是否已有年排名資料 List<DAL.UserDefData> uddList = new List<JointAdmissionModule.DAL.UserDefData>(); if (_StudRankData.ContainsKey(stud.StudentID)) uddList = _StudRankData[stud.StudentID]; ReportStudentData rsd = new ReportStudentData(); // 班級 if (stud.Class != null) rsd.ClassName = stud.Class.Name; // 座號 rsd.SeatNo = stud.SeatNo; // 姓名 rsd.Name = stud.Name; // 家長姓名 foreach (JHParentRecord rec in ParentRecList.Where(x => x.RefStudentID == stud.StudentID)) { rsd.ParentName = rec.CustodianName; } foreach (JHPhoneRecord rec in PhoneRecList.Where(x => x.RefStudentID == stud.StudentID)) { // 電話 rsd.Phone = rec.Contact; // 手機 rsd.CellPhone = rec.Cell; } // 生日 DateTime dt; if (DateTime.TryParse(stud.Birthday, out dt)) { rsd.Birthday = dt; } // 性別 if (stud.Gender == "男") rsd.GenderCode = "1"; if (stud.Gender == "女") rsd.GenderCode = "2"; // 身分證字號 rsd.IDNumber = stud.IDNumber; foreach (JHAddressRecord rec in AddressRecList.Where(x => x.RefStudentID == stud.StudentID)) { // 郵區號 rsd.ZipCode = rec.MailingZipCode; // 聯絡地址 rsd.Address = rec.MailingCounty + rec.MailingTown + rec.MailingDistrict + rec.MailingArea + rec.MailingDetail; } //string code = ""; // 特種身分(原民未持1、原民認證2、身心障礙3、其他4) if (!string.IsNullOrEmpty(stud.SpcStudTypeName)) { rsd.SpceTypeCode = 4; if (stud.SpcStudTypeName == StudSpcName1) rsd.SpceTypeCode = 2; if (stud.SpcStudTypeName == StudSpcName2) rsd.SpceTypeCode = 1; if (stud.SpcStudTypeName == StudSpcName6) rsd.SpceTypeCode = 3; } PlaceCollection Places = stud.Places.NS("年排名"); // 這寫法主要判斷當名次在不同年級時可能會讀到錯誤資料 //if (Places[placeKey].Score == 0 && Places[placeKey].Level == 1) { } //else foreach (SLearningDomainParser semsIndex in allsems) { if ((semsIndex.Grade == 1 || semsIndex.Grade == 7) && semsIndex.sm == 1) { // 一上名次 // 一上名次百分比 string placeKey = semsIndex.Name; if (Places.Contains(placeKey)) { if (Places[placeKey].Score == 0 && Places[placeKey].Level == 1) { } else { rsd.Rank11 = Places[placeKey].Level; rsd.RankPercent11 = Places[placeKey].Percentage; // 當有加分即時運算 if (stud.AddWeight.HasValue) { int Level = 1,Percentage=1; List<Place> PList = new List<Place>(); decimal sc = Places[placeKey].Score * stud.AddWeight.Value; if (DAL.DALTransfer.StudRankScoreDict.ContainsKey(stud.GradeYear)) { if (DAL.DALTransfer.StudRankScoreDict[stud.GradeYear].ContainsKey(placeKey)) { PList = (from data in DAL.DALTransfer.StudRankScoreDict[stud.GradeYear][placeKey] where data.Place.Score >= sc orderby data.Place.Score ascending select data.Place).ToList(); if (PList.Count > 0) { PList.OrderBy(x => x.Score); Level = PList[0].Level; Percentage = PList[0].Percentage; } rsd.Rank11 = Level; rsd.RankPercent11 = Percentage; } } } } } } if ((semsIndex.Grade == 1 || semsIndex.Grade == 7) && semsIndex.sm == 2) { // 一下名次 // 一下名次百分比 string placeKey = semsIndex.Name; if (Places.Contains(placeKey)) { if (Places[placeKey].Score == 0 && Places[placeKey].Level == 1) { } else { rsd.Rank12 = Places[placeKey].Level; rsd.RankPercent12 = Places[placeKey].Percentage; // 當有加分即時運算 if (stud.AddWeight.HasValue) { int Level = 1, Percentage = 1; List<Place> PList = new List<Place>(); decimal sc = Places[placeKey].Score * stud.AddWeight.Value; if (DAL.DALTransfer.StudRankScoreDict.ContainsKey(stud.GradeYear)) { if (DAL.DALTransfer.StudRankScoreDict[stud.GradeYear].ContainsKey(placeKey)) { PList = (from data in DAL.DALTransfer.StudRankScoreDict[stud.GradeYear][placeKey] where data.Place.Score >= sc orderby data.Place.Score ascending select data.Place).ToList(); if (PList.Count > 0) { PList.OrderBy(x => x.Score); Level = PList[0].Level; Percentage = PList[0].Percentage; } rsd.Rank12 = Level; rsd.RankPercent12 = Percentage; } } } } } } if ((semsIndex.Grade == 2 || semsIndex.Grade == 8) && semsIndex.sm == 1) { // 二上名次 // 二上名次百分比 string placeKey = semsIndex.Name; if (Places.Contains(placeKey)) { if (Places[placeKey].Score == 0 && Places[placeKey].Level == 1) { } else { rsd.Rank21 = Places[placeKey].Level; rsd.RankPercent21 = Places[placeKey].Percentage; // 當有加分即時運算 if (stud.AddWeight.HasValue) { int Level = 1, Percentage = 1; List<Place> PList = new List<Place>(); decimal sc = Places[placeKey].Score * stud.AddWeight.Value; if (DAL.DALTransfer.StudRankScoreDict.ContainsKey(stud.GradeYear)) { if (DAL.DALTransfer.StudRankScoreDict[stud.GradeYear].ContainsKey(placeKey)) { PList = (from data in DAL.DALTransfer.StudRankScoreDict[stud.GradeYear][placeKey] where data.Place.Score >= sc orderby data.Place.Score ascending select data.Place).ToList(); if (PList.Count > 0) { PList.OrderBy(x => x.Score); Level = PList[0].Level; Percentage = PList[0].Percentage; } } rsd.Rank21 = Level; rsd.RankPercent21 = Percentage; } } } } } if ((semsIndex.Grade == 2 || semsIndex.Grade == 8) && semsIndex.sm == 2) { // 二下名次 // 二下名次百分比 string placeKey = semsIndex.Name; if (Places.Contains(placeKey)) { if (Places[placeKey].Score == 0 && Places[placeKey].Level == 1) { } else { rsd.Rank22 = Places[placeKey].Level; rsd.RankPercent22 = Places[placeKey].Percentage; // 當有加分即時運算 if (stud.AddWeight.HasValue) { int Level = 1, Percentage = 1; List<Place> PList = new List<Place>(); decimal sc = Places[placeKey].Score * stud.AddWeight.Value; if (DAL.DALTransfer.StudRankScoreDict.ContainsKey(stud.GradeYear)) { if (DAL.DALTransfer.StudRankScoreDict[stud.GradeYear].ContainsKey(placeKey)) { PList = (from data in DAL.DALTransfer.StudRankScoreDict[stud.GradeYear][placeKey] where data.Place.Score >= sc orderby data.Place.Score ascending select data.Place).ToList(); if (PList.Count > 0) { PList.OrderBy(x => x.Score); Level = PList[0].Level; Percentage = PList[0].Percentage; } rsd.Rank22 = Level; rsd.RankPercent22 = Percentage; } } } } } } if ((semsIndex.Grade == 3 || semsIndex.Grade == 9) && semsIndex.sm == 1) { // 三上名次 // 三上名次百分比 string placeKey = semsIndex.Name; if (Places.Contains(placeKey)) { if (Places[placeKey].Score == 0 && Places[placeKey].Level == 1) { } else { rsd.Rank31 = Places[placeKey].Level; rsd.RankPercent31 = Places[placeKey].Percentage; // 當有加分即時運算 if (stud.AddWeight.HasValue) { int Level = 1, Percentage = 1; List<Place> PList = new List<Place>(); decimal sc = Places[placeKey].Score * stud.AddWeight.Value; if (DAL.DALTransfer.StudRankScoreDict.ContainsKey(stud.GradeYear)) { if (DAL.DALTransfer.StudRankScoreDict[stud.GradeYear].ContainsKey(placeKey)) { PList = (from data in DAL.DALTransfer.StudRankScoreDict[stud.GradeYear][placeKey] where data.Place.Score >= sc orderby data.Place.Score ascending select data.Place).ToList(); if (PList.Count > 0) { PList.OrderBy(x => x.Score); Level = PList[0].Level; Percentage = PList[0].Percentage; } } rsd.Rank31 = Level; rsd.RankPercent31 = Percentage; } } } } } } if (Places.Contains("學習領域")) { string placeKey = "學習領域"; // 五學期名次 rsd.AvgRank5 = Places[placeKey].Level; // 五學期名次百分比 rsd.AvgRankPercent5 = Places[placeKey].Percentage; // 當有加分即時運算 if (stud.AddWeight.HasValue) { int Level = 1, Percentage = 1; List<Place> PList = new List<Place>(); decimal sc = Places[placeKey].Score * stud.AddWeight.Value; if (DAL.DALTransfer.StudRankScoreDict.ContainsKey(stud.GradeYear)) { placeKey = "學期總平均"; if (DAL.DALTransfer.StudRankScoreDict[stud.GradeYear].ContainsKey(placeKey)) { PList = (from data in DAL.DALTransfer.StudRankScoreDict[stud.GradeYear][placeKey] where data.Place.Score >= sc orderby data.Place.Score ascending select data.Place).ToList(); if (PList.Count > 0) { PList.OrderBy(x => x.Score); Level = PList[0].Level; Percentage = PList[0].Percentage; } } rsd.AvgRank5 = Level; rsd.AvgRankPercent5 = Percentage; } } } // 處理已有年排名(在UDT有存資料,填入 UDT 資料) if (uddList.Count > 0 && stud.LastEnterSemester.HasValue) { foreach (DAL.UserDefData udd in uddList) { // 七上 if ((udd.GradeYear == 1 || udd.GradeYear == 7) && udd.Semester == 1) { rsd.Rank11 = udd.GradeRank; rsd.RankPercent11 = udd.GradeRankPercent; // 特種身分加分 if (stud.AddWeight.HasValue) { rsd.Rank11 = udd.GradeRankAdd; rsd.RankPercent11 = udd.GradeRankPercentAdd; } } // 七下 if ((udd.GradeYear == 1 || udd.GradeYear == 7) && udd.Semester == 2) { rsd.Rank12 = udd.GradeRank; rsd.RankPercent12 = udd.GradeRankPercent; // 特種身分加分 if (stud.AddWeight.HasValue) { rsd.Rank12 = udd.GradeRankAdd; rsd.RankPercent12 = udd.GradeRankPercentAdd; } } // 八上 if ((udd.GradeYear==2 ||udd.GradeYear==8) && udd.Semester ==1) { rsd.Rank21 = udd.GradeRank; rsd.RankPercent21 = udd.GradeRankPercent; // 特種身分加分 if (stud.AddWeight.HasValue) { rsd.Rank21 = udd.GradeRankAdd; rsd.RankPercent21 = udd.GradeRankPercentAdd; } } // 八下 if ((udd.GradeYear == 2 || udd.GradeYear == 8) && udd.Semester == 2) { rsd.Rank22 = udd.GradeRank; rsd.RankPercent22 = udd.GradeRankPercent; // 特種身分加分 if (stud.AddWeight.HasValue) { rsd.Rank22 = udd.GradeRankAdd; rsd.RankPercent22 = udd.GradeRankPercentAdd; } } // 九上 if ((udd.GradeYear == 3 || udd.GradeYear == 9) && udd.Semester == 1) { rsd.Rank31 = udd.GradeRank; rsd.RankPercent31 = udd.GradeRankPercent; // 特種身分加分 if (stud.AddWeight.HasValue) { rsd.Rank31 = udd.GradeRankAdd; rsd.RankPercent31 = udd.GradeRankPercentAdd; } } } } // 判斷轉入生,清空之前名次 if (stud.LastEnterGradeYear.HasValue && stud.LastEnterSchoolyear.HasValue ) { int grYear = 0; // 先判斷年級 if (stud.LastEnterGradeYear >= 6) { grYear = stud.LastEnterGradeYear.Value - 6; } else { grYear = stud.LastEnterGradeYear.Value; } // 判斷udd有輸入資料 if (grYear == 3 && stud.LastEnterSemester == 1) { bool clear11 = true, clear12 = true, clear21 = true, clear22 = true; foreach (DAL.UserDefData udd in uddList) { if (udd.GradeYear == 1 && udd.Semester == 1) clear11 = false; if (udd.GradeYear == 1 && udd.Semester == 2) clear12 = false; if (udd.GradeYear == 2 && udd.Semester == 1) clear21 = false; if (udd.GradeYear == 2 && udd.Semester == 2) clear22 = false; } if (clear11) { rsd.Rank11 = null; rsd.RankPercent11 = null; } if (clear12) { rsd.Rank12 = null; rsd.RankPercent12 = null; } if (clear21) { rsd.Rank21 = null; rsd.RankPercent21 = null; } if (clear22) { rsd.Rank22 = null; rsd.RankPercent22 = null; } } if (grYear == 2 && stud.LastEnterSemester == 2) { bool clear11 = true, clear12 = true, clear21 = true; foreach (DAL.UserDefData udd in uddList) { if (udd.GradeYear == 1 && udd.Semester == 1) clear11 = false; if (udd.GradeYear == 1 && udd.Semester == 2) clear12 = false; if (udd.GradeYear == 2 && udd.Semester == 1) clear21 = false; } if (clear11) { rsd.Rank11 = null; rsd.RankPercent11 = null; } if (clear12) { rsd.Rank12 = null; rsd.RankPercent12 = null; } if (clear21) { rsd.Rank21 = null; rsd.RankPercent21 = null; } } if (grYear == 2 && stud.LastEnterSemester == 1) { bool clear11 = true, clear12 = true; foreach (DAL.UserDefData udd in uddList) { if (udd.GradeYear == 1 && udd.Semester == 1) clear11 = false; if (udd.GradeYear == 1 && udd.Semester == 2) clear12 = false; } if (clear11) { rsd.Rank11 = null; rsd.RankPercent11 = null; } if (clear12) { rsd.Rank12 = null; rsd.RankPercent12 = null; } } if (grYear == 1 && stud.LastEnterSemester == 2) { bool clear11 = true; foreach (DAL.UserDefData udd in uddList) { if (udd.GradeYear == 1 && udd.Semester == 1) clear11 = false; } if (clear11) { rsd.Rank11 = null; rsd.RankPercent11 = null; } } // 當沒有輸入全部清空 if (uddList.Count == 0) { if (grYear == 3 && stud.LastEnterSemester == 1) { rsd.Rank22 = null; rsd.Rank21 = null; rsd.Rank12 = null; rsd.Rank11 = null; rsd.RankPercent22 = null; rsd.RankPercent21 = null; rsd.RankPercent12 = null; rsd.RankPercent11 = null; } if (grYear == 2 && stud.LastEnterSemester == 2) { rsd.Rank21 = null; rsd.Rank12 = null; rsd.Rank11 = null; rsd.RankPercent21 = null; rsd.RankPercent12 = null; rsd.RankPercent11 = null; } if (grYear == 2 && stud.LastEnterSemester == 1) { rsd.Rank12 = null; rsd.Rank11 = null; rsd.RankPercent12 = null; rsd.RankPercent11 = null; } if (grYear == 1 && stud.LastEnterSemester == 2) { rsd.Rank11 = null; rsd.RankPercent11 = null; } } } rsd.UpdateDate3 = stud.TransUpdateDateStr; ExportData.Add(rsd); } // 印報表 if (ExportData.Count > 0) { Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook(); wb.Open(new MemoryStream(Properties.Resources._100高雄高中多元入學匯入檔)); Aspose.Cells.Worksheet wst = wb.Worksheets[0]; int RowIdx = 1; foreach (ReportStudentData rsd in ExportData) { // 班級 0 wst.Cells[RowIdx, 0].PutValue(rsd.ClassName); // 座號 1 wst.Cells[RowIdx, 1].PutValue(string.Format("{0:00}",rsd.SeatNo)); // 姓名 2 wst.Cells[RowIdx, 2].PutValue(rsd.Name); // 家長姓名 3 wst.Cells[RowIdx, 3].PutValue(rsd.ParentName); if (rsd.Birthday.HasValue) { // 年 4 wst.Cells[RowIdx, 4].PutValue((rsd.Birthday.Value.Year -1911)); // 月 5 wst.Cells[RowIdx, 5].PutValue(rsd.Birthday.Value.Month); // 日 6 wst.Cells[RowIdx, 6].PutValue(rsd.Birthday.Value.Day); } // 性別 7 wst.Cells[RowIdx, 7].PutValue(rsd.GenderCode); // 身分證字號 8 wst.Cells[RowIdx, 8].PutValue(rsd.IDNumber); // 郵區號 9 wst.Cells[RowIdx, 9].PutValue(rsd.ZipCode); // 聯絡地址 10 wst.Cells[RowIdx, 10].PutValue(rsd.Address); // 電話 11 wst.Cells[RowIdx, 11].PutValue(rsd.Phone); // 手機 12 wst.Cells[RowIdx, 12].PutValue(rsd.CellPhone); // 特種身分(原民未持1、原民認證2、身心障礙3、其他4) 13 if(rsd.SpceTypeCode.HasValue) wst.Cells[RowIdx, 13].PutValue(rsd.SpceTypeCode.Value); else wst.Cells[RowIdx, 13].PutValue(""); // 一上名次 14 if(rsd.Rank11.HasValue) wst.Cells[RowIdx, 15].PutValue(rsd.Rank11.Value); // 一上名次百分比 15 if(rsd.RankPercent11.HasValue ) wst.Cells[RowIdx, 16].PutValue(rsd.RankPercent11.Value); // 一下名次 16 if(rsd.Rank12.HasValue) wst.Cells[RowIdx, 17].PutValue(rsd.Rank12.Value); // 一下名次百分比 17 if(rsd.RankPercent12.HasValue) wst.Cells[RowIdx, 18].PutValue(rsd.RankPercent12.Value); // 二上名次 18 if(rsd.Rank21.HasValue ) wst.Cells[RowIdx, 19].PutValue(rsd.Rank21.Value); // 二上名次百分比 19 if(rsd.RankPercent21.HasValue ) wst.Cells[RowIdx, 20].PutValue(rsd.RankPercent21.Value); // 二下名次 20 if(rsd.Rank22.HasValue) wst.Cells[RowIdx, 21].PutValue(rsd.Rank22.Value); // 二下名次百分比 21 if(rsd.RankPercent22.HasValue) wst.Cells[RowIdx, 22].PutValue(rsd.RankPercent22.Value); // 三上名次 22 if(rsd.Rank31.HasValue) wst.Cells[RowIdx, 23].PutValue(rsd.Rank31.Value); // 三上名次百分比 23 if(rsd.RankPercent31.HasValue ) wst.Cells[RowIdx, 24].PutValue(rsd.RankPercent31.Value); // 五學期名次 24 if(rsd.AvgRank5.HasValue ) wst.Cells[RowIdx, 25].PutValue(rsd.AvgRank5.Value); // 五學期名次百分比 25 if(rsd.AvgRankPercent5.HasValue ) wst.Cells[RowIdx, 26].PutValue(rsd.AvgRankPercent5.Value); // 是否是轉學生1是,否空白 if(!string.IsNullOrEmpty(rsd.UpdateDate3)) wst.Cells[RowIdx, 27].PutValue("1"); RowIdx++; } string FileName = "高中(高雄)用國中匯入檔.xls"; // 儲存檔案 try { string path = Path.Combine(System.Windows.Forms.Application.StartupPath, "Reports"); DirectoryInfo dir = new DirectoryInfo(path); if (!dir.Exists) dir.Create(); path = Path.Combine(path, FileName); if (File.Exists(path)) { int i = 1; while (true) { string newPath = Path.GetDirectoryName(path) + "\\" + Path.GetFileNameWithoutExtension(path) + (i++) + Path.GetExtension(path); if (!File.Exists(newPath)) { path = newPath; break; } } } wb.Save(path, Aspose.Cells.FileFormatType.Excel2003); if (MsgBox.Show(FileName + "產生完成,是否立刻開啟?", MessageBoxButtons.YesNo) == DialogResult.Yes) { System.Diagnostics.Process.Start(path); } } catch (Exception ex) { MsgBox.Show("儲存失敗"); } } }
private void btnImport_Click(object sender, EventArgs e) { Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook(); try { wb.Open(this.textBoxX1.Text); Aspose.Cells.Worksheet ws = wb.Worksheets[0]; int rowIndex = 1; int totalCount = 0; while (ws.Cells[rowIndex, 0].Value != null && !string.IsNullOrWhiteSpace(ws.Cells[rowIndex, 0].Value.ToString())) { rowIndex += 1; totalCount += 1; } this.progressBarX1.Maximum = totalCount; rowIndex = 1; AccessHelper ah = new AccessHelper(); while (ws.Cells[rowIndex, 0].Value != null && !string.IsNullOrWhiteSpace(ws.Cells[rowIndex, 0].Value.ToString())) { string ntu_sys_no = GetCellValue(ws.Cells[rowIndex, 0]); string tea_name = GetCellValue(ws.Cells[rowIndex, 1]); string tea_eng_name = GetCellValue(ws.Cells[rowIndex, 2]); string tea_account = GetCellValue(ws.Cells[rowIndex, 7]); string tea_email = GetCellValue(ws.Cells[rowIndex, 8]); string tea_office_telno = GetCellValue(ws.Cells[rowIndex, 12]); string unit = GetCellValue(ws.Cells[rowIndex, 15]); //string emp_no = ws.Cells[rowIndex, 5].Value.ToString(); K12.Data.TeacherRecord tea = new K12.Data.TeacherRecord(); tea.Name = tea_name; tea.TALoginName = tea_account; tea.Email = tea_email; string newTID = K12.Data.Teacher.Insert(tea); //K12.Data.Teacher.Update(tea); UDT.TeacherExtVO udtTe = new UDT.TeacherExtVO(); udtTe.EnglishName = tea_eng_name; udtTe.TeacherID = int.Parse(newTID); udtTe.NtuSystemNo = ntu_sys_no; //udtTe.EmployeeNo = emp_no; udtTe.OtherPhone = tea_office_telno; udtTe.MajorWorkPlace = unit; List<ActiveRecord> rec = new List<ActiveRecord>(); rec.Add(udtTe); ah.SaveAll(rec); rowIndex += 1; this.labelX1.Text = string.Format("{0} / {1} ", rowIndex.ToString(), totalCount.ToString()); this.progressBarX1.Value = rowIndex; } } catch (Exception ex) { MessageBox.Show(ex.Message); return; } }
private void btnKetXuat_Click(object sender, EventArgs e) { if (grvDanhSachSinhVien.DataRowCount == 0) { ThongBao("Không có dữ liêu."); return; } DataTable dtTemp = dtSinhVien.GetChanges(); if (dtTemp != null) { ThongBao("Dữ liệu đã bị thay đổi. Hay lưu lại trước khi kết xuất."); return; } CreateWaitDialog("Đang xuất dữ liệu, xin vui lòng chờ.", "Xuất dữ liệu"); try { MemoryStream ms = new MemoryStream(Resources.DanhSachMienGiam); var book = new Aspose.Cells.Workbook(); book.Open(ms); var sheet = book.Worksheets[0]; var cells = sheet.Cells; cells["A2"].PutValue("Danh sách đối tượng miễn giảm lớp " + pDM_LopInfo.TenLop); var rIndex = 5; var svFilter = dtSinhVien.Select("IDDM_DoiTuongMienGiam > 0"); for (var i = 0; i < svFilter.Length; i++) { if (i < svFilter.Length - 2) { cells.InsertRow(rIndex + i + 1); } cells["A" + (rIndex + i)].PutValue(i + 1); cells["B" + (rIndex + i)].PutValue(svFilter[i]["MaSinhVien"]); cells["C" + (rIndex + i)].PutValue(svFilter[i]["HoVaTen"]); cells["D" + (rIndex + i)].PutValue(svFilter[i]["NgaySinh"]); cells["E" + (rIndex + i)].PutValue(dicDoiTuongMienGiam[(int)svFilter[i]["IDDM_DoiTuongMienGiam"]]); } SaveFileDialog sDlg = new SaveFileDialog(); sDlg.Title = "Lưu file kết xuất"; sDlg.InitialDirectory = Application.StartupPath; sDlg.FileName = "DanhSachMienGiam_" + DateTime.Now.ToString("yyyyMMddhhmm") + ".xls"; if (sDlg.ShowDialog() != DialogResult.Cancel) { book.Save(sDlg.FileName, Aspose.Cells.FileFormatType.Excel2003); Process.Start(sDlg.FileName); } } catch (Exception ex) { ThongBaoLoi("Có lỗi khi xuất dữ liệu. " + ex.Message); } finally { CloseWaitDialog(); } }
public string ImportTrainPlan() { var user = OperatorProvider.Provider.Current(); if (user.IsSystem) { return("超级管理员无此操作权限"); } string message = "请选择格式正确的文件再导入!"; try { int error = 0; string falseMessage = ""; int count = HttpContext.Request.Files.Count; var currUser = OperatorProvider.Provider.Current(); if (count > 0) { HttpPostedFileBase file = HttpContext.Request.Files[0]; if (string.IsNullOrEmpty(file.FileName)) { return(message); } if (!(file.FileName.Substring(file.FileName.IndexOf('.')).Contains("xls") || file.FileName.Substring(file.FileName.IndexOf('.')).Contains("xlsx"))) { return(message); } string fileName = DateTime.Now.ToString("yyyyMMddHHmmss") + System.IO.Path.GetExtension(file.FileName); file.SaveAs(Server.MapPath("~/Resource/temp/" + fileName)); Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook(); wb.Open(Server.MapPath("~/Resource/temp/" + fileName)); Aspose.Cells.Cells cells = wb.Worksheets[0].Cells; if (cells.MaxDataRow <= 1) { return("共有0条记录,成功导入0条"); } DataTable dt = cells.ExportDataTable(1, 1, cells.MaxDataRow, cells.MaxColumn + 1, true); int order = 1; List <SafeTrainPlanEntity> list = new List <SafeTrainPlanEntity>(); for (int i = 0; i < dt.Rows.Count; i++) { order = i; //培训项目 string projectName = dt.Rows[i]["培训项目"].ToString().Trim(); //培训内容 string content = dt.Rows[i]["培训内容"].ToString().Trim(); //培训对象 string participants = dt.Rows[i]["培训对象"].ToString().Trim(); //培训时间 string trainDate = dt.Rows[i]["培训时间"].ToString().Trim(); //责任部门 string dutyDeptName = dt.Rows[i]["责任部门"].ToString().Trim(); //责任人 string dutyUserName = dt.Rows[i]["责任人"].ToString().Trim(); //执行人/监督人 string executeUserName = dt.Rows[i]["执行人/监督人"].ToString().Trim(); //---****值存在空验证*****-- if (string.IsNullOrEmpty(projectName) || string.IsNullOrEmpty(content) || string.IsNullOrEmpty(participants) || string.IsNullOrEmpty(trainDate) || string.IsNullOrEmpty(dutyDeptName) || string.IsNullOrEmpty(dutyUserName) || string.IsNullOrEmpty(executeUserName)) { falseMessage += "</br>" + "第" + (i + 1) + "行值存在空,未能导入."; error++; continue; } //验证所填部门是否存在 电厂整个组织机构 var entity = departmentBLL.GetList() .Where(t => t.EnCode.StartsWith(currUser.OrganizeCode) && t.EnCode != currUser.OrganizeCode && t.FullName == dutyDeptName) .FirstOrDefault(); string deptCode = string.Empty, deptId = string.Empty; if (entity == null) { falseMessage += "</br>" + "第" + (i + 1) + "行部门信息不存在,未能导入."; error++; continue; } else { deptId = entity.DepartmentId; deptCode = entity.EnCode; } string dutyUserId = string.Empty; string executeUserId = string.Empty; //验证责任人是否存在责任部门下 var uentity = userBLL.GetList().Where(t => t.RealName.Equals(dutyUserName) && t.DepartmentId == entity.DepartmentId).FirstOrDefault(); if (uentity == null) { falseMessage += "</br>" + "第" + (i + 1) + "行责任人不存在,未能导入."; error++; continue; } else { dutyUserId = uentity.UserId; } //验证执行人/监督人是否存在责任部门下 uentity = userBLL.GetList().Where(t => t.RealName.Equals(executeUserName) && t.DepartmentId == entity.DepartmentId).FirstOrDefault(); if (uentity == null) { falseMessage += "</br>" + "第" + (i + 1) + "行执行人/监督人不存在,未能导入."; error++; continue; } else { executeUserId = uentity.UserId; } //验证日期格式 DateTime?trainDate1 = null; try { //yyyy-MM或yyyy.MM trainDate = trainDate.Replace(".", "-"); if (trainDate.Split('-').Length != 2 || trainDate.Split('-')[0].Length != 4) { falseMessage += "</br>" + "第" + (i + 1) + "行培训时间格式不正确,未能导入."; error++; continue; } trainDate1 = Convert.ToDateTime(trainDate); } catch (Exception ex) { falseMessage += "</br>" + "第" + (i + 1) + "行培训时间格式不正确,未能导入."; error++; continue; } SafeTrainPlanEntity safeTrainPlan = new SafeTrainPlanEntity() { Id = Guid.NewGuid().ToString(), ProjectName = projectName, TrainDate = trainDate1, TrainContent = content, Participants = participants, DepartmentId = deptId, DepartmentName = dutyDeptName, DepartmentCode = deptCode, DutyUserId = dutyUserId, DutyUserName = dutyUserName, ExecuteUserId = executeUserId, ExecuteUserName = executeUserName, ProcessState = 0 }; //数据查重 根据培训项目、培训内容、培训对象、培训时间以及责任部门 bool flag = safeTrainPlanBLL.CheckDataExists(safeTrainPlan); if (flag) { //数据重复 falseMessage += "</br>" + "第" + (i + 1) + "行数据已存在,未能导入."; error++; } else { safeTrainPlan.Create(); list.Add(safeTrainPlan); } } if (list.Count() > 0) { //保存 safeTrainPlanBLL.InsertSafeTrainPlan(list); } count = dt.Rows.Count; message = "共有" + count + "条记录,成功导入" + (count - error) + "条,失败" + error + "条"; message += "</br>" + falseMessage; } } catch (Exception ex) { return("导入失败!"); } return(message); }