public ActionResult GetValidateExcel(DataAccess.StandardModel model, string PRG_CODE) { //string PRG_CODE = "ZQM013P";//SessionHelper.SYS_CurrentPRG_CODE; //GetData DataAccess.EXC001.EXC001DA da = new DataAccess.EXC001.EXC001DA(); SetStandardErrorLog(da.DTO); da.DTO.Execute.ExecuteType = DataAccess.EXC001.EXC001ExecuteType.GetQueryAllVal; da.DTO.Model.COM_CODE = SessionHelper.SYS_COM_CODE; da.DTO.Model.PRG_CODE = PRG_CODE; da.Select(da.DTO); EXC001Model result = WEBAPP.Helper.ExcelData.ValidateExcel(model.EXCEL_UPLOAD[0].File.ToArrayByte(), model.EXCEL_UPLOAD[0].FILE_NAME, SessionHelper.SYS_COM_CODE, PRG_CODE, da.DTO.Models); return(JsonAllowGet(result)); }
public static EXC001Model Open_ExcelPop(string procode, string filename) { //!!--ไม่จำเป็นต้องแก้ มาจาก Themeplate string total = ""; string complete = ""; string error = ""; DataSet ds = new DataSet("open_popup"); ds = GetResultExcelByRank(procode, 0, 1); string strCol = GenerateColumn110px(ds.Tables[0]); string strModel = GenerateModel(ds.Tables[0]); if (ds.Tables[1] != null && ds.Tables[1].Rows.Count > 0) { //filename = fileName; //field filename total = ds.Tables[1].Rows[0][1].ToString(); complete = ds.Tables[1].Rows[0][2].ToString(); error = ds.Tables[1].Rows[0][3].ToString(); } //List<Dictionary<object, string>> lisdic = new List<Dictionary<object, string>>(); //Dictionary<object, string> dic = new Dictionary<object, string>(); EXC001Model lisdic = new EXC001Model(); lisdic.filename = filename; lisdic.total = total; lisdic.error = error; lisdic.complete = complete; lisdic.callback = ""; lisdic.exportparam = "excel"; lisdic.columns = strCol; lisdic.model = strModel; lisdic.continueUrl = ""; lisdic.procode = procode.Trim(); lisdic.control_id = ""; //Tab Summary lisdic.data_sum = ""; lisdic.model_sum = ""; lisdic.columns_sum = ""; lisdic.colover = _colover; return(lisdic); }
public static EXC001Model ValidateExcel(byte[] File, string FileName, string COM_CODE, string PRG_CODE, List <EXC001Model> dsVal, int SheetIndex = 1) { DataTable dsData = new DataTable(); EXC001Model result = new EXC001Model(); try { MemoryStream ms = new MemoryStream(File); using (ExcelPackage xlPackage = new ExcelPackage(ms)) { dsData = new DataTable(); ExcelWorksheet worksheet = xlPackage.Workbook.Worksheets[SheetIndex]; if (worksheet != null) { ExcelCellAddress startCell = worksheet.Dimension.Start; ExcelCellAddress endCell = worksheet.Dimension.End; string sColName = ""; for (int col = startCell.Column; col <= endCell.Column; col++) { if (worksheet.Cells[1, col].Value != null) { sColName = worksheet.Cells[1, col].Value.ToString(); //dsData.Columns.Add(sColName.Substring(0, sColName.ToString().Length - 1)); dsData.Columns.Add(sColName); } } for (int row = (startCell.Row + 1); row <= endCell.Row; row++) { DataRow dr = dsData.NewRow(); for (int col = startCell.Column; col <= endCell.Column; col++) { if (worksheet.Cells[row, col].Value != null) { dr[col - 1] = worksheet.Cells[row, col].Value; } } dsData.Rows.Add(dr); } dsData.TableName = worksheet.Name; } } for (int i = 0; i < dsData.Columns.Count; i++) { for (int ii = 0; ii < dsVal.Count; ii++) { if ((i + 1).ToString() == dsVal[ii].LIST_NO.ToString()) { try { dsData.Columns[i].ColumnName = dsVal[ii].COL_NAME.ToString().ToUpper(); } catch (Exception) { } continue; } } } int ress = ValidateExcelDataType(PRG_CODE, dsData, dsVal); if (ress != 99) { // OpenPopup(page, PrgCode, fi.Name, url); result = Open_ExcelPop(PRG_CODE, FileName); } UPLOAD_FILENAME = FileName; HttpContext.Current.Session["json_param"] = null; HttpContext.Current.Session.Add("json_param", result); } catch (Exception ex) { throw ex; } return(result); }