티스토리 뷰

student.mdb 파일은 Student라는 테이블이 있으며 그 내용은 다음과 같습니다.

id        name        addr        tel
1        가길동        서울        11-2222
2        나기롱        울산        222-3333
3        다길동        부산        444-5555

이를 통해 만들어진 student.xls 파일은 다음과 같이 생겼습니다...
위의 mdb 파일과 내용이 같겠죠^^;;;

id        name        addr        tel
1        가길동        서울        11-2222
2        나기롱        울산        222-3333
3        다길동        부산        444-5555

소스 코드는 다음과 같습니다.

---------------------------------------------------


using System;
using System.Drawing;
using System.Collections;
using System.Windows.Forms;
using System.Data;
using System.Data.OleDb;
//아래의 using 문이 가능할려면 프로젝트 --> 참조추가 --> com 탭에서 Microsofot Excel Object 10.0 또는 11.0(2003 버전) 추가
//using Excel = Microsoft.Office.Interop.Excel;  //for Office 2003
using Excel;

namespace Exam
{
        /// <summary>
        /// Form1에 대한 요약 설명입니다.
        /// </summary>
        public class Form1 : System.Windows.Forms.Form
        {
                private System.Windows.Forms.Button button1;
                /// <summary>
                /// 필수 디자이너 변수입니다.
                /// </summary>
                private System.ComponentModel.Container components = null;

                public Form1()
                {
                        //
                        // Windows Form 디자이너 지원에 필요합니다.
                        //
                        InitializeComponent();

                        //
                        // TODO: InitializeComponent를 호출한 다음 생성자 코드를 추가합니다.
                        //
                }

                /// <summary>
                /// 사용 중인 모든 리소스를 정리합니다.
                /// </summary>
                protected override void Dispose( bool disposing )
                {
                        if( disposing )
                        {
                                if (components != null) 
                                {
                                        components.Dispose();
                                }
                        }
                        base.Dispose( disposing );
                }

                #region Windows Form 디자이너에서 생성한 코드
                /// <summary>
                /// 디자이너 지원에 필요한 메서드입니다.
                /// 이 메서드의 내용을 코드 편집기로 수정하지 마십시오.
                /// </summary>
                private void InitializeComponent()
                {
                        this.button1 = new System.Windows.Forms.Button();
                        this.SuspendLayout();
                        // 
                        // button1
                        // 
                        this.button1.Font = new System.Drawing.Font("굴림", 12F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((System.Byte)(129)));
                        this.button1.Location = new System.Drawing.Point(32, 16);
                        this.button1.Name = "button1";
                        this.button1.Size = new System.Drawing.Size(208, 72);
                        this.button1.TabIndex = 0;
                        this.button1.Text = "MDB 파일을 읽어 Excel에 저장 후 Excel 실행";
                        this.button1.Click += new System.EventHandler(this.button1_Click);
                        // 
                        // Form1
                        // 
                        this.AutoScaleBaseSize = new System.Drawing.Size(6, 14);
                        this.ClientSize = new System.Drawing.Size(280, 126);
                        this.Controls.Add(this.button1);
                        this.Name = "Form1";
                        this.Text = "Form1";
                        this.ResumeLayout(false);

                }
                #endregion

                /// <summary>
                /// 해당 응용 프로그램의 주 진입점입니다.
                /// </summary>
                [STAThread]
                static void Main() 
                {
                        System.Windows.Forms.Application.Run(new Form1());
                }

                
                private void button1_Click(object sender, System.EventArgs e)
                {
                        OleDbConnection AdoConn = null;
                        OleDbConnection ExcelConn = null;
                        try  
                        { 
                                string strConn =@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=""D:\강의자료\울산\예제\C#\C#(2)\ADO\ExcelInsert-FileLoad\Exam\Exam\bin\Debug\student.mdb"";";
                                string ExcelCon=@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=""D:\강의자료\울산\예제\C#\C#(2)\ADO\ExcelInsert-FileLoad\Exam\Exam\bin\Debug\student.xls"";Extended Properties=""Excel 8.0;HDR=YES""" ;             

                                AdoConn = new OleDbConnection (strConn);
                                AdoConn.Open();

                                string AdoSQL="Select id, name, addr, tel from student";
                                OleDbCommand Cmd = new OleDbCommand(AdoSQL, AdoConn);               
                                OleDbDataReader reader=Cmd.ExecuteReader();

                                //  엑셀이 있는지  확인하여 없으면 만든다
                                //  필드을 확인후 없으면 만든다

                                ExcelConn=new OleDbConnection(ExcelCon);
                                ExcelConn.Open();
                                OleDbCommand ExcelCom;          

                                while(reader.Read())
                                {               
                                        string ExcelSQL="INSERT INTO[sheet1$] (id, name, addr, tel) " 
                                                + "VALUES('" + reader[0]+ "'," 
                                                + "'"+ reader[1]+ "',"
                                                + "'"+reader[2]+ "',"
                                                + "'"+reader[3]+ "') ";

                                        ExcelCom=new OleDbCommand(ExcelSQL,ExcelConn);              

                                        int nrow=ExcelCom.ExecuteNonQuery();
                                }

                                reader.Close();
                                ExcelConn.Close();


                                                 
                                //엑셀화일열기 ------------------------------------------------------
                                //방법1
                                /*
                                Excel.Application App=new Excel.Application();

                                Excel.Workbook wk=App.Workbooks.Open(@"D:\강의자료\울산\예제\C#\C#(2)\ADO\ExcelInsert-FileLoad\Exam\Exam\bin\Debug\student.xls"
                                        ,Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                                        Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                                        Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                                        Type.Missing, Type.Missing);            


                                App.Workbooks[1].Activate();

                                App.Visible=true;
                                App.UserControl=true;  
                                */ 

                                //방법2, 경로는 본인의 환경에 맞게 적절히 주세요....
                                System.Diagnostics.Process.Start("Excel",@"D:\강의자료\울산\예제\C#\C#(2)\ADO\ExcelInsert-FileLoad\Exam\Exam\bin\Debug\student.xls");

                                //마찬가지로 웹브라우저를 실행 하기 위해서는 아래와 같이 하면 됩니다.
                                System.Diagnostics.Process.Start("IEXPLORE.EXE",  "www.oraclejava.co.kr");

                                
                        }
                        catch(Exception ex) 
                        {
                                MessageBox.Show(ex.ToString());
                        }
                        finally 
                        {
                                AdoConn.Close();
                                ExcelConn.Close();
                        }

                }
        }
}
댓글
댓글쓰기 폼
공지사항
Total
3,579,592
Today
95
Yesterday
492
«   2022/01   »
            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          
글 보관함