Take the 2-minute tour ×

So I have data that is supplier by country where the supplier information changes by country. 600 suppliers and will be upwards of 35 countries(15 attributes per country per supplier). The excel data sheet looks similar to this:

SupplierID   SupplierName USsupplierCategory USsupplierCategoryCode UKSupplierCategory ...
1            Sup1         Beverages          1                      Ropes
3            Sup5         Ladders            46                     Small Ladders

If I could figure out a simple way to get this excel data into an array(even copying and pasting works if the formatting goes quickly since I only have to do this once a month at the most) I can then loop through it an build the needed objects off of the array. But I can't find a simple way to build an array with the excel data without going through a lot of formatting for an array assignment.

I'm pretty new to VB.net and still an amateur programmer and I just can't seem to envision a simple solution to this.

Is an array the way to go? Should I instead loop through each row as a string and break the data by tabs and assign the data that way?

If I am too vague I apologize, let me know and I will provide more specific detail and some code if needed.

share|improve this question
to make it clear- do you want to make an array of values only or array of values + formatting + formulas + etc? –  KazJaw Jun 15 '13 at 18:25
I'd look at saving the Excel file as a CSV file, then parsing the CSV file to build your objects. –  Tim Jun 15 '13 at 18:37
@ KazJaw I'm only looking to make an array of values, strings and integers(or just strings and I can convert them to integers later). @Tim Thanks for the suggestion, I'll look into that as an option. –  Zifendale Jun 15 '13 at 19:59
If you can modify the Excel file, change the data into an Excel Table (Insert tab -> Table). Then you can access it as a ListObject - and address columns by their names (lo.ListColumns("SupplierName")(5)), etc... –  Peter Albert Jun 16 '13 at 10:25

1 Answer 1

up vote 0 down vote accepted

The high level approach I'd use is as follows:

1) Define your supplier object

2) Create a supplier object for each row of your datatable and add it to a collection, e.g. a List of supplier objects

Once you have done this, you can easily iterate through your collection of supplier objects to do whatever you like.

Detailed approach:

1) Define your supplier object:

Public Class Supplier
    Public property SupplierID as integer
    Public Property SupplierName as string
    Public Property USsupplierCategory as string
    Public property USsuppliercategorycode as integer
    Public property UKSupplierCategory as string
End Class

2) Read your excel object into a datatable

This gets a bit tricker. As Tim said, it is easier to save your excel files as csv and then read them in. This is because it is easy to read in csv, but it is also complicated to read in excel files. They can be either xls or xlsx format, and deciding on which one can be a problem. We can also get into COM object issues etc.

If you are to stick with reading in excel, I recommend using Linq2Excel, as this library handles the xls / xlsx issue for you. Here is some example code I knocked up based on your example spreadsheet that returns a list of supplier objects:

Imports LinqToExcel
Imports Remotion.Data.Linq
Imports System.Data
Imports System.Linq

Public Class ReadInExcelData

Public Shared Function GetSupplierListFromSupplierExcel() As List(Of Supplier)
    Dim excel As ExcelQueryFactory = New ExcelQueryFactory("C:\Users\YourUserName\Desktop\ExampleData.xls")
    Dim suppliers = From c In excel.Worksheet
                    Select c

    Dim list As New List(Of Supplier)

    For Each supplier In suppliers
        list.Add(New Supplier(supplier.Item(0),

    Return list
End Function
End Class

You can get the linqtoexcel library here: http://code.google.com/p/linqtoexcel/

share|improve this answer
Thank you this is just what I needed, I'll be taking the csv approach since I have no issue using that format. –  Zifendale Jun 17 '13 at 13:22

Your Answer


