如何跳过excel文件上传的第一行 [英] How do I skip first row in excel file upload

问题描述

我有一个小网站，用户上传excel表并存储在db中。上传的工作表在A1：D中有一个标题，然后是列名，然后是数据。我想跳过A1：D中的标题并从那里开始导入



我试图复制我的工作表的样子：

i have a small website where users upload excel sheet and is stored in db. the sheets uploaded have a heading in A1:D then column names and then the data. i would like to skip the heading in A1:D and start importing from there

ive tried to copy what my sheet looks like:

Report for March 2019				
Task    Caption   Start Date	
19139   GP        01/11/2017		

i想跳过标题' 2019年3月的报告'



i我正在使用sqlbulk copy和oledb，我在连接字符串中设置了HDR = Yes



我尝试过：

i want to skip the heading 'Report for March 2019'

i am using sqlbulk copy and oledb and i have set HDR=Yes in connection string

What I have tried:

//declare destination table
        string ssqltable = "mytab";
        // make sure your sheet name is correct, here sheet name is sheet1, so you can change your sheet name if have different
        string myexceldataquery = "Select * FROM [Details$])";
        try
        {
            //create our connection strings
            string sexcelconnectionstring = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excelfilepath + ";Extended Properties=" + "\"excel 12.0;hdr=yes;\"";

            string sclearsql = "TRUNCATE TABLE " + ssqltable;
            SqlConnection sqlconn = new SqlConnection(strConnString);
            SqlCommand sqlcmd = new SqlCommand(sclearsql, sqlconn);
            sqlconn.Open();
            sqlcmd.ExecuteNonQuery();

            //series of commands to bulk copy data from the excel file into our sql table
            OleDbConnection oledbconn = new OleDbConnection(sexcelconnectionstring);
            OleDbCommand oledbcmd = new OleDbCommand(myexceldataquery, oledbconn);
            oledbconn.Open();
            OleDbDataReader dr = oledbcmd.ExecuteReader();




            SqlBulkCopy bulkcopy = new SqlBulkCopy(strConnString);
            bulkcopy.DestinationTableName = ssqltable;
            //Mapping Table column    

            bulkcopy.ColumnMappings.Add("Task", "[Task]");
            bulkcopy.ColumnMappings.Add("Caption", "[Caption]");
            bulkcopy.ColumnMappings.Add("Start Date", "[Start_Date]");



            {
                bulkcopy.WriteToServer(dr);

            }
            oledbconn.Close();
            sqlconn.Close();

推荐答案

）;
试试
{
//创建我们的连接字符串
string sexcelconnectionstring = @Provider = Microsoft.ACE.OLEDB.12.0; Data Source =+ excelfilepath +; Extended Properties =+\excel 12.0; hdr = yes ; \;

字符串sclearsql =TRUNCATE TABLE+ ssqltable;
SqlConnection sqlconn = new SqlConnection（strConnString）;
SqlCommand sqlcmd = new SqlCommand（sclearsql，sqlconn ）;
sqlconn.Open（）;
sqlcmd.ExecuteNonQuery（）;

//将数据从excel文件批量复制到我们的sql表中的一系列命令
OleDbConnection oledbconn = new OleDbConnection（sexcelconnectionstring）;
OleDbCommand oledbcmd = new OleDbCommand（myexceldataquery，oledbconn）;
oledbconn.Open（）;
OleDbDataReader dr = oledbcmd.ExecuteReader（）;




SqlBulkCopy bulkcopy = new SqlBulkCopy（strConnString）;
bulkcopy.DestinationTableName = ssqltable;
//映射表列

bulkcopy.ColumnMappings.Add（任务，[任务]）;
bulkcopy.ColumnMappings.Add（Caption，[Caption]）;
bulkcopy.ColumnMappings.Add（开始日期，[Start_Date]）;



{
bulkcopy.WriteToServer（dr）;

}
oledbconn.Close（）;
sqlconn.Close（）;

)"; try { //create our connection strings string sexcelconnectionstring = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excelfilepath + ";Extended Properties=" + "\"excel 12.0;hdr=yes;\""; string sclearsql = "TRUNCATE TABLE " + ssqltable; SqlConnection sqlconn = new SqlConnection(strConnString); SqlCommand sqlcmd = new SqlCommand(sclearsql, sqlconn); sqlconn.Open(); sqlcmd.ExecuteNonQuery(); //series of commands to bulk copy data from the excel file into our sql table OleDbConnection oledbconn = new OleDbConnection(sexcelconnectionstring); OleDbCommand oledbcmd = new OleDbCommand(myexceldataquery, oledbconn); oledbconn.Open(); OleDbDataReader dr = oledbcmd.ExecuteReader(); SqlBulkCopy bulkcopy = new SqlBulkCopy(strConnString); bulkcopy.DestinationTableName = ssqltable; //Mapping Table column bulkcopy.ColumnMappings.Add("Task", "[Task]"); bulkcopy.ColumnMappings.Add("Caption", "[Caption]"); bulkcopy.ColumnMappings.Add("Start Date", "[Start_Date]"); { bulkcopy.WriteToServer(dr); } oledbconn.Close(); sqlconn.Close();

试试这个：

Try this:

string myexceldataquery = "Select * FROM [Details

A2：G];;

A2:G];";

这篇关于如何跳过excel文件上传的第一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！