I've been doing this recently, how to excel Import into database , After many searches , Finally find a suitable , And after their own improvement can be used normally ( Forget the link address of the original author's blog , Excuse me )

  1. The first is the creation of the form , The text box shows the path to the file , Button to perform the operation ,DataGridView Display imported information
  2. The code is as follows : You can modify it according to your own needs , I want to refresh my other form after importing , Delegation is defined , You can ignore it .

     // Define the entrusted 
    public delegate void Refresh(); // Defining events
    public event Refresh myRefresh;
    public ExcelRoprtForm()
    {
    InitializeComponent();
    } private void button1_Click(object sender, EventArgs e)
    {
    // Select the imported file
    try
    {
    //openFileDialog1.Filter = "Excel file |*.xls";// Specifies the file format type
    OpenFileDialog fd = new OpenFileDialog();
    fd.Filter = "Excel file (*.xls,xlsx)|*.xls;*.xlsx";
    if (fd.ShowDialog() == DialogResult.OK)
    {
    string fileName = fd.FileName.ToString();
    this.textBox1.Text = fileName;
    } }
    catch (Exception ee)
    {
    MessageBox.Show(" Error opening file !" + ee.Message.ToString());
    }
    } private DataSet xsldata(string filepath)
    {
    string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties='Excel 8.0;IMEX=1'"; //string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " + filepath + ";Extended Properties='Excel 12.0; HDR=Yes; IMEX=1'"; System.Data.OleDb.OleDbConnection Conn = new System.Data.OleDb.OleDbConnection(strCon); string strCom = "SELECT * FROM [Sheet1$]"; Conn.Open(); System.Data.OleDb.OleDbDataAdapter myCommand = new System.Data.OleDb.OleDbDataAdapter(strCom, Conn); DataSet ds = new DataSet(); myCommand.Fill(ds, "[Sheet1$]");
    dataGridView1.DataSource = ds.Tables[0];
    Conn.Close();
    return ds;
    }
    private void button2_Click(object sender, EventArgs e)
    {
    if (textBox1.Text == "")
    {
    MessageBox.Show(" Please select the Excel file !", " System prompt ", MessageBoxButtons.OK, MessageBoxIcon.Information);
    return;
    }
    string filepath = textBox1.Text;
    string strcon1 = ConfigurationManager.ConnectionStrings["connString"].ToString();
    SqlConnection conn = new SqlConnection(strcon1);// Linked database
    conn.Open();
    try
    {
    DataSet ds = new DataSet();
    // Get the data set
    // Call the function above
    ds = xsldata(filepath);
    int errorcount = 0;// Record the number of error messages int insertcount = 0;// Number of records inserted successfully int updatecount = 0;// Number of records updating information for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
    {
    string carnumber = ds.Tables[0].Rows[i][0].ToString();
    int carstatus = Convert.ToInt32(ds.Tables[0].Rows[i][1].ToString());
    int cartype = Convert.ToInt32(ds.Tables[0].Rows[i][2].ToString());
    string carbrand = ds.Tables[0].Rows[i][3].ToString(); if (carnumber != "" && carstatus != 0 && cartype != 0)
    {
    SqlCommand selectcmd = new SqlCommand("select count(*) from CarInfo where CarNumber='" + carnumber + "'", conn); int count = Convert.ToInt32(selectcmd.ExecuteScalar());
    if (count > 0)
    {
    updatecount++;
    }
    else
    {
    SqlCommand insertcmd = new SqlCommand("insert into CarInfo(CarNumber,CarStatusID,CarTypeID,CarBrand) values(" + "'" + carnumber + "'," + carstatus + "," + cartype + ",'" + carbrand + "'" + ")", conn); insertcmd.ExecuteNonQuery(); insertcount++; }
    }
    else
    {
    //MessageBox.Show(" Error in spreadsheet information !");
    errorcount++;
    }
    }
    myRefresh();
    MessageBox.Show(insertcount + " Data imported successfully !" + updatecount + " Duplicate data !" + errorcount + " The partial information of the data is empty and has not been imported !");
    }
    catch (Exception ex)
    { MessageBox.Show(ex.Message);
    } finally
    {
    conn.Close(); }
    }

