C# 엑셀 파일 저장하기 (Write Excel Data)

1
2
using System.Runtime.InteropServices;
using Excel = Microsoft.Office.Interop.Excel;

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
79
80
81
82
83
84
private static bool WriteExcelData(string path, string[][] targetData)
{
    uint excelProcessId = 0;
    Excel.Application excelApp = null;
    Excel.Workbook wb = null;
    Excel.Worksheet ws = null;

    try
    {
        // Execute Excel application
        excelApp = new Excel.Application();
        GetWindowThreadProcessId(new IntPtr(excelApp.Hwnd), out excelProcessId);


        // 엑셀파일 열기 or 새로 만들기
        bool isFileExist = File.Exists(path);
        wb = isFileExist ? excelApp.Workbooks.Open(path, ReadOnly: false, Editable: true) : excelApp.Workbooks.Add(Missing.Value);

        ws = wb.Worksheets.get_Item(1) as Excel.Worksheet;
        // Worksheet 이름 설정하기
        // ws.Name = targetWorksheetName ;
        
        int row = targetData.GetLength(0);
        int column = targetData[0].Length;

        object[,] data = new object[row, column];

        for (int r = 0; r < row; r++)
        {
            for (int c = 0; c < column; c++)
            {
                data[r, c] = targetData[r][c];
            }
        }

        // row, column 번호로 Cell 접근
        // Excel.Range rng = ws.Range[ws.Cells[1, 1], ws.Cells[row, column]];

        Excel.Range rng = ws.get_Range("A1", Missing.Value);
        rng = rng.get_Resize(row, column);

        // 저장하는 여러 방법 중 두가지
        // rng.Value = data;
        rng.set_Value(Missing.Value, data);

        if (isFileExist)
        {
            wb.Save(); // 덮어쓰기
        }
        else
        {
            wb.SaveCopyAs(path); // 새 파일 만들기
        }

        wb.Close(false);
        excelApp.Quit();
    }
    catch (Exception e)
    {
        if (wb != null)
        {
            wb.Close(SaveChanges: false);
        }
        if (excelApp != null)
        {
            excelApp.Quit();
        }

        return false;
    }
    finally
    {
        ReleaseExcelObject(ws);
        ReleaseExcelObject(wb);
        ReleaseExcelObject(excelApp);

        if (excelApp != null && excelProcessId > 0)
        {
            Process.GetProcessById((int)excelProcessId).Kill();
        }
    }

    return true;
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
private static void ReleaseExcelObject(object obj)
{
    try
    {
        if (obj != null)
        {
            Marshal.ReleaseComObject(obj);
            obj = null;
        }
    }
    catch (Exception)
    {
        obj = null;
        throw;
    }
    finally
    {
        GC.Collect();
    }
}

1
2
[DllImport("user32.dll", SetLastError = true)]
static extern uint GetWindowThreadProcessId(IntPtr hWnd, out uint lpdwProcessId);