ole - Selecting a range in a worksheet C# -

i have following code opening , reading in excel spreadsheet:

var connectionstring = string.format("provider=microsoft.ace.oledb.12.0;data source={0};extended properties=excel 12.0;", filenametextbox.text); var querystring = string.format("select * [{0}]",details_sheet_name); var adapter = new oledbdataadapter(querystring, connectionstring); var ds = new dataset();  adapter.fill(ds, details_sheet_name);  datatable data = ds.tables[details_sheet_name];  datagridview1.datasource = data;                 datagridview1.autoresizecolumns(datagridviewautosizecolumnsmode.allcellsexceptheader); 

this , except i'm not interested in first row (possibly first 2 rows row 2 headers) of worksheet. how can modify select query select range in excel?

i'm interested in reading in columns a-n in rows rows 2 onwards contain data.

i need access couple of specific cells on different worksheet, assume have build adaptor different query string each of cells?

modify select statement including columns need instead of wildcard "*" in following example:

("select column1, column2 details_sheet_name"); 

you can apply additional logic in order remove unnecessary rows, example, "paging solution" (i.e. selecting rows n m) following one:

assuming database table "tbl_item" contains 2 columns (fields) of interest: “item” column, representing unique id , “rank”, used sorting in ascending order, general paging problem stated following: select n-rows table ordered rank offsetting (i.e. skipping) (m-n) rows:

select top n item,  rank (select top m rank, item tbl_item order rank)  [sub_tab] order rank desc  

this solution , extensions/samples thoroughly discussed in article pure sql solution database table paging (link: http://www.codeproject.com/tips/441079/pure-sql-solution-to-database-table-paging)

finally, can use code snippet shown below in listing 2 export content of datatable object in excel file plenty of customization features added code;

listing 2. export datatable excel file (2007/2010):

internal static bool export2excel(datatable datatable, bool interactive)  {     object misvalue = system.reflection.missing.value;      // note: don't include microsoft.office.interop.excel in reference (using),     // cause ambiguity w/system.data: both have datatable obj     microsoft.office.interop.excel.application _appexcel = null;     microsoft.office.interop.excel.workbook _excelworkbook = null;     microsoft.office.interop.excel.worksheet _excelworksheet = null;     try     {         // excel app object         _appexcel = new microsoft.office.interop.excel.application();          // make visible user if interactive flag set         _appexcel.visible = interactive;          // excel workbook object added app         _excelworkbook = _appexcel.workbooks.add(misvalue);          _excelworksheet = _appexcel.activeworkbook.activesheet              microsoft.office.interop.excel.worksheet;          // column names row (range obj)         microsoft.office.interop.excel.range _columnsnamerange;         _columnsnamerange = _excelworksheet.get_range("a1", misvalue);         _columnsnamerange = _columnsnamerange.get_resize(1, datatable.columns.count);          // data range obj         microsoft.office.interop.excel.range _datarange;         _datarange = _excelworksheet.get_range("a2", misvalue);         _datarange = _datarange.get_resize(datatable.rows.count, datatable.columns.count);          // column names array assigned columnnamerange         string[] _arrcolumnnames = new string[datatable.columns.count];          // 2d-array of data assigned _datarange         string[,] _arrdata = new string[datatable.rows.count, datatable.columns.count];          // populate both arrays: _arrcolumnnames , _arrdata         // note: 2d-array structured row[idx=0], col[idx=1]         (int = 0; < datatable.columns.count; i++)         {             (int j = 0; j < datatable.rows.count; j++)             {                 _arrcolumnnames[i] = datatable.columns[i].columnname;                 _arrdata[j, i] = datatable.rows[j][i].tostring();             }         }          //assign column names array _columnsnamerange obj         _columnsnamerange.set_value(misvalue, _arrcolumnnames);          //assign data array _datarange obj         _datarange.set_value(misvalue, _arrdata);          // save , close if interactive flag not set         if (!interactive)         {             // excel 2010 - "14.0"             // excel 2007 - "12.0"             string _ver = _appexcel.version;              string _filename ="tableexport_" +                  datetime.today.tostring("yyyy_mm_dd") + "-" +                 datetime.now.tostring("hh_mm_ss");              // check version , select file extension             if (_ver == "14.0" || _ver == "12.0")  { _filename += ".xlsx";}             else { _filename += ".xls"; }              // save , close excel workbook             _excelworkbook.close(true, "{drive letter}:\\" + _filename, misvalue);         }         return true;     }     catch (exception ex) {  throw; }         {         // quit excel app process         if (_appexcel != null)         {             _appexcel.usercontrol = false;             _appexcel.quit();             _appexcel = null;             misvalue = null;         }     } }