/// <summary>
/// 导入
/// </summary>
private void btn_In_Click(object sender, EventArgs e)
{
int i = DataTableToDB();
MessageBox.Show("成功导入" + i + "条商品信息!");
} /// <summary>
/// 获取后缀名为*.xlsx的文件
/// </summary>
public void GetFile()
{
System.IO.DirectoryInfo dir = new DirectoryInfo(VPath);
if (dir.Exists)//判读是否存在改文件
{
fiList = dir.GetFiles("*.xlsx"); //获取后缀名为*.xlsx的文件
}
} /// <summary>
/// Excel数据转化为DataTable
/// </summary>
/// <param name="strSheetName"></param>
/// <param name="strExcelFileName">文件路径</param>
/// <returns>返回DataTable</returns>
public DataTable ExcelToDataTable(string strExcelFileName, string strSheetName)
{
string strConn = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR=NO;IMEX=1;'", strExcelFileName);
string strExcel = string.Format("select * from [{0}$]", strSheetName);
DataSet ds = new DataSet(); using (OleDbConnection conn = new OleDbConnection(strConn))
{
conn.Open();
OleDbDataAdapter adapter = new OleDbDataAdapter(strExcel, strConn);
adapter.Fill(ds, strSheetName);
conn.Close();
} return ds.Tables[strSheetName];
} public int DataTableToDB()
{
GetFile();
int count = ;
string _strExcelFileName = "";
for (int i = ; i < fiList.Length; i++)
{
_strExcelFileName = dir + "\\" + fiList[i]; DataTable dtExcel = Global.g_objDb.ExcelToDataTable(_strExcelFileName, "Sheet1");
for (int j = ; j < dtExcel.Rows.Count; j++)
{
if ((ReturnSqlResultCount("select * from A where a1='" + dtExcel.Rows[j][].ToString() + "'")) > )
{
continue;
}
else
{
Global.g_objDb.InsertDataToAccess(dtExcel.Rows[j][].ToString(), dtExcel.Rows[j][].ToString(), dtExcel.Rows[j][].ToString(), dtExcel.Rows[j][].ToString(), dtExcel.Rows[j][].ToString(), dtExcel.Rows[j][].ToString(), dtExcel.Rows[j][].ToString(), dtExcel.Rows[j][].ToString()); count++;
}
}
} return count;
} String connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Access_DataBase.mdb;Jet OLEDB:Database Password=123456""; OleDbConnection Connection = new OleDbConnection(connectionString); /// <summary>
/// 执行一查询语句语句,同时返回bool值
/// </summary>
public bool InsertDataToAccess(string col1, string col2, string col3, string col4, string col5, string col6, string col7, string col8)
{
bool resultState = false; Connection.Open();
string strSQL = "insert into spdm(a,b,c,d,e,f,g,h) values('" + col1 + "','" + col1 + "','" + col1 + "','" + col1 + "','" + col1 + "','" + col1 + "','" + col1 + "','" + col1 + "')";
OleDbTransaction myTrans = Connection.BeginTransaction();
OleDbCommand command = new OleDbCommand(strSQL, Connection, myTrans); try
{
command.ExecuteNonQuery();
myTrans.Commit();
resultState = true;
}
catch
{
myTrans.Rollback();
resultState = false;
}
finally
{
Connection.Close();
}
return resultState;
} /// <summary>
/// 执行一查询语句,同时返回查询结果数目
/// </summary>
/// <param name="strSQL"></param>
/// <returns></returns>
public int ReturnSqlResultCount(string strSQL)
{
int sqlResultCount = ; try
{
Connection.Open();
OleDbCommand command = new OleDbCommand(strSQL, Connection);
OleDbDataReader dataReader = command.ExecuteReader(); while (dataReader.Read())
{
sqlResultCount++;
}
dataReader.Close();
}
catch
{
sqlResultCount = ;
}
finally
{
Connection.Close();
}
return sqlResultCount;
}