Excel To Datatable using Oracle Client in C#

• Stylez

  SSCrazy

  Points: 2195

  More actions

  January 26, 2018 at 1:55 pm

  #410107

  Good Morning Sir/Mam,

  I would like to ask if it is possible to use the Oracle Client in C# to Extract Data from Excel and insert it into a Data Table? i currently was able to do this using OleDB but i am not sure if it is possible in Oracle Client. My Code Are as follows.

  private static DataSet ImportExcelData(string fileName, bool hasHeaders)
     {

      string hdr = hasHeaders ? "Yes" : "No";
      string connString = "Driver=Microsoft.Jet.OLEDB.4.0;Data Source=" +
          fileName + ";Extended Properties=\"Excel 8.0;HDR=" +
          hdr + ";IMEX=1\"";

   

      DataSet output = new DataSet();

      try
      {
        using (OracleConnection conn = new OracleConnection(connString))
        {
         conn.Open();
         string[] restrictions = new string[4] { null, null, null, "TABLE" };
         /* DataTable schemaTable = conn.GetSchema(
           OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });*/
         DataTable schemaTable = conn.GetSchema("Columns", restrictions);

         foreach (DataRow schemaRow in schemaTable.Rows)
         {
          string sheet = schemaRow["TABLE_NAME"].ToString();
          OracleCommand cmd = new OracleCommand("SELECT * FROM [" + sheet + "]", conn);
          cmd.CommandType = CommandType.Text;

          DataTable outputTable = new DataTable(sheet);
          output.Tables.Add(outputTable);
          new OracleDataAdapter(cmd).Fill(outputTable);
         }
        }
      }
      catch (OracleException ex)
      {
        MessageBox.Show(ex.InnerException.ToString(), "May Mali");
        _log.Error(ex);
        if (ex.ErrorCode == -2147467259)
        {
         MessageBox.Show("Specified file is open and being edited. Please complete the edit before proceeding.", "Import Error", MessageBoxButtons.OK, MessageBoxIcon.Stop);
        }
        else
        {
         MessageBox.Show(ex.Message, "Import Error", MessageBoxButtons.OK, MessageBoxIcon.Stop);
        }
        return null;
      }
      catch (Exception ex)
      {
        _log.Error(ex);
        MessageBox.Show(ex.Message + " ImportExcelData", "Import Error", MessageBoxButtons.OK, MessageBoxIcon.Stop);
      }

      return output;
     }