excel sheet showing only headings of columns selected from oledb database

Question

HI,

   I am created excel add-ins which is using  the data from database(i.e.OLE DB) & creating data set. I want to show only data table heading columns in excel sheet first column not whole data in sheet. But when I apply the  data-filter on columns in excel sheet, it should display all the  distinct data of that column by using the data set because data is not present in the sheet(only column headings are present in sheet ) & it is in the data table of data set. i should able to select the required data by applying filters on it to give it as feed for pivot table. 

   showing whole data on excel sheet is time consuming so i want to show only column name & must able to apply filter as it contain whole data from data set . please help me in this regards. i am stuck from  long time over this point. 

---

mahesh

Answer 1

1. when you want to use datatable :

            DataTable dt = new DataTable();
            dt.Columns[1].ColumnName;

2. when you want to use datareader : dr.GetName

         private void Sheet1_Startup(object sender, System.EventArgs e)
         {
             string oledbConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" +
                 @"Data Source=C:\tmp\Book1.xlsx;" +  
                "Extended Properties=\"Excel 12.0;HDR=YES\";";
             OleDbConnection conn = new OleDbConnection(oledbConnectionString);
             conn.Open();
             OleDbCommand cmd = new OleDbCommand("select * from [Sheet1$]", conn);
 

            OleDbDataReader dr = cmd.ExecuteReader();
 
            for (int i = 0; i < dr.FieldCount; i++)
             {
                 this.Range["a1", missing].get_Offset(0, i).Value2 = dr.GetName(i);
             }
             
            dr.Close();
             conn.Close();
         }

3. I think it would be better to use linq to dataset & Listobject.

---

http://vsto.tistory.com

Answer 2

Thank very much for reply,

              yes, option 1 is helpful for getting column name of data table but it is not satisfing my requirement. My requirement is i want all column name of data table in excel sheet first row. also I want the distinct data of each column so that i can apply filter on it(Because filter uses the distinct data of given range column). I don't want whole data of all columns because it is time consuming to put whole data in excel sheet. so that how i can get distinct data of each column from data table?  I want to put only distinct data of corresponding columns in excel sheet.

    can u give me the link so that i can try your 3rd option.

---

mahesh

Answer 3

simple Example

        private void Sheet1_Startup(object sender, System.EventArgs e)
        {
            DataSet ds = new DataSet();
            OleDbConnection connection = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;" +
                "Data Source=" + Globals.ThisWorkbook.Path + "\\" + Globals.ThisWorkbook.Name + "; Extended Properties=\"Excel 12.0;HDR=YES\";");

            OleDbDataAdapter test = new OleDbDataAdapter("select * from [Sheet2$]", connection);
            test.Fill(ds, "test");
            
            var query = from c in ds.Tables["test"].AsEnumerable()
                        select c;

            listobject.SetDataBinding(ds, "test");

            listobject.AutoSetDataBoundColumnHeaders = true;
        }

---

http://vsto.tistory.com

Answer 4

thanks ,

  but it is not  exactly what i expected. 

---

mahesh

Answer 5

Hey,

I am able to show the distinct data for each column in excel sheet it is possible because of data view.

data view have ".totable(true,"columname") method" which gives the distinct  value for passed column name.

   example:

      dim ds as dataset

  dim dv as dataview

dv.table=ds.tables(0)

dim newtable as datatable= dv.totable(true,"columnname")

---

mahesh