Method for C to import Excel table data into Sql Server database

This article illustrates the method of C # to import Excel table data into Sql Server database. Share it for your reference, as follows:

There are many ways to import Excel table data into Sql Server database, and here is only one of them:

1. First, we need to create a new my_test table in the test database, which has three fields: tid int type, tname nvarchar type and tt nvarchar type
(Note: The data type in the my_test table must match the type 1 of the corresponding field in Excel.)

2. We use SELECT * FROM OPENROWSET ('Microsoft. Jet. OLEDB. 4.0', 'Excel 5.0; DatabASE = [Excel table. Path to xsl file]; HDR = YES; IMEX=1 ', Sheet1 to read the data in the Excel table. The data read out is the same as the data read out from the table in the database, including the field name and data. Of course, we can also use the field name list to obtain the department data in Excel table. SELECT Field 1, Field 2, Field 3 [...] FROM OPENROWSET ('Microsoft. Jet. OLEDB. 4.0', 'Excel 5.0; DatabASE = [Excel table. Path to xsl file]; HDR = YES; IMEX=1 ', Sheet1

Note: HDR=Yes, which means that line 1 is the header and is not used as data; IMEX (IMport EXport mode) Settings
IMEX has three modes:
0 is Export mode
1 is Import mode
2 is Linked mode (full update capabilities)
What I want to explain here is the IMEX parameter, because different modes represent different read and write behaviors:
When IMEX = 0, it is "export mode", and Excel files opened in this mode can only be used for "write" purposes.
When IMEX = 1, it is "import mode", and Excel files opened in this mode can only be used for "read" purposes.
When IMEX = 2, it is "link mode". The Excel files opened in this mode can support both "read" and "write" purposes.
The meaning is as follows:
0--Output mode;
1--Input mode;
2--Link Mode (Full Update Capability)

3. In Excel, the column name is defined in line 1, and the data is from line 2. The data read from Excel through the Sql statement also starts at Line 2, where the column name becomes the field name. If you have column names defined in Line 1, then the names of the fields of the data obtained from Excel are the column names in Excel. For example, the field names of the data obtained from the test. xls sheet table are numbered name comments. If the column name is not defined in the first row of the Excel table you defined, the field names of the obtained data are F1, F2, F3... and so on. If you only want to get the data in the middle of Excel table, then you can use the above content.

4. Create a new web form in VS (test. aspx, note: winform form can also be used), add an Button control to it, and click this button to perform import. Double-click the button to define the event handler. The code in test. aspx. cs is as follows:

using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
public partial class admin_test : System.Web.UI.Page
{
  protected void Page_Load(object sender, EventArgs e)
  {
  }
  public SqlConnection con()
  {
    return new SqlConnection("server=localhost;uid=test;pwd=test;database=test");
    // Here's uid=test In test Must be System Administrtor ,   Otherwise something goes wrong 
  }
  protected void Button1_Click1(object sender, EventArgs e)
  {
    SqlConnection mycon = con();
    string sqlstr = "insert into my_test select  Numbering ,  Name ,  Remarks  from OPENROWSET('MICROSOFT.JET.OLEDB.4.0','Excel 5.0;HDR=YES;DATABASE=e:\\test.xls',sheet1$)";
/* You can use it here  *  Substitute   Numbering ,  Name ,  Remarks , These representations excel Column name in   */
    SqlCommand cmd = new SqlCommand(sqlstr, mycon);
    mycon.Open();
    cmd.ExecuteNonQuery();
    mycon.Close();
  }
}

Executing the above code may cause the following problems:

SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component has been shut down as part of the security configuration for this server. System administrators can enable 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configurator" in SQL Server Books Online.

Solution:

/* Enable Ad Hoc Distributed Queries : */
exec sp_configure 'show advanced options',1
reconfigure
exec sp_configure 'Ad Hoc Distributed Queries',1
reconfigure
 
/* When the use is complete, close the Ad Hoc Distributed Queries : */
exec sp_configure 'Ad Hoc Distributed Queries',0
reconfigure
exec sp_configure 'show advanced options',0
reconfigure

For more readers interested in C # related content, please check the topics on this site: "Summary of C # Operating Excel", "Summary of C # Programming Thread Use Skills", "Summary of XML File Operation Skills in C #", "C # Common Control Usage Tutorial", "WinForm Control Usage Tutorial", "C # Data Structure and Algorithm Tutorial", "C # Array Operation Skills Summary" and "C # Object-Oriented Programming Introduction Tutorial"

I hope this article is helpful to everyone's C # programming.