기본카테고리

[C#] 엑셀파일을 데이터그리드컨트롤로(Import Excel File To DataGridView)

DevReff 2015. 4. 23. 13:16




728x90

/// <summary>
  /// Import Excel File To DataGridView
  /// </summary>
  /// <param name="grid">DataGridView Control</param>
  /// <param name="sFilePath">Excel File Path</param>
  /// <param name="sFirstRowHeader">if first row in excel is header is then yes else no</param>
  /// <param name="isExcelFileVersion2003">엑셀파일의 버전이 2003보다 큰지의 여부</param>
  /// <return>성공하면 true, 그렇지않으면 false</return>
  public bool GridViewExcel(DataGridView grid, string sFilePath, string sFirstRowHeader = "no", bool isExcelFileVersion2003=false)
  {
   try
   {
    string sConn = string.Empty;
    string sFileType = sFilePath.Substring(sFilePath.LastIndexOf('.'));
    string sSheetName = "작업개요";

    //if (sFileType == ".xlsx") //version >2003
    if (isExcelFileVersion2003 == false)
    {
     sConn = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0 Xml;HDR={1};IMEX=1\";"
      , sFilePath, sFirstRowHeader);

    }
    else
    {
     sConn = string.Format("Provider=Microsoft.Jet.OLEDB.4.0; data source={0}; Extended Properties=\"Excel 8.0;HDR={1};IMEX=1\";"
      , sFilePath, sFirstRowHeader);
    }

    System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(sConn);
    System.Data.OleDb.OleDbDataAdapter dataAdapter = new System.Data.OleDb.OleDbDataAdapter("Select * from [" + sSheetName + "$]", conn);
    DataTable dt = new DataTable();
    dataAdapter.Fill(dt);
    grid.DataSource = dt;

    return true;
   }
   catch (Exception ex)
   {
    string errorMessage;
    errorMessage = "Error: " + ex.Message + " Line: " + ex.Source;

    MessageBox.Show(errorMessage, "Error");
   }

   return false;
  }