Svideo (21) [Avatar] Offline
#1
I'm using the openxml ctp to retrieve rows from a spreadsheet, and the openxml is pretty easy but if there is no value in a cell it skips over and there is nothing in the result. In trying to insert the results of the linq into a business object then there is no way to sync up the cells to the array.

Is there a way to insert a placeholder empty string value into the result of a query or directly project it.

Again the rub is when there is no value in a spreadsheet cell but we still need like a empty string to preserve the field order.

Public Shared Function LoadCustomers(ByVal worksheet As Worksheet, ByVal sharedString As SharedStringTable) As List(Of Customer)

Try


' Initialize Customer List.
Dim result As New List(Of Customer)()

' LINQ query to skip first row with column names.
Dim dataRows As IEnumerable(Of Row) = _
From row In worksheet.Descendants(Of Row)() _
Where row.RowIndex.Value > 2 AndAlso row.RowIndex.Value < 10 _
Select row

For Each row As Row In dataRows

'This is where we need to preserve the field order by putting in at least an empty string. Or is there a better way?

Dim textValues As IEnumerable(Of String) = _
From cell In row.Descendants(Of Cell)() _
Select (If(cell.DataType IsNot Nothing AndAlso cell.DataType.HasValue AndAlso cell.DataType.Value = CellValues.SharedString, _
sharedString.ChildElements(Integer.Parse(cell.CellValue.InnerText)).InnerText, cell.CellValue.InnerText))

' Check to verify the row contains data.
If textValues.Count() > 0 Then
'Create a Customer and add it to the list.
Dim textArray = textValues.ToArray()
Dim customer As New Customer()
customer.LotNumber = textArray(0)
customer.Name = textArray(1)
customer.E911Address = textArray(2)
customer.Addressline1 = textArray(3)
customer.Addressline2 = textArray(4)
customer.Addressline3 = textArray(5)
customer.City = textArray(6)
customer.State = textArray(7)
customer.CountryCode = textArray(smilie
customer.ZipCode = textArray(9)
customer.PhoneNumber = textArray(10)
customer.TotalDue = textArray(11)
customer.CustomerType = textArray(12)
result.Add(customer)
Else
' If no cells, you have reached the end of the table.
Exit For
End If
Next

' Return populated list of customers.
Return result

Catch ex As Exception
MessageBox.Show(ex.ToString)
Return Nothing
End Try

End Function