Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Create a DataTable from an Excel sheet

Posted on 2014-11-26
2
110 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

828 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