Excel到SQL Server - C＃(Excel to SQL Server - C#)

 理念： 
 
 
 
 阅读XLS并在Microsoft SQL上上载数据 
 
 
 问题： 
 
 
 
 只有第一列正在上传到数据库 
 
 
 我的代码： 
 
  private void button1_Click(object sender, EventArgs e) {
   // string path = @"XXXX\xls_test\Book1.xlsx";
   string path = @ "XXXX\xls_test\Book1.xlsx";
   ImportDataFromExcel(path);
  }


  public void ImportDataFromExcel(string excelFilePath) {
   //declare variables - edit these based on your particular situation 
   string ssqltable = "Table1";
   // 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 student from [Sheet1$]";
   try {


    //create our connection strings 
    //string sexcelconnectionstring = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + excelFilePath +  ";" + "Extended Properties=" + "\"Excel 8.0; HDR=Yes; IMEX=1;\"";
    string sexcelconnectionstring = @ "Provider=Microsoft.ACE.OLEDB.12.0;Data Source= XXXX\xls_test\Book1.xlsx" + ";" + "Extended Properties=Excel 12.0";

    string ssqlconnectionstring = "XXXX";
    //execute a query to erase any previous data from our destination table 
    string sclearsql = "delete from " + ssqltable;
    SqlConnection sqlconn = new SqlConnection(ssqlconnectionstring);
    SqlCommand sqlcmd = new SqlCommand(sclearsql, sqlconn);
    sqlconn.Open();
    sqlcmd.ExecuteNonQuery();
    sqlconn.Close();
    //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(ssqlconnectionstring);
    bulkcopy.DestinationTableName = ssqltable;
    while (dr.Read()) {
     bulkcopy.WriteToServer(dr);
    }
    dr.Close();
    oledbconn.Close();
    MessageBox.Show("File imported into sql server.");
   } catch (Exception ex) {
    //handle exception
    MessageBox.Show(ex.ToString());
    MessageBox.Show("Enter exception");
   }
  }
 
 DB上的表格： 
 
CREATE TABLE [dbo].[Table1](
    [student] [varchar](50) NULL,
    [rollno] [int] NULL,
    [course] [varchar](50) NULL
) ON [PRIMARY]
GO
 
 如果您想从XLS和DB的示例访问2张图片，请访问： https : //drive.google.com/drive/folders/0B98UpTa2n4XbeHZtOHU2cVotUms?usp=sharing 
 
 最后，我从这个代码中取得了大部分代码： https : //code.msdn.microsoft.com/office/Import-Excel-Spreadsheet-2b7ca7cf#content我改变了一点，但大多数想法都来自这些链接。 
 
 欢迎任何帮助！ 谢谢。 

Idea:
 
 
 
Read a XLS and upload the data on a Microsoft SQL
 
 
Problem:
 
 
 
Only the first column is being upload to the DB
 
 
My code:
 
  private void button1_Click(object sender, EventArgs e) {
   // string path = @"XXXX\xls_test\Book1.xlsx";
   string path = @ "XXXX\xls_test\Book1.xlsx";
   ImportDataFromExcel(path);
  }


  public void ImportDataFromExcel(string excelFilePath) {
   //declare variables - edit these based on your particular situation 
   string ssqltable = "Table1";
   // 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 student from [Sheet1$]";
   try {


    //create our connection strings 
    //string sexcelconnectionstring = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + excelFilePath +  ";" + "Extended Properties=" + "\"Excel 8.0; HDR=Yes; IMEX=1;\"";
    string sexcelconnectionstring = @ "Provider=Microsoft.ACE.OLEDB.12.0;Data Source= XXXX\xls_test\Book1.xlsx" + ";" + "Extended Properties=Excel 12.0";

    string ssqlconnectionstring = "XXXX";
    //execute a query to erase any previous data from our destination table 
    string sclearsql = "delete from " + ssqltable;
    SqlConnection sqlconn = new SqlConnection(ssqlconnectionstring);
    SqlCommand sqlcmd = new SqlCommand(sclearsql, sqlconn);
    sqlconn.Open();
    sqlcmd.ExecuteNonQuery();
    sqlconn.Close();
    //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(ssqlconnectionstring);
    bulkcopy.DestinationTableName = ssqltable;
    while (dr.Read()) {
     bulkcopy.WriteToServer(dr);
    }
    dr.Close();
    oledbconn.Close();
    MessageBox.Show("File imported into sql server.");
   } catch (Exception ex) {
    //handle exception
    MessageBox.Show(ex.ToString());
    MessageBox.Show("Enter exception");
   }
  }
 
Table on the DB:
 
CREATE TABLE [dbo].[Table1](
    [student] [varchar](50) NULL,
    [rollno] [int] NULL,
    [course] [varchar](50) NULL
) ON [PRIMARY]
GO
 
If you wanna access 2 images from examples of the XLS and DB: https://drive.google.com/drive/folders/0B98UpTa2n4XbeHZtOHU2cVotUms?usp=sharing
 
Finally, most part of this code I took from: https://code.msdn.microsoft.com/office/Import-Excel-Spreadsheet-2b7ca7cf#content I changed a little bit but most ideas come from these links.
 
Any help is welcome! Thank you. 

原文：https://stackoverflow.com/questions/44590007