[C#] Excel Data to DataGridView with OleDB

 

private string Excel03ConString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR={1}'";
private string Excel07ConString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR={1}'";
 
public void ReadExcel(string filePath, string fileExtension)
{
    string connectionString = string.Empty;
    string sheetName = string.Empty;
 
    // 확장자로 구분하여 커넥션 스트링을 가져옮
    switch (fileExtension)
    {
        case ".xls":    //Excel 97-03
            connectionString = string.Format(Excel03ConString, filePath, "YES");
            break;
        case ".xlsx":  //Excel 07
            connectionString = string.Format(Excel07ConString, filePath, "YES");
            break;
    }
 
    // 첫 번째 시트의 이름을 가져옮
    using (OleDbConnection con = new OleDbConnection(connectionString))
    {
        using (OleDbCommand cmd = new OleDbCommand())
        {
            cmd.Connection = con;
            con.Open();
            System.Data.DataTable dtExcelSchema = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
            sheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
            con.Close();
        }
    }
 
    // 첫 번째 시트의 데이타를 읽어서 datagridview 에 보이게 함.
    using (OleDbConnection con = new OleDbConnection(connectionString))
    {
        using (OleDbCommand cmd = new OleDbCommand())
        {
            using (OleDbDataAdapter oda = new OleDbDataAdapter())
            {
                DataTable dt = new DataTable();
                cmd.CommandText = $"SELECT * FROM [{sheetName}]";
                cmd.Connection = con;
                con.Open();
                oda.SelectCommand = cmd;
                oda.Fill(dt);
                con.Close();
 
                //Populate DataGridView.
                dataGridView1.DataSource = dt;
                dataGridView1.Columns[0].Width = 150;
            }
        }
    }
}