public string SaveUploadExcel(HttpPostedFile hpf)//驗證Excel檔案的正確性
{
//檢查檔案格式各檔案大小是否有效
if(hpf == null || hpf.ContentLength < 1 || System.IO.Path.GetExtension(hpf.FileName).ToLower() != ".xls")
{
throw new InnerException("檔案上傳失敗或檔案格式不正確。!");
}
//儲存檔案
try
{
string fileFullName = MakeFileName();
hpf.SaveAs(fileFullName);
return fileFullName;
}
catch//(Exception ex)
{
throw new InnerException("儲存檔案失敗。");// + ex.Message);
}
}
public string MakeFileName()//產生唯一檔案名的Excel檔案
{
string filePath = HttpContext.Current.Server.MapPath(HttpContext.Current.Request.ApplicationPath) +"\\uploadFiles\\PlanExcel";
if(!Directory .Exists(filePath))
Directory.CreateDirectory(filePath);
string fileName = Convert.ToString(HttpContext.Current.Session["UserID"]) + "_"
+ DateTime.Now.ToString("yyyyMMddHHmmss")
+ ".xls";
return filePath + "\\" + fileName;
}
public DataTable ReadNoHeadExcelData(string fileFullName)//讀取Excel檔案最後返回一個DataTable,可以在返回DataTable後對這個DataTable進行檢驗!
{
string excelNoHeadConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\"Excel 8.0;HDR=NO;IMEX=1;\"";//連接字串
OleDbConnection oleCn =new OleDbConnection();
try
{
string currConnStr = string.Format(excelNoHeadConnStr, fileFullName);//連接字串
oleCn.ConnectionString = currConnStr;
DataTable dt = new DataTable();
oleCn.Open();
string excelBookName = oleCn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null).Rows[0][2].ToString();
if(excelBookName!=string.Empty && excelBookName.Trim()!="")
{
string currSqlStr = string.Format(excelSqlStr, excelBookName);//sql字串
OleDbCommand oleCmd = new OleDbCommand(currSqlStr, oleCn);
OleDbDataAdapter oleDda = new OleDbDataAdapter(oleCmd);
oleDda.Fill(dt);
}
oleCn.Close();
return dt;
}
catch
{
string errorMsg="Excel檔案格式不對,請嘗試用excel開啟該檔案檢查內容並另存一下,確保成為正確的excel檔案,再重新上傳";
System.Web.HttpContext.Current.Response.Write("<script>alert('"+ errorMsg +"')</script>");
oleCn.Close();
return null;
}
}