The Author Online Book Forums are Moving

The Author Online Book Forums will soon redirect to Manning's liveBook and liveVideo. All book forum content will migrate to liveBook's discussion forum and all video forum content will migrate to liveVideo. Log in to liveBook or liveVideo with your Manning credentials to join the discussion!

Thank you for your engagement in the AoF over the years! We look forward to offering you a more enhanced forum experience.

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