none
Object Reference not set to an instance of an object LINQ RRS feed

  • Question

  • IEnumerable<String> textValues =
                              from cell in row.Descendants<Cell>()
                              select Convert.ToString(cell.CellValue.InnerText);

    If I use (cell.CellValue)  I get the xml path to cellValue not the actual value.

    This is driving me crazy...

     

    I have some null or empty cells that I have to contend with that is why I am attempting to convert it to a string.

     

    • Moved by Martin_Xie Friday, November 25, 2011 8:54 AM Move to LINQ Forum for better support (From:Visual C# General)
    Tuesday, November 22, 2011 7:46 PM

Answers

  • Response Followup:
     
    I realized shortly after posting this response that the DataRow Function was not optimized.  I am finally posting the re-factored solution.
    Change Load SubRoutine and DataRow Function as shown below:
     
    -----Begin Snip from Load DataTable SubRoutine----- 
     
            If x_value.Count > 0 Then
              ' Convert the ArrayList to a Typed DataRow, add it to the DataTable
              If tbl.Columns.Contains("V_DATE") Then
                tbl.Rows.Add(Data_Row(x_value, tbl.Columns).ToArray)
              Else
                ' If DataTypes can be Cast automatically, just add DataRow here...
                tbl.Rows.Add(x_value.ToArray)
              End If
            Else
              Exit For
            End If

    -----End Snip from Load DataTable SubRoutine-----
     
      Private Shared Function Data_Row(ByVal i_str As ArrayList, _
                                ByVal i_col As DataColumnCollection) _
                                As ArrayList
        Try
          ' Use LINQ to capture the Table Column Types
          Dim x_col As IEnumerable(Of String) = _
          From t_col In i_col Select ((t_col.DataType).ToString)
          Dim x_value = i_str.ToArray
          Dim x_data As New ArrayList
          For i As Integer = 0 To x_value.Count - 1
            ' Only Convert Required Types
            ' DataSet consumption will automatically Cast most Types...
            Select Case x_col(i).ToString
              Case "System.DateTime"
                x_data.Add(DateTime.FromOADate(Double.Parse(x_value(i))))
              Case Else
                x_data.Add(x_value(i))
            End Select
          Next
          Return x_data
        Catch ex As Exception
          Return Nothing
        End Try
      End Function
    
     
    Best Regards,
    Paul Barcomb

    Paul F. Barcomb Principle Application Developer Belcan TechServices
    • Proposed as answer by Paul Barcomb Wednesday, January 25, 2012 7:33 PM
    • Edited by Paul Barcomb Wednesday, January 25, 2012 7:46 PM
    • Marked as answer by mwdenko Wednesday, January 25, 2012 8:23 PM
    Wednesday, January 25, 2012 7:33 PM

All replies

  • If cell.CellValue is null, this could cause the issue.  You could handle that explicitly:

    IEnumerable<String> textValues = 
               row.Descendants<Cell>()
                    .Select(cell => cell.CellValue == null ? "NULL" : cell.CellValue.InnerText);
    
    



    Reed Copsey, Jr. - http://reedcopsey.com
    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    Tuesday, November 22, 2011 7:51 PM
  • Or to add to what Reed is saying, maybe put in a where clause to remove the null ones before they are converted such as:


    row.Descendants
         .OfType<Cell>()
         .Where( cl => cl.CellValue != null)
         .Select( cl => Convert.ToString(cl.CellValue.InnerText) );
    

    Note, can't you also just say cell.CellValue.InnerText.ToString() without the convert? 

     

    HTH
    William Wegerson (www.OmegaCoder.Com)
    • Edited by OmegaManModerator Tuesday, November 22, 2011 8:04 PM bad psuedo code fix; Hi Reed. :-)
    Tuesday, November 22, 2011 8:01 PM
    Moderator
  • I would love to skip them but they are intermittant and when I read the textValues the data gets messed up.

     

    ex.

    Date - Name - ID - Notes

    8/8/11 - Bill - 1 - something

    8/9/11 -   - 2 - nothing.

     

    When the name is blank...  the read textValues will be

    [0] = 8/9/11

    [1] = 2

    [2] = nothing.

     

    It should be...

    [0] = 8/9/11

    [1] = NULL

    [2] = 2

    [3] = nothing.

    Tuesday, November 22, 2011 8:07 PM
  • I'm not sure what it is.  I am getting strange data.  I'll get the "NULL".  But, the data being read isn't what is in the sheet.

     

    It looks like it is some kind of pointer to data.  Not the actual data. 

     

    Thanks for your help!!!

    Tuesday, November 22, 2011 8:09 PM
  • What is your actual goal? Do you need to push the data to the database or generate a quick row report?
    William Wegerson (www.OmegaCoder.Com)
    Tuesday, November 22, 2011 8:28 PM
    Moderator
  • I'm attempting to put the data from excel into a "row" class.  Then I am loading into sql 2008 after all of the rows have been read.

     


    If loading directly is easier then I'm all for it.
    • Edited by mwdenko Tuesday, November 22, 2011 8:42 PM added note...
    Tuesday, November 22, 2011 8:32 PM
  • Ok...you are doing interop programming. Yes things will be null at certain times. You will need to write code to handle those situations. Here is an article I haven't published yet on working with excel rows here is the top level code:

    Excel.Range range = TheSheet.get_Range( "A3", "C5" );
    
    List< List<string> > RangeValues = ConvertToStringArray( (System.Array) range.get_Value(), 3 );
    
    var data = string.Join("|", RangeValues.Select( ln => string.Join( " ", ln.ToArray() ) ).ToArray());
    

    here is the Covert function:

    /// <summary>
    /// Take in a System Array usually from an Excel Range and create a list of list of strings.
    /// Each new row is a new list.
    /// </summary>
    /// <param name="values"></param>
    /// <param name="columns">Hint for number of columns being looked at.</param>
    /// <returns></returns>
    List< List<string> > ConvertToStringArray( System.Array values, int columns )
    {
        List<List<string>> retList = new List<List<string>>();
        int column = columns;
        List<string> currentList = null;
    
        foreach ( object obj in values )
        {
            if ( ++column > columns )
            {
                retList.Add( currentList = new List<string>() );
                column = 1;
            }
    
            currentList.Add( ( obj != null ) ? obj.ToString().Trim() : string.Empty );
        }
    
        return retList;
    }
    

    so I believe you maybe running into something else.

     

    On a side note, this is old code, Since I learned Linq I don't use foreach'es anymore! :-)


    William Wegerson (www.OmegaCoder.Com)
    Tuesday, November 22, 2011 9:01 PM
    Moderator
  • Thanks a million!  I'll give this a try later tonight!
    Tuesday, November 22, 2011 9:13 PM
  • I would love to skip them but they are intermittant and when I read the textValues the data gets messed up.

     

    ex.

    Date - Name - ID - Notes

    8/8/11 - Bill - 1 - something

    8/9/11 -   - 2 - nothing.

     

    When the name is blank...  the read textValues will be

    [0] = 8/9/11

    [1] = 2

    [2] = nothing.

     

    It should be...

    [0] = 8/9/11

    [1] = NULL

    [2] = 2

    [3] = nothing.

    So why don't you use @Reed's suggestion ?

    aelassas.free.fr
    Tuesday, November 22, 2011 9:45 PM
  • I'm not sure what it is.  I am getting strange data.  I'll get the "NULL".  But, the data being read isn't what is in the sheet.

     

    It looks like it is some kind of pointer to data.  Not the actual data. 

     

    Thanks for your help!!!


    Tuesday, November 22, 2011 10:08 PM
  • Link.Fr...thanks for the reply on my blog to the post: Asp.Net C# Creating an Excel Document From Data without using Office Interops

    :-)


    William Wegerson (www.OmegaCoder.Com)
    Tuesday, November 22, 2011 10:30 PM
    Moderator
  • @OmegaMan, you're welcome. Here is the rss version.
    aelassas.free.fr
    Tuesday, November 22, 2011 10:35 PM
  •             public static List<Customer> LoadCustomers(Worksheet worksheet,
                  SharedStringTable sharedString)
                {
                    //Initialize the customer list.
                    List<Customer> result = new List<Customer>();
    
    
                    IEnumerable<Row> dataRows =
                      from row in worksheet.Descendants<Row>()
                      where row.RowIndex > 1
                      select row;
                    int countr = 0;
                    try
                    {
                        foreach (Row row in dataRows)
                        {
               
                            IEnumerable<String> textValues =
                              from cell in row.Descendants<Cell>()
                              select (
                              //  //cell.DataType != null
                              //  //  &&
                              //  //  cell.DataType.HasValue
                              //  //  &&
                                cell.DataType == CellValues.SharedString
                                 ? sharedString.ChildElements[
                                  int.Parse(cell.CellValue.InnerText)].InnerText
                                : cell.CellValue.InnerText
                                )
                              ;
                            bool check = false;
                            //Check to verify the row contained data.
                            if (textValues.Count() > 0)
                            {
                                //Create a customer and add it to the list.
                                var textArray = textValues.ToArray();
    
    

    This was my original code.  It seems that I will only get pointers to values if I use the two examples stated earlier.  (Not sure how to get values.)

    If I leave the code as is it will bomb when a null value hits.  But, I need to know if a field is null when loading the class.  Is there a way to add a test to the (?sharedString.ChildElements) statement? 

    If I uncomment the != null and has values I lose the fact that a cell is null.

    I appreciate all the help so far.  I wish I knew more about Linq.  But, this project is forcing me to fly by the seat of my pants..  :)

    Thanks again!!!!

    Wednesday, November 23, 2011 3:12 AM
  • Which version of the Office interops are you targetting?
    William Wegerson (www.OmegaCoder.Com)
    Wednesday, November 23, 2011 1:54 PM
    Moderator
  • 2010
    Wednesday, November 23, 2011 1:59 PM
  • LINQ to SQL for XML SpreadSheet, SpreadSheet Query to DataTable INCLUDING Null or Empty Cells

    I recently ran into the same problem while developing an Excel Application AddIn. LINQ was not my first approach but since I was not happy with the speed of my initial approach I kept looking for a better option which brought me to LINQ and the problem you described.  The problem as I see it occurs when running a LINQ Query on Excel SpreadSheet Row with the intent to return ALL the Cell values (including empty cells). After researching the issue I found that LINQ query will not return values for SpreadSheet Cells that are empty. Instead, the query will return a collapsed record wherein cells of empty value are filled with values from down-column cells. This was a frustrating issue and much of my initial excitement about the LINQ capability dwindled as I looked for a resolution.

    As I understand it, this issue is related to two limitations:

    1) The limitation in the XML format where no Objects are included for "InnerText or CellValue" for empty Cells

    2) Limitation in LINQ where no extension exists to accommodate the mentioned XML imparity. I think this stinks but after all, we get paid to overcome problems...

    I like LINQ for many reasons so I stayed with it and developed this workaround.  If someone has a direct solution using a single LINQ Query I would love to know about it.  Given my time constraints, this was the best I could do...

    My goal was to fill a System.Data.DataTable with SpreadSheet.Rows using LINQ to SQL Query on XML SpreadSheet Table. Initially I followed the solution by Michael Case (see link to code just below) which got me pretty far although I was not performing the exact same tasks as outlined in his solution.

    http://msdn.microsoft.com/en-us/library/dd920313(v=office.12).aspx

    When I accepted that the LINQ query would not return empty cell values (at least in a straight forward manner) I transformed my code to a combination of the best from my original code and the LINQ approach. I ended up with this code which does exactly what I want and has some speed increase over my original approach.

    The first Sub-Routine is adapted from the Michael Case solution referenced above.  I modified that code to capture and include Null / Empty Cells.  Then I added a Function that interprets and Types the data stream based on System.DataTable requirements and returns Typed DataRows that are directly appended to a DataTable.

    The code is in VB but easy to follow. I hope that this gives something back for all the times I have benefitted from these boards…

    Best Regards to all,

    Paul Barcomb

    Belcan TechServices

     

    Private Sub Load_DataTable(ByVal x_sht As Worksheet, _
                                   ByVal x_str As SharedStringTable, _
                                   ByRef tbl As DataTable)
          Try
            Dim cdx As Integer = tbl.Columns.Count
            Dim x_value As ArrayList = Nothing
            Dim renum As IEnumerator = Nothing
            Dim x_cell As Cell = Nothing
            Dim dRows As IEnumerable(Of Row) = _
            From x_row In x_sht.Descendants(Of Row)() _
            Where x_row.RowIndex.Value > 1 Select x_row
            '
            For Each x_row As Row In dRows
              x_value = New ArrayList
              renum = x_row.GetEnumerator
              While renum.MoveNext
                x_cell = renum.Current
                ' Here is where the Empty Cells are included!!!
                Select Case True
                  Case x_cell.CellValue Is Nothing
                    x_value.Add("")
                  Case Else
                    ' This bit is from the Solution by Michael Case
                    ' It is revised to meet my requirements
                    If (x_cell.DataType IsNot Nothing _
                        AndAlso x_cell.DataType.HasValue _
                        AndAlso x_cell.DataType.Value = _
                        CellValues.SharedString) Then
                      x_value.Add( _
                      x_str.ChildElements( _
                      Integer.Parse(x_cell.CellValue.InnerText)).InnerText)
                    Else
                      x_value.Add(x_cell.CellValue.InnerText)
                    End If
                End Select
              End While
              '
              If x_value.Count > 0 Then
                ' Convert the ArrayList to a Typed DataRow, add it to the DataTable
                tbl.Rows.Add(Data_Row(x_value, tbl.Columns).ToArray)
              Else
                Exit For
              End If
              '
            Next
            '
          Catch ex As Exception
    
          End Try
        End Sub
    
        Private Function Data_Row(ByVal i_str As ArrayList, _
                                  ByVal i_col As DataColumnCollection) _
                                  As ArrayList
          Try
            ' Use LINQ to capture the Table Column Types
            Dim x_col As IEnumerable(Of String) = _
            From t_col In i_col Select ((t_col.DataType).ToString)
            Dim x_value = i_str.ToArray
            Dim x_data As New ArrayList
            For i As Integer = 0 To x_value.Count - 1
              ' These were my Types, you can add yuor own as needed
              Select Case x_col(i).ToString
                Case "System.Int32"
                  x_data.Add(Integer.Parse(x_value(i)))
                Case "System.Decimal"
                  Select Case x_value(i).ToString
                    Case ""
                      ' ***To accommodate DBNull*** 
                      ' The DataTable must be configured to allow DBNull
                      ' Using DBNull keeps the file size smaller..
                      x_data.Add(DBNull.Value)
                    Case Else
                      x_data.Add(Decimal.Parse(x_value(i)))
                  End Select
                Case "System.String"
                  x_data.Add(x_value(i))
                Case "System.DateTime"
                  x_data.Add(DateTime.FromOADate(Double.Parse(x_value(i))))
              End Select
            Next
            Return x_data
          Catch ex As Exception
            Return Nothing
          End Try
        End Function
    


    Paul F. Barcomb Principle Application Developer Belcan TechServices
    • Proposed as answer by Paul Barcomb Friday, December 23, 2011 11:36 PM
    • Unproposed as answer by Paul Barcomb Friday, December 23, 2011 11:37 PM
    • Proposed as answer by Paul Barcomb Wednesday, January 25, 2012 7:21 PM
    Friday, December 23, 2011 11:28 PM
  • Response Followup:
     
    I realized shortly after posting this response that the DataRow Function was not optimized.  I am finally posting the re-factored solution.
    Change Load SubRoutine and DataRow Function as shown below:
     
    -----Begin Snip from Load DataTable SubRoutine----- 
     
            If x_value.Count > 0 Then
              ' Convert the ArrayList to a Typed DataRow, add it to the DataTable
              If tbl.Columns.Contains("V_DATE") Then
                tbl.Rows.Add(Data_Row(x_value, tbl.Columns).ToArray)
              Else
                ' If DataTypes can be Cast automatically, just add DataRow here...
                tbl.Rows.Add(x_value.ToArray)
              End If
            Else
              Exit For
            End If

    -----End Snip from Load DataTable SubRoutine-----
     
      Private Shared Function Data_Row(ByVal i_str As ArrayList, _
                                ByVal i_col As DataColumnCollection) _
                                As ArrayList
        Try
          ' Use LINQ to capture the Table Column Types
          Dim x_col As IEnumerable(Of String) = _
          From t_col In i_col Select ((t_col.DataType).ToString)
          Dim x_value = i_str.ToArray
          Dim x_data As New ArrayList
          For i As Integer = 0 To x_value.Count - 1
            ' Only Convert Required Types
            ' DataSet consumption will automatically Cast most Types...
            Select Case x_col(i).ToString
              Case "System.DateTime"
                x_data.Add(DateTime.FromOADate(Double.Parse(x_value(i))))
              Case Else
                x_data.Add(x_value(i))
            End Select
          Next
          Return x_data
        Catch ex As Exception
          Return Nothing
        End Try
      End Function
    
     
    Best Regards,
    Paul Barcomb

    Paul F. Barcomb Principle Application Developer Belcan TechServices
    • Proposed as answer by Paul Barcomb Wednesday, January 25, 2012 7:33 PM
    • Edited by Paul Barcomb Wednesday, January 25, 2012 7:46 PM
    • Marked as answer by mwdenko Wednesday, January 25, 2012 8:23 PM
    Wednesday, January 25, 2012 7:33 PM