【C#】Excel 轉 DataTable

.xls or .xlsx to datatable using ACE.OLEDB

字面上的意思，比較特別值得注意的只有連線字串裡 HDR=Yes 這個參數表示第一行為欄名。

public DataTable RenderDataTableFromExcel(string fullPath)
{
    string connStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=1\"";
    connStr = string.Format(connStr, fullPath);
    string sheetName = null;
    DataTable sheetNames = default(DataTable);
    OleDbConnection conn = default(OleDbConnection);
 
    conn = new OleDbConnection(connStr);
    conn.Open();
    sheetNames = conn.GetSchema("Tables");
    if (sheetNames.Rows.Count > 0)
        sheetName = sheetNames.Rows[0]["TABLE_NAME"].ToString();
    sheetNames.Clear();
    sheetNames.Dispose();
 
    OleDbCommand cmd = new OleDbCommand(string.Format("SELECT * FROM [{0}]", sheetName), conn);
    OleDbDataAdapter adapter = new OleDbDataAdapter(cmd);
    DataTable dt = new DataTable();
    adapter.Fill(dt);
 
    conn.Close();
    return dt;
}

ref: Sai Fan Chen's Blog