<html xmlns="http://www./1999/xhtml" >
<head runat="server">
<title>无标题页</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:FileUpload ID="FuloadExcelFile" runat="server" Height="25px" Width="473px" />
<asp:Button ID="btnReadDataTo" runat="server" Text="导入" OnClick="btnReadDataTo_Click" /></div>
</form>
</body>
</html>
protected void btnReadDataTo_Click(object sender, EventArgs e)
{
if (FuloadExcelFile.FileName == "")
return;
string fileExt = System.IO.Path.GetExtension(FuloadExcelFile.FileName);
if (fileExt != ".xls")//必须是EXCEL文件
return;
string filepath = FuloadExcelFile.PostedFile.FileName;//文件路径
DataTable dt = new DataTable();
dt = CallExcel(filepath);//返回EXCEL文件的数据
if(InsertSQLServer(dt,FuloadExcelFile.FileName.Split('.')[0]))//导入数据库
{}
}
protected DataTable CallExcel(string filepath)
{
OleDbConnection con = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties=Excel 8.0");
con.Open();
string sql = "select * from [Sheet1$]";//选择第一个数据SHEET
//OleDbCommand command = new OleDbCommand(sql, con);
//OleDbDataReader reader = command.ExecuteReader();
//if (reader.Read())
//{
// reader[0].ToString();//直接读出数据
//}
OleDbDataAdapter adapter = new OleDbDataAdapter(sql, con);
DataTable dt = new DataTable();
adapter.Fill(dt);
//reader.Close();
//command.Dispose();
con.Close();
con.Dispose();
return dt;
}
protected bool InsertSQLServer(DataTable dt,string dataname)
{
string strCon = @"User ID=SA;Initial Catalog=CarManage;Data Source=(local);Password=SA";//无数据库名连接
string strTest="testTable";
try
{
SqlConnection con = new SqlConnection(strCon);//创建数据库
con.Open();
string strSQL = "IF NOT EXISTS(SELECT name FROM sysobjects WHERE name=N'" + dataname + "') CREATE DATABASE [" + dataname + "]";
SqlCommand command = new SqlCommand(strSQL, con);
command.ExecuteNonQuery();
//创建数据库表
strSQL = "USE[" + dataname + "] IF NOT EXISTS(SELECT * FROM sysobjects WHERE object_id=OBJECT_ID(N'[DBO].[" + strTest + "]') AND type in (N'U')) CREATE TABLE " + strTest + " (";
string strColumn=string.Empty;
for (int i = 0; i < dt.Columns.Count; i++)
{
strSQL += "[" + dt.Columns[i].ColumnName + "] [nvarchar](256),";
strColumn+=dt.Columns[i].ColumnName+",";
}
strSQL += " )";
SqlCommand newcom = new SqlCommand(strSQL, con);
newcom.ExecuteNonQuery();
//插入数据
strColumn=strColumn.Substring(0,strColumn.Length-1);
for(int i=0;i<dt.Rows.Count;i++)
{
strSQL = "USE[" + dataname + "] Insert into "+strTest+" (" + strColumn + ") values (";
for (int k = 0; k < dt.Columns.Count; k++)
{
strSQL += "'"+dt.Rows[i][k].ToString()+"',";
}
strSQL = strSQL.Substring(0, strSQL.Length - 1);
strSQL += ")";
SqlCommand insertCom = new SqlCommand(strSQL, con);
insertCom.ExecuteNonQuery();
}
return true;
}
catch
{
return false;
}
}
从Excel表格中读取数据导入到Access数据库中
//一、把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[i][0].ToString(), float.Parse(dtExcel.Rows[i][1].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[strSheetName];
}
//三、向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();
}
