コード例 #1
0
    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();
        }
    }