将Excel文件数据库导入SQL Server的三种方案

 将Excel文件数据库导入SQL Server的三种方案

 

//方案一： 通过OleDB方式获取Excel文件的数据，然后通过DataSet中转到SQL Server

openFileDialog = new OpenFileDialog();
openFileDialog.Filter = "Excel files(*.xls)|*.xls";

if(openFileDialog.ShowDialog()==DialogResult.OK)
{
    FileInfo fileInfo = new FileInfo(openFileDialog.FileName);
    string filePath = fileInfo.FullName;
    string connExcel = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties=Excel 8.0";
   
    try
    {
        OleDbConnection oleDbConnection = new OleDbConnection(connExcel);
        oleDbConnection.Open();
       
        //获取excel表
        DataTable dataTable = oleDbConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

        //获取sheet名，其中[0][1]...[N]: 按名称排列的表单元素
        string tableName = dataTable.Rows[0][2].ToString().Trim();
        tableName = "[" + tableName.Replace("'","") + "]";

        //利用SQL语句从Excel文件里获取数据
        //string query = "SELECT classDate,classPlace,classTeacher,classTitle,classID FROM " + tableName;
        string query = "SELECT 日期,开课城市,讲师,课程名称,持续时间 FROM " + tableName;
        dataSet = new DataSet();