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 ?
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.
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.
}
}
You could also export into a file format, ie xml or csv, and then parse this from your c# program.
