SoFunction
Updated on 2025-03-06

C# Operate Excel instance code through oledb

Organize the document, search out a C# operation of Excel instance code through oledb, and organize it a little and streamline it to share it.

public string GetConnectionString()
    {
      Dictionary<string, string> props = new Dictionary<string, string>();
 
      // XLSX - Excel 2007, 2010, 2012, 2013
      props["Provider"] = ".12.0;";
      props["Extended Properties"] = "Excel 12.0 XML";
      props["Data Source"] = @"C:\tools\";
 
      // XLS - Excel 2003 and Older
      //props["Provider"] = ".4.0";
      //props["Extended Properties"] = "Excel 8.0";
      //props["Data Source"] = "C:\\";
 
      var sb = new StringBuilder();
 
      foreach (KeyValuePair<string, string> prop in props)
      {
        ();
        ('=');
        ();
        (';');
      }
 
      return ();
    }
 
    public void WriteExcelFile()
    {
      string connectionString = GetConnectionString();
 
      using (OleDbConnection conn = new OleDbConnection(connectionString))
      {
        ();
        OleDbCommand cmd = new OleDbCommand();
         = conn;
 
         = "CREATE TABLE [table1] (id INT, name VARCHAR, datecol DATE );";
        ();
 
         = "INSERT INTO [table1](id,name,datecol) VALUES(1,'AAAA','2014-01-01');";
        ();
 
         = "INSERT INTO [table1](id,name,datecol) VALUES(2, 'BBBB','2014-01-03');";
        ();
 
         = "INSERT INTO [table1](id,name,datecol) VALUES(3, 'CCCC','2014-01-03');";
        ();
 
         = "UPDATE [table1] SET name = 'DDDD' WHERE id = 3;";
        ();
 
        ();
      }
    }
 
    public DataSet ReadExcelFile()
    {
      DataSet ds = new DataSet();
 
      string connectionString = GetConnectionString();
 
      using (OleDbConnection conn = new OleDbConnection(connectionString))
      {
        ();
        OleDbCommand cmd = new OleDbCommand();
         = conn;
 
        // Get all Sheets in Excel File
        DataTable dtSheet = (, null);
 
        // Loop through all Sheets to get data
        foreach (DataRow dr in )
        {
          string sheetName = dr["TABLE_NAME"].ToString();
 
          if (!("$"))
            continue;
 
          // Get all rows from the Sheet
           = "SELECT * FROM [" + sheetName + "]";
 
          DataTable dt = new DataTable();
           = sheetName;
 
          OleDbDataAdapter da = new OleDbDataAdapter(cmd);
          (dt);
 
          (dt);
        }
 
        cmd = null;
        ();
      }
 
      return ds;
    }

The above is all the content of this article. I hope it will be helpful to everyone's study and I hope everyone will support me more.