public ActionResult DownExcelProd(PROD_EXCEL model)
{
//권한 체크===================================================
if (!chk.chkPermission("ProdList", "PER_SELECT"))
{
return(RedirectToAction("Index", "Home", new { msg = chk.alertStr }));
}
//===========================================================
MemoryStream stream = act.DownExcelProd(model);
return(File(stream, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "SamPle.xlsx"));
}
public MemoryStream DownExcelProd(PROD_EXCEL model)
{
MemoryStream stream = new MemoryStream();
using (ExcelPackage excelPackage = new ExcelPackage())
{
ExcelWorksheet workSheet = excelPackage.Workbook.Worksheets.Add("SearchResult");
int row = 1;
int col = 1;
// 첫줄
workSheet.Cells[row, col++].Value = comm_global.Language.Resources.ESE_Comm_Barcord;
workSheet.Cells[row, col++].Value = comm_global.Language.Resources.ESE_Comm_PRODUCT_NAME;
workSheet.Cells[row, col++].Value = comm_global.Language.Resources.ESE_ProdAdd_PRODUCT_NAME_EN;
workSheet.Cells[row, col++].Value = comm_global.Language.Resources.ESE_ProdAdd_PRODUCT_NAME_CN;
workSheet.Cells[row, col++].Value = comm_global.Language.Resources.ESE_ProdAdd_PRODUCT_NAME_KR;
workSheet.Cells[row, col++].Value = comm_global.Language.Resources.ESE_ProdAdd_SKU;
workSheet.Cells[row, col++].Value = comm_global.Language.Resources.ESE_ProdAdd_BRAND;
workSheet.Cells[row, col++].Value = comm_global.Language.Resources.ESE_ProdAdd_UNIT_WEIGHT; //더블
workSheet.Cells[row, col++].Value = comm_global.Language.Resources.ESE_ProdAdd_viewWEIGHT_UNIT; //더블
workSheet.Cells[row, col++].Value = comm_global.Language.Resources.ESE_ProdAdd_STANDARD;
workSheet.Cells[row, col++].Value = comm_global.Language.Resources.ESE_ProdAdd_EXPIRATION; //int
workSheet.Cells[row, col++].Value = comm_global.Language.Resources.ESE_ProdAdd_ORIGIN;
workSheet.Cells[row, col++].Value = comm_global.Language.Resources.ESE_ProdAdd_INGREDIENT;
workSheet.Cells[row, col++].Value = comm_global.Language.Resources.ESE_ProdAdd_SPEC;
workSheet.Cells[row, col++].Value = comm_global.Language.Resources.ESE_ProdAdd_SALE_SITE_URL;
workSheet.Cells[row, col++].Value = comm_global.Language.Resources.ESE_ProdAdd_PRODUCT_IMAGE;
workSheet.Cells[row, 1, row, (col - 1)].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
workSheet.Cells[row, 1, row, (col - 1)].Style.Fill.PatternType = ExcelFillStyle.Solid;
workSheet.Cells[row, 1, row, (col - 1)].Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.LightGray);
workSheet.Cells.AutoFitColumns();
excelPackage.SaveAs(stream);
}
stream.Flush();
stream.Position = 0;
return(stream);
}
public PROD_EXCEL UploadExcelProd(PROD_EXCEL model)
{
HttpContext context = HttpContext.Current;
string eseCode = context.Session["ESE_CODE"].ToString();
string estCode = context.Session["EST_CODE"].ToString();
model.result = true;
string error_str = "";
GlobalFunction comModel = new GlobalFunction();
DataTable data = comModel.getUploadExcelData(model.File.FILE, out error_str);
model.errList = new List <string>();
if (error_str != "")
{
model.errList.Add(error_str);
model.result = false;
return(model);
}
//유효성 검사
if (data != null && data.Rows.Count != 0)
{
for (int i = 0; i < data.Rows.Count; i++)
{
// 0,1 필수 :: 7, 8 더블 :: 10 인트
if (data.Rows[i][0].ToString().Trim() == "") //필수값 체크
{
model.errList.Add("[" + i + "] - " + comm_global.Language.Resources.Script_RequiredInput + "[" + comm_global.Language.Resources.ESE_Comm_Barcord + "]");
error_str = "error";
}
if (data.Rows[i][1].ToString().Trim() == "") //필수값 체크
{
model.errList.Add("[" + i + "] - " + comm_global.Language.Resources.Script_RequiredInput + "[" + comm_global.Language.Resources.ESE_Comm_PRODUCT_NAME + "]");
error_str = "error";
}
try
{
Convert.ToDouble(data.Rows[i][7].ToString().Trim()); //더블형 체크
}
catch
{
model.errList.Add("[" + i + "] - " + comm_global.Language.Resources.Script_DoubleInput + "[" + comm_global.Language.Resources.ESE_ProdAdd_UNIT_WEIGHT + "]");
error_str = "error";
}
/*
* try
* {
* Convert.ToDouble(data.Rows[i][8].ToString().Trim()); //더블형 체크
* }
* catch
* {
* model.errList.Add("[" + i + "] - " + comm_global.Language.Resources.Script_DoubleInput + "[" + comm_global.Language.Resources.ESE_ProdAdd_viewWEIGHT_UNIT + "]");
* error_str = "error";
* }
*/
try
{
int.Parse(data.Rows[i][10].ToString().Trim()); //인트 체크
}
catch
{
model.errList.Add("[" + i + "] - " + comm_global.Language.Resources.Script_IntInput + "[" + comm_global.Language.Resources.ESE_ProdAdd_EXPIRATION + "]");
error_str = "error";
}
string str_queryErr = "";
//바코드 중복 체크
string chkQuery = " SELECT COUNT(*) as cnt FROM stc_goods WHERE ESE_CODE = '" + eseCode + "' AND BARCODE = '" + data.Rows[i][0].ToString().Trim() + "' ";
if (getQueryCnt(chkQuery, out str_queryErr) > 0)
{
model.errList.Add("[" + i + "] - " + comm_global.Language.Resources.Script_OverLappedProd);
error_str = "error";
}
}
}
if (error_str == "error")
{
model.result = false;
return(model);
}
string exeQueryStr = "";
List <string> linqList = new List <string>();
string tmpQuery = "";
exeQueryStr = " INSERT INTO stc_goods (EST_CODE, ESE_CODE, BARCODE, PRODUCT_NAME, PRODUCT_NAME_KR, PRODUCT_NAME_CN, PRODUCT_NAME_EN ,SKU, BRAND, UNIT_WEIGHT, WEIGHT_UNIT, STANDARD, EXPIRATION, ORIGIN, INGREDIENT, SPEC, SALE_SITE_URL, PRODUCT_IMAGE ) VALUES ";
if (data != null && data.Rows.Count != 0)
{
for (int i = 0; i < data.Rows.Count; i++)
{
// 0,1 필수 :: 7, 8 더블 :: 10 인트
tmpQuery = "( '" + estCode + "', " + "'" + eseCode + "'";
tmpQuery += ", '" + data.Rows[i][0].ToString().Trim() + "'";
tmpQuery += ", '" + data.Rows[i][1].ToString().Trim() + "'";
tmpQuery += ", '" + data.Rows[i][2].ToString().Trim() + "'";
tmpQuery += ", '" + data.Rows[i][3].ToString().Trim() + "'";
tmpQuery += ", '" + data.Rows[i][4].ToString().Trim() + "'";
tmpQuery += ", '" + data.Rows[i][5].ToString().Trim() + "'";
tmpQuery += ", '" + data.Rows[i][6].ToString().Trim() + "'";
tmpQuery += ", " + data.Rows[i][7].ToString().Trim();
tmpQuery += ", '" + data.Rows[i][8].ToString().Trim() + "'";
tmpQuery += ", '" + data.Rows[i][9].ToString().Trim() + "'";
tmpQuery += ", " + data.Rows[i][10].ToString().Trim();
tmpQuery += ", '" + data.Rows[i][11].ToString().Trim() + "'";
tmpQuery += ", '" + data.Rows[i][12].ToString().Trim() + "'";
tmpQuery += ", '" + data.Rows[i][13].ToString().Trim() + "'";
tmpQuery += ", '" + data.Rows[i][14].ToString().Trim() + "'";
tmpQuery += ", '" + data.Rows[i][15].ToString().Trim() + "' )";
linqList.Add(tmpQuery);
}
}
exeQueryStr += string.Join(",", linqList);
model.result = exeQuery(exeQueryStr, out error_str);
return(model);
}
