1

I use LinqToExcel in my applications but cannot figure the best way to dynamically set the end range when reading data. We currently use an arbitrarily large value e.g. 30000 but this only ok when I'm sure the range won't exceed a certain value.

Aside from incrementally checking each sequential row for content is there any better way to dynamically determine the endrange when using LinqToExcel? Here's my current (untested) solution:

Private Function GetPreviousUnquoted() As Integer
    Dim MFIDColumn As String = "E:"
    Dim row As Integer = 9
    Dim data As Boolean = True
    Dim excel = New ExcelQueryFactory(_PathAndName)

    Do Until data = False
        Dim unquotedQuery = From item In excel.WorksheetRange(
                                         MFIDColumn & row, MFIDColumn & row, 1)
                            Select item

        If unquotedQuery.FirstOrDefault.Item(0).ToString.Length > 0 Then
            row += 1
            data = True
        Else
            data = False
        End If
    Loop
    Return row
End Function
Victor Zakharov
  • 25,801
  • 18
  • 85
  • 151
majjam
  • 1,286
  • 2
  • 15
  • 32
  • I don't know LinqToExcel, but if there's a way to fetch Worksheet.UsedRange property, then that'll be your solution. – AdamL Dec 09 '13 at 17:09
  • Many thanks frikozoid, unfortunately I don't believe usedrange is available in linqtoexcel, and even if it was the spreadsheet is not a straightforward table, there is a key and smaller used ranges dotted about it so I don't think it would work. I'm working out a modification of my approach above though so fingers crossed. – majjam Dec 09 '13 at 17:48

0 Answers0