你如何导入使用ASP.NET C＃所在的表的数据不会在第一行启动Excel数据到SQL Server？ [英] 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?

问题描述

我要导入数据从excel文件到SQL Server数据库。其中，数据表不在Excel中第一行开始。就像这样的画面：

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

        }

当我尝试上传文件的第一行Excel是数据表，数据是全成导入到SQL Server。但是，当文件excel数据活脱脱的图片，消息返回为：给定的ColumnName'故宫'不与数据源的任何列匹配

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?

推荐答案

我已经找到了回答我的问题。我改变了从Excel文件中检索数据的方式，而是采用OLEDB改为使用Office互操作。所以我增加了一个参考的Office互操作和修改源代码是这样的：

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


            }
        }

要跳过多少行读取数据，我就在这个代码编辑：（INT RNUM = 3; RNUM< = rowCount等; RNUM ++）

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

这篇关于你如何导入使用ASP.NET C＃所在的表的数据不会在第一行启动Excel数据到SQL Server？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！