C# Import data from specified Excel sheet into SQL Server

Question

I have some C# code to import Excel into a database, but it can only import data from the first sheet (Sheet1) into the SQL Server database. I want to import data from the second sheet (Sheet2) of Excel file into the database. Please modify the code to teach me how to do that.

private void button1_Click(object sender, EventArgs e)
{
        OpenFileDialog ope = new OpenFileDialog();
        ope.Filter = "Excel Files|*.xls;*.xlsx;*slsm";

        if (ope.ShowDialog() == DialogResult.Cancel)
            return;

        FileStream stream = new FileStream(ope.FileName, FileMode.Open);
        IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);

        DataSet result = excelReader.AsDataSet();
        DataClasses1DataContext conn = new DataClasses1DataContext();           

        foreach(DataTable table in result.Tables)
        {
            //Default start value of foreach statement is 0
            //Please tell me: How to count dr from 1 instead of 0???
            foreach (DataRow dr in table.Rows)
            {
                //if index of dr==1, then continue
                if (table.Rows.IndexOf(dr) == 0)
                {
                    continue;
                }

                test addtable = new test()
                {
                    id = Convert.ToInt32(dr[0]),
                    name = Convert.ToString(dr[1]),
                    surname = Convert.ToString(dr[2]),
                    age = Convert.ToInt32(dr[3])
                };

                conn.tests.InsertOnSubmit(addtable);
            }
        }

        conn.SubmitChanges();
        excelReader.Close();
        stream.Close();

        MessageBox.Show("Import table completed!");
}

The indexes start at zero (0). To get the “Second” sheet in the workbook (if it exists) have you tried result.Tables[1]… it appears your code is looping through all the sheets. — JohnG
– JohnG, Commented Mar 13, 2017 at 5:06

Frederic · Accepted Answer · 2017-03-13 15:32:38Z

1

Assuming you are looking to get the data from the spreadsheet Sheet2, this should work :

DataSet result = excelReader.AsDataSet();
while (excelReader.NextResult())
{
    if (excelReader.Name == "Sheet2")
    {
        result = excelReader.AsDataSet();
        break;
    }
}

answered Mar 13, 2017 at 15:32

Frederic

2,0834 gold badges24 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

C# Import data from specified Excel sheet into SQL Server

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related