엑셀파일에 ODBC를 연결하여 엑셀의 데이터 가지고 오기

엑셀파일에 ODBC를 연결하여 엑셀의 데이터를 DataGridView에 표시해 보도록 하겠습니다.

 

1. 엑셀파일에 ODBC 연결하기

string excelODBCConString = "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};Extended Properties='Excel 8.0;HDR=YES';DBQ=";
string conStr = excelODBCConString + excelFilePath;
OdbcConnection con = new OdbcConnection(conStr);

 

 

2. 엑셀의 시트명 얻기

string sheetName = null;
string excelODBCConString = "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};Extended Properties='Excel 8.0;HDR=YES';DBQ=";
string conStr = excelODBCConString + excelFilePath;

try
{
    using (OdbcConnection con = new OdbcConnection(conStr))
    {
        using (OdbcCommand cmd = new OdbcCommand())
        {
            cmd.Connection = con;
            con.Open();
            DataTable dtExcelSchema = con.GetSchema("Tables");
            // 첫번째 시트명
            sheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
            con.Close();
        }
    }
}
catch(Exception e)
{
    MessageBox.Show(e.Message);
}

 

 

3. 엑셀의 데이타 얻기

string excelODBCConString = "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};Extended Properties='Excel 8.0;HDR=YES';DBQ="
string conStr = excelODBCConString + excelFilePath;

try
{
    using (OdbcConnection con = new OdbcConnection(conStr))
{
    DataTable dt = new DataTable();
    string sql = "SELECT * FROM [" + sheetName + "]";

    con.Open();
    OdbcCommand cmd = new OdbcCommand(sql, con);
    OdbcDataAdapter adapter = new OdbcDataAdapter(cmd);
    adapter.Fill(dt);
    con.Close();

    return dt;
}
}
catch(Exception e)
{
    MessageBox.Show(e.Message);
}​

 

4. 전체 소스 및 실행화면

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.Odbc;

namespace ExcelTest
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void btnOpenExcel_Click(object sender, EventArgs e)
        {
            if (openFileDialog.ShowDialog() != System.Windows.Forms.DialogResult.OK) return;

            dgvExcel.DataSource = GetDataTableFromExcelODBC(openFileDialog.FileName);
        }

        private string GetSheetNameFromExcelODBC(string excelFilePath)
        {
            string sheetName = null;
            string excelODBCConString = "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};Extended Properties='Excel 8.0;HDR=YES';DBQ=";
            string conStr = excelODBCConString + excelFilePath;

            try
            {
                using (OdbcConnection con = new OdbcConnection(conStr))
                {
                    using (OdbcCommand cmd = new OdbcCommand())
                    {
                        cmd.Connection = con;
                        con.Open();
                        DataTable dtExcelSchema = con.GetSchema("Tables");
                        sheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
                        con.Close();
                    }
                }
            }
            catch(Exception e)
            {
                MessageBox.Show(e.Message);
            }

            return sheetName;
        }

        public DataTable GetDataTableFromExcelODBC(string excelFilePath)
        {
            string excelODBCConString = "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};Extended Properties='Excel 8.0;HDR=YES';DBQ=";
            string conStr = excelODBCConString + excelFilePath;

            try
            {
                using (OdbcConnection con = new OdbcConnection(conStr))
                {
                    DataTable dt = new DataTable();
                    string sql = "SELECT * FROM [" + GetSheetNameFromExcelODBC(excelFilePath) + "]";

                    con.Open();
                    OdbcCommand cmd = new OdbcCommand(sql, con);
                    OdbcDataAdapter adapter = new OdbcDataAdapter(cmd);
                    adapter.Fill(dt);
                    con.Close();

                    return dt;
                }
            }
            catch(Exception e)
            {
                MessageBox.Show(e.Message);
            }

            return null;
        }
    }
}​