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


우리의 컴퓨터가 어떤 작업을 할때 마우스가 빙글빙글 돌아가는 모습을 본적 있으시죠?


winform 에서도 그렇게 할 수 있습니다


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
this.Cursor = Cursors.WaitCursor;   //Waitting
 
this.Cursor = Cursors.Default;   //원래대로
 
 
//둘다 같다 난 밑에걸 선호(위에건 폼안에서만 돈다고하네요)
 
 
Cursor.Current = Cursors.WaitCursor;   //Waitting
 
Cursor.Current = Cursors.Default;   //원래대로
 
 
 
 
cs


저작자표시

'Winform' 카테고리의 다른 글

winform DataGridView to Excel  (0) 2019.01.22
winform 엑셀 import  (0) 2019.01.17
winform dataGridView 행전체선택, 앞열삭제, 뷰가득채우기, 마지막행 삭제  (0) 2019.01.15
winform property resources 추가  (0) 2019.01.15
winform SqlCommand  (0) 2019.01.15

데이터 그리드 뷰의 유용한 기능들을 모았어요


1
2
3
4
5
6
7
8
9
10
11
12
13
14
            //행 전체 선택 할 수 있도록
            dataGridView1.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
 
            //맨 앞 열 삭제
            dataGridView1.RowHeadersVisible = false;
 
            // 그리드뷰 컬럼폭 채우기
            dataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells;
 
            // 데이터 그리드 뷰 가득 채우기
            dataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;
 
            //마지막행 삭제
            dataGridView1.AllowUserToAddRows = false;
cs



저작자표시

'Winform' 카테고리의 다른 글

winform 엑셀 import  (0) 2019.01.17
winform cursor waiting 마우스 로딩  (0) 2019.01.17
winform property resources 추가  (0) 2019.01.15
winform SqlCommand  (0) 2019.01.15
winform SqlConnection  (0) 2019.01.15

winform 하다보면 프로퍼티에 사진을 추가하고 싶을때가 있습니다


한번 해볼까요?





이렇게 사용하실 수 있습니다! ㅎㅎ

저작자표시

'Winform' 카테고리의 다른 글

winform cursor waiting 마우스 로딩  (0) 2019.01.17
winform dataGridView 행전체선택, 앞열삭제, 뷰가득채우기, 마지막행 삭제  (0) 2019.01.15
winform SqlCommand  (0) 2019.01.15
winform SqlConnection  (0) 2019.01.15
winform 엑셀 export  (0) 2019.01.15

ㄷ

쿼리를 수행하기 위해 SqlCommand를 선언해 사용해줍니다!


ExecuteQuery는 select 쿼리일때 많이 쓰이고

ExecuteNonQuery는 insert, update, delete 쿼리때 많이 쓰입니다.


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
//SqlCommand를 사용하기 위해서 적어주자
using System.Data.SqlClient;
 
 
//방법 1
string sql ="select * from table";
SqlConnection conn = new SqlConnection(conString);
 
//선언
SqlCommand cmd = new SqlCommand(sql, conn);
 
//수행
cmd.ExecuteQuery();
cmd.ExecuteNonQuery();
 
 
//방법2
SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "select * from table";
            cmd.Connection = conn;
 
//수행
cmd.ExecuteQuery();
cmd.ExecuteNonQuery();
 
cs




ㅇㅇㅇㅇㅇㅇㅇ




'Winform' 카테고리의 다른 글

winform cursor waiting 마우스 로딩  (0) 2019.01.17
winform dataGridView 행전체선택, 앞열삭제, 뷰가득채우기, 마지막행 삭제  (0) 2019.01.15
winform property resources 추가  (0) 2019.01.15
winform SqlConnection  (0) 2019.01.15
winform 엑셀 export  (0) 2019.01.15

SqlConnection은 winform에서 DB와 연결하기 위한 클래스입니다


3가지 사용법을 알아보겠습니다


세가지 모두 알고보면 같은 내용인데


유지보수를 위해서 2번째나 3번째 방법을 저는 선호합니다



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
// SqlConnection을 쓰기 위해선 반드시 적어주도록 합니다
using System.Data.SqlClient;
 
 
//방법1
//선언
SqlConnection conn = new SqlConnection(@"Server=localhost; database=test; uid=sa; pwd=123");
 
//열기
conn.open();
 
//수행
.......
 
//닫기
conn.close();
 
 
 
//방법2
//선언
SqlConnection conn = new SqlConnection();
string connString = @"Server=localhost; database=test; uid=sa; pwd=123";
conn.ConnectionString = ConnString;
 
//열기
conn.open();
 
//수행
.......
 
