title: How to import all the Excel sheets to DataSet in C#
body: <p>I've searched internet for this and couldn't really find a question like it. Everyone was looking for a way to import an individual sheet in the excel file but what I want is to import all the sheets in the file to <code>DataTable</code>'s in <code>DataSet</code> without knowing the sheet names.</p>
  
  <p>I've not done much things with Excel before. This a sample and partially working code I've found on the internet and it only parses the given sheet name:</p>
  
  <pre><code>public static DataSet Parse(string fileName, string workSheetName)
  {
      string connectionString = string.Format("provider=Microsoft.Jet.OLEDB.4.0; data source={0};Extended Properties=Excel 8.0;", fileName);
      string query = string.Format("SELECT * FROM [{0}$]", workSheetName);
  
      DataSet data = new DataSet();
      using (OleDbConnection con = new OleDbConnection(connectionString))
      {
          con.Open();
          OleDbDataAdapter adapter = new OleDbDataAdapter(query, con);
          adapter.Fill(data);
      }
  
      return data;
  }
  </code></pre>
  
  <p>In the code above, as you see, workSheetName should be passed in so the query can know where to look at to import. In my case, I want it to traverse all the sheets no matter what they are named like and import them to individual <code>DataTable</code>'s of a <code>DataSet</code>.</p>
  
  <p>So in essence, the final thing will be a <code>DataSet</code> in which each <code>DataTable</code> holds rows for each sheet in the imported file.</p>