I want query a linq to xml data structure. The xml is saved from the xls[Excel] file. The excel has many sheets, columns, rows and cell.
I want to get the values from cells from this file. I have written a LINQ query. But the query is not seems to be ok. It is in VB.
Dim sheet = From s In book...<Worksheet> Where (s.@ss:Name = "employee list") _
Let OrderId = s.<Row>.<Cell>(0).Value _
Let Age = s.<Row>.<Cell>(4).Value _
Let sex = s.<Row>.<Cell>(5).Value _
Let SEmpDate = s.<Row>.<Cell>(6).Value _
Let EEmpDate = s.<Row>.<Cell>(7).Value _
Let avgPartTime = s.<Row>.<Cell>(9).Value _
Let isClause = s.<Row>.<Cell>(10).Value _
Let shutDownMethodReserve = s.<Row>.<Cell>(15).Value _
Let policyInPaying = s.<Row>.<Cell>(16).Value _
Let currReserveBefore2003 = s.<Row>.<Cell>(17).Value _
Let currReserveAfter2003 = s.<Row>.<Cell>(18).Value _
Let currReserveBefore2004 = s.<Row>.<Cell>(19).Value _
Let currReserveAfter2004 = s.<Row>.<Cell>(20).Value _
Let currReserveProvFund = s.<Row>.<Cell>(21).Value _
How can I derive the cells value, from this complex xml structure. This excel have many sheets, rows and columns.
I extracted the rows properly. By the following lines of code.
Dim sheet = From s In book...<Worksheet> Where (s.@ss:Name = "employee list") _
Let rows = s...<Row> _
Select rows
Dim xmlExcel = <val><%= sheet ></val>
I still have the problem, I modified some codes and the xml is also too simple, but I don't understand, why am I not getting the result, and why I am getting "object reference not set to an instance of an object"- error is popping up when I try to fetch the data in a foreach loop after the codes below.
Dim xmlSheet = From val In xmlExcel...<Row> _
Let OrderId = val.<Cell>(0) _
Let Age = val.<Cell>(4) _
Let sex = val.<Cell>(5) _
Let SEmpDate = val.<Cell>(6) _
Let EEmpDate = val.<Cell>(7) _
Let avgPartTime = val.<Cell>(9) _
Let isClause = val.<Cell>(10) _
Let shutDownMethodReserve = val.<Cell>(15) _
Let policyInPaying = val.<Cell>(16) _
Let currReserveBefore2003 = val.<Cell>(17) _
Let currReserveAfter2003 = val.<Cell>(18) _
Let currReserveBefore2004 = val.<Cell>(19) _
Let currReserveAfter2004 = val.<Cell>(20) _
Let currReserveProvFund = val.<Cell>(21) _
Select _
OrderId = OrderId.Value, _
Age = Age.Value, _
Sex = sex.Value, _
SEmpDate = SEmpDate.Value, _
EEmpDate = EEmpDate.Value, _
avgPartTime = avgPartTime.Value, _
isClause = isClause.Value, _
shutDownMethodReserve = shutDownMethodReserve.Value, _
policyInPaying = policyInPaying.Value, _
currReserveBefore2003 = currReserveBefore2003.Value, _
currReserveAfter2003 = currReserveAfter2003.Value, _
currReserveBefore2004 = currReserveBefore2004.Value, _
currReserveAfter2004 = currReserveAfter2004.Value, _
currReserveProvFund = currReserveProvFund.Value
For Each customer In xmlSheet
Console.WriteLine(customer.Age)
Next
That is guessing as to what might be the problem and how to cure it. If you still have problems then consider to post a sample of the XML you are trying to process. Otherwise it is hard to tell where exactly things go wrong.
Martin Honnen --- MVP Data Platform Development
My blog
prithiraj
Member
7 Points
11 Posts
LINQ to XML.
Mar 12, 2010 07:32 AM|LINK
Hi,
I want query a linq to xml data structure. The xml is saved from the xls[Excel] file. The excel has many sheets, columns, rows and cell.
I want to get the values from cells from this file. I have written a LINQ query. But the query is not seems to be ok. It is in VB.
Dim sheet = From s In book...<Worksheet> Where (s.@ss:Name = "employee list") _ Let OrderId = s.<Row>.<Cell>(0).Value _ Let Age = s.<Row>.<Cell>(4).Value _ Let sex = s.<Row>.<Cell>(5).Value _ Let SEmpDate = s.<Row>.<Cell>(6).Value _ Let EEmpDate = s.<Row>.<Cell>(7).Value _ Let avgPartTime = s.<Row>.<Cell>(9).Value _ Let isClause = s.<Row>.<Cell>(10).Value _ Let shutDownMethodReserve = s.<Row>.<Cell>(15).Value _ Let policyInPaying = s.<Row>.<Cell>(16).Value _ Let currReserveBefore2003 = s.<Row>.<Cell>(17).Value _ Let currReserveAfter2003 = s.<Row>.<Cell>(18).Value _ Let currReserveBefore2004 = s.<Row>.<Cell>(19).Value _ Let currReserveAfter2004 = s.<Row>.<Cell>(20).Value _ Let currReserveProvFund = s.<Row>.<Cell>(21).Value _How can I derive the cells value, from this complex xml structure. This excel have many sheets, rows and columns.
Regards
Prithiraj Sengupta
prithiraj
Member
7 Points
11 Posts
Re: LINQ to XML.
Mar 14, 2010 04:37 PM|LINK
I extracted the rows properly. By the following lines of code.
Dim sheet = From s In book...<Worksheet> Where (s.@ss:Name = "employee list") _ Let rows = s...<Row> _ Select rows Dim xmlExcel = <val><%= sheet ></val>I still have the problem, I modified some codes and the xml is also too simple, but I don't understand, why am I not getting the result, and why I am getting "object reference not set to an instance of an object"- error is popping up when I try to fetch the data in a foreach loop after the codes below.
Dim xmlSheet = From val In xmlExcel...<Row> _ Let OrderId = val.<Cell>(0) _ Let Age = val.<Cell>(4) _ Let sex = val.<Cell>(5) _ Let SEmpDate = val.<Cell>(6) _ Let EEmpDate = val.<Cell>(7) _ Let avgPartTime = val.<Cell>(9) _ Let isClause = val.<Cell>(10) _ Let shutDownMethodReserve = val.<Cell>(15) _ Let policyInPaying = val.<Cell>(16) _ Let currReserveBefore2003 = val.<Cell>(17) _ Let currReserveAfter2003 = val.<Cell>(18) _ Let currReserveBefore2004 = val.<Cell>(19) _ Let currReserveAfter2004 = val.<Cell>(20) _ Let currReserveProvFund = val.<Cell>(21) _ Select _ OrderId = OrderId.Value, _ Age = Age.Value, _ Sex = sex.Value, _ SEmpDate = SEmpDate.Value, _ EEmpDate = EEmpDate.Value, _ avgPartTime = avgPartTime.Value, _ isClause = isClause.Value, _ shutDownMethodReserve = shutDownMethodReserve.Value, _ policyInPaying = policyInPaying.Value, _ currReserveBefore2003 = currReserveBefore2003.Value, _ currReserveAfter2003 = currReserveAfter2003.Value, _ currReserveBefore2004 = currReserveBefore2004.Value, _ currReserveAfter2004 = currReserveAfter2004.Value, _ currReserveProvFund = currReserveProvFund.ValueFor Each customer In xmlSheet Console.WriteLine(customer.Age) Nextlinq to xml
Martin_Honne...
Star
14481 Points
2006 Posts
MVP
Re: LINQ to XML.
Mar 14, 2010 05:34 PM|LINK
The error could happen if there is a Row element that does not have a Cell element with the index you use (e.g. 21).
To avoid, instead of
currReserveProvFund = currReserveProvFund.Value
you could code
currReserveProvFund = CType(currReserveProvFund, String)
That is guessing as to what might be the problem and how to cure it. If you still have problems then consider to post a sample of the XML you are trying to process. Otherwise it is hard to tell where exactly things go wrong.
My blog
prithiraj
Member
7 Points
11 Posts
Re: LINQ to XML.
Mar 15, 2010 06:43 AM|LINK
Yes we should use the currReserveProvFund = CType(currReserveProvFund, String).
Finaly, found the answer, I missed the Where clause.
The query will look like
Dim xmlFinal = From k In sheet...<Row> _ Let OrderId = k.<Cell>(0) _ Let Age = k.<Cell>(4) _ Let sex = k.<Cell>(5) _ Let SEmpDate = k.<Cell>(6) _ Let EEmpDate = k.<Cell>(7) _ Let avgPartTime = k.<Cell>(9) _ Let isClause = k.<Cell>(10) _ Let shutDownMethodReserve = k.<Cell>(15) _ Let policyInPaying = k.<Cell>(16) _ Let currReserveBefore2003 = k.<Cell>(17) _ Let currReserveAfter2003 = k.<Cell>(18) _ Let currReserveBefore2004 = k.<Cell>(19) _ Let currReserveAfter2004 = k.<Cell>(20) _ Let currReserveProvFund = k.<Cell>(21) _ Where (OrderId IsNot Nothing And _ Age IsNot Nothing And _ sex IsNot Nothing And _ SEmpDate IsNot Nothing And _ EEmpDate IsNot Nothing And _ avgPartTime IsNot Nothing And _ isClause IsNot Nothing And _ shutDownMethodReserve IsNot Nothing And _ policyInPaying IsNot Nothing And _ currReserveBefore2003 IsNot Nothing And _ currReserveAfter2003 IsNot Nothing And _ currReserveBefore2004 IsNot Nothing And _ currReserveAfter2004 IsNot Nothing And _ currReserveProvFund IsNot Nothing) _ Select orderId = OrderId.Value, _ Age = Age.Value, _ sex = sex.Value, _ SEmpDate = SEmpDate.Value, _ EEmpDate = EEmpDate.Value, _ avgPartTime = avgPartTime.Value, _ isClause = isClause.Value, _ shutDownMethodReserve = shutDownMethodReserve.Value, _ policyInPaying = policyInPaying.Value, _ currReserveBefore2003 = currReserveBefore2003.Value, _ currReserveAfter2003 = currReserveAfter2003.Value, _ currReserveBefore2004 = currReserveBefore2004.Value, _ currReserveAfter2004 = currReserveAfter2004.Value, _ currReserveProvFund = currReserveProvFund.Valuelinq to xml