C# - Excel 연동하기

1. 참조에 excel을 참조한다.

 

2. 예제 코드

using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Runtime.InteropServices;
using System.Text;
using Excel = Microsoft.Office.Interop.Excel;       //microsoft Excel 14 object in references-> COM tab

namespace excel
{
    public class Program
    {
        public static void Main(string[] args)
        {
            string[] filename = { };
            string[] strip = { };

            for (int i = 0; i < filename.Length; i++)
            {
                readExcel(strip[i], filename[i]);
            }
            System.Console.WriteLine("press any key...");
            string name = Console.ReadLine();
        }

        private static void readExcel(string strip, string filename)
        {
            Excel.Application xlApp;
            Excel.Workbook xlWorkBook;
            Excel.Worksheet xlWorkSheet;
            Excel.Range range;

            string str;
            int rCnt = 0; // 열 갯수
            int cCnt = 0; // 행 갯수

            xlApp = new Excel.Application();
            xlWorkBook = xlApp.Workbooks.Open(@"C:\exceltest\test.xlsx", 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
            xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); // 첫번째 시트를 가져 옴.

            range = xlWorkSheet.UsedRange; // 가져 온 시트의 데이터 범위 값
            /*
            for (rCnt = 1; rCnt <= range.Rows.Count; rCnt++)
            {
                for (cCnt = 1; cCnt <= range.Columns.Count; cCnt++)
                {
                    str = (string)(range.Cells[rCnt, cCnt] as Excel.Range).Value2; // 열과 행에 해당하는 데이터를 문자열로 반환
                    System.Console.WriteLine("[" + rCnt + "," + cCnt + "] " + str);

                }
            }*/
            
            xlWorkSheet.Cells[4, 5] = strip;
            xlWorkSheet.Cells[5, 5] = strip;
            xlWorkSheet.Cells[6, 5] = strip;
            xlWorkSheet.Cells[7, 5] = strip;
            filename = "test_"+filename + ".xlsx";
            xlWorkBook.SaveAs(@"C:\exceltest\"+filename);
            xlWorkBook.Close(true, null, null);
            xlApp.Quit();

            releaseObject(xlWorkSheet);
            releaseObject(xlWorkBook);
            releaseObject(xlApp);
        }

        private static void releaseObject(object obj)
        {
            try
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
                obj = null;
            }
            catch (Exception ex)
            {
                obj = null;
                System.Console.WriteLine("Unable to release the Object " + ex.ToString());
            }
            finally
            {
                GC.Collect();
            }
        }
    }
}