/// <summary>
/// 누적확률 분포적용 엑셀 다운로드
/// </summary>
public void SetNormDistExcelDown()
{
MicroBSC.BSC.Biz.Biz_Bsc_Term_Detail objBSC = new MicroBSC.BSC.Biz.Biz_Bsc_Term_Detail();
DataSet rDs = objBSC.GetOriginalScorePerKPI(this.IEstTermRefID, this.IYMD);
int intRow = rDs.Tables[0].Rows.Count;
int intCol = rDs.Tables[0].Columns.Count;
if (intRow < 1)
{
return;
}
ExcelEngine xlsEgn = new ExcelEngine(); //Step 2 : Instantiate the excel xlsApp object.
IApplication xlsApp = xlsEgn.Excel;
//A new workbook is created.[Equivalent to creating a new workbook in MS Excel]
//The new workbook will have 3 worksheets
IWorkbook workbook = xlsEgn.Excel.Workbooks.Create(3);
//The first worksheet object in the worksheets collection is accessed.
IWorksheet xlsSht = workbook.Worksheets[0];
string strQrtMark = "\"";
string strColName = "";
string strColEsttermRefID = "";
string strColKpiRefID = "";
string strColYMD = "";
string strColUseMS = "";
string strColOriMS = "";
string strColAvgMS = "";
string strColStdMS = "";
string strColAdaMS = "";
string strColAdsMS = "";
string strColNorMS = "";
string strColFnlMS = "";
string strColUseTS = "";
string strColOriTS = "";
string strColAvgTS = "";
string strColStdTS = "";
string strColAdaTS = "";
string strColAdsTS = "";
string strColNorTS = "";
string strColFnlTS = "";
int itxr_user = gUserInfo.Emp_Ref_ID;
MicroBSC.BSC.Biz.Biz_Bsc_Kpi_Score_Detail objSCR = new MicroBSC.BSC.Biz.Biz_Bsc_Kpi_Score_Detail();
for (int i = 0; i < intCol; i++)
{
strColName = WebCommon.GetExcelColumnName(i) + Convert.ToString(1);
xlsSht.Range[strColName].Text = rDs.Tables[0].Columns[i].ColumnName;
xlsSht.Range[strColName].ColumnWidth = 10;
}
for (int i = 0; i < intRow; i++)
{
for (int j = 0; j < intCol; j++)
{
strColName = WebCommon.GetExcelColumnName(j) + Convert.ToString(i + 2);
switch (rDs.Tables[0].Columns[j].DataType.Name)
{
case ("Decimal"):
xlsSht.Range[strColName].Number = Convert.ToDouble(rDs.Tables[0].Rows[i][j].ToString());
break;
case ("Double"):
xlsSht.Range[strColName].Number = Convert.ToDouble(rDs.Tables[0].Rows[i][j].ToString());
break;
case ("Int16"):
xlsSht.Range[strColName].Number = Convert.ToInt16(rDs.Tables[0].Rows[i][j].ToString());
break;
case ("Int32"):
xlsSht.Range[strColName].Number = Convert.ToInt32(rDs.Tables[0].Rows[i][j].ToString());
break;
case ("Int64"):
xlsSht.Range[strColName].Number = Convert.ToInt64(rDs.Tables[0].Rows[i][j].ToString());
break;
default:
xlsSht.Range[strColName].Text = rDs.Tables[0].Rows[i][j].ToString();
break;
}
if (j == (intCol - 1))
{
strColEsttermRefID = WebCommon.GetExcelColumnName(0) + Convert.ToString(i + 2);
strColKpiRefID = WebCommon.GetExcelColumnName(1) + Convert.ToString(i + 2);
strColYMD = WebCommon.GetExcelColumnName(2) + Convert.ToString(i + 2);
strColUseMS = WebCommon.GetExcelColumnName(3) + Convert.ToString(i + 2);
strColOriMS = WebCommon.GetExcelColumnName(4) + Convert.ToString(i + 2);
strColAvgMS = WebCommon.GetExcelColumnName(5) + Convert.ToString(i + 2);
strColStdMS = WebCommon.GetExcelColumnName(6) + Convert.ToString(i + 2);
strColAdaMS = WebCommon.GetExcelColumnName(7) + Convert.ToString(i + 2);
strColAdsMS = WebCommon.GetExcelColumnName(8) + Convert.ToString(i + 2);
strColNorMS = WebCommon.GetExcelColumnName(9) + Convert.ToString(i + 2);
strColFnlMS = WebCommon.GetExcelColumnName(10) + Convert.ToString(i + 2);
strColUseTS = WebCommon.GetExcelColumnName(11) + Convert.ToString(i + 2);
strColOriTS = WebCommon.GetExcelColumnName(12) + Convert.ToString(i + 2);
strColAvgTS = WebCommon.GetExcelColumnName(13) + Convert.ToString(i + 2);
strColStdTS = WebCommon.GetExcelColumnName(14) + Convert.ToString(i + 2);
strColAdaTS = WebCommon.GetExcelColumnName(15) + Convert.ToString(i + 2);
strColAdsTS = WebCommon.GetExcelColumnName(16) + Convert.ToString(i + 2);
strColNorTS = WebCommon.GetExcelColumnName(17) + Convert.ToString(i + 2);
strColFnlTS = WebCommon.GetExcelColumnName(18) + Convert.ToString(i + 2);
//=IF(IF(D739="N",E739,(85+(J739-0.5)*30))<70,0, IF(D739="N",E739,(85+(J739-0.5)*30))) --최초계산식
//=IF(E11<1,0, IF(D11="N",E11,(85+(J11-0.5)*30)))
//=IF(D739="N",E739, IF(G11=0, 85, (85+(J739-0.5)*30)))
xlsSht.Range[strColNorMS].Formula = "=IF(" + strColStdMS + "=0,0,ROUNDDOWN(NORMDIST(" + strColOriMS + "," + strColAvgMS + "," + strColStdMS + ",TRUE),8))";
xlsSht.Range[strColNorTS].Formula = "=IF(" + strColStdTS + "=0,0,ROUNDDOWN(NORMDIST(" + strColOriTS + "," + strColAvgTS + "," + strColStdTS + ",TRUE),8))";
//xlsSht.Range[strColFnlMS].Formula = "=IF(IF("+strColUseMS+"="+strQrtMark+"N"+strQrtMark+","+strColOriMS+",(85+("+strColNorMS+"-0.5)*30))<70,0, IF("+strColUseMS+"="+strQrtMark+"N"+strQrtMark+","+strColOriMS+",(85+("+strColNorMS+"-0.5)*30)))";
//xlsSht.Range[strColFnlTS].Formula = "=IF(IF("+strColUseMS+"="+strQrtMark+"N"+strQrtMark+","+strColOriTS+",(85+("+strColNorTS+"-0.5)*30))<70,0, IF("+strColUseTS+"="+strQrtMark+"N"+strQrtMark+","+strColOriTS+",(85+("+strColNorTS+"-0.5)*30)))";
//xlsSht.Range[strColFnlMS].Formula = "=IF("+strColStdMS+"=0, 85, IF("+strColUseMS+"="+strQrtMark+"N"+strQrtMark+","+strColOriMS+",(85+("+strColNorMS+"-0.5)*30)))";
//xlsSht.Range[strColFnlTS].Formula = "=IF("+strColStdTS+"=0, 85, IF("+strColUseTS+"="+strQrtMark+"N"+strQrtMark+","+strColOriTS+",(85+("+strColNorTS+"-0.5)*30)))";
xlsSht.Range[strColFnlMS].Formula = "=IF([" + strColUseMS + "]=" + strQrtMark + "N" + strQrtMark + ",[" + strColOriMS + "], IF([" + strColStdMS + "]=0, 85, (85+([" + strColNorMS + "]-0.5)*30)))";
xlsSht.Range[strColFnlTS].Formula = "=IF([" + strColUseTS + "]=" + strQrtMark + "N" + strQrtMark + ",[" + strColOriTS + "], IF([" + strColStdTS + "]=0, 85, (85+([" + strColNorTS + "]-0.5)*30)))";
objSCR.Iestterm_ref_id = Convert.ToInt32(xlsSht.Range[strColEsttermRefID].Text);
objSCR.Ikpi_ref_id = Convert.ToInt32(xlsSht.Range[strColKpiRefID].Text);
objSCR.Iymd = xlsSht.Range[strColYMD].Text;
objSCR.Inrmdst_use_ms = xlsSht.Range[strColUseMS].Text;
objSCR.Ipoints_ori_ms = xlsSht.Range[strColOriMS].Number.ToString();
objSCR.Ipoints_avg_ms = xlsSht.Range[strColAvgMS].Number.ToString();
objSCR.Ipoints_std_ms = xlsSht.Range[strColStdMS].Number.ToString();
objSCR.Ipoints_ada_ms = xlsSht.Range[strColAdaMS].Number.ToString();
objSCR.Ipoints_ads_ms = xlsSht.Range[strColAdsMS].Number.ToString();
objSCR.Ipoints_nor_ms = xlsSht.Range[strColNorMS].Number.ToString();
objSCR.Ipoints_fnl_ms = xlsSht.Range[strColFnlMS].Number.ToString();
objSCR.Inrmdst_use_ts = xlsSht.Range[strColUseTS].Text;
objSCR.Ipoints_ori_ts = xlsSht.Range[strColOriTS].Number.ToString();
objSCR.Ipoints_avg_ts = xlsSht.Range[strColAvgTS].Number.ToString();
objSCR.Ipoints_std_ts = xlsSht.Range[strColStdTS].Number.ToString();
objSCR.Ipoints_ada_ts = xlsSht.Range[strColAdaTS].Number.ToString();
objSCR.Ipoints_ads_ts = xlsSht.Range[strColAdsTS].Number.ToString();
objSCR.Ipoints_nor_ts = xlsSht.Range[strColNorTS].Number.ToString();
objSCR.Ipoints_fnl_ts = xlsSht.Range[strColFnlTS].Number.ToString();
//intRtn = objSCR.InsertData
// ( objSCR.Iestterm_ref_id
// , objSCR.Ikpi_ref_id
// , objSCR.Iymd
// , objSCR.Inormdist_use_yn
// , objSCR.Ipoints_ori_ms
// , objSCR.Ipoints_avg_ms
// , objSCR.Ipoints_std_ms
// , objSCR.Ipoints_ada_ms
// , objSCR.Ipoints_ads_ms
// , objSCR.Ipoints_nor_ms
// , objSCR.Ipoints_fnl_ms
// , objSCR.Ipoints_ori_ms
// , objSCR.Ipoints_avg_ms
// , objSCR.Ipoints_std_ms
// , objSCR.Ipoints_ada_ms
// , objSCR.Ipoints_ads_ms
// , objSCR.Ipoints_nor_ms
// , objSCR.Ipoints_fnl_ms
// , itxr_user);
//objBSC.Iestterm_ref_id = sheet.Range[strColEsttermRefID].FormulaNumberValue.ToString();
//string strTmp = xlsSht.Range[strColNorm].FormulaNumberValue.ToString();
}
}
}
# region Static Data
# region Formatting results
//IStyle headerStyle = workbook.Styles.Add("Heading", null);
////Add custom colors to the palette.
//workbook.SetPaletteColor(8, Color.FromArgb(255, 174, 33));
//headerStyle.Color = Color.FromArgb(255, 174, 33);
//headerStyle.Font.Bold = true;
//headerStyle.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin;
//headerStyle.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin;
//headerStyle.Borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.Thin;
//headerStyle.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thin;
////Body Style
//IStyle bodyStyle = workbook.Styles.Add("BodyStyle");
////Add custom colors to the palette.
//workbook.SetPaletteColor(9, Color.FromArgb(239, 243, 247));
//workbook.SetPaletteColor(10, Color.FromArgb(204, 212, 230));
//bodyStyle.Color = Color.FromArgb(239, 243, 247);
//bodyStyle.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin;
//bodyStyle.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin;
//bodyStyle.Borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.Thin;
//bodyStyle.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thin;
//bodyStyle.Borders.ColorRGB = Color.FromArgb(204, 212, 230);
//bodyStyle.HorizontalAlignment = ExcelHAlign.HAlignLeft;
////Formatting Results
//xlsSht.Range["A1:B144"].CellStyleName = "BodyStyle";
//xlsSht.Range["A1:A2"].CellStyleName = "Heading";
//xlsSht.Range["B2"].CellStyleName = "Heading";
//xlsSht.Range["A21"].CellStyleName = "Heading";
//xlsSht.Range["B21"].CellStyleName = "Heading";
# endregion Formatting Reults
# endregion