Imports System.IO
Imports GemBox.Spreadsheet

Public NotInheritable Class CsvFileReader
    Inherits TextReader
    Private Const MaxRow As Integer = 1048576
    Private ReadOnly reader As TextReader

    Private currentRow As Integer
    Private isCompleted As Boolean

    Public Shared Function Read(path As String, options As CsvLoadOptions) As ExcelFile
        Dim workbook = New ExcelFile()
        Dim sheetIndex As Integer = 0

        Using reader = New CsvFileReader(path)
            While reader.CanReadNextSheet()
                sheetIndex += 1
                workbook.Worksheets.AddCopy(
                    String.Format("Sheet {0}", sheetIndex),
                    ExcelFile.Load(reader, options).Worksheets.ActiveWorksheet)
            End While
        End Using

        Return workbook
    End Function

    Private Sub New(path As String)
        Me.reader = File.OpenText(path)
    End Sub

    Private Function CanReadNextSheet() As Boolean
        If Me.isCompleted Then
            Return False
        End If

        Me.currentRow = 0
        Return True
    End Function

    Public Overrides Function ReadLine() As String
        If Me.currentRow < MaxRow Then
            Me.currentRow += 1
            Dim line As String = Me.reader.ReadLine()

            If line Is Nothing Then
                Me.isCompleted = True
            End If

            Return line
        End If

        Return Nothing
    End Function

    Protected Overrides Sub Dispose(disposing As Boolean)
        Me.reader.Dispose()
    End Sub
End Class