#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2008
    Posts
    71
    Rep Power
    7

    C#: Reading specific row/column in dataSet


    Hey everyone,

    I have what seems like a simple question, but for some reason I can't get it to work. I currently have a program that can read an excel file by loading it in to a dataset, however I can't figure out how to read a specific row/column combination. Here's what I have so far.

    Code:
    // Connect to Excel file:
                OleDbConnection excel_conn = new OleDbConnection( str_connection );
    
                if ( excel_conn.State != System.Data.ConnectionState.Open) {
                    excel_conn.Open();
                }
                OleDbDataAdapter myCommand = new OleDbDataAdapter( @"SELECT * FROM [Sheet1$]", excel_conn );
    
                DataSet myDataSet = new DataSet();
                DataTable sheetData = new DataTable("ExcelInfo");
                myCommand.Fill(sheetData);
    
                myDataSet.Tables.Add(sheetData);
    
    
                // Access rows and columns:
                Response.Write("<br /> * ");
                foreach(DataTable thisTable in myDataSet.Tables){
                    // For each row, print the values of each column.
                    foreach(DataRow myRow in thisTable.Rows){
                        foreach(DataColumn myCol in thisTable.Columns){
                            // Console.WriteLine(myRow[myCol]);
                            Response.Write(myRow[myCol]);
                        }
                        Response.Write("<br /> * ");
                    }
    As you can see from the last bit of code, the previous person who worked on this code just needed all the data, I don't though. If someone could help me out by showing how I might access cell "A1" that would be much appreciated.

    Thanks,
    vital101
  2. #2
  3. ASP.Net MVP
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Aug 2003
    Location
    WI
    Posts
    4,378
    Rep Power
    1511
    Code:
    int ColumnIndex = 0; // can set this to anything, as long as a column at that index exists
    int RowIndex = 0; // can set this to anything, as long as row at that index exists
    
    // now look here for the value.  Since I don't know your data, I'm assuming it's a string:
    string result = sheetData.Rows[RowIndex].Items[ColumnIndex].ToString();
    
    // you can also go by column name:
    string ColumnName = "SomeColumn"; //need to use a real column name here
    result = sheetData.Rows[RowIndex].Items[ColumnName].ToString();
    One more thing... the sample your provided shows an unhealthy use of response.Write() in ASP.Net. You're much better off using a repeater or grid control for this kind of thing. Response.Write() in ASP.Net can get you in trouble.
    Primary Forum: .Net Development
    Holy cow, I'm now an ASP.Net MVP!

    [Moving to ASP.Net] | [.Net Dos and Don't for VB6 Programmers]

    http://twitter.com/jcoehoorn
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2008
    Posts
    71
    Rep Power
    7
    For using column name, is it possible to just use "A", "B", "C", etc, or does it actually have to be named?

    Originally Posted by f'lar
    Code:
    int ColumnIndex = 0; // can set this to anything, as long as a column at that index exists
    int RowIndex = 0; // can set this to anything, as long as row at that index exists
    
    // now look here for the value.  Since I don't know your data, I'm assuming it's a string:
    string result = sheetData.Rows[RowIndex].Items[ColumnIndex].ToString();
    
    // you can also go by column name:
    string ColumnName = "SomeColumn"; //need to use a real column name here
    result = sheetData.Rows[RowIndex].Items[ColumnName].ToString();
    One more thing... the sample your provided shows an unhealthy use of response.Write() in ASP.Net. You're much better off using a repeater or grid control for this kind of thing. Response.Write() in ASP.Net can get you in trouble.
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2008
    Posts
    71
    Rep Power
    7
    Also, I'm getting a compiler error:

    Compiler Error Message: CS0117: 'System.Data.DataRow' does not contain a definition for 'Items'

    Originally Posted by f'lar
    Code:
    int ColumnIndex = 0; // can set this to anything, as long as a column at that index exists
    int RowIndex = 0; // can set this to anything, as long as row at that index exists
    
    // now look here for the value.  Since I don't know your data, I'm assuming it's a string:
    string result = sheetData.Rows[RowIndex].Items[ColumnIndex].ToString();
    
    // you can also go by column name:
    string ColumnName = "SomeColumn"; //need to use a real column name here
    result = sheetData.Rows[RowIndex].Items[ColumnName].ToString();
    One more thing... the sample your provided shows an unhealthy use of response.Write() in ASP.Net. You're much better off using a repeater or grid control for this kind of thing. Response.Write() in ASP.Net can get you in trouble.
  8. #5
  9. ASP.Net MVP
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Aug 2003
    Location
    WI
    Posts
    4,378
    Rep Power
    1511
    If the column has been renamed, the column letter will not work. You can put a break point there and check what the column name is supposed to be in the debugger.

    I wrote the code from memory. It might be Item instead of Items. In vb you can omit the property name entirely and just say .Rows[RowIndex][Column], but I don't think that works in C#.
    Primary Forum: .Net Development
    Holy cow, I'm now an ASP.Net MVP!

    [Moving to ASP.Net] | [.Net Dos and Don't for VB6 Programmers]

    http://twitter.com/jcoehoorn
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2008
    Posts
    71
    Rep Power
    7
    Thanks for the help. "ItemArray" is what worked.
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2008
    Posts
    71
    Rep Power
    7
    Now that I am able to get data out of the spread sheet, I'm running into a slightly different problem. I can't seem to get data out of it in the right places. For instance, row/column 0/0 corresponds to A2, not A1 as you'd expect. Also, using a column name doesn't really work. Apparently only ints can be passed it ItemArray.

    Originally Posted by f'lar
    If the column has been renamed, the column letter will not work. You can put a break point there and check what the column name is supposed to be in the debugger.

    I wrote the code from memory. It might be Item instead of Items. In vb you can omit the property name entirely and just say .Rows[RowIndex][Column], but I don't think that works in C#.
  14. #8
  15. ASP.Net MVP
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Aug 2003
    Location
    WI
    Posts
    4,378
    Rep Power
    1511
    I don't know about the mis-aligned index problem, but it might be ignoring the first row as a header.

    I did some checking, and item array does only support ints, but you can use strings or ints with the shorthand syntax:
    sheetData.Rows[0]["ColumnName"];
    Primary Forum: .Net Development
    Holy cow, I'm now an ASP.Net MVP!

    [Moving to ASP.Net] | [.Net Dos and Don't for VB6 Programmers]

    http://twitter.com/jcoehoorn

IMN logo majestic logo threadwatch logo seochat tools logo