How do you import Excel data to SQL Server using ASP.NET C# where the table's data does not start at the first row?

Question

I want to import data from excel file to sql server database. Where data table does not start at first row in excel. Just like this picture:

Here this my code when click button:

protected void btnUpload_Click(object sender, EventArgs e)
        {
            if (fileUpload.HasFile)
            {
                string path = string.Concat(Server.MapPath("~/File/" + fileUpload.FileName));
                fileUpload.SaveAs(path);

                string connExcelString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1\";", path);
                OleDbConnection ExcelConn = new OleDbConnection(connExcelString);
                try
                {


                    OleDbCommand cmd = new OleDbCommand("select * from [Sheet1$]", ExcelConn);

                    ExcelConn.Open();
                    OleDbDataReader dr = cmd.ExecuteReader();


                    string connSql = @"Data Source=.; Database=dbtest; User Id=sa; Password=mypassword;";

                    SqlBulkCopy bulkcopy = new SqlBulkCopy(connSql);

                    SqlBulkCopyColumnMapping mapNPM = new SqlBulkCopyColumnMapping("npm", "npm");
                    bulkcopy.ColumnMappings.Add(mapNPM);
                    SqlBulkCopyColumnMapping mapProdi = new SqlBulkCopyColumnMapping("prodi", "prodi");
                    bulkcopy.ColumnMappings.Add(mapProdi);
                    SqlBulkCopyColumnMapping mapGrade = new SqlBulkCopyColumnMapping("grade", "grade");
                    bulkcopy.ColumnMappings.Add(mapGrade);

                    bulkcopy.DestinationTableName = "testUpload";
                    bulkcopy.WriteToServer(dr);

                    msg.Text = "success";
                }
                catch (Exception ex)
                {
                    msg.Text = ex.Message.ToString();
                }
                finally
                {
                    ExcelConn.Close();
                }
            }

        }

When I try to upload file excel with the first row is data table, data was successfull import into sql server. But when file data excel look just like that picture, the message return is : The given ColumnName 'npm' does not match up with any column in data source.

Can you help me to solve my problem?

Answer 1

I've found the answer to my own question. I changed the way to retrieve data from an excel file, instead of using OLEDB changed to use Office Interop. So I added a reference Office Interop and modify the source code to be like this:

protected void btnUpload_Click(object sender, EventArgs e)
        {
            if (fileUpload.HasFile)
            {
                string path = string.Concat(Server.MapPath("~/File/" + fileUpload.FileName));
                fileUpload.SaveAs(path);

                Microsoft.Office.Interop.Excel.Application appExcel;
                Microsoft.Office.Interop.Excel.Workbook workbook;
                Microsoft.Office.Interop.Excel.Range range;
                Microsoft.Office.Interop.Excel._Worksheet worksheet;

                appExcel = new Microsoft.Office.Interop.Excel.Application();
                workbook = appExcel.Workbooks.Open(path, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
                worksheet = (Microsoft.Office.Interop.Excel._Worksheet)workbook.Sheets[1];
                range = worksheet.UsedRange;

                int rowCount = range.Rows.Count;
                int colCount = range.Columns.Count;

                System.Data.DataTable dt = new System.Data.DataTable();
                dt.Columns.Add("npm");
                dt.Columns.Add("prodi");
                dt.Columns.Add("grade");

                for (int Rnum = 3; Rnum <= rowCount; Rnum++)
                {
                    DataRow dr = dt.NewRow();
                    //Reading Each Column value From sheet to datatable Colunms                  
                    for (int Cnum = 1; Cnum <= colCount; Cnum++)
                    {
                        dr[Cnum - 1] = (range.Cells[Rnum, Cnum]).Value2.ToString();
                    }
                    dt.Rows.Add(dr); // adding Row into DataTable
                    dt.AcceptChanges();
                }

                workbook.Close(true);
                appExcel.Quit();

                try
                {
                    string connSql = @"Data Source=.; Database=dbkuring; User Id=sa; Password=pohodeui;";

                    SqlBulkCopy bulkcopy = new SqlBulkCopy(connSql);

                    SqlBulkCopyColumnMapping mapNPM = new SqlBulkCopyColumnMapping("npm", "npm");
                    bulkcopy.ColumnMappings.Add(mapNPM);
                    SqlBulkCopyColumnMapping mapProdi = new SqlBulkCopyColumnMapping("prodi", "prodi");
                    bulkcopy.ColumnMappings.Add(mapProdi);
                    SqlBulkCopyColumnMapping mapGrade = new SqlBulkCopyColumnMapping("grade", "grade");
                    bulkcopy.ColumnMappings.Add(mapGrade);

                    bulkcopy.DestinationTableName = "testUpload";
                    bulkcopy.WriteToServer(dt);

                    msg.Text = "success";
                }
                catch (Exception ex)
                {
                    msg.Text = ex.Message.ToString();
                }


            }
        }

To skip how much row to read data, I just edit at this code: for (int Rnum = 3; Rnum <= rowCount; Rnum++)