SoFunction
Updated on 2025-03-01

C# Convert data in Excel to DataSet

Convert data in Excel to DataSet without using third-party plugins using C#

/// <summary>
    /// EXCEL data conversion DataSet    /// </summary>
    /// <param name="filePath">Full path of file</param>    /// <param name="search">Table name</param>    /// &lt;returns&gt;&lt;/returns&gt;    
    private DataSet GetDataSet(string fileName)
    {
      string strConn = "Provider=.12.0;Data Source=" + fileName + ";Extended Properties='Excel 12.0;HDR=Yes;IMEX=1';";
      OleDbConnection objConn = null;
      objConn = new OleDbConnection(strConn);
      ();
      DataSet ds = new DataSet();
      //List<string> List = new List<string> { "Amount of collection", "Tariff payment", "Advance payment", "Excessive period", "Profit incurred" };      List&lt;string&gt; List = new List&lt;string&gt; { };
      DataTable dtSheetName = (, new object[] { null, null, null, "TABLE" });
      foreach (DataRow dr in )
      {
        if (dr["Table_Name"].ToString().Contains("$") &amp;&amp; !dr[2].ToString().EndsWith("$"))
        {
          continue;
        }
        string s = dr["Table_Name"].ToString();
        (s);
      }
      try
      {
        for (int i = 0; i &lt; ; i++)
        {
          (List[i]);
          string SheetName = List[i];
          string strSql = "select * from [" + SheetName + "]";
          OleDbDataAdapter odbcCSVDataAdapter = new OleDbDataAdapter(strSql, objConn);
          DataTable dt = [i];
          (dt);
        }
        return ds;
      }
      catch (Exception ex)
      {
        return null;
      }
      finally
      {
        ();
        ();
      }
    }

The above is the entire content of this article, I hope you like it.