transfer data from excel to data table

Posted on 2009-02-22
Last Modified: 2012-05-06
I want to tranfer data from excel to data table, then tranfer data into a different excel file
Question by:pmoulema
    LVL 30

    Expert Comment

    What kind of data table - i.e. which database type are you inserting into?

    Author Comment

    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.
    LVL 30

    Expert Comment

    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?
    LVL 4

    Accepted Solution

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

    Expert Comment

    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.AddHeader("content-disposition", "attachment; filename=MyExcelFile.xls")
                Response.ContentType = "application/excel"
                Dim sw As New StringWriter()
                Dim htw As New HtmlTextWriter(sw)
                GridView2.GridLines = GridLines.Both
                ' Style is added dynamically

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    This tutorial demonstrates one way to create an application that runs without any Forms but still has a GUI presence via an Icon in the System Tray. The magic lies in Inheriting from the ApplicationContext Class and passing that to Application.Ru…
    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…
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

    760 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

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now