WINFrom Excal 数据导入数据库

System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Linq; using System.Text; using System.Threading.Tasks; using System.Windows.Forms; using System.Data.SQLite; using System.Data.OleDb; using System.IO; using MySql.Data.MySqlClient; using Dapper; using *******.DataDefine; //数据库引用 namespace LangLibSpeakingImport {     public partial class Import : Form     {         public Import()         {             InitializeComponent();         }         private void button2_Click(object sender, EventArgs e)         {             string excelName = string.Empty;             string dirName = Path .Combine(Directory.GetCurrentDirectory(), "root" );//bin目录下的文件夹的文件名             string[] subDirs = Directory.GetDirectories(dirName);             List<SK_SysTopicInfo > topicList = new List <SK_SysTopicInfo>();             foreach (string dNames in subDirs)             {                 string dirSeName = Path .Combine(Directory.GetCurrentDirectory(), dNames);                 string[] subSeDirs = Directory.GetDirectories(dirSeName);                                //获取文件夹下子文件夹下的子Excel的路径                 foreach (string secName in subSeDirs)                 {                     try                     {                         excelName = secName.Substring(secName.LastIndexOf(@"\" ) + 1, 4);                         string fileName = Path .Combine(secName, excelName + ".xlsx" );     //获取文件夹下子文件夹下的子Excel的路径
                        var connectionString = string .Format("Provider=Microsoft.ACE.OLEDB.12.0; Data Source={0}; Extended Properties=Excel 12.0;", fileName); //连接Excel表的数据
                        var adapter1 = new OleDbDataAdapter( "SELECT * FROM [SK_SysTopic$]", connectionString);   //Excel表的share名
                                               var ds = new DataSet();                         adapter1.Fill(ds, "SysTopicDatas" );
                        DataTable readingTopicTable = ds.Tables["SysTopicDatas" ];                         foreach (DataRow row in readingTopicTable.Rows)                         {                             SK_SysTopicInfo topicQuest = new SK_SysTopicInfo ();                             topicQuest.Id = Convert.ToString(row["Id"]);//字段名                             topicQuest.TitleText = Convert .ToString(row["TitleText"]);//字段名                             topicQuest.MediaLength = Convert .ToString(row["MediaLength"]);//字段名                             topicQuest.Difficulty = Convert .ToString(row["Difficulty"]);//字段名                             topicQuest.Source = Convert.ToString(row["Source"]);//字段名                             topicQuest.Scene = Convert.ToString(row["Scene"]);//字段名                             topicList.Add(topicQuest);                         }

                        using (MySql.Data.MySqlClient.MySqlConnection connection = new MySql.Data.MySqlClient.MySqlConnection ("Server=*******;Database=***;Uid=**;Pwd=****;Charset=utf8;" ))//连接数据库                         {                             try                             {                                 connection.Open();                                 connection.Execute( "delete from SK_SysTopic" );                                 connection.Execute( "Insert into SK_SysTopic(Id, TitleText, MediaLength, Difficulty, Source, Scene) values(@Id, @TitleText, @MediaLength, @Difficulty, @Source, @Scene)", topicList);//添加数据                             }                             catch (Exception )                             {                                 throw;                             }                         }                     }                     catch (Exception ex)                     {                         this.textBox1.Text += string.Format( "{0} Exception caught.", ex);                     }                 }             }         }     } }

标签：