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