//닫기
conn.close();
 
 
//방법3
//선언
string connString = @"Server=localhost; database=test; uid=sa; pwd=123";
SqlConnection conn = new SqlConnection(connString);
 
//열기
conn.open();
 
//수행
.......
 
//닫기
conn.close();
cs
ㄷ


'Winform' 카테고리의 다른 글

winform cursor waiting 마우스 로딩  (0) 2019.01.17
winform dataGridView 행전체선택, 앞열삭제, 뷰가득채우기, 마지막행 삭제  (0) 2019.01.15
winform property resources 추가  (0) 2019.01.15
winform SqlCommand  (0) 2019.01.15
winform 엑셀 export  (0) 2019.01.15


winform 제작을 하다보면 데이터그리드뷰의 내용을 엑셀로 변환할 필요가 있습니다.


아래의 소스는 엑셀변환함수를 만들고 그것을 버튼에서 호출하는 식으로 쓰였습니다.


한번만 훑어보시고 잘 쓰셨으면 좋겠어요


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
        // 파일저장창
        public SaveFileDialog saveFileDialog = new SaveFileDialog();
 
 
         // 엑셀 다운 버튼
        private void button9_Click(object sender, EventArgs e)
        {
            //엑셀 함수 불러오기
            ExportExcel(true);
        }
 
        // 엑셀 export 함수
        private void ExportExcel(bool captions)
        {
            this.saveFileDialog.FileName = "TempName";
            this.saveFileDialog.DefaultExt = "xls";
            this.saveFileDialog.Filter = "Excel files (*.xls)|*.xls";
            this.saveFileDialog.InitialDirectory = "c:\\";
 
            DialogResult result = saveFileDialog.ShowDialog();
 
            if (result == DialogResult.OK)
            {
                int num = 0;
                object missingType = Type.Missing;
 
                Excel.Application objApp;
                Excel._Workbook objBook;
                Excel.Workbooks objBooks;
                Excel.Sheets objSheets;
                Excel._Worksheet objSheet;
                Excel.Range range;
 
                string[] headers = new string[dataGridView1.ColumnCount];
                string[] columns = new string[dataGridView1.ColumnCount];
 
                for (int c = 0; c < dataGridView1.ColumnCount; c++)
                {
                    headers[c] = dataGridView1.Rows[0].Cells[c].OwningColumn.HeaderText.ToString();
                    num = c + 65;
                    columns[c] = Convert.ToString((char)num);
                }
 
                try
                {
                    objApp = new Excel.Application();
                    objBooks = objApp.Workbooks;
                    objBook = objBooks.Add(Missing.Value);
                    objSheets = objBook.Worksheets;
                    objSheet = (Excel._Worksheet)objSheets.get_Item(1);
 
                    if (captions)
                    {
                        for (int c = 0; c < dataGridView1.ColumnCount; c++)
                        {
                            range = objSheet.get_Range(columns[c] + "1", Missing.Value);
                            range.set_Value(Missing.Value, headers[c]);
                        }
                    }
 
                    for (int i = 0; i < dataGridView1.RowCount - 1; i++)
                    {
                        for (int j = 0; j < dataGridView1.ColumnCount; j++)
                        {
                            range = objSheet.get_Range(columns[j] + Convert.ToString(i + 2),
                                                                   Missing.Value);
                            range.set_Value(Missing.Value,
                                                  dataGridView1.Rows[i].Cells[j].Value.ToString());
                        }
                    }
 
                    objApp.Visible = false;
                    objApp.UserControl = false;
 
                    objBook.SaveAs(@saveFileDialog.FileName,
                              Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal,
                              missingType, missingType, missingType, missingType,
                              Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
                              missingType, missingType, missingType, missingType, missingType);
                    objBook.Close(false, missingType, missingType);
 
                    Cursor.Current = Cursors.Default;
 
                    MessageBox.Show("Save Success!!!");
                }
                catch (Exception theException)
                {
                    String errorMessage;
                    errorMessage = "Error: ";
                    errorMessage = String.Concat(errorMessage, theException.Message);
                    errorMessage = String.Concat(errorMessage, " Line: ");
                    errorMessage = String.Concat(errorMessage, theException.Source);
 
                    MessageBox.Show(errorMessage, "Error");
                }
            }
        }
cs


'Winform' 카테고리의 다른 글

winform cursor waiting 마우스 로딩  (0) 2019.01.17
winform dataGridView 행전체선택, 앞열삭제, 뷰가득채우기, 마지막행 삭제  (0) 2019.01.15
winform property resources 추가  (0) 2019.01.15
winform SqlCommand  (0) 2019.01.15
winform SqlConnection  (0) 2019.01.15

+ Recent posts

티스토리툴바