Tuesday, 21 August 2012

Read data from Excel in VB.NET or C# windows forms | Export data from Excel to Text file

                                    Hi friends, in this article I will explain about how to read the data from excel in VB.NET or C# windows Forms.
                                   I searched so many sites and finally get the solution of how to read the data from excel in VB.NET or C# windows Forms.You can know the How to add dynamic controls in VB.NET or C# windows forms | How to add textbox at runtime | How to dynamically add control with loop at Runtime in the next article How to add dynamic controls in VB.NET or C# windows forms | How to add textbox at runtime | How to dynamically add control with loop at Runtime
For this first we have to add the reference Microsoft.Office.Interop.Excel.
Menu—project—Add reference—COM—Microsoft Excel 12.0 Object Library
Next come to code and imports or using Microsoft.Office.Interop.Excel
Our excel file like in the below figure.

Take 1 label ,one textbox and one button as seen below figure.

In the button click copy paste or write the below code.

VB.NET: 
Dim excel As New Microsoft.Office.Interop.Excel.Application()
Dim path1 As String = path.Text
        path1 = """" & path1 & """"
        Dim wb As Microsoft.Office.Interop.Excel.Workbook = excel.Workbooks.Open(path1)
        Dim ws As Microsoft.Office.Interop.Excel.Worksheet = TryCast(excel.ActiveSheet, Microsoft.Office.Interop.Excel.Worksheet)
       Dim total_records As Integer
        total_records = ws.Cells(1, 2).Value
        Dim swriter As StreamWriter
        Dim filename As String
        filename = "C:\temp\test.txt"
        If System.IO.File.Exists(filename) = True Then
            System.IO.File.Delete(filename)
            'MsgBox("delete")

        End If
        For i As Integer = 0 To total_records
            swriter = File.AppendText(filename)
            swriter.WriteLine(ws.Cells(2 + i, 1).Value & " " & ws.Cells(2 + i, 2).Value & " ")

            swriter.Close()
        Next
        wb.Close(True, Type.Missing, Type.Missing)
        excel.Quit()
        Me.Close()


Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
string path1 = path.Text;
path1 = "\"" + path1 + "\"";
Microsoft.Office.Interop.Excel.Workbook wb = excel.Workbooks.Open(path1);
Microsoft.Office.Interop.Excel.Worksheet ws = excel.ActiveSheet as Microsoft.Office.Interop.Excel.Worksheet;
int total_records = 0;
total_records = ws.Cells(1, 2).Value;
StreamWriter swriter = default(StreamWriter);
string filename = null;
filename = "C:\\temp\\test.txt";
if (System.IO.File.Exists(filename) == true) {
     System.IO.File.Delete(filename);
     //MsgBox("delete")

}
for (int i = 0; i <= total_records; i++) {
     swriter = File.AppendText(filename);
     swriter.WriteLine(ws.Cells(2 + i, 1).Value + " " + ws.Cells(2 + i, 2).Value + " ");

     swriter.Close();
}
wb.Close(true, Type.Missing, Type.Missing);
excel.Quit();
this.Close();


For write data to the text file we have to import or using System.IO namespace.in this path.text is the text what we enter the text.path is the textbox id.

No comments:

Post a Comment

© 2012-2014 Aspdotnet-Kishore.blogspot.com. All Rights Reserved.
The content is copyrighted to Kishore and may not be reproduced on other websites without permission from the owner.