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 If
Dim 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
Try
fs = 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

Advertisements

14 thoughts on “VB.NET – How to export excel data into text file.

  1. it works great out of the box!
    I found that since I had an Excel 2007 file, I had to use a different connection string >>

    “Provider=Microsoft.ACE.OLEDB.12.0;Data Source=” & SourceFile & “;Extended Properties=””Excel 12.0;HDR=YES;”””

  2. I am new to .NET world. In case I want the data entered in excel to be exported in particular format say “fixed length” – then how should I proceed.

  3. Could u pls tell me … which COM component we have to add to access Excel object ????
    And frm where we can add tht… ????
    i m using office 2000.

  4. ////////export gridview data in to textfile ///////////

    String strDestinationFile;
    strDestinationFile = “C:\\Report.txt”;
    TextWriter tw = new StreamWriter(strDestinationFile);
    //writing the header

    tw.Write(“Master Capital Services Limited”.PadLeft(50));
    tw.WriteLine();
    tw.Write(“Holding Summary: From:31/03/2008 Trades Upto:31/03/2008 Transactions Upto:31/03/2008”);
    tw.WriteLine();
    tw.Write(“Page No:1 Date:15/02/2008”.PadLeft(40));
    tw.WriteLine();
    tw.Write(“————————————————————————————“);
    tw.WriteLine();
    tw.Write(GridView1.Columns[0].HeaderText.PadRight(15));
    tw.Write(GridView1.Columns[1].HeaderText.PadRight(17));
    tw.Write(GridView1.Columns[2].HeaderText.PadRight(27));
    tw.Write(GridView1.Columns[3].HeaderText.PadRight(15));
    tw.Write(GridView1.Columns[4].HeaderText.PadRight(15));
    tw.WriteLine();
    tw.Write(“————————————————————————————-“);
    tw.WriteLine();

    //writing the data
    for (int x = 0; x < GridView1.Rows.Count; x++)
    {

    tw.Write(“————————————————————————————-“);
    tw.WriteLine();
    tw.Write(GridView1.Rows[x].Cells[0].Text.PadRight(15));
    tw.Write(GridView1.Rows[x].Cells[1].Text.PadRight(17));
    tw.Write(GridView1.Rows[x].Cells[2].Text.PadRight(27));
    tw.Write(GridView1.Rows[x].Cells[3].Text.PadLeft(10));
    tw.Write(GridView1.Rows[x].Cells[4].Text.PadLeft(10));
    tw.WriteLine();

    }
    tw.Close();

    }

  5. i am working in treeview,so when i click the save button the node and subnodes in treeview should be inserted into the excel worksheet.
    is it possible or not if possible what is the code.

  6. i what to export treeview into excel sheet
    i did not use any database.
    i used two textboxes
    1.node(textbox1.text)
    2. childnode(textbox2.text)
    when i press enter the data is added to treeview
    now want export the treeview data to excel sheet
    can u help
    give me any idea
    or
    sample code
    please
    gouse

  7. I’m trying to read an Excel file, perform some validations and then create a text file. The issue I’m having is with long numbers like a telephone number. The entire spreadsheet is formatted as text, but when I import the file into a dataset the number changes from 9497259688 to 9.49726E+009. Any suggestions on how to stop this??

  8. I’m impressed, I must say. Rarely do I come across a blog that’s
    both educative and amusing, and let me tell you,
    you’ve hit the nail on the head. The problem is an issue that not enough men and women are speaking intelligently about. I am very happy I found this in my hunt for something relating to this.

  9. Good day! I know this is kinda off topic however I’d figured I’d ask.
    Would you be interested in exchanging links or maybe guest writing a blog article or vice-versa?
    My blog addresses a lot of the same topics as yours and I feel we
    could greatly benefit from each other. If you happen to be interested feel
    free to send me an e-mail. I look forward to hearing from you!
    Fantastic blog by the way!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s