VB.NET – How to export excel data into text file.

I am writing two solution to acheive this task the second one is most recommended.

First: 

In first example i am using Excel.Application object

You need to import a reference of Microsoft.Office.Interof.excel

Most important thing, dont forget to close Excel Object otherwise it will be retain in the memory and that cause CONTEXTSWITCHDEADLOCK.

It only occurs when there is a delay in the processing or there is a huge excel file to read.

to resolve this problem follow these steps.

1 . Debug

2 . Exception

3. Managed Debugging Assistants

4. Disable ContextSwitchDeadLock.

——————————————————————————–

Public Sub ExportExcelIntoText(ByVal FullFileName As String)
Dim oXL As New Excel.Application
Dim oWBK As Excel.Workbook
Dim oWS As Excel.Worksheet
Dim TextFilename As String = FullFileNameTextFilename = Left(TextFilename, Len(TextFilename) – 4)TextFilename += “.txt”
‘deleting a existing file
If File.Exists(TextFilename) Then
File.Delete(TextFilename)
End IfDim fs As New FileStream(TextFilename, FileMode.Create, FileAccess.Write)
Dim sWrtier As New StreamWriter(fs)oWBK = oXL.Workbooks.Open(FullFileName)
oWS = oXL.Worksheets(1)
Try
Dim rowIndex As Int16 = 1
Dim colIndex As Int16 =1sWrtier.BaseStream.Seek(0, SeekOrigin.End)

For rowIndex = 1 To 50
colIndex = 1
If Not (CType(oWS.Cells(rowIndex, colIndex), Excel.Range)).Value2 Is Nothing Then
For colIndex = 1 To 15
If (CType(oXL.Cells(rowIndex, colIndex), Excel.Range)).Value2 Is Nothing Then
sWrtier.Write(“” + vbTab)
Else
sWrtier.Write((CType(oXL.Cells(rowIndex, colIndex), Excel.Range)).Value2.ToString() + vbTab)
End If
Next
End If
sWrtier.WriteLine()
Next‘closing the file
Catch ex As Exception
Console.Write(ex.Message)
Finally
oWBK.Close()
oWBK = Nothing
oXL = Nothing
sWrtier.Close()
sWrtier.Dispose()
sWrtier = Nothing
End Try
End Sub
——————————————————————–

Second: In a below example i am reading excel file using OleDB and insert first into dataset and later writing into text file.Public Sub ExportExcelIntoText(ByVal FullFileName As String)
Dim TextFilename As String 
TextFilename = Left(TextFilename, Len(TextFilename) – 4) TextFilename += “.txt”
If File.Exists(TextFilename) Then
File.Delete(TextFilename)
End IfDim fs As FileStream
Dim sWrtier As StreamWriter
Dim da As OleDb.OleDbDataAdapter
Dim ds As DataSetds = Nothing
da = Nothing
sWrtier = Nothing
Tryfs = New FileStream(TextFilename, FileMode.Create, FileAccess.Write)
sWrtier = New StreamWriter(fs)
Dim cnn As New OleDb.OleDbConnection(“Provider=Microsoft.Jet.OLEDB.4.0;Data Source=” + FullFileName + “;Extended Properties=””Excel 8.0;HDR=YES;”””)
da = New OleDb.OleDbDataAdapter(“Select * from [Sheet1$]”, cnn)
ds = New DataSet(“ExcelFile”)da.Fill(ds)
Dim rowIndex As DataRow
Dim colIndex As DataColumnsWrtier.BaseStream.Seek(0, SeekOrigin.End)
For Each rowIndex In ds.Tables(0).Rows
For Each colIndex In ds.Tables(0).Columns
If rowIndex(colIndex) Is Nothing Then
sWrtier.Write(“” + vbTab)
Elses
Wrtier.Write(rowIndex(colIndex).ToString + vbTab)
End If
Next
sWrtier.WriteLine()
Next
‘closing the file
Catch ex As Exception
Console.Write(ex.Message)
Finally
ds.Dispose()
ds = Nothing
da.Dispose()
da = Nothing
sWrtier.Close()
sWrtier.Dispose()
sWrtier = Nothing
End Try
End Sub
—————————————

I would appreciate your comments.

regards,

Rana