如何使用ASP.NET C#将Excel数据导入SQL Server,其中表的数据不在

发布时间：2020-12-16 09:40:31 所属栏目：asp.Net 来源：网络整理

导读：我想将数据从excel文件导入到sql server数据库.数据表不在excel的第一行开始.就像这张照片：点击按钮时这是我的代码： protected void btnUpload_Click(object sender,EventArgs e) { if (fileUpload.HasFile) { string path = string.Concat(Server.MapPat

我想将数据从excel文件导入到sql server数据库.数据表不在excel的第一行开始.就像这张照片：

点击按钮时这是我的代码：

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’npm’与数据源中的任何列都不匹配.

你能帮我解决我的问题吗？

解决方法

我找到了自己问题的答案.我改变了从excel文件中检索数据的方式,而不是使用OLEDB更改为使用Office Interop.所以我添加了一个引用Office Interop并修改源代码如下：

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,true,5,"",Microsoft.Office.Interop.Excel.XlPlatform.xlWindows,"t",false,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","grade");
                    bulkcopy.ColumnMappings.Add(mapGrade);

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

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


            }
        }

要跳过读取数据的行数,我只需编辑此代码：for(int Rnum = 3; Rnum< = rowCount; Rnum)

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!