ABOUT ME

-

Today
-
Yesterday
-
Total
-
  • C# DataGridView 데이터 엑셀파일에 저장 using oledb
    닷넷/C# 2017. 5. 24. 13:43
    반응형

    엑셀 라이브러리 참조없이 OleDb를 이용한 방법으로 

    DataGridView의 데이터를 엑셀로 출력하는 방법이다.

    이때, 엑셀파일을 File 을 이용해 생성하면 제대로 생성되지 않아 사용할 없다.

    따라서 소스를 보면 알겠지만 엑셀파일 하나를 복사해서 거기에 데이터를 저장하는 편법을 이용했다.

     

               OleDbConnection conn = null;
                try
                {
                    SaveFileDialog sfd = new SaveFileDialog();
                    sfd.Filter = "Excel File(.xls)|*.xls";
     
                    if (sfd.ShowDialog() == DialogResult.OK)
                    {
                        FileInfo fi = new FileInfo(sfd.FileName);                   
                        if (!fi.Exists)
                        {
                            File.Copy(Application.StartupPath + "\\Report.xls", sfd.FileName);
                        }
     
                        string connString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\"Excel 8.0;IMEX=2;HDR=YES;\"",sfd.FileName);
     
                        conn = new OleDbConnection(connString);
                        conn.Open();
                        StringBuilder FldsInfo = new StringBuilder();
                        StringBuilder Flds = new StringBuilder();
                        foreach (DataGridViewColumn dc in dgvReport.Columns)
                        {
                            if (FldsInfo.Length > 0)
                            {
                                FldsInfo.Append(",");
                                Flds.Append(",");
                            }
     
                            FldsInfo.Append("[" + dc.HeaderText + "] CHAR(255)");
                            Flds.Append(dc.HeaderText);
                        }
     
                        // Create Table
                        OleDbCommand cmd = new OleDbCommand("CREATE TABLE Report (" + FldsInfo.ToString() + ")", conn);
                        cmd.ExecuteNonQuery();
     
                        // Insert Data
                        foreach (DataGridViewRow dr in dgvReport.Rows)
                        {
                            StringBuilder values = new StringBuilder();
                            foreach (DataGridViewColumn dc in dgvReport.Columns)
                            {
                                if (values.Length >0) { values.Append(","); }
                                values.Append("'" + dr.Cells[dc.Name].Value.ToString() + "'");
                            }
                            cmd = new OleDbCommand("INSERT INTO [Report$](" + Flds.ToString() + ") VALUES (" + values.ToString() + ")", conn);
                            cmd.ExecuteNonQuery();
                        }
     
                        cmd = new OleDbCommand("DROP TABLE [sheet1$]", conn);
                        cmd.ExecuteNonQuery();
     
                        MessageBox.Show("엑셀 출력 완료");
                    }
                }
                catch(Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
                finally
                {
                    if (conn != null && conn.State == ConnectionState.Open) conn.Close();
                }
    cs


    반응형

    댓글

Designed by Tistory.