Exporting tab delimited file to Excel with EPPlus

Question

I am trying to export to Excel a tab delimited file that contains two columns. I'm not able to find examples that show how to do this with EPPlus. Most examples I have seen pertain to LoadFromDatabase. My guess is to read the file and store the data as an array or an array list and then perform a LoadFromArrays.

The data from the file looks something like this:

RFID Comments ABC245 Device added to the system BBT988 Device not found GBN471 Device sold

The following is my attempt to write code to perform the export to Excel. At the moment, I am getting a casting error and trying to get around it. What I'm looking for is to get feedback on what's the correct and efficient way of writing the code to perform the action. Thank you in advance!

Dim fLoc As String
fLoc = Server.MapPath(".")
Dim tFile As StreamReader
tFile = File.OpenText(fileLoc)
Dim writeLn As String
writeLn = tFile.ReadToEnd
Response.Write(writeLn)

fLoc = Server.MapPath(".") & "/maindirectory" & param1 & param2
tFile = File.OpenText(fileLoc)
writeLn = ""
writeLn = tFile.ReadToEnd 
writeLn = RTrim(writeLn)

Dim aList As New ArrayList
aList.Add(writeLn)

Dim attachment as String
attachment = "attachment; filename=" + "sheet.xlsx"
Dim xlPack As ExcelPackage = New ExcelPackage()
Dim ws As ExcelWorksheet = xlPack.Workbook.Worksheets.Add(sheet)
Dim ms As MemoryStream = New MemoryStream()

ws.Cells(1, 1).LoadFromArrays(aList)
xlPack.SaveAs(ms)
ms.WriteTo(Response.OutputStream)

Answer 1

Use ExcelTextFormat to parse the file, which also allows setting some primitive data types for column/cell formatting if needed. For example with this tab-delimited data:

Header 1    Header 2
0   03/22/2017 18:03
1   03/23/2017 18:03
2   03/24/2017 18:03
3   03/25/2017 18:03
4   03/26/2017 18:03

Generate Excel file:

Dim excelTextFormat = New ExcelTextFormat()
excelTextFormat.Delimiter = vbTab
excelTextFormat.DataTypes = New eDataTypes() _
{
    eDataTypes.Number, eDataTypes.DateTime
}

Using package = New ExcelPackage(New FileInfo(output))
    package.Workbook.Worksheets.Add("sheet1")
    Dim ws = package.Workbook.Worksheets(1)
    ws.Column(2).Style.Numberformat.Format = "mm/dd/yyyy"
    ws.Cells("A1").LoadFromText(input, excelTextFormat)
    package.Save()
End Using

Output: