locked
Collecting data from existing ListObject RRS feed

  • Question

  • Hi dear,

    I create an Excel 2007 WorkBook project in Visual Studio 2010

    the purpose is loop through data in ListObject ( i also want to store data into Database after processing the data)

    I can get value of an cells in Range of ListObject to assign to another cells

    But i can't get correct value when assigning to a variable

    Example:

    //find the ListObject that is named "Table1" in Worksheet2 
    string lstobjName = "Table1";
    Worksheet ws = (Excel.Worksheet)Application.Worksheets[2];
    
    Excel.ListObject mListObj = null;
    
    foreach (Excel.ListObject lstobj in ws.ListObjects)
    {
      if (string.Compare(lstobj.Name, lstobjName) == 0)
      {
        mListObj = lstobj;
        break;
      }
    }
    
    //If found ListObject with "Table1" name
    if (mListObj != null)
    {
    	Excel.Range mRange = mListObj.DataBodyRange;
    	// assign the value of 1st row and 1st col of data range of list object to Cells "A1"
    	this.Range["A1", missing].Value = mRange.Offset[0,0].value2; //Successful, correctly value
    	// assign the value of 1st row and 1st col of data range of list object to variable mValue
    	string mValue = mRange.Offset[0,0].value2; // the mValue = "System.Object[,]"
    }
    
    Do you have any suggestion for me?

    there is no DataMember/DataSource in Excel.ListObject.

    how can i generate a table from ListObject?

    Thank you

    Dan

    Monday, August 22, 2011 5:22 AM

Answers

  • Hi DanDc,

     

    Thanks for posting in the MSDN Forum.

     

    I can’t see the clear goal in your description. I assume that you want the variable “mValue” can assess the value of ListObject’s first row’s first cell. Is it right?

     

    If it is, please use the “mRange.Offset[1,1].value2” in your snippet. The Range’s Offset collection’s index will begin from “1” instead of “0” in normal C# Collection’s index “0”.

     

    I hope what I said can help you.

     

    Have a good day,

     

    Tom


    Tom Xu [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by 许阳(无锡) Monday, September 5, 2011 2:58 AM
    Wednesday, August 24, 2011 1:38 AM

All replies

  • Hi DanDc,

     

    Thanks for posting in the MSDN Forum.

     

    I can’t see the clear goal in your description. I assume that you want the variable “mValue” can assess the value of ListObject’s first row’s first cell. Is it right?

     

    If it is, please use the “mRange.Offset[1,1].value2” in your snippet. The Range’s Offset collection’s index will begin from “1” instead of “0” in normal C# Collection’s index “0”.

     

    I hope what I said can help you.

     

    Have a good day,

     

    Tom


    Tom Xu [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by 许阳(无锡) Monday, September 5, 2011 2:58 AM
    Wednesday, August 24, 2011 1:38 AM
  • Hi Tom,

    yes, you guess what i want to do

    the variable “mValue” can assess the value of a cell of ListObject

    but it doesn't work and throw an exception  "System.Object[,]"

    can you test the below code

    or download https://skydrive.live.com/?cid=f783092db9f4669d&sc=documents&uc=1&id=F783092DB9F4669D%21132#

    File: ListObjectValue.zip

    private void Test(Excel.Worksheet worksheet)
            {
                try
                {
                    Excel.ListObject lstObj = null;
    
                    foreach (Excel.ListObject obj in worksheet.ListObjects)
                    {
                        if (string.Compare(obj.Name, "Table1") == 0)
                        {
                            lstObj = obj;
                            break;
                        }
                    }
    
                    if (lstObj != null)
                    {
                        Excel.Range exRange = lstObj.DataBodyRange;
    
                        worksheet.Range["B6", missing].Value = exRange.Offset[0, 0].Value2;
                        worksheet.Range["C6", missing].Value = exRange.Offset[0, 1].Value2;
                        worksheet.Range["D6", missing].Value = exRange.Offset[0, 2].Value2;
    
                        int iValue = Convert.ToInt32(exRange.Offset[0, 0].Value2);
                        double dValue = Convert.ToDouble(exRange.Offset[0, 1].Value2);
                        string sValue = Convert.ToString(exRange.Offset[0, 2].Value2);
                        worksheet.Range["B7", missing].Value = iValue;
                        worksheet.Range["C7", missing].Value = dValue;
                        worksheet.Range["D7", missing].Value = sValue;
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
            }


    Thank you very much

    Dan

     

     



    • Edited by DanDC Saturday, September 10, 2011 7:43 AM
    Saturday, September 10, 2011 7:29 AM