using System;
using System.Collections.Generic;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel;
using System.Runtime.InteropServices;

public class ExcelTest
{
    public static void RunTest()
    {
        List<string> testData = new List<string>() 
            { "Excel", "Access", "Word", "OneNote" };

        Excel.Application excelApp = null;
        Excel.Workbook wb = null;
        Excel.Worksheet ws = null;

        try
        {
            // Excel 첫번째 워크시트 가져오기                
            excelApp = new Excel.Application();
            wb = excelApp.Workbooks.Add();
            ws = wb.Worksheets.get_Item(1) as Excel.Worksheet;

            // 데이타 넣기
            int r = 1;
            foreach (var d in testData)
            {
                ws.Cells[r, 1] = d;
                r++;
            }

            // 엑셀파일 저장
            wb.SaveAs(@"C:\temp\test.xls", Excel.XlFileFormat.xlWorkbookNormal);
            wb.Close(true);
            excelApp.Quit();
        }
        finally
        {
            // Clean up
            ReleaseExcelObject(ws);
            ReleaseExcelObject(wb);
            ReleaseExcelObject(excelApp);
        }
    }

    private static void ReleaseExcelObject(object obj)
    {
        try
        {
            if (obj != null)
            {
                Marshal.ReleaseComObject(obj);
                obj = null;
            }
        }
        catch (Exception ex)
        {
            obj = null;
            throw ex;
        }
        finally
        {
            GC.Collect();
        }
    }
}

private void ExcelTest()
{
   // OLEDB를 이용한 엑셀 연결
   // Excel 97-2003 .xls
   // string szConn = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\x\test.xls;Extended Properties='Excel 8.0;HDR=No'";
      
   // Excel 2007 이후 .xlsx
   string szConn = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\x\test.xlsx;Extended Properties='Excel 8.0;HDR=No'";
   
   OleDbConnection conn = new OleDbConnection(szConn);
   conn.Open();

   // 엑셀로부터 데이타 읽기
   OleDbCommand cmd = new OleDbCommand("SELECT * FROM [Sheet1$]", conn);
   OleDbDataAdapter adpt = new OleDbDataAdapter(cmd);
   DataSet ds = new DataSet();
   adpt.Fill(ds);

   foreach (DataRow dr in ds.Tables[0].Rows)
   {
      string data = string.Format("F1:{0}, F2:{1}, F3:{2}", dr[0], dr[1], dr[2]);
      MessageBox.Show(data);
   }
         
   // 엑셀 데이타 갱신
   cmd = new OleDbCommand("UPDATE [Sheet1$] SET F2='Hello' WHERE F1='a'", conn); 
   cmd.ExecuteNonQuery();
   cmd = new OleDbCommand("UPDATE [Sheet1$A2:C2] SET F2='World'", conn);
   cmd.ExecuteNonQuery();

   // 데이타 추가
   cmd = new OleDbCommand("INSERT INTO [Sheet1$](F1,F2,F3) VALUES ('A3','B3','C3')", conn);
   cmd.ExecuteNonQuery();

   conn.Close();
}

public void ReadExcelData()
{
    Excel.Application excelApp = null;
    Excel.Workbook wb = null;
    Excel.Worksheet ws = null;

    try
    {
        excelApp = new Excel.Application();

        // 엑셀 파일 열기
        wb = excelApp.Workbooks.Open(@"C:\Temp\test.xlsx");

        // 첫번째 Worksheet
        ws = wb.Worksheets.get_Item(1) as Excel.Worksheet;

        // 현재 Worksheet에서 사용된 Range 전체를 선택
        Excel.Range rng = ws.UsedRange;

        // 현재 Worksheet에서 일부 범위만 선택
        // Excel.Range rng = ws.Range[ws.Cells[2, 1], ws.Cells[5, 3]];

        // Range 데이타를 배열 (One-based array)로
        object[,] data = rng.Value;

        for (int r = 1; r <= data.GetLength(0); r++)
        {
            for (int c = 1; c <= data.GetLength(1); c++)
            {
                Debug.Write(data[r, c].ToString() + " ");
            }
            Debug.WriteLine("");
        }

        wb.Close(true);
        excelApp.Quit();
    }
    finally
    {
        // Clean up
        ReleaseExcelObject(ws);
        ReleaseExcelObject(wb);
        ReleaseExcelObject(excelApp);
    }
}