Selecting a range in a worksheet C#

Question

I currently have the following code opening and reading in an 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 * FROM [{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 is all good and well except I'm not interested in the first row (Possibly first two rows as row 2 is headers) of the worksheet. How can I modify the select Query to select a range like I would in excel?

I'm interested in reading in say columns A-N in rows all rows from 2 onwards that contain data.

I also need to access a couple of specific cells on a different worksheet, I assume I have to build another adaptor with a different query string for each of those cells?

Please don't repeat tags in question titles. – Ondrej Tucny May 10 '13 at 16:55 — Ondrej Tucny, May 10 '13 at 16:55

Alexander Bell · Answer 1 · 2013-05-10T18:26:43.773

Modify Select statement including just the columns you need instead of wildcard "*" like in the following example:

("SELECT Column1, Column2 FROM DETAILS_SHEET_NAME");

You can apply additional logic in order to remove unnecessary rows, for example, a "paging solution" (i.e. selecting rows from N to M) like the following one:

Assuming the Database Table "TBL_ITEM" contains two columns (fields) of interest: “Item” column, representing the unique ID and “Rank”, which is used for sorting in ascending order, the general paging problem is stated as following: Select N-rows from the table ordered by Rank offsetting (i.e. skipping) (M-N) rows:

SELECT TOP N Item, 
Rank FROM (SELECT TOP M Rank, Item FROM TBL_ITEM ORDER BY Rank) 
AS [SUB_TAB] ORDER BY Rank DESC

This solution and its extensions/samples are thoroughly discussed in my article Pure SQL solution to Database Table Paging (link: http://www.codeproject.com/Tips/441079/Pure-SQL-solution-to-Database-Table-Paging)

Finally, you can use a code snippet shown below in Listing 2 to export a content of DataTable object in Excel file with plenty of customization features that could be added to a code;

Listing 2. Export DataTable to 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),
    // it will 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 it visible to User if Interactive flag is set
        _appExcel.Visible = Interactive;

        // excel workbook object added to app
        _excelWorkbook = _appExcel.Workbooks.Add(misValue);

        _excelWorksheet = _appExcel.ActiveWorkbook.ActiveSheet 
            as 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 to be assigned to columnNameRange
        string[] _arrColumnNames = new string[dataTable.Columns.Count];

        // 2d-array of data to be assigned to _dataRange
        string[,] _arrData = new string[dataTable.Rows.Count, dataTable.Columns.Count];

        // populate both arrays: _arrColumnNames and _arrData
        // note: 2d-array structured as row[idx=0], col[idx=1]
        for (int i = 0; i < dataTable.Columns.Count; i++)
        {
            for (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 to _columnsNameRange obj
        _columnsNameRange.set_Value(misValue, _arrColumnNames);

        //assign data array to _dataRange obj
        _dataRange.set_Value(misValue, _arrData);

        // save and 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 and select file extension
            if (_ver == "14.0" || _ver == "12.0")  { _fileName += ".xlsx";}
            else { _fileName += ".xls"; }

            // save and close Excel workbook
            _excelWorkbook.Close(true, "{DRIVE LETTER}:\\" + _fileName, misValue);
        }
        return true;
    }
    catch (Exception ex) {  throw; }
    finally
    {
        // quit excel app process
        if (_appExcel != null)
        {
            _appExcel.UserControl = false;
            _appExcel.Quit();
            _appExcel = null;
            misValue = null;
        }
    }
}

I am giving a solution for this (quote): "I'm interested in reading in say columns A-N" — Alexander Bell, May 10 '13 at 16:37
@Abe Miessler: I am just wondering, why are you down-voting my answer? I gave your a reasonable explanation, and also extended my code to address a second issue. Rgds, — Alexander Bell, May 10 '13 at 17:43
your first answer and the subsequent explanation of why it is a legitimate answer simply don't fly. It's obvious that the main point of this question is to determine how to get a sub set of rows that is in the middle of the parent set. The post does mention (in passing) that they also want to select specific columns, but if you are suggesting that is the question, I would say you are wrong. Your update does seem to be relevant. If you include a sample query in your answer rather than just posting the link I will reverse my vote. — Abe Miessler, May 10 '13 at 17:58
OK, I've amended my answer with code snippets and brief description; detailed discussion included in referenced article. Rgds, — Alexander Bell, May 10 '13 at 18:32

score 0 · Answer 2 · edited May 23 '17 at 12:21

0

You can simply ask for no headers. Modify your connection string, add HDR=No.

For your second issue, I found this post, Maybe you'll find it helpful.

edited May 23 '17 at 12:21

Community

1
1

answered May 10 '13 at 16:36

Novak

2,760
9
42
63

I don't think the first two rows are headers. – Omar Kooheji May 10 '13 at 16:40

Selecting a range in a worksheet C#

2 Answers2