利用C#写一个能将execl导入数据库的一个程序怎么写
利用C#写一个能将execl导入数据库的一个程序怎么写 环境：c#.2005+Access+ Sql一、把DataTable插入数据库
public static void DataTableToDB()
{
string _strExcelFileName = @"D:\example.xls";
DataTable dtExcel = ExcelToDataTabe(_strExcelFileName,"Sheet1");
for (int i = 0; i < dtExcel.Rows.Count; i++)
{
InsertDataToAccess(dtExcel.Rows.ToString(), float.Parse(dtExcel.Rows.ToString()));
}
}
二、把Excel数据读入DataTable
public static DataTable ExcelToDataTable(string strExcelFileName, string strSheetName)
{
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + strExcelFileName + ";"
+"Extended Properties=Excel 5.0;";
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;
}
三、向Access数据库表插入数据
public static void InsertDataToAccess(string _strPara,float _fPara)
{
OleDbConnection oleDbConn = new OleDbConnection();
oleDbConn.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\ExcelData.mdb;User
Id=admin;Password=;";
oleDbConn.Open();
string strInsertString = "INSERT INTO tb_excelData (strCollumn1,fCollumn2) VALUES
(@strCollumn1,@fCollumn2)";
OleDbCommand oComm = new OleDbCommand(strInsertString, oleDbConn);
oComm.Parameters.Add("@strCollumn1", OleDbType.Char , 50);
oComm.Parameters["@strCollumn1"].Value = _strPara;
oComm.Parameters.Add("@fCollumn2", OleDbType.Double);
oComm.Parameters["@fCollumn2"].Value = _fPara;
ocomm.ExecuteNonQuery();
oleDbConn.Close();
}
环境：c#.2005+Access+ Sql 一、把DataTable插入数据库public static void DataTableToDB(){ string _strExcelFileName = @"D:\example.xls"; DataTable dtExcel = ExcelToDataTable(_strExcelFileName,"Sheet1"); for (int i = 0; i < dtExcel.Rows.Count; i++) { InsertDataToAccess(dtExcel.Rows.ToString(), float.Parse(dtExcel.Rows.ToString())); }}二、把Excel数据读入DataTablepublic static DataTable ExcelToDataTable(string strExcelFileName, string strSheetName){ string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + strExcelFileName + ";" +"Extended Properties=Excel 5.0;"; 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;}三、向Access数据库表插入数据public static void InsertDataToAccess(string _strPara,float _fPara){ OleDbConnection oleDbConn = new OleDbConnection(); oleDbConn.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\ExcelData.mdb;User Id=admin;Password=;"; oleDbConn.Open(); string strInsertString = "INSERT INTO tb_excelData (strCollumn1,fCollumn2) VALUES (@strCollumn1,@fCollumn2)"; OleDbCommand oComm = new OleDbCommand(strInsertString, oleDbConn); oComm.Parameters.Add("@strCollumn1", OleDbType.Char , 50); oComm.Parameters["@strCollumn1"].Value = _strPara; oComm.Parameters.Add("@fCollumn2", OleDbType.Double); oComm.Parameters["@fCollumn2"].Value = _fPara; ocomm.ExecuteNonQuery(); oleDbConn.Close();}
页:
[1]