Late Binding을 통한 Excel Automation class

Late Binding을 하게 되면 사용자 머신에 설치된 Excel의 버젼에 상관없이 사용할 수 있게 되어 유용합니다.

참조 사이트 : http://www.codeproject.com/KB/cs/How2LateBinding.aspx

사용 법:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78

/// <summary>   /// export data to excel file   /// </summary>   /// <param name="sender"></param>   /// <param name="e"></param>   private void btnExport_Click(object sender, EventArgs e)   {       int iExlStartRow = 2 , iNextColumn = 10;       try      {          CExcelAutomation xlApp = new CExcelAutomation(true);          if (true == xlApp.Open("d:\\MicroStation Analysis.xlsx"))          {              object[] parameter = new object[1];              parameter[0] = 1;              object objWorksheet = xlApp.Worksheets.GetType().InvokeMember("item", BindingFlags.GetProperty, null, xlApp.Worksheets, parameter);                //! copy sheets              int iSheetCount = dataGridView_LineData.Rows.Count / ITEM_COUNT_PER_SHEET;              int iRemain = (dataGridView_LineData.Rows.Count % ITEM_COUNT_PER_SHEET);              if (iRemain > 0) ++iSheetCount;                           for (int i = 0; i < iSheetCount - 1; ++i)              {                  parameter[0] = objWorksheet;                  xlApp.InvokeMethod(objWorksheet, "Copy", parameter);                  {                      objWorksheet = xlApp.GetActiveSheet();                      parameter[0] = "Sheet " + (i + 2);                      xlApp.SetProperty(objWorksheet, "Name", parameter);                  }              }                for (int iSheet = 0; iSheet < iSheetCount; ++iSheet)              {                  objWorksheet = xlApp.SetActiveSheet(iSheet + 1);                  int iRow = iExlStartRow , iNo = 1;                  for (int k = 0; k < ITEM_COUNT_PER_SHEET; ++k)                  {                      int i = (iSheet - 1) * ITEM_COUNT_PER_SHEET + k + 1;                      int row = iExlStartRow + k;                      if (k > ITEM_HALF_COUNT_PER_SHEET)                      {                          iNo = iNextColumn + 1;                          row = row - ITEM_HALF_COUNT_PER_SHEET;                      }                    string sCellNo = xlApp.MakeCellNo(row, iNo);                  xlApp.SetCellValue(objWorksheet , new object[] {sCellNo , Missing.Value} , "num");                  }              }                ///object objRange = xlApp.GetProperty(objWorksheet, "Range", new object[] { "A1", "A2"});              object[,] Values = xlApp.GetCellValue(objWorksheet, new object[] { "A1", "A2" });                //object objValue = xlApp.GetProperty(objRange, "Value", null);              ///object[,] strData = (object[,])objValue;              for (int i = Values.GetLowerBound(0); i <= Values.GetUpperBound(0); ++i)              {                  for (int j = Values.GetLowerBound(1); j <= Values.GetUpperBound(1); ++j)                  {                      string value = Values[i, j].ToString();                      Console.WriteLine(value);                  }              }                xlApp.SetCellTextColor(objWorksheet, new object[] { "A1", "A2" }, 3);              object objRange = xlApp.GetProperty(objWorksheet, "Range", new object[] { "A1", "A2" });              object objFont = xlApp.GetProperty(objRange, "Font", null);              parameter[0] = true;              xlApp.SetProperty(objFont, "Strikethrough", parameter);          }      }      catch(Exception ex)      {          Console.WriteLine("Error Message {0}" , ex.Message);      }  }  Colored by Color Scripter

cs

문제점 ; 작업 종료후 엑셀 프로그래을 닫지 못하는 문제
    - 소멸자에서 실행중인 엑셀 프로세서를 죽임
    - 또 다른 문제 발생 : 원치 않는 엑셀이 닫히는 경우가 발생할 수 있음.