protected void iBtnExcelImport_Click(object sender, ImageClickEventArgs e)
{
if (fldExcelFile.PostedFile.FileName.Equals(""))
{
// 업로드하실 엑셀파일을 선택해주세요.
ltrScript.Text = JSHelper.GetAlertScript("업로드하실 엑셀파일을 선택해주세요.", false);
return;
}
System.IO.Stream fileStream = null;
try
{
fileStream = fldExcelFile.PostedFile.InputStream;
}
catch (Exception ex)
{
// 업로드 중 오류가 발생하였습니다.
ltrScript.Text = JSHelper.GetAlertScript("업로드 중 오류가 발생하였습니다.", false);
return;
}
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
//application.UseNativeStorage = false; //user sync version 8...
IWorkbook workbook = null;
workbook = excelEngine.Excel.Workbooks.Open(fldExcelFile.PostedFile.InputStream);
/*
* try
* {
* workbook = excelEngine.Excel.Workbooks.Open(fldExcelFile.PostedFile.InputStream);
* }
* catch (Exception ex)
* {
* // 엑셀형식의 파일이 아닙니다.
* Response.Write(ex.Message);
* ltrScript.Text = JSHelper.GetAlertScript("엑셀형식의 파일이 아닙니다.", false);
* excelEngine.ThrowNotSavedOnDestroy = false;
* excelEngine.Dispose();
* return;
* }
*/
IWorksheet sheet = workbook.Worksheets[0];
if (sheet.Rows.Length < 3)
{
// 빈 엑셀 파일입니다.
ltrScript.Text = JSHelper.GetAlertScript("빈 엑셀 파일입니다.", false);
excelEngine.ThrowNotSavedOnDestroy = false;
excelEngine.Dispose();
return;
}
try
{
for (int i = 0; i < sheet.Columns.Length; i++)
{
string columnKey = string.Format("{0}2", DataTypeUtility.GetInt32ToAlphabet(i + 1));
sheet.Range[columnKey].Text = sheet.Range[columnKey].AddComment().Text;
}
DataTable ExcelUnitUploadData = new DataTable();
ExcelUnitUploadData.Columns.Add("EMP_REF_ID", typeof(int));
ExcelUnitUploadData.Columns.Add("EST_DEPT_REF_ID", typeof(string));
ExcelUnitUploadData.Columns.Add("DEPT_NAME", typeof(string));
ExcelUnitUploadData.Columns.Add("POS_CLS_ID", typeof(string));
ExcelUnitUploadData.Columns.Add("POS_CLS_NAME", typeof(string));
ExcelUnitUploadData.Columns.Add("POS_GRP_ID", typeof(string));
ExcelUnitUploadData.Columns.Add("POS_GRP_NAME", typeof(string));
ExcelUnitUploadData.Columns.Add("EMP_CODE", typeof(string));
ExcelUnitUploadData.Columns.Add("EMP_NAME", typeof(string));
ExcelUnitUploadData.Columns.Add("ORGANIZATION_POINT", typeof(double));
ExcelUnitUploadData.Columns.Add("ORGANIZATION_WEIGHT", typeof(int));
ExcelUnitUploadData.Columns.Add("APPRAISAL_POINT", typeof(double));
ExcelUnitUploadData.Columns.Add("APPRAISAL_WEIGHT", typeof(int));
ExcelUnitUploadData.Columns.Add("OTHERS1_POINT", typeof(double));
ExcelUnitUploadData.Columns.Add("OTHERS1_WEIGHT", typeof(int));
ExcelUnitUploadData.Columns.Add("OTHERS2_POINT", typeof(double));
ExcelUnitUploadData.Columns.Add("OTHERS2_WEIGHT", typeof(int));
ExcelUnitUploadData.Columns.Add("OTHERS3_POINT", typeof(double));
ExcelUnitUploadData.Columns.Add("OTHERS3_WEIGHT", typeof(int));
ExcelUnitUploadData.Columns.Add("WEIGHT_SUM", typeof(int));
ExcelUnitUploadData.Columns.Add("POINT_SUM", typeof(double));
DataTable _tmpTable = sheet.ExportDataTable(2, 1, sheet.Rows.Length, 19, ExcelExportDataTableOptions.ColumnNames | ExcelExportDataTableOptions.ComputedFormulaValues);
_iestterm_ref_id = PageUtility.GetIntByValueDropDownList(ddlEstTermInfo);
_iymd = PageUtility.GetByValueDropDownList(ddlEstTermMonth, "");
DataTable _estData1 = new DataTable();
DataTable _estData2 = new DataTable();
Biz_Datas est_data = new Biz_Datas();
_estData1 = est_data.GetPersonEavluation(COMP_ID, _iestterm_ref_id, _iymd).Tables[0];
_estData2 = est_data.GetPersonEavluationPoint(COMP_ID, _iestterm_ref_id, _iymd).Tables[0];
if (_tmpTable.Rows.Count > 1 && _estData2.Rows.Count > 1)
{
DataRow[] _setRow;
DataRow[] _setRow2;
foreach (DataRow _pointRow in _tmpTable.Rows)
{
if (_pointRow["EST_DEPT_REF_ID"] != null && !_pointRow["EST_DEPT_REF_ID"].ToString().Equals(""))
{
_setRow = _estData2.Select("EST_DEPT_REF_ID = " + _pointRow["EST_DEPT_REF_ID"]);
if (_setRow.Length == 1)
{
_pointRow["ORGANIZATION_POINT"] = _setRow[0]["ORGANIZATION_POINT"];
}
else
{
_pointRow["ORGANIZATION_POINT"] = 0;
}
_setRow2 = _estData1.Select("EMP_REF_ID = " + _pointRow["EMP_REF_ID"]);
if (_setRow2.Length == 1)
{
_pointRow["POS_GRP_ID"] = _setRow2[0]["POS_GRP_ID"].ToString();
}
}
}
_tmpTable.AcceptChanges();
}
foreach (DataRow _dr in _tmpTable.Rows)
{
if (_dr["EST_DEPT_REF_ID"] != null && !_dr["EST_DEPT_REF_ID"].ToString().Equals(""))
{
DataRow _drMain = ExcelUnitUploadData.NewRow();
_drMain["EMP_REF_ID"] = _dr["EMP_REF_ID"].ToString();
_drMain["EST_DEPT_REF_ID"] = _dr["EST_DEPT_REF_ID"].ToString();
_drMain["DEPT_NAME"] = _dr["DEPT_NAME"].ToString();
_drMain["POS_CLS_ID"] = _dr["POS_CLS_ID"].ToString();
_drMain["POS_CLS_NAME"] = _dr["POS_CLS_NAME"].ToString();
_drMain["POS_GRP_ID"] = _dr["POS_GRP_ID"].ToString();
_drMain["POS_GRP_NAME"] = _dr["POS_GRP_NAME"].ToString();
_drMain["EMP_CODE"] = _dr["EMP_CODE"].ToString();
_drMain["EMP_NAME"] = _dr["EMP_NAME"].ToString();
_drMain["ORGANIZATION_POINT"] = _dr["ORGANIZATION_POINT"].ToString();
_drMain["ORGANIZATION_WEIGHT"] = Convert.ToInt32(Convert.ToDouble(_dr["ORGANIZATION_WEIGHT"]));
_drMain["APPRAISAL_POINT"] = DataTypeUtility.GetToDouble(_dr["APPRAISAL_POINT"]);
_drMain["APPRAISAL_WEIGHT"] = Convert.ToInt32(Convert.ToDouble(_dr["APPRAISAL_WEIGHT"]));
_drMain["OTHERS1_POINT"] = DataTypeUtility.GetToDouble(_dr["OTHERS1_POINT"]);
_drMain["OTHERS1_WEIGHT"] = Convert.ToInt32(Convert.ToDouble(_dr["OTHERS1_WEIGHT"]));
_drMain["OTHERS2_POINT"] = DataTypeUtility.GetToDouble(_dr["OTHERS2_POINT"]);
_drMain["OTHERS2_WEIGHT"] = Convert.ToInt32(Convert.ToDouble(_dr["OTHERS2_WEIGHT"]));
_drMain["OTHERS3_POINT"] = DataTypeUtility.GetToDouble(_dr["OTHERS3_POINT"]);
_drMain["OTHERS3_WEIGHT"] = Convert.ToInt32(Convert.ToDouble(_dr["OTHERS3_WEIGHT"]));
_drMain["WEIGHT_SUM"] = Convert.ToInt32(Convert.ToDouble(_dr["ORGANIZATION_WEIGHT"]))
+ Convert.ToInt32(Convert.ToDouble(_dr["APPRAISAL_WEIGHT"]))
+ Convert.ToInt32(Convert.ToDouble(_dr["OTHERS1_WEIGHT"]))
+ Convert.ToInt32(Convert.ToDouble(_dr["OTHERS2_WEIGHT"]))
+ Convert.ToInt32(Convert.ToDouble(_dr["OTHERS3_WEIGHT"]));
_drMain["POINT_SUM"] = DataTypeUtility.GetToDouble(_dr["ORGANIZATION_POINT"])
+ DataTypeUtility.GetToDouble(_dr["APPRAISAL_POINT"])
+ DataTypeUtility.GetToDouble(_dr["OTHERS1_POINT"])
+ DataTypeUtility.GetToDouble(_dr["OTHERS2_POINT"])
+ DataTypeUtility.GetToDouble(_dr["OTHERS3_POINT"]);
ExcelUnitUploadData.Rows.Add(_drMain);
}
}
ugrdResultTotal.Clear();
ugrdResultTotal.DataSource = ExcelUnitUploadData;
ugrdResultTotal.DataBind();
foreach (UltraGridRow _ugr in ugrdResultTotal.Rows)
{
if (Convert.ToDouble(_ugr.Cells.FromKey("WEIGHT_SUM").Value) != 100)
{
_ugr.Cells.FromKey("WEIGHT_SUM").Style.ForeColor = Color.Red;
}
}
excelEngine.ThrowNotSavedOnDestroy = false;
excelEngine.Dispose();
}
catch (Exception ex)
{
Response.Write(ex);
ltrScript.Text = JSHelper.GetAlertScript("업로드 중 오류가 발생하였습니다.", false);
excelEngine.ThrowNotSavedOnDestroy = false;
excelEngine.Dispose();
}
}