Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

transfer data from excel to data table

Posted on 2009-02-22
6
Medium Priority
?
393 Views
Last Modified: 2012-05-06
I want to tranfer data from excel to data table, then tranfer data into a different excel file
0
Comment
Question by:pmoulema
5 Comments
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 23707873
What kind of data table - i.e. which database type are you inserting into?
0
 

Author Comment

by:pmoulema
ID: 23707896
what i want to do is to load  an excel file into a data table, manipulate data then transfer the data table into an excel file.
the excel file has many rows and columns.
thanks
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 23708098
Sorry I have not done any programming for a while. Is Data Table a .Net term is are you using it as a general term for a database table?
0
 
LVL 4

Accepted Solution

by:
mahadevan_v earned 2000 total points
ID: 23709539
Protected Function GetDatatableFromExcel(ByVal fileName As String, ByVal sheetName As String) As DataTable  
Dim conn As System.data.oledb.OleDbConnection
Dim dataResult As New DataTable
Try
conn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" _  + fileName + ";Extended Properties=Excel 8.0;")  
conn.Open()  
Dim command As New System.Data.OleDb.OleDbCommand(" SELECT * FROM [" + sheetName + "$]")
command.Connection = conn
Dim adaperForExcelBook As New OleDbDataAdapter  
adaperForExcelBook.SelectCommand = command
adaperForExcelBook.Fill(dataResult)
conn.Close()
Catch err As Exception
Throw New Exception("Error reading file: " + fileName)
End Try
Return dataResult
End Function
0
 
LVL 3

Expert Comment

by:AxleWack
ID: 23731609
Agreed with mahadevan_v. That code should work, I use the same type of code to import data from Excel to SQL. But then from a GridView to Excel, you can use the following:

Dim style As String = "<style> .text { mso-number-format:\@; } </style> "
   
            Response.ClearContent()
            Response.AddHeader("content-disposition", "attachment; filename=MyExcelFile.xls")
            Response.ContentType = "application/excel"
   
            Dim sw As New StringWriter()
            Dim htw As New HtmlTextWriter(sw)
   
            GridView2.RenderControl(htw)
            GridView2.GridLines = GridLines.Both
            ' Style is added dynamically
           
            Response.Write(style)
            Response.Write(sw.ToString())
            Response.[End]()
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

1.0 - Introduction Converting Visual Basic 6.0 (VB6) to Visual Basic 2008+ (VB.NET). If ever there was a subject full of murkiness and bad decisions, it is this one!   The first problem seems to be that people considering this task of converting…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?
Suggested Courses
Course of the Month15 days, 23 hours left to enroll

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question