Saturday, May 4, 2013

Import data from Excel File into a dataTable or dataset ASP.Net: column limit 255

It is not possible that if you want to get columns after 255 into a single dataset. You can use the column Range into query to get required columns.
                string connectionString = String.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 8.0;HDR=YES;IMEX=1;""", "FileName.xlxs");
                string query = String.Format("select * from [SAX0207$A10:A20]", comboBox1.SelectedItem);
                OleDbDataAdapter dataAdapter = new OleDbDataAdapter(query, connectionString);
                DataSet ds= new DataSet();
                dataAdapter.Fill(ds);
                dataGridView.DataSource = ds.Tables[0];