지식정보 :: C# 에서 엑셀 표현하기

C# with WinForm

C# 에서 엑셀 표현하기

2008/09/24 14:46 | Posted by 희달이

C# with WinForm에서 엑셀을 표한하는 방법에 대해서 간단히 설명해드리겠습니다.

   

   

먼저 네임스페이스를 정의를 해야댑니다.

   

using Microsoft.Office.Interop.Excel;

using System.Reflection;

using System.Runtime.InteropServices;

   

   

요기서  Microsoft.Office.Interop.Excel 네임스페이스가 없다고 하시는분은 참조에서

com 에서 Microsoft Office 12.0 Object Library  혹은 Microsoft Office 11.0 Object Library  을 추가해 주시면됩니다..

   

네임스페이스를 추가하셨다면  다음과 같이 코딩을 하시면되고요

   

   

 //엑셀(워크북) 변수선언

            Microsoft.Office.Interop.Excel.Workbook workbook = null;

   

            //엑셀 시트 변수 선언

            Microsoft.Office.Interop.Excel.Worksheet worksheet = null;

            //엑셀 실행하는 변수선언

            Microsoft.Office.Interop.Excel.Application application = null;

            //엑셀 위치 나타내는 변수선언

            Microsoft.Office.Interop.Excel.Range[] range = new Range[65];

   

            //실행 번수 생성

            application = new Microsoft.Office.Interop.Excel.Application();

   

            //워크북추가

            workbook = application.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);

            //1번쨰 워크시트 지성

            worksheet = workbook.Worksheets[1] as   Microsoft.Office.Interop.Excel.Worksheet;

            //엑셀 활성화

            application.Visible = true;

   

