完美解决Excel的连接字符串与上传方法
1:第一个连接字符串为上传03的,第2个连接字符串能兼容所有的
//string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filenameurl + ";extended properties='Excel 8.0;HDR=No;IMEX=1;'";
string strCon = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + filenameurl + ";" + "Extended Properties=Excel 8.0;";
2:导入按钮
protected void btnExport_Click(object sender,EventArgs e)
{
if (FileUpload1.HasFile == false)
{
Response.Write("<script>alert('请您选择Excel文件')</script> ");
return;//当无文件时,返回
}
string IsXls = System.IO.Path.GetExtension(FileUpload1.FileName).ToString().ToLower();
if (IsXls != ".xls" && IsXls != ".xlsx")
{
Response.Write("<script>alert('只可以选择Excel文件')</script>");
return;//当选择的不是Excel文件时,返回
}
string filename = FileUpload1.FileName; //获取Execle文件名
if (!Directory.Exists(path))
{
Directory.CreateDirectory(path);
}
string FilePath = path + filename;
if (File.Exists(FilePath))
File.Delete(FilePath);
//FileUpload1.SaveAs(path + filename);
DataTable ds = ExecleDs(path + filename);
}
private DataTable ExecleDs(string filenameurl)
{
//string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filenameurl + ";extended properties='Excel 8.0;HDR=No;IMEX=1;'";
string strCon = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + filenameurl + ";" + "Extended Properties=Excel 8.0;";
OleDbConnection conn = new OleDbConnection(strCon);
OleDbDataAdapter odda = new OleDbDataAdapter("select * from [Sheet1$]", conn);
DataTable dt = new DataTable();
odda.Fill(dt);
return dt;
}