excel file reading one column and that rows continiously

Question

I have an excel file with two columns. I want to get one column and all its rows then 2nd column and all that rows. In the same way I should have those columns repeatedly of 12 columns.

enter image description here

I have used below code but I am not getting required output where I am getting Emp_name in first column but I should get it in second column.

for (int Cnum = 1; Cnum <= ShtRange.Columns.Count; Cnum++)
{

    if (Cnum == 1)
    {
        dt.Columns.Add((ShtRange.Cells[1, Cnum] as Excel.Range).Value2.ToString());
        for (int Rnum = 1; Rnum <= 9; Rnum++)
        {
            dt.Rows.Add((ShtRange.Cells[Rnum, 1] as Excel.Range).Value2.ToString());

        }
    }
    else if (Cnum == 2)
    {

        dt.Columns.Add((ShtRange.Cells[1, Cnum] as Excel.Range).Value2.ToString());


        for (int Rnum = 1; Rnum <= 9; Rnum++)
        {

            dt.Rows.Add((ShtRange.Cells[Rnum, 2] as Excel.Range).Value2).ToString();

        }
    }

OK, and... does this code go wrong somehow?

doctorlove
– doctorlove

2013-11-25 11:50:21 +00:00
Commented Nov 25, 2013 at 11:50 — doctorlove
– doctorlove, Commented Nov 25, 2013 at 11:50

Rohit · Accepted Answer · 2013-11-25 11:55:32Z

Frankly I dont think there is any problem in your code...Here is a simple method that get's your excel content in dataset

            ds = new DataSet();
            string myConnStr = "";
            if (txtdestination.Contains(".xlsx"))
            {
                myConnStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + txtdestination.ToString() + ";" + "Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1\"";
            }
            else
            {

                myConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + txtdestination.ToString() + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\"";
            }
            OleDbConnection myConn = new OleDbConnection(myConnStr);
            OleDbCommand cmd = new OleDbCommand("select * from [Sheet1$]", myConn);
            OleDbDataAdapter adapter = new OleDbDataAdapter();
            adapter.SelectCommand = cmd;
            myConn.Open();
            adapter.Fill(ds);


            myConn.Close();

Note you need to have sheetname as Sheet1

txtdestination is the path of your excel file

Collectives™ on Stack Overflow

excel file reading one column and that rows continiously

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related