c# excel parsing

[hitchiker]

Excel.Application ExcelApp = new Excel.ApplicationClass();
ExcelApp.Visible = false;
String WorkbookPath = filename;
Excel.Workbook ExcelWorkbook = ExcelApp.Workbooks.Open(WorkbookPath, 0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
Excel.Worksheet Sheet = (Excel.Worksheet)ExcelWorkbook.Sheets[1];
DataTable newTable = new DataTable();
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filename + ";Extended Properties= Excel 8.0";
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
OleDbCommand cmd = new OleDbCommand("SELECT * FROM [" + Sheet + "$]", conn);
cmd.CommandType = CommandType.Text;
OleDbDataAdapter da = new OleDbDataAdapter(cmd);
da.Fill(newTable);
dataGridView1.DataSource = newTable;

I am using this code to read an excel file and save the data in a datatable ....
I am getting this error "System.__ComObject$' is not a valid name. Make sure that it does not include invalid characters or punctuation and that it is not too long." and the error pointer comes on the second last line of the code

can neone help ... its real urgent

[serkan sendur]

do you have a table with this name System.__ComObject$ ?
if not what do you expect?

[hitchiker]

do you have a table with this name System.__ComObject$ ?
if not what do you expect?

This is what i expected ... http://www.codeproject.com/Messages/3110560/Re-Excel-Parseing.aspx ... check the thread named excel parseing :)

[sknake]

What line of code is failing? You can get the OleDb driver's table names using a connection factory instead of opening an instance of Excel.

This code won't compile because it uses my data library (VConnection) -- but the only property it is looking for is driver type and connection string. You should be able to port this code very easily to get the table names:

private VConnection _conn;
    private DataTable _dtTables = null;
    private DataTable _dtColumns = null;
    //
    public DataTable Tables { get { return _dtTables; } }
    public DataTable Columns { get { return _dtColumns; } }
    public VConnection Connection
    {
      get 
      { 
        return _conn; 
      }
    }
    public void SetConnection(VConnection Connection)
    {
      listBoxControlColumns.Items.Clear();
      listBoxControlTables.Items.Clear();
      if (_dtTables != null)
      {
        _dtTables.Clear();
        _dtTables.Dispose();
        _dtTables = null;
      }
      if (_dtColumns != null)
      {
        _dtColumns.Clear();
        _dtColumns.Dispose();
        _dtColumns = null;
      }

      if (Connection == null)
      {
        return;
      }

      _conn = Connection;
      if (_conn.ConnectionType == ConnectionWrapperType.ODBC)
      {
        SetTableList(@"System.Data.Odbc");
      }
      else if (_conn.ConnectionType == ConnectionWrapperType.OleDb)
      {
        SetTableList(@"System.Data.OleDb");
      }
      else if (_conn.ConnectionType == ConnectionWrapperType.SQL)
      {
        SetTableList(@"System.Data.SqlClient");
      }
      else if (_conn.ConnectionType == ConnectionWrapperType.Oracle)
      {
        SetTableList(@"System.Data.OracleClient");
      }
      else
        throw new InvalidEnumArgumentException("ConnectionWrapperType", (int)_conn.ConnectionType, _conn.ConnectionType.GetType());
    }

    private void SetTableList(string factoryName)
    {
      try
      {
        DbProviderFactory factory = DbProviderFactories.GetFactory(factoryName);

        using (DbConnection connection =
                    factory.CreateConnection())
        {
          connection.ConnectionString = _conn.ConnectionString;
          connection.Open();
          _dtTables = connection.GetSchema("Tables");
          _dtColumns = connection.GetSchema("Columns");

          List<string> Sql = new List<string>();
          for (int i1 = 0; i1 < _dtTables.Rows.Count; i1++)
          {
            string tableName = (string)_dtTables.Rows[i1]["TABLE_NAME"];

            try
            {
              string tableType = (string)_dtTables.Rows[i1]["TABLE_TYPE"];
              if ((tableType.ToLower().Contains("system")) || (tableType.ToLower().Equals("access table")))
                continue;
            }
            catch { }

            Sql.Add(tableName);
          }

          Sql.Sort();

          listBoxControlTables.Items.BeginUpdate();
          listBoxControlTables.Items.Clear();

          for (int i1 = 0; i1 < Sql.Count; i1++)
          {
            listBoxControlTables.Items.Add(Sql[i1]);
          }
          listBoxControlTables.Items.EndUpdate();
        }

        if (listBoxControlTables.Items.Count > 0)
          listBoxControlTables.SelectedIndex = 0;
      }
      catch { }
    }

[hitchiker]

i got the solution to my problem on the link i sent u before this .... if u will read that thread u will understand what caused the problem .... so i dont need this code u just sent ...neways thanx for the effort

[sknake]

I'm glad you found a solution to your problem. I thought the link included was a reference so I was trying to get you another way to approach it :)

Please mark this thread as solved as you have found a solution to your problem. Good luck!