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

x
?
Solved

Create a DataTable from an Excel sheet

Posted on 2014-11-26
2
Medium Priority
?
117 Views
Last Modified: 2014-11-27
Hi

The following code is used to create a DataTable in Eric Moreau's post at
http://emoreau.com/Entries/Articles/2010/12/Using-ADONet-SQL-Bulk-Copy-feature.aspx
I am trying to work out how to create a the same table from a spreadsheet like the one shown in the image
How would I do this?

    Private Function CreateDataSource(ByVal pLargeDataTable As Boolean) As DataTable
        Dim dt As New DataTable("Person")
        With dt
            .Columns.Add("ID", GetType(Integer))
            .Columns.Add("FirstName", GetType(String))
            .Columns.Add("LastName", GetType(String))

            'Add rows
            .LoadDataRow(New Object() {-1, "Joe", "Dalton"}, True)
            .LoadDataRow(New Object() {-2, "Jack", "Dalton"}, True)
            .LoadDataRow(New Object() {-3, "Willam", "Dalton"}, True)
            .LoadDataRow(New Object() {-4, "Averell", "Dalton"}, True)

            If pLargeDataTable Then
                Dim intID As Integer = 5
                Do While intID <= 100000
                    .LoadDataRow(New Object() {intID * -1, "FN" + intID.ToString, "Dalton"}, True)
                    intID += 1
                Loop
            End If
        End With

        Return dt
    End Function

1
0
Comment
Question by:Murray Brown
2 Comments
 
LVL 24

Accepted Solution

by:
Phillip Burton earned 2000 total points
ID: 40469085
Are you trying to create the spreadsheet?

Something like this:

    Function CreateDataSource(ByVal pLargeDataTable As Boolean)
        xlApp = CreateObject("Excel.Application")
        xlApp.Workbooks.Add()
        xlApp.Visible = True
        introw = 1
        With xlApp.activeworkbook.sheets(1)
            Call AddToExcel(-1, "Joe", "Dalton")
            Call AddToExcel(-2, "Jack", "Dalton")
            Call AddToExcel(-3, "Willam", "Dalton")
            Call AddToExcel(-4, "Averell", "Dalton")
            If pLargeDataTable Then
                Do While introw <= 100000
                    Call AddToExcel(introw * -1, "FN" + introw.ToString, "Dalton")
                Loop
            End If
        End With
        CreateDataSource = xlApp
    End Function
    Sub AddToExcel(ByVal ParamArray ToAdd())
        xlApp.range("A" & introw & ":C" & introw).value = ToAdd
        introw += 1
    End Sub

Open in new window

0
 

Author Closing Comment

by:Murray Brown
ID: 40469168
Yes. Thanks
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

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

I think the Typed DataTable and Typed DataSet are very good options when working with data, but I don't like auto-generated code. First, I create an Abstract Class for my DataTables Common Code.  This class Inherits from DataTable. Also, it can …
Since .Net 2.0, Visual Basic has made it easy to create a splash screen and set it via the "Splash Screen" drop down in the Project Properties.  A splash screen set in this manner is automatically created, displayed and closed by the framework itsel…
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…
Kernel Data Recovery is a renowned Data Recovery solution provider which offers wide range of softwares for both enterprise and home users with its cost-effective solutions. Let's have a quick overview of the journey and data recovery tools range he…
Suggested Courses
Course of the Month11 days, 19 hours left to enroll

564 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