Solved

Create a DataTable from an Excel sheet

Posted on 2014-11-26
2
111 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:murbro
2 Comments
 
LVL 24

Accepted Solution

by:
Phillip Burton earned 500 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:murbro
ID: 40469168
Yes. Thanks
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
VB.net and sql server 4 53
VB.Net - MemoryMappedFiles - Confirm receipt 2 34
Get distinct values from excel or comm seperated file 4 40
vb.net sendkey 4 17
A while ago, I was working on a Windows Forms application and I needed a special label control with reflection (glass) effect to show some titles in a stylish way. I've always enjoyed working with graphics, but it's never too clever to re-invent …
Introduction When many people think of the WebBrowser (http://msdn.microsoft.com/en-us/library/2te2y1x6%28v=VS.85%29.aspx) control, they immediately think of a control which allows the viewing and navigation of web pages. While this is true, it's a…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

679 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