我想从Excel工作表中跳过6行并将剩余的值绑定到数据库 [英] I wanna Skip 6 lines from Excel Sheet and Bind the Remaining Values to Database

问题描述

这里我想将数据从Excel工作表绑定到数据库....我想跳过前6行然后我想将数据绑定到数据库...这是我的代码如何做到这一点... 。请帮帮我

Here I want to Bind the data from Excel Sheet to Database....I want to skip First 6 rows and then i want to bind the data to database ...this is my code how to do this....Please help me

string connectionString = "";
       if (FileUpload1.HasFile)
       {
           string fileName = Path.GetFileName(FileUpload1.PostedFile.FileName);
           string fileExtension = Path.GetExtension(FileUpload1.PostedFile.FileName);
           string fileLocation = Server.MapPath(fileName);
           FileUpload1.SaveAs(fileLocation);

           //Check whether file extension is xls or xslx

           if (fileExtension == ".xls")
           {
               connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
           }
           else if (fileExtension == ".xlsx")
           {
               connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
           }

           //Create OleDB Connection and OleDb Command

           OleDbConnection con = new OleDbConnection(connectionString);
           OleDbCommand cmd = new OleDbCommand();
           cmd.CommandType = System.Data.CommandType.Text;
           cmd.Connection = con;
           OleDbDataAdapter dAdapter = new OleDbDataAdapter(cmd);
           DataTable dtExcelRecords = new DataTable();
           con.Open();
           DataTable dtExcelSheetName = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
           string getExcelSheetName = dtExcelSheetName.Rows[0]["Table_Name"].ToString();
           cmd.CommandText = "SELECT * FROM [" + getExcelSheetName + "]";
           dAdapter.SelectCommand = cmd;
           dAdapter.Fill(dtExcelRecords);
           con.Close();
           GridView1.DataSource = dtExcelRecords;
           GridView1.DataBind();

推荐答案

你可以用Linq来做

you can do it with Linq

GridView1.DataSource = dtExcelRecords.AsEnumerable().Skip(6).CopyToDataTable();

参考：

http ：//msdn.microsoft.com/en-us/library/bb396189（v = vs.90）.aspx [ ^ ]

您可以删除数据表中的前6行将它绑定到GridView：

You can delete top 6 rows from datatable before binding it to GridView:

cmd.CommandText = "SELECT * FROM [" + getExcelSheetName + "]";
dAdapter.SelectCommand = cmd;
dAdapter.Fill(dtExcelRecords);
con.Close();
//This will delete top 6 rows from datatable
for(int i = 0; i< = 5; i++)
{
    dtExcelRecords.Rows[0].Delete();
}
GridView1.DataSource = dtExcelRecords;
GridView1.DataBind();

这篇关于我想从Excel工作表中跳过6行并将剩余的值绑定到数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！