通过跳过n行，使用ASP.NET从excel导入数据到SQL [英] Importing data into SQL from excel using ASP.NET by skipping n rows

问题描述

您好，



我使用以下代码使用ASP.NET将数据从excel导出到SQL中。

Hello,

I am using following code to export data from excel into SQL using ASP.NET.

protected void Upload(object sender, EventArgs e)
    {
        //Upload and save the file
        string excelPath = Server.MapPath("~/Files") + Path.GetFileName(FileUpload1.PostedFile.FileName);
        FileUpload1.SaveAs(excelPath);

        string conString = string.Empty;
        string extension = Path.GetExtension(FileUpload1.PostedFile.FileName);
        switch (extension)
        {
            case ".xls": //Excel 97-03
                conString = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;
                break;
            case ".xlsx": //Excel 07 or higher
                conString = ConfigurationManager.ConnectionStrings["Excel07+ConString"].ConnectionString;
                break;
        }
        conString = string.Format(conString, excelPath);
        using (OleDbConnection excel_con = new OleDbConnection(conString))
        {
            excel_con.Open();
            string sheet1 = excel_con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null).Rows[0]["TABLE_NAME"].ToString();
            DataTable dtExcelData = new DataTable();

            //[OPTIONAL]: It is recommended as otherwise the data will be considered as String by default.
            dtExcelData.Columns.AddRange(new DataColumn[3] 
            { 
                new DataColumn("ID", typeof(string)),
                new DataColumn("Name", typeof(DateTime)),
                new DataColumn("Designation",typeof(DateTime))});

            using (OleDbDataAdapter oda = new OleDbDataAdapter("SELECT * FROM [" + sheet1 + "]", excel_con))
            {
                oda.Fill(dtExcelData);
            }
            excel_con.Close();

            string consString = ConfigurationManager.ConnectionStrings["TestConnection"].ConnectionString;
            using (SqlConnection con = new SqlConnection(consString))
            {
                using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
                {
                   
                    //Set the database table name
                    sqlBulkCopy.DestinationTableName = "dbo.Employee";

                    //[OPTIONAL]: Map the Excel columns with that of the database table
                    sqlBulkCopy.ColumnMappings.Add("ID", "ID");
                    sqlBulkCopy.ColumnMappings.Add("Name", "Name");
                    sqlBulkCopy.ColumnMappings.Add("Designation", "Designation");
                    
                    con.Open();
                    sqlBulkCopy.WriteToServer(dtExcelData);
                    con.Close();
                }
            }
        }
    }

问题是在我的Excel文件中，实际数据是从第7行开始的。

所以它不起作用，但每当我将数据移动到第1行时，它就可以很好地获取和上传SQL中的数据。

所以我想要的是在我的代码中预定义从excel文件中的第7行中选择数据。

请帮助，因为我真的被困在这里。





更新



另外只是另一个查询我的SQL和Excel工作表的标题名称是不同的，所以除非两个标题都相同，否则它不会选择数据，所以无论如何我可以保持标题不同并仍然获取数据吗？

< br $>
谢谢



我的尝试：



关于此

The problem is that in my Excel file the actual data is starting from Row 7.
So it is not working but whenever i move data to row 1 it fetches and uploades the data in SQL just fine.
So what i want is to predefine in my code that pick the data from row 7 in excel file.
Please help, as i am really stuck here.


UPDATE

Also just another query that my header name of SQL and Excel sheets are different, so unless both headers are same it does not pick the data, so is there anyway that i can keep the header different and still fetch the data?

Thanks

What I have tried:

Not much help available on internet regarding this

推荐答案

在互联网上提供的帮助不多，您不需要跳过6行。您可以定义获取数据的范围，例如：

You don't need to skip 6 rows. You can define the range to get data, for example:

SELECT * FROM [Sheet1

A7：d]

For有关详细信息，请参阅： Excel连接字符串 - ConnectionStrings.com [ ^ ] - 请注意标准替代方案 - Microsoft.Jet.OLEDB.4.0 - ConnectionStrings.com [ ^ ]

For further details, please see: Excel connection strings - ConnectionStrings.com[^] - note to Standard alternative - Microsoft.Jet.OLEDB.4.0 - ConnectionStrings.com[^]

这篇关于通过跳过n行，使用ASP.NET从excel导入数据到SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！