본문 바로가기
.Net

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

by Dokon Jang 2015. 12. 24.
반응형

엑셀파일에 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;
        }
    }
}​

 

 

댓글0