c# excel How to import into sqlserver More articles on the database

  1. EXCEL Batch import to Sqlserver Database and batch modification of data between two tables

    Excel A lot of data is imported into sqlserver A field in the original table that generates a temporary table and batch updates the data of a field in the temporary table 1: First of all, we have to deal with EXCEL To deal with Change your name to English , Don't have extra columns and rows ( adopt ctrl+shift Left or ...

  2. Excel Data import to Sqlserver In the database ltrim() 、rtrim() 、replace() function Invalid space in turn

    Today, some data from Excel Medium to Sqlserver In the database , When doing data merging and de duplication, we find that , There are two data as like as two peas. , There was no merger : It turns out there's one, and there's one behind it “ Space ”, It's because of this “ Space ” It drives me crazy for a long time , Because it ...

  3. Npoi take excel Data import to sqlserver database

    /// <summary> /// take excel Import to datatable /// </summary> /// <param name="filePath&qu ...

  4. NodeJs And EXCEL File import and export MongoDB Database data

    NodeJs And EXCEL File import and export MongoDB Database data One , Introduction and requirements 1.1, Introduce (1),node-xlsx : be based on Node.js analysis excel File data and generation excel file . (2),ex ...

  5. Silverlight take Excel Import to SQLserver database

    Recent obsession with reading Excel Template data , Import data SQLServer Of Silverlight Realization , This article will post the implementation code , As a simple example , For your convenience : 1. First design the foreground interface and create a new one Silverlight5.0 Applications ...

  6. Excel The table data is imported into SQLServer database

    Reprint :http://blog.csdn.net/lishuangzhe7047/article/details/8797416 step : 1, Select the database to insert -- Right click -- Mission -- Import data 2, Click next ...

  7. take Excel Import file data to SqlServer Three schemes of database

    Scheme 1 :  adopt OleDB How to get Excel File data , And then through DataSet Transfer to SQL Server, The advantage of this method is very flexible , It can be done to Excel Each cell in the table does what the user needs . openFil ...

  8. Research on the data volume of one hundred thousand and one million Excel File import and write to database

    One . Demand analysis There's a demand recently , Import 100000 level , It may even record the number of vehicle blacklists with millions of data Excel file , Take this opportunity to analyze the coding process ; First, break down the requirement , It is found that there are three more complex problems : Question 1 :Excel After file import ...

  9. Batch will make all the files in the folder Excel File import to Microsoft SQL database

    The following code will c:\cs\ All under the folder Excle Import data into SQL database declare @query vARCHAR(1000) declare @max1 int declare @count1 i ...

Random recommendation

  1. 1001. A+B Format (20)

    Link to the original question :https://www.patest.cn/contests/pat-a-practise/1001 The title is as follows : Calculate a + b and output the sum in ...

  2. Getting Started With Hazelcast Reading notes ( The fifth chapter , Chapter six )

    The fifth chapter monitor This chapter should be Hazelcast It's the core mechanism ,Hazelcast By registering a variety of listeners, we can learn the modification of data by other applications in the cluster , Membership , Quit and so on . It is divided into 3 A hierarchical . 1.EntryListener( Data pair ...

  3. Foreach And Random

    [ Memory post ] 1)foreach Statements can be used for data or any other purpose Iterable, But that doesn't mean that arrays are definitely one Iterable, And any automatic packaging doesn't happen automatically . package thinking.in.ja ...

  4. Json Analysis of key points

    analysis Json when , There was a new problem yesterday , It's all parsed arrays , It's not an array. It's a class . This is a Json strand ; {"status":"00001","ver" ...

  5. MCDownloader(iOS Downloader ) Instructions

    Example Preface quite a lot iOS All applications need to download data , And manage the process and results of these downloads , That's why I got to write this MCDownloader Ideas . stay IOS File downloader -MCDownloadManager In this article , I make ...

  6. ios Summary of knowledge points ——UITableView The expansion and contraction and transverse direction of Table

    UITableVIew yes iOS One of the most widely used controls in development , about UITableView This article will not discuss the basic usage of , This article mainly aims at UITableView The expansion and contraction of , In the end of the article, we will also discuss the horizontal ta ...

  7. ● Introduction to Du Jiaosha (BZOJ 3944 Sum)

    Introduction to Du Jiao . http://blog.csdn.net/skywalkert/article/details/50500009( Good writing !) Can be in $O(N^{\frac{2}{3}}) or O(N^{\f ...

  8. Android Search box SearchView Usage of -android Learning journey ( Thirty-nine )

    SearchView brief introduction SearchView Is the search box component , It allows users to search for text , Then show .' Code example This example adds the moth ListView Used to SearchView Add the function of automatic completion . package ...

  9. SQLI DUMB SERIES-9&amp;&amp;10

    The fifth level . The eighth and ninth levels . The tenth level is to use blind annotation , In addition to the double injection mentioned in the fifth level , Time injection can also be used (1) No matter what you type , All echoes are the same (2) ?id=1' and sleep(3) --+ There was a significant delay , The explanation can ...

  10. RedHat7 install vmware Virtual machine startup error

    The error is as follows : Kernel Headers for version 3.10.0-229.14.1.el7.x86_64 were not found.If you.... install kernel dev ...