请教C#net EXCEL导入到MSSQL??
-------------------------------------
string strCon;
strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("~/UploadFile/imei.xls") + "; Extended Properties=Excel 8.0;";
OleDbConnection olecon = new OleDbConnection(strCon);
OleDbDataAdapter myda = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", strCon);
/////////这里SELECT * FROM [Sheet1$]读取后怎么写一个语句insert into itsproduct (sheetno,imeino,color) SELECT * FROM [Sheet1$] 直接写入数据库??
DataSet myds = new DataSet();
myda.Fill(myds);
Session["bomcheck"] = myds;
return myds;
-------------------------------------
string strCon;
strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("~/UploadFile/imei.xls") + "; Extended Properties=Excel 8.0;";
OleDbConnection olecon = new OleDbConnection(strCon);
OleDbDataAdapter myda = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", strCon);
/////////这里SELECT * FROM [Sheet1$]读取后怎么写一个语句insert into itsproduct (sheetno,imeino,color) SELECT * FROM [Sheet1$] 直接写入数据库??
DataSet myds = new DataSet();
myda.Fill(myds);
Session["bomcheck"] = myds;
return myds;
解决方案 »
- FCKEditor
- 用continue还是break呢
- 问一个C# .net 的问题 输入 输出
- 为什么里面的CDATA的用法是错的?应该如何用?
- 在新浪的新闻列表(http://news.sina.com.cn/)中可以看到一行信息有1条、2条、3条组成等等,是如何控制的?
- 求助:把数据输入到EXCEL时,中文变成了乱码怎么办?而且是随机出现乱码的....在线等
- DropdownList控件中保留空格问题
- 用Response.Write和Page.RegisterStartupScript显示的提示框有什么区别?请看具体的代码
- 很奇怪的问题,大家来看下~~~~~~搞懂就给分(很初级的问题!!!)
- .net中执行数据库操作时,如何rollback?
- 高手请进,问题。
- asp.net控制excel有点小问题
,excel 5.0;hdr=yes;database=c:test.xls,sheet1$)
select * from 表
要怎么写呢?
{
string filePath = "";
string getErrMsg = "";
DataSet excelDs = new DataSet();
if (FileUpload1.PostedFile.FileName == "")
{
Response.Write(" <script language=javascript>alert('请选择要上传的文件!'); </script>");
return;
}
else
{
string name = FileUpload1.PostedFile.FileName;
int i = name.LastIndexOf(".");
string namelast = name.Substring(i);
if (namelast == ".xls" || namelast == ".xlsx")
{
//从Excel读取数据
filePath = FileUpload1.PostedFile.FileName;
string connString = "Provider=Microsoft.Ace.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties='Excel 12.0;HDR=YES;IMEX=1'";
/*excel 2000 ~ 2003的 OleDb 连接串的格式如下:
Provider=Microsoft.Jet.OleDb.4.0;Data Source='excel文件路径';Extended Properties='Excel 8.0;HDR=YES'excel 2007 的 OleDb 的连接串的格式如下:Provider=Microsoft.Ace.OleDb.12.0;Data Source='excel文件路径';Extended Properties='Excel 12.0;HDR=YES'
*/
OleDbConnection excelConn = new OleDbConnection(connString);
excelConn.Open();
OleDbDataAdapter ExcelDA = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", excelConn);
try
{
ExcelDA.Fill(excelDs, "[Sheet1$]");
}
catch (Exception err)
{
Response.Write(err.Message);
}
finally
{
excelConn.Close();
excelConn = null;
} if (excelDs.Tables[0].Rows.Count != 0)
{
for (int j = 0; j < excelDs.Tables[0].Rows.Count; j++)
{
int id = int.Parse(excelDs.Tables[0].Rows[j]["ID"].ToString());
string ename = excelDs.Tables[0].Rows[j]["ENAME"].ToString();
string realname = excelDs.Tables[0].Rows[j]["REALNAME"].ToString();
string password = excelDs.Tables[0].Rows[j]["PASSWORD"].ToString();
string mobilephone = excelDs.Tables[0].Rows[j]["MOBILEPHONE"].ToString();
Response.Write(mobilephone);
}
}
else
{
Response.Write(" <script language=javascript>alert('不是excel文件!'); </script>");
return;
}
}
}
}
OleDbConnection myConn = new OleDbConnection(strCon);
string strCom = " SELECT * FROM [Sheet1$] ";
myConn.Open();
OleDbDataAdapter myCommand = new OleDbDataAdapter(strCom, myConn); DataSet myDataSet = new DataSet();
myCommand.Fill(myDataSet, "[Sheet1$]");
SqlConnection con = new SqlConnection("server=.;database=CDS;uid=sa;pwd=123456");
SqlCommand cmd = new SqlCommand();
DataTable dt = myDataSet.Tables[0];
con.Open();
for (int i = 4; i < dt.Rows.Count; i++)
{
string sql = "insert into Ga_course (course_school,course_area,course_core_courses,course_career_opportunity,course_requirements_english_TOEFL,course_requirements_english_IELTS,course_requirements_english_GMAT_GRE,course_requirements_english_background,course_RAE_ranking,course_IntakeDuration,course_Fee_for,course_Website_Contacts) values ('" + tools.validateinput(dt.Rows[i][0].ToString()) + "','" + tools.validateinput(dt.Rows[i][1].ToString()) + "','" + tools.validateinput(dt.Rows[i][2].ToString()) + "','" + tools.validateinput(dt.Rows[i][3].ToString()) + "','" + tools.validateinput(dt.Rows[i][4].ToString()) + "','" + tools.validateinput(dt.Rows[i][5].ToString()) + "','" + tools.validateinput(dt.Rows[i][6].ToString()) + "','" + tools.validateinput(dt.Rows[i][7].ToString()) + "','" + tools.validateinput(dt.Rows[i][8].ToString()) + "','" + tools.validateinput(dt.Rows[i][9].ToString()) + "','" + tools.validateinput(dt.Rows[i][10].ToString()) + "','" + tools.validateinput(dt.Rows[i][11].ToString()) + "')";
cmd.Connection = con;
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
}
con.Close();
我的这个excel前几行可能有什么标题之类的 ,总之很不规范,还有excel中的数据不是所有的都要,所以写的很烦,还要一列一列的对应数据库中的字段
1.excel中的数据是否和数据库中一致,长度类型
2.excel中列的数据是否有字符串和数字混合的
3.如果出错了该怎么处理?是用事务处理还是其他处理方式?
string execelConnectionStr = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileName + ";Extended Properties=\"Excel 8.0;HDR=YES;\"";
using (OleDbConnection conn = new OleDbConnection(execelConnectionStr))
{
OleDbCommand cmd = new OleDbCommand("select * FROM [" + sheetName + "]", conn);
conn.Open();
using (DbDataReader dr = cmd.ExecuteReader())
{
string sConnectionString = ConfigurationManager.ConnectionStrings["DatabaseString"].ConnectionString;
// Bulk Copy to SQL Server
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sConnectionString))
{
bulkCopy.DestinationTableName = strTable;
bulkCopy.WriteToServer(dr);
}
}
}批量导入,速度很快