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
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
// 엑셀 가져오기 함수
        private void importExcel()
        {
            OpenFileDialog openFileDialog = new OpenFileDialog();
            openFileDialog.Title = "엑셀 파일 선택하세요";
            openFileDialog.Filter = "Excel Files|*.xls;*.xlsx;*.xlsm";
            DialogResult result = openFileDialog.ShowDialog();
 
            if (result == DialogResult.OK)
            {
 
                this.Cursor = Cursors.WaitCursor;
                //엑셀 앱
                Excel.Application app = new Excel.Application();
 
                Excel.Workbooks workbooks = app.Workbooks;
 
                //엑셀 워크북(파일경로읽어서)
                Excel.Workbook workbook = workbooks.Open(openFileDialog.FileName);
 
                // 엑셀 워크싯 객체
                Excel.Sheets sheets = workbook.Worksheets;
 
                Excel.Worksheet worksheet = (Excel.Worksheet)sheets.get_Item(1);
 
                
 
                // 엑셀파일 이름이 나온다
                string excelFileName = workbook.Name;
                
                //excelFileName = excelFileName.Substring(book.Name.Length-3);
                string[] str = excelFileName.Split('.');
 
                // 워크시트 첫번째 이름
                string workSheetName = worksheet.Name;
                
                try
                {
                    if (str[1].Equals("xls"))
                    {
                        // 연결 string
                        string constr = "provider=Microsoft.Jet.OLEDB.4.0;Data Source='" 
+ openFileDialog.FileName + "';Extended Properties=Excel 8.0;";
 
                        //경로까지 다 포함해서 .xls라고 뜨네;
                        //MessageBox.Show(openFileDialog.FileName);
 
                        // excel conn
                        OleDbConnection conn = new OleDbConnection(constr);
 
                        // excel cmd
                        OleDbCommand cmd = new OleDbCommand("SELECT * FROM [" + workSheetName + "$]", conn);
                        conn.Open();
 
                        OleDbDataAdapter sda = new OleDbDataAdapter(cmd);
                        DataTable dt = new DataTable();
                        sda.Fill(dt);
                        this.Cursor = Cursors.WaitCursor;
                        dataGridView1.DataSource = dt;
                        this.Cursor = Cursors.Default;
                        conn.Close();
 
                        
                    }
                    else if (str[1].Equals("xlsx"))
                    {
                        // 연결 string
                        String constr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
                        openFileDialog.FileName +
                        ";Extended Properties='Excel 12.0 XML;HDR=YES;';";
 
                        //경로까지 다 포함해서 .xls라고 뜨네;
                        //MessageBox.Show(openFileDialog.FileName);
 
                        // excel conn
                        OleDbConnection conn = new OleDbConnection(constr);
 
                        // excel cmd
                        OleDbCommand cmd = new OleDbCommand("SELECT * FROM [" + workSheetName + "$]", conn);
                        conn.Open();
 
                        OleDbDataAdapter sda = new OleDbDataAdapter(cmd);
                        DataTable dt = new DataTable();
                        sda.Fill(dt);
                        
                        dataGridView1.DataSource = dt;
                        this.Cursor = Cursors.Default;
                        conn.Close();
                       
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.ToString());
                }
                finally
                {
                    //메모리                     
                    workbook.Close(Type.Missing, openFileDialog.FileName, Type.Missing);
                    app.Quit();
 
                    if(worksheet!=null) releaseObject(worksheet);
                    if (sheets != null) releaseObject(sheets);
                    if (workbook != null) releaseObject(workbook);
                    if (workbooks != null) releaseObject(workbooks);
                    if (app != null) releaseObject(app);
 
 
                }
            }
        }
 
        #region 메모리해제
        private static void releaseObject(object obj)
        {
            try
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
                obj = null;
            }
            catch (Exception e)
            {
                obj = null;
                throw e;
            }
            //finally
            //{
            //    GC.Collect();
            //}
        }
        #endregion
cs


저작자표시

'Winform' 카테고리의 다른 글

winform 엑셀 죽이기 완벽한 방법  (0) 2019.01.29
winform DataGridView to Excel  (0) 2019.01.22
winform cursor waiting 마우스 로딩  (0) 2019.01.17
winform dataGridView 행전체선택, 앞열삭제, 뷰가득채우기, 마지막행 삭제  (0) 2019.01.15
winform property resources 추가  (0) 2019.01.15

+ Recent posts

티스토리툴바