Excel To Datatable using Oracle Client in C#

  • 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;
       }

Viewing post 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply