C# 导入Excel表至SqlServer数据库

水深无声 2022-02-02 03:13 345阅读 0赞

本文实现要求如下：将Excel表（包括 xls 和 xlsx 后缀的Excel表）导至sqlserver中存在的表中，若数据库中无该表，则会自动创建。

现给出代码如下：

private void btnImport_Click(object sender, EventArgs e)
            {
                string oleCon = string.Empty;
                DataBase database = new DataBase();
    
                try
                {
                    if (txtFilePath.Text.Trim() == string.Empty)//如果没有选择文件
                    {
                        MessageBox.Show("请先选择要插入的Excel文件", "警告", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                        return;
                    }
    
                    else //导入Excel表
                    {
                        ImportExportExcel import = new ImportExportExcel();
                        database = new DataBase();
                        string strconn = database.getStrCon();
    
                        if (txtFilePath.Text.Substring(txtFilePath.Text.Length - 4) == ".xls")
                        {
                            oleCon = "Provider = Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + txtFilePath.Text.Trim() + ";"
                        + "Extended Properties = Excel 8.0;";
                        }
    
                        else if (txtFilePath.Text.Substring(txtFilePath.Text.Length - 5) == ".xlsx")
                        {
                            oleCon = "Provider = Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + txtFilePath.Text.Trim() + ";"
                        + "Extended Properties = Excel 12.0;";
                        }
    
                        string tbName = "tbHosInfo2";//将Excel表的数据插入该数据库表的表名
    
                        string strCreateTb = "if not exists(select * from sysobjects where name = '{0}') "
                         + "create table {0}( ID varchar(50) PRIMARY KEY,"
                         + "Name varchar(50), Province varchar(50),"
                         +"City varchar(50),Headcount smallint,"
                         +"BoyNum smallint,GirlNum smallint )";//当数据库中无该表，创建
    
                        import.ImportExcel(tbName, strconn, oleCon, strCreateTb);
                    }
                }
                catch
                {
                    MessageBox.Show("导入失败！请重新导入Excel表！","警告",MessageBoxButtons.OK,MessageBoxIcon.Warning);
                }
            }

调用到的类库有：

class ImportExportExcel
        {
            public void ImportExcel(string sheetName, string strCon,string oleCon,string strCreateTb)//导入Excel表
            {
                DataSet ds = new DataSet();
                try
                {
                    OleDbConnection conn = new OleDbConnection(oleCon);
                    conn.Open();
                    string strExcel = "";
                    OleDbDataAdapter myCommand = null;
                    strExcel = string.Format("select * from [sheet1$]", sheetName);
                    myCommand = new OleDbDataAdapter(strExcel, oleCon);
                    myCommand.Fill(ds, sheetName);
    
                    //若导入目标表不存在则创建,excel文件的第一行为列标题,从第二行开始全部都是数据记录     
                    string strSql = string.Format(strCreateTb, sheetName);//以sheetName为表名   
                    DataBase database = new DataBase();
                    database.getExecuteNonQuery(strSql);
    
                    using (SqlBulkCopy bcp = new SqlBulkCopy(strCon))//导入Excel表数据
                    {
                        bcp.BatchSize = 100;//每次传输的行数        
                        bcp.NotifyAfter = 100;//进度提示的行数        
                        bcp.DestinationTableName = sheetName;//目标表        
                        bcp.WriteToServer(ds.Tables[0]);//写入服务器
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
            }
        }

class DataBase
        {
            //数据库中，点击：工具->选项->Analysis Serviecs 设计器(可查看‘连接超时值’或‘查询超时值’，默认15s）
            static string strCon = "server=.;Database=HospitalAllocation;UID=sa;PWD=123;Connect TimeOut=10";
            static SqlConnection sqlConn = new SqlConnection(strCon);
    
            public SqlConnection getSqlCon()//连接数据库
            {
                return sqlConn;
            }
    
            public string getStrCon()
            {
                return strCon;
            }
    
            public int getExecuteNonQuery(string strSql)//执行SQL语句
            {
                SqlConnection conn = getSqlCon();
                conn.Open();
                SqlCommand cmd = new SqlCommand(strSql, conn);
                int i = cmd.ExecuteNonQuery();
                conn.Close();
                return i;
            }
        }