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, Mar 13 '17 at 05:06
See a similar questions with possible answers [here](http://stackoverflow.com/questions/21849756/excel-data-reader-issues-column-names-and-sheet-selection) — Frederic, Mar 13 '17 at 05:08

score 1 · Answer 1 · answered Mar 13 '17 at 15:32

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

C# Import data from specified Excel sheet into SQL Server

1 Answers1