The First Column of the excel file to put in string variable C# ?

Question

What i need id to take the first column of an excel file and put that column in a string variable but numbers that will be taken from excel to be separated with blank space.

For Example:

Excel file:

1 3 4 56 76 7 876 23 43 (in column)

and string in C#

string number = "1 3 4 56 76 7 876 23 43"

Any code will help me I have made the connection with excel file and i have created the dataset/datatable but now i can not take those numbers as the sample above ?

Answer 1

Have a link - Reading Excel Document

Answer 2

Here's what I would do...first open the spreadsheet with this code.

xlApp = New Excel.Application
filePath = "FILENAME"
xlWorkBook = xlApp.Workbooks.Open(filePath)
xlWorkSheet = xlWorkBook.Worksheets(1)

Then read through the column like this

For data As Integer = 0 To 8
    Dim obj As Excel.Range = CType(xlWorkSheet.Cells(data, 1), Range)
    If obj.Value IsNot Nothing Then
        MyArray(data) = obj.Value
    Else
        Exit Do
    End If
Next

Then you should have all your data in MyArray then loop through and create a string with spaces and you should be good. This code is in VB but it should give you a good idea of the commands and structure to use.

Answer 3

You just want to loop through the rows in the Dataset now:

var numberStr = EmptyStr;
foreach (DataRow dr in MyDataSet.Tables[0].Rows)
{
    numberStr = EmptyStr ? numberStr += dr[0].ToString() : numberStr += " " + dr[0].ToString(); 
}

Updated Solution

private void Form1_Load(object sender, EventArgs e) 
{
    String s = String.Empty;
    String sConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;DataSource=unsorted.xls;Extended Properties=""Excel 12.0;HDR=NO;""";
    using (OleDbConnection conn = new OleDbConnection(sConnectionString)) 
    {
        conn.Open();
        DataTable schemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
        foreach (DataRow schemaRow in schemaTable.Rows) 
        {
            string sheet = schemaRow["TABLE_NAME"].ToString();
            OleDbCommand cmd = new OleDbCommand("SELECT * FROM [" + sheet + "]", conn);
            cmd.CommandType = CommandType.Text;

            DataTable outputTable = new DataTable(sheet);
            output.Tables.Add(outputTable);
            new OleDbDataAdapter(cmd).Fill(outputTable);
        }

        // populate string with value from rows
        foreach (DataRow dr in MyDataSet.Tables[0].Rows)
        {
           s = String.Empty ? s += dr[0].ToString() : s += " " + dr[0].ToString(); 
        }       

        dataGridView1.DataSource = dsExcelContent1;        
        objConn.Close();
    }

    this.label1.Text = s;        
    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);
        }        
    }        
    this.listBox1.DataSource = numberList;
}

Answer 4

If you have the data in a datset...you can do something like this....

////TRY THIS!!!

DataSet dsExcelContent = new DataSet();


     //Fill from db
        //
         StringBuilder builder = new StringBuilder();
        foreach (DataRow row in dsExcelContent.Tables[0].Rows)
        {


            builder.Append(row[0].ToString());
            builder.Append(" ");


        }
        Console.WriteLine(builder.ToString());

.....More exact to your code....

   OleDbConnection objConn = new OleDbConnection(sConnectionString);

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

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

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

        dataGridView1.DataSource = dsExcelContent1;
        objConn.Close();

        int test = dsExcelContent.Tables[0].Rows.Count;

StringBuilder builder = new StringBuilder();

        foreach (DataRow row in dsExcelContent.Tables[0].Rows)
        {

            builder.Append(row[0].ToString());
            builder.Append(" ");


        }
            //s has the data you want.....
        string s = builder.ToString();

            //REST OF YOUR CODE.....

Answer 5

private void Form1_Load(object sender, EventArgs e) { String sConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0; Data Source=unsorted.xls;Extended Properties=""Excel 12.0;HDR=NO;""";

        OleDbConnection objConn = new OleDbConnection(sConnectionString);

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

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

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

        dataGridView1.DataSource = dsExcelContent1;
        objConn.Close();

        int test = dsExcelContent.Tables[0].Rows.Count;

       foreach(DataRow row in dsExcelContent.Tables[0].Rows)
        {
            StringBuilder builder = new StringBuilder();
            foreach (DataColumn col in dsExcelContent.Tables[0].Columns)
            {
                builder.Append(row[col].ToString());
                builder.Append(" ");
            }

            string s = builder.ToString();

            this.label1.Text = s;

            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);
            }

            this.listBox1.DataSource = numberList;

        }






    }
}