string clientFilename = FileUpload1.PostedFile.FileName.ToLower();
string serverFilename = "";
if (clientFilename == "")
{
return;
}
if (clientFilename.ToLower().IndexOf(".xlsx") > 0)
{
serverFilename = ".xlsx";
}
else
{
if (clientFilename.ToLower().IndexOf(".csv") > 0)
{
serverFilename = ".csv";
}
else if (clientFilename.ToLower().IndexOf(".xls") > 0 && clientFilename.EndsWith("xls"))
{
serverFilename = ".xls";
}
else
{
return;
}
}
string type = serverFilename;
serverFilename = "~/uploadexcel/" + "Test" + DateTime.Now.Year.ToString()
+ (DateTime.Now.Month > 9 ? DateTime.Now.Month.ToString() : "0" + DateTime.Now.Month.ToString())
+ (DateTime.Now.Day > 9 ? DateTime.Now.Day.ToString() : "0" + DateTime.Now.Day.ToString())
+ (DateTime.Now.Hour > 9 ? DateTime.Now.Hour.ToString() : "0" + DateTime.Now.Hour.ToString())
+ (DateTime.Now.Minute > 9 ? DateTime.Now.Minute.ToString() : "0" + DateTime.Now.Minute.ToString())
+ (DateTime.Now.Second > 9 ? DateTime.Now.Second.ToString() : "0" + DateTime.Now.Second.ToString())
+ DateTime.Now.Millisecond.ToString() + serverFilename;
serverFilename = MapPath(serverFilename);
if (File.Exists(serverFilename))
{
File.Delete(serverFilename);
}
FileUpload1.SaveAs(serverFilename);//上传文件
if (type == ".csv")
{
string c = serverFilename.Substring(0, serverFilename.LastIndexOf(@"\") + 1);//取到文件的上一级路径
string b = serverFilename.Substring(serverFilename.LastIndexOf(@"\") + 1);//取到文件名
string strConn = @"Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq= " + c;
strConn += ";Extensions=asc,csv,tab,txt;";
OdbcConnection objConn = new OdbcConnection(strConn);
DataSet ds = new DataSet();
try
{
string strSql = @"select * from " + b;
OdbcDataAdapter odbcCSVDataAdapter = new OdbcDataAdapter(strSql, objConn);
odbcCSVDataAdapter.Fill(ds);
}
catch (Exception ex)
{
throw ex;
}
}
else if(type==".xls" || type==".xlsx")
{
DataSet ds = new DataSet();
string sconn="";
if (type == ".xls")
{
sconn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + serverFilename + "';Extended Properties='Excel 8.0;HDR=YES;'";
}
else
{
sconn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + serverFilename + "';Extended Properties='Excel 12.0;HDR=YES'";
}
OleDbConnection conn = new OleDbConnection(sconn);
OleDbDataAdapter odda = new OleDbDataAdapter("select * from [Sheet1$]", conn);
odda.Fill(ds);
}