Collecting data from existing ListObject

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

Answer 1

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.

Answer 2

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