using 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);
}
}
}
}
}
}