//엑셀에 나타낼 값 위치 지정

            string nCells1 = "개인별 급여자료";

            range[0] = worksheet.get_Range("B1", Missing.Value);

            range[0].Value2 = nCells1;

            string nCells3 = "지사명 : " + ds.Tables[0].Rows[0]["branch_name"].ToString();

            range[1] = worksheet.get_Range("A2", Missing.Value);

            range[1].Value2 = nCells3;

            string nCells2 = "";

            if (tbxDegreeFrom.Text == "" && tbxDegreeTo.Text == "")

            {

                nCells2 = cmbDateType.SelectedDisplayText + ": " + tbxDateStart.Text + " ~ " + tbxDateEnd.Text;

                range[2] = worksheet.get_Range("A3",  Missing.Value);

                range[2].Value2 = nCells2;

            }

            else if (tbxDateStart.Text == "" && tbxDateEnd.Text == "")

            {

                if (tbxDegreeTo.Text == tbxDegreeFrom.Text)

                {

                    nCells2 = "차수: " + tbxDegreeFrom.Text;

                }

                else

                {

                    nCells2 = "차수: " + tbxDegreeFrom.Text + " ~ " + tbxDegreeTo.Text;

                }

                range[2] = worksheet.get_Range("A3", Missing.Value);

                range[2].Value2 = nCells2;

            }

            else

            {

                if (tbxDegreeTo.Text == tbxDegreeFrom.Text)

                {

                    nCells2 = cmbDateType.SelectedDisplayText + ": " + tbxDateStart.Text + " ~ " + tbxDateEnd.Text + "차수 :" + tbxDegreeFrom.Text;

                }

                else

                {

                    nCells2 = cmbDateType.SelectedDisplayText + ": " + tbxDateStart.Text + " ~ " + tbxDateEnd.Text + "차수 :" + tbxDegreeFrom.Text + " ~ " + tbxDegreeTo.Text;

                }

                range[2] = worksheet.get_Range("A3", Missing.Value);

                range[2].Value2 = nCells2;

            }

             

   

            string nCells4 = "배송사원명 : " + ds.Tables[0].Rows[0]["career_name"].ToString();

            range[3] = worksheet.get_Range("C2", Missing.Value);

            range[3].Value2 = nCells4;

            string nCells5 = "배송분류";

            range[4] = worksheet.get_Range("A4", Missing.Value);

            range[4].Value2 = nCells5;

            string nCells9 = "일반";

            range[5] = worksheet.get_Range("A5", Missing.Value);

            range[5].Value2 = nCells9;

            string nCells10 = "동의서";

            range[6] = worksheet.get_Range("A8", Missing.Value);

            range[6].Value2 = nCells10;

            string nCells11 = "긴급";

            range[7] = worksheet.get_Range("A11", Missing.Value);

            range[7].Value2 = nCells11;

            string nCells12 = "플래티늄";

            range[8] = worksheet.get_Range("A14", Missing.Value);

            range[8].Value2 = nCells12;

            string nCells13 = "퀵";

            range[9] = worksheet.get_Range("A17", Missing.Value);

            range[9].Value2 = nCells13;

            string nCells14 = "지점";

            range[10] = worksheet.get_Range("A20", Missing.Value);

            range[10].Value2 = nCells14;

            string nCells15 = "갱신";

            range[11] = worksheet.get_Range("A23", Missing.Value);

            range[11].Value2 = nCells15;

            string nCells16 = "총계";

            range[12] = worksheet.get_Range("A26", Missing.Value);

            range[12].Value2 = nCells16;

            string nCells6 = "항목";

            range[13] = worksheet.get_Range("B4", Missing.Value);

            range[13].Value2 = nCells6;

            string nCells17 = "배송량";

            range[14] = worksheet.get_Range("B5", Missing.Value);

            range[14].Value2 = nCells17;

            range[17] = worksheet.get_Range("B8", Missing.Value);

            range[17].Value2 = nCells17;

            range[20] = worksheet.get_Range("B11", Missing.Value);

            range[20].Value2 = nCells17;

            range[23] = worksheet.get_Range("B14", Missing.Value);

            range[23].Value2 = nCells17;

            range[26] = worksheet.get_Range("B17", Missing.Value);

            range[26].Value2 = nCells17;

            range[29] = worksheet.get_Range("B20", Missing.Value);

            range[29].Value2 = nCells17;

            range[32] = worksheet.get_Range("B23", Missing.Value);

            range[32].Value2 = nCells17;

            string nCells18 = "단가";

            range[15] = worksheet.get_Range("B6", Missing.Value);

            range[15].Value2 = nCells18;

            range[18] = worksheet.get_Range("B9", Missing.Value);

            range[18].Value2 = nCells18;

            range[21] = worksheet.get_Range("B12", Missing.Value);

            range[21].Value2 = nCells18;

            range[24] = worksheet.get_Range("B15", Missing.Value);

            range[24].Value2 = nCells18;

            range[27] = worksheet.get_Range("B18", Missing.Value);

            range[27].Value2 = nCells18;

            range[30] = worksheet.get_Range("B21", Missing.Value);

            range[30].Value2 = nCells18;

            range[33] = worksheet.get_Range("B24", Missing.Value);

            range[33].Value2 = nCells18;

   

            string nCells19 = "금액";

            range[16] = worksheet.get_Range("B7", Missing.Value);

            range[16].Value2 = nCells19;

            range[19] = worksheet.get_Range("B10", Missing.Value);

            range[19].Value2 = nCells19;

            range[22] = worksheet.get_Range("B13", Missing.Value);

            range[22].Value2 = nCells19;

            range[25] = worksheet.get_Range("B16", Missing.Value);

            range[25].Value2 = nCells19;

            range[28] = worksheet.get_Range("B19", Missing.Value);

            range[28].Value2 = nCells19;

            range[31] = worksheet.get_Range("B22", Missing.Value);

            range[31].Value2 = nCells19;

            range[34] = worksheet.get_Range("B25", Missing.Value);

            range[34].Value2 = nCells19;

   

            string nCells20 = "총배송량";           

            range[35] = worksheet.get_Range("B26", Missing.Value);

            range[35].Value2 = nCells20;

            string nCells21 = "배송총금액";

            range[36] = worksheet.get_Range("B27", Missing.Value);

            range[36].Value2 = nCells21;

            string nCells22 = "재방량";

            range[37] = worksheet.get_Range("B28", Missing.Value);

            range[37].Value2 = nCells22;

            string nCells23 = "반송량";

            range[38] = worksheet.get_Range("B29", Missing.Value);

            range[38].Value2 = nCells23;

            string nCells24 = "분실량";

            range[39] = worksheet.get_Range("B30", Missing.Value);

            range[39].Value2 = nCells24;

            string nCells25 = "차감금액";

            range[40] = worksheet.get_Range("B31", Missing.Value);

            range[40].Value2 = nCells25;

            string nCells26 = "증가금액";

            range[41] = worksheet.get_Range("B32", Missing.Value);

            range[41].Value2 = nCells26;

            string nCells27 = "지불총금액";

            range[42] = worksheet.get_Range("B33", Missing.Value);

            range[42].Value2 = nCells27;

            string nCells7 = "내용";

            range[43] = worksheet.get_Range("C4", Missing.Value);

            range[43].Value2 = nCells7;

   

            string nCells29 = ds.Tables[0].Rows[0]["delivery_count"].ToString();

            range[44] = worksheet.get_Range("C5", Missing.Value);

            range[46] = worksheet.get_Range("C8", Missing.Value);

            range[48] = worksheet.get_Range("C11", Missing.Value);

            range[50] = worksheet.get_Range("C14", Missing.Value);

            range[52] = worksheet.get_Range("C17", Missing.Value);

            range[54] = worksheet.get_Range("C20", Missing.Value);

            range[56] = worksheet.get_Range("C23", Missing.Value);

   

            if (ds.Tables[0].Rows[0]["kind_name"].ToString() == "일반")

            {

                range[44].Value2 = nCells29;

            }

            else

            {

                range[44].Value2 = "0";

            }

            if (ds.Tables[0].Rows[0]["kind_name"].ToString() == "동의서")

            {

                range[46].Value2 = nCells29;

            }

            else

            {

                range[46].Value2 = "0";

            }

            if (ds.Tables[0].Rows[0]["kind_name"].ToString() == "긴급")

            {

                range[48].Value2 = nCells29;

            }

            else

            {

                range[48].Value2 = "0";

            }

            if (ds.Tables[0].Rows[0]["kind_name"].ToString() == "플래티늄")

            {

                range[50].Value2 = nCells29;

            }

            else

            {

                range[50].Value2 = "0";

            }

            if (ds.Tables[0].Rows[0]["kind_name"].ToString() == "퀵")

            {

                range[52].Value2 = nCells29;

            }

            else

            {

                range[52].Value2 = "0";

            }

            if (ds.Tables[0].Rows[0]["kind_name"].ToString() == "지점")

            {

                range[54].Value2 = nCells29;

            }

            else

            {

                range[54].Value2 = "0";

            }

            if (ds.Tables[0].Rows[0]["kind_name"].ToString() == "갱신")

            {

                range[56].Value2 = nCells29;

            }

            else

            {

                range[56].Value2 = "0";

            }

            string nCells33 = "=C5*C6";

            range[45] = worksheet.get_Range("C7", Missing.Value);

            range[45].Value2 = nCells33;

            string nCells34 = "=C8*C9";

            range[47] = worksheet.get_Range("C10", Missing.Value);

            range[47].Value2 = nCells34;

            string nCells35 = "=C11*C12";

            range[49] = worksheet.get_Range("C13", Missing.Value);

            range[49].Value2 = nCells35;

            string nCells36 = "=C14*C15";

            range[51] = worksheet.get_Range("C16", Missing.Value);

            range[51].Value2 = nCells36;

            string nCells37 = "=C17*C18";

            range[53] = worksheet.get_Range("C19", Missing.Value);

            range[53].Value2 = nCells37;

            string nCells38 = "=C20*C21";

            range[55] = worksheet.get_Range("C22", Missing.Value);

            range[55].Value2 = nCells38;

            string nCells39 = "=C23*C24";

            range[57] = worksheet.get_Range("C25", Missing.Value);

            range[57].Value2 = nCells39;

   

   

   

            string nCells40 = "=C5+C8+C11+C14+C17+C20+C23";

            range[58] = worksheet.get_Range("C26", Missing.Value);

            range[58].Value2 = nCells40;

            string nCells41 = "=C7+C10+C13+C16+C19+C22+C25";

            range[59] = worksheet.get_Range("C27", Missing.Value);

            range[59].Value2 = nCells41;

            int nCells30 = 0;

            for (int i = 0; i < ds.Tables[0].Rows.Count; i++)

            {

                nCells30 +=int.Parse(ds.Tables[0].Rows[i]["revisit_count"].ToString());// 재방량

            }

            range[60] = worksheet.get_Range("C28", Missing.Value);

            range[60].Value2 = nCells30.ToString();

            int nCells31 = 0;

            for (int j = 0; j < ds.Tables[0].Rows.Count; j++)

            {

                nCells31 +=int.Parse(ds.Tables[0].Rows[j]["return_count"].ToString());//반송량

            }

            range[61] = worksheet.get_Range("C29", Missing.Value);

            range[61].Value2 = nCells31.ToString();

            int nCells32 = 0;

            for (int k = 0; k< ds.Tables[0].Rows.Count; k++)

            {

                nCells32 += int.Parse(ds.Tables[0].Rows[k]["rost_count"].ToString());//분실량

            }

            range[62] = worksheet.get_Range("C30", Missing.Value);

            range[62].Value2 = nCells32.ToString();

            string nCells42 = "=C27+C31+C32";

            range[63] = worksheet.get_Range("C33", Missing.Value);

            range[63].Value2 = nCells42;

            string nCells8 = "비고";

            range[64] = worksheet.get_Range("D4", Missing.Value);

            range[64].Value2 = nCells8;

이코딩은 제가 작업을 하면서 원하는 부분을 한것이기 때문에 엑셀값을 뿌려주실때는 그부분을 바꿔서 해주시면 될듯하네여.....

합계같은 수식도 표현되니 보시면 될 듯하네여....

   

코딩 방법을 잊어 먹지 않기 위해서 이렇게 글을 남기네요 .........

   

   

<http://ios123.tistory.com/102>에서 삽입