From Excel (or any file) to Array List C#

Question

I want to put some numbers from Excel to array. Let's say I have an Excel file with some numbers like this:

23 34 1 3 100 56 45 43 56 4 87 6 89 9

this is just one column in excel (or any file) And i want to put those numers in arraylist as integer numbers, i dont need the result as one number but all those numbers to be in int value.

Any help please ?

Answer 1

You could also export into a file format, ie xml or csv, and then parse this from your c# program.

Answer 2

Assuming that the above is a string (and the source does not matter), you can do the following:

string s = "23 34 1 3 100 56 45 43 56 4 87 6 89 9";
string[] numbers = s.Split(' ');

ArrayList numberList = new ArrayList();
int i;

foreach (String num in numbers)
{
    if (Int32.TryParse(num, out i))
        numberList.Add(i);
    else
        Console.WriteLine("'{0}' is not a number!", num);
}

listBox1.DataSource = numberList;

I suggest using List<int> instead of ArrayList for type safety.

The following code reads all values from an Excel sheet into a data set using a DB connection. You can then pick the value needed.:

String sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + filename + ";" + "Extended Properties=\"Excel 8.0;HDR=NO;IMEX=1ReadOnly=False\"";

OleDbConnection objConn = new OleDbConnection(sConnectionString);

objConn.Open();
OleDbCommand objCmdSelect = new OleDbCommand("SELECT * FROM [" + sheetname + "$]", objConn);

OleDbDataAdapter objAdapter1 = new OleDbDataAdapter();
objAdapter1.SelectCommand = objCmdSelect;

DataSet dsExcelContent = new DataSet();
objAdapter1.Fill(dsExcelContent);

objConn.Close();

EDIT
You did not specify the exact source for the string, so if this question is about how to read a file or how to import data from an Excel spreadsheet, you should probably rephrase your question a little.

EDIT 2
Replaced List<int> by ArrayList on OP's wish (against better design).

EDIT 3
Added a new line to show the OP how to use the ArrayList as a data source for a ListBox...

EDIT 4
Added code to read Excel sheet using OleDB.

Answer 3

i dont have problem reading from excel i have problem when i put those numbers in ArrayList, This way numbers are stored in the ArrayList as string so as output i get one string like that: 23443223564563457, wat i want is to store those numbers as integer 1, 4, 100, 300, 59, 6, ..............

It will be good if someone can write me some code ?

Answer 4

This is quite easy be splitting the string and parsing each item into a list.

String input = "23 34 1 3 100 56 45 43 56 4 87 6 89 9";

List<Int32> result = input
    .Split(new[] { ' ' }, StringSplitOptions.RemoveEmptyEntries)
    .Select(i => Int32.Parse(i))
    .ToList();

Note that this code has no error handling. If an item is not a valid integer, an exception will be thrown. You can handle this case by using Int32.TryParse(). You best do this without LINQ, but you can use the following inefficent LINQ code, too.

Int32 dummy;
List<Int32> result = input
    .Split(new[] { ' ' }, StringSplitOptions.RemoveEmptyEntries)
    .Where(i => Int32.TryParse(i, out dummy))
    .Select(i => Int32.Parse(i))
    .ToList();

Without LINQ.

String input = "23 34 1 3 100 56 h45 43 56 4 87 6 89 9";

Int32 dummy;
List<Int32> result = new List<Int32>();

foreach (String item in input.Split(new[] { ' ' }, StringSplitOptions.RemoveEmptyEntries))
{
    Int32 number;
    if (Int32.TryParse(item, out number))
    {
        result.Add(number);
    }
    else
    {
        // Handle invalid items.
    }
}

Answer 5

With this Code i only get the first number in the listbox, whats the problem Thorsten Dittmar ?

string s = "23 34 1 3 100 56 45 43 56 4 87 6 89 9"; string[] numbers = s.Split(' ');

        ArrayList numberList = new ArrayList();

        int i;

        foreach (String num in numbers)
        {
            if (Int32.TryParse(num, out i))
            {
                numberList.Add(i);
                this.listBox1.DataSource = numberList;
            }
            else
                Console.WriteLine("'{0}' is not a number!", num);