protected void btnUpload_Click(object sender, EventArgs e) { if (Convert.ToInt64(ddcategory.SelectedItem.Value) == -1) { lblMsg.ForeColor = System.Drawing.Color.Red; lblMsg.Text = "Please select Group and the Category first"; return; } if (btnUpload.Text == "Upload") { refresh.Visible = true; btnUpload.Visible = false; //Get path from web.config file to upload string FilePath = "~//excell//"; string filename = string.Empty; //To check whether file is selected or not to uplaod if (FileUploadToServer.HasFile) { bool isvalidfile = checkextension(); string FileExt = System.IO.Path.GetExtension(FileUploadToServer.PostedFile.FileName); if (!isvalidfile) { lblMsg.ForeColor = System.Drawing.Color.Red; lblMsg.Text = "Please upload only Excel"; } else { int FileSize = FileUploadToServer.PostedFile.ContentLength; if (FileSize <= 10485760)//1048576 byte = 1MB { //Get file name of selected file filename = Path.GetFileName(Server.MapPath(FileUploadToServer.FileName)); OleDbConnection con = null; //Save selected file into server location FileUploadToServer.SaveAs(Server.MapPath(FilePath) + "ExcelSend" + FileExt); //Get file path try { string filePath = Server.MapPath(FilePath) + "ExcelSend" + FileExt; //Open the connection with excel file based on excel version if (FileExt == ".xls") { con = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties=Excel 8.0;"); } else if (FileExt == ".xlsx") { con = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties=Excel 12.0;"); } } catch (IOException) { } try { con.Open(); } catch (System.InvalidOperationException) { lblMsg.Text = "Please save your Excel 2007 File AS Excel 2003 and then try uploading again"; return; } //Get the list of sheet available in excel sheet DataTable dt = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); //Get first sheet name string getExcelSheetName = dt.Rows[0]["Table_Name"].ToString(); //Select rows from first sheet in excel sheet and fill into dataset OleDbCommand ExcelCommand = new OleDbCommand(@"SELECT [Party Name],Items,Place,[Delivery Terms],[Qty(bags)],[Rate/Qtl],Pack FROM [" + getExcelSheetName + @"]", con); OleDbDataAdapter ExcelAdapter = new OleDbDataAdapter(ExcelCommand); try { DataTable dtbl = new DataTable(); ExcelAdapter.Fill(dtbl); ViewState["maintable"] = dtbl; sendmessage(); con.Close(); con.Dispose(); } catch (Exception) { lblMsg1.ForeColor = System.Drawing.Color.Red; lblMsg1.Visible = true; lblMsg1.Text = "Error uploading file: Uploaded file is not in correct format.<br>It must atleast consist of columns named :<i>Party Name,Items,Place,Delivery Terms,Qty(bags),Rate/Qtl,Pack</i><br>Correct your file and try uploading it again"; } } } } else { Response.Redirect("run_excelsend.aspx"); } } }
public void BindGridView() { try { string FilePath = ResolveUrl("~/FILES/"); // Give Upload File Path string filename = string.Empty; if (FileUploadToServer.HasFile) // Check FileControl has any file or not { try { string[] allowdFile = { ".xls", ".xlsx" }; string FileExt = System.IO.Path.GetExtension(FileUploadToServer.PostedFile.FileName).ToLower();// get extensions bool isValidFile = allowdFile.Contains(FileExt); // check if file is valid or not if (!isValidFile) { } else { int FileSize = FileUploadToServer.PostedFile.ContentLength; // get filesize if (FileSize <= 1048576) //1048576 byte = 1MB { filename = Path.GetFileName(Server.MapPath(FileUploadToServer.FileName)); // get file name FileUploadToServer.SaveAs(Server.MapPath(FilePath) + filename); // save file to uploads folder string filePath = Server.MapPath(FilePath) + filename; string conStr = ""; if (FileExt == ".xls") // check for excel file type { conStr = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString; } else if (FileExt == ".xlsx") { conStr = ConfigurationManager.ConnectionStrings["Excel07ConString"].ConnectionString; } conStr = String.Format(conStr, filePath, "Yes"); OleDbConnection con = new OleDbConnection(conStr); OleDbCommand ExcelCommand = new OleDbCommand(); ExcelCommand.Connection = con; con.Open(); DataTable ExcelDataSet = new DataTable(); ExcelDataSet = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); if (ExcelDataSet != null && ExcelDataSet.Rows.Count > 0) { string SheetName = ExcelDataSet.Rows[0]["TABLE_NAME"].ToString(); // get sheetname ExcelCommand.CommandText = "SELECT * From [" + SheetName + "]"; OleDbDataAdapter ExcelAdapter = new OleDbDataAdapter(ExcelCommand); ExcelAdapter.SelectCommand = ExcelCommand; ExcelAdapter.Fill(dt); } con.Close(); if (dt != null && dt.Rows.Count > 0) // Check if File is Blank or not { GridView1.DataSource = dt; GridView1.DataBind(); ViewState["sdtt"] = dt; } else { } FilePath = ResolveUrl("~/FILES/"); string fileName = Server.MapPath(FilePath) + filename; FileInfo f = new FileInfo(fileName); if (f.Exists) { f.IsReadOnly = false; f.Delete(); } } else { } } } catch (Exception ex) { } } else { } } catch { } }
protected void btnImport_Click(object sender, EventArgs e) { try { string ConStr = ""; //Extantion of the file upload control saving into ext because //there are two types of extation .xls and .xlsx of Excel string ext = Path.GetExtension(FileUploadToServer.FileName).ToLower(); //getting the path of the file string path = Server.MapPath("~/Userfiles/" + FileUploadToServer.FileName); //saving the file inside the MyFolder of the server FileUploadToServer.SaveAs(path); lblMessage.Text = FileUploadToServer.FileName + "\'s Data showing into the GridView"; //checking that extantion is .xls or .xlsx if (ext.Trim() == ".xls") { //connection string for that file which extantion is .xls ConStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\""; } else if (ext.Trim() == ".xlsx") { //connection string for that file which extantion is .xlsx ConStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\""; } //making query OleDbConnection con = null; con = new OleDbConnection(ConStr); con.Close(); con.Open(); DataTable dtquery = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); //Get first sheet name string getExcelSheetName = dtquery.Rows[0]["Table_Name"].ToString(); //string query = "SELECT * FROM [Total Deduction$]"; //Providing connection OleDbConnection conn = new OleDbConnection(ConStr); //checking that connection state is closed or not if closed the //open the connection if (conn.State == ConnectionState.Closed) { conn.Open(); } //create command object OleDbCommand cmd = new OleDbCommand(@"SELECT * FROM [" + getExcelSheetName + @"]", conn); // create a data adapter and get the data into dataadapter OleDbDataAdapter da = new OleDbDataAdapter(cmd); //DataSet ds = new DataSet(); DataTable dt = new DataTable(); //fill the Excel data to data set da.Fill(dt); //set data source of the grid view for (int i = dt.Rows.Count - 1; i >= 0; i--) { if (dt.Rows[i][1] == DBNull.Value) { dt.Rows[i].Delete(); } } dt.AcceptChanges(); grvExcelData.DataSource = dt; //binding the gridview grvExcelData.DataBind(); Session["dtImport"] = dt; //close the connection conn.Close(); } catch (Exception ex) { lblMessage.Text = ex.Message.ToString(); } }
protected void btnImport_Click(object sender, EventArgs e) { try { string FilePath = ConfigurationManager.AppSettings["FilePath"].ToString(); string filename = string.Empty; //To check whether file is selected or not to uplaod if (FileUploadToServer.HasFile) { try { string[] allowdFile = { ".xls", ".xlsx" }; //Here we are allowing only excel file so verifying selected file pdf or not string FileExt = System.IO.Path.GetExtension(FileUploadToServer.PostedFile.FileName); //Check whether selected file is valid extension or not bool isValidFile = allowdFile.Contains(FileExt); if (!isValidFile) { lblMessage.ForeColor = System.Drawing.Color.Red; lblMessage.Text = "Please upload only Excel"; } else { // Get size of uploaded file, here restricting size of file int FileSize = FileUploadToServer.PostedFile.ContentLength; if (FileSize <= 1048576)//1048576 byte = 1MB { //Get file name of selected file filename = Path.GetFileName(Server.MapPath(FileUploadToServer.FileName)); //Save selected file into server location FileUploadToServer.SaveAs(Server.MapPath(FilePath) + filename); //Get file path string filePath = Server.MapPath(FilePath) + filename; //Open the connection with excel file based on excel version OleDbConnection con = null; if (FileExt == ".xls") { con = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties=Excel 8.0;"); } else if (FileExt == ".xlsx") { con = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties=Excel 12.0;"); } con.Close(); con.Open(); //Get the list of sheet available in excel sheet DataTable dt = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); //Get first sheet name string getExcelSheetName = dt.Rows[0]["Table_Name"].ToString(); //Select rows from first sheet in excel sheet and fill into dataset "SELECT * FROM [Sheet1$]"; OleDbCommand ExcelCommand = new OleDbCommand(@"SELECT * FROM [" + getExcelSheetName + @"]", con); OleDbDataAdapter ExcelAdapter = new OleDbDataAdapter(ExcelCommand); DataSet ExcelDataSet = new DataSet(); ExcelAdapter.Fill(ExcelDataSet); //Bind the dataset into gridview to display excel contents grvExcelData.DataSource = ExcelDataSet; grvExcelData.DataBind(); Session["dtImport"] = ExcelDataSet.Tables[0]; btnsave.Visible = true; } else { lblMessage.Text = "Attachment file size should not be greater then 1 MB!"; } } } catch (Exception ex) { lblMessage.Text = "Error occurred while uploading a file: " + ex.Message; } } else { lblMessage.Text = "Please select a file to upload."; } } catch (Exception ex) { lblMessage.Text = ex.ToString(); lblMessage.Visible = true; } }
protected void btnUpload_Click(object sender, EventArgs e) { string sheet_name = ""; int sheet_num; int dLastNum; int cate_items = 0; String Cate_Username = "******"; String Cate_Status = "Y"; DateTime dt = DateTime.Now; string inf_ID = ""; List <string> List_Inf_ID = new List <string>(); Boolean sign_count = false; /*List<string> Cate_Iiitems = new List<string>(); * List<string> Cate_SpeChar = new List<string>(); * List<string> Cate_Md = new List<string>(); * List<string> Cate_Cate = new List<string>(); * List<string> Cate_KP = new List<string>(); */ List <int> success_insert = new List <int>(); List <int> fail_insert = new List <int>(); int success_count = 0; int fail_count = 0; List <string> history_cate = new List <string>(); string fileName = ""; string time = ""; string count_sheet_name = ""; int debug_count = 0; Boolean sign = false; Boolean sign_repeat = false; clsMySQL db = new clsMySQL(); try { fileName = Path.GetFileName(FileUploadToServer.PostedFile.FileName); //string filePath = Server.MapPath("~\\bruno\\FileUpload_Folder\\") + Path.GetFileName(FileUploadToServer.PostedFile.FileName); string filePath = Server.MapPath("FileUpload_Folder\\") + Path.GetFileName(FileUploadToServer.PostedFile.FileName); FileUploadToServer.SaveAs(filePath); if (CheckExcelFile(fileName)) { /* * if (FileUploadToServer.HasFile) * { * * } */ XSSFWorkbook wk = new XSSFWorkbook(FileUploadToServer.FileContent); XSSFSheet hst; XSSFRow hr; DataTable myDT = new DataTable(); sheet_num = wk.NumberOfSheets; for (int k = 0; k < sheet_num; k++) //從每張工作表開始做 { hst = (XSSFSheet)wk.GetSheetAt(k); cate_items = hst.LastRowNum; //每一張工作表有幾筆資料 sheet_name = hst.SheetName; if (k != sheet_num - 1) //顯示匯入的Stage有哪些 { count_sheet_name += sheet_name + ","; } else { count_sheet_name += sheet_name; } hr = (XSSFRow)hst.GetRow(0); dLastNum = hr.LastCellNum; //每一列的欄位數 /* for (int i = hr.FirstCellNum; i < hr.LastCellNum; i++) * { * if (hr.GetCell(i) != null) * { * DataColumn myColumn = new DataColumn(hr.GetCell(i).StringCellValue); * myDT.Columns.Add(myColumn); * * } * * }*/ for (int j = 1; j <= cate_items; j++) { hr = (XSSFRow)hst.GetRow(j); //XSSFRow row = (XSSFRow) mySheet.GetRow(i); DataRow myrow = myDT.NewRow(); /*for(int i=1;i<dLastNum;i++) * { * //string strcell = hr.GetCell(i) == null ? "0" : hr.GetCell(i).ToString(); * myrow = * * }*/ String insert_cate = string.Format("insert into npi_ep_category" + "(npi_EP_Cate_Username,npi_EP_Cate_UpdateTime,npi_EP_Cate_Status," + "EP_Cate_Stage,EP_Cate_Iiitems,EP_Cate_SpeChar," + "EP_Cate_Md,EP_Cate_Cate,EP_Cate_KP)values" + "('{0}',NOW(),'{1}'," + "'{2}','{3}','{4}','{5}','{6}','{7}')" , Cate_Username, Cate_Status, sheet_name, hr.GetCell(0), hr.GetCell(1), hr.GetCell(2), hr.GetCell(3), hr.GetCell(4)); string excel_sql = sheet_name + "|" + hr.GetCell(0) + "|" + hr.GetCell(1) + "|" + hr.GetCell(2) + "|" + hr.GetCell(3) + "|" + hr.GetCell(4); Label1.Text = dt.ToString("yyyy/MM/dd hh:mm:ss"); time = Label1.Text; if (jude_npiepcategory_data(excel_sql, sheet_name) == true) { if (db.QueryExecuteNonQuery(insert_cate) == true) { success_count++; sign = true; } else { fail_count++; history_cate.Add(hr.GetCell(0) + "|" + hr.GetCell(1) + "|" + hr.GetCell(2) + "|" + hr.GetCell(3) + "|" + hr.GetCell(4)); } debug_count++; } else { List_Inf_ID.Add(Session["value_ID"].ToString()); sign_repeat = true; sign_count = true; } Session.Clear(); debug_count = 0; } } wk = null; hst = null; hr = null; } else { lblMsg.ForeColor = System.Drawing.Color.Red; lblMsg.Text = "您選擇的[" + fileName + "]無法匯入,請重新選擇Excel檔案或檢查Excel檔案內容!!"; } /*string strScript2 = string.Format("<script language='javascript'>alert('完成匯入\\n成功匯入筆數:" + success_count + "\\n匯入的Stage:" + count_sheet_name + "');</script>"); * Page.ClientScript.RegisterStartupScript(this.GetType(), "onload", strScript2);*/ Lab_item.Text = success_count.ToString(); Lab_Stage.Text = count_sheet_name; lblMsg.Text = fileName + "上傳成功!!"; System.IO.File.Delete(filePath); if (sign_count == true) { for (int i = 0; i < List_Inf_ID.Count; i++) { inf_ID += List_Inf_ID[i]; } string[] spilit_inf_id = inf_ID.Split(','); string strScript2 = string.Format("<script language='javascript'>alert('重複資料筆數:" + List_Inf_ID.Count.ToString() + "\\n資料重複編號:" + str_id(spilit_inf_id) + "');</script>"); Page.ClientScript.RegisterStartupScript(this.GetType(), "onload", strScript2); } } catch (Exception exfile) { lblMsg.ForeColor = System.Drawing.Color.Red; excp.Text = exfile.ToString(); if (fileName == "") { lblMsg.Text = "[Import Error Message] 請選擇要匯入的Excel檔案!!"; } else { lblMsg.Text = "[Import Error Message]您選擇的[" + fileName + "]無法匯入,請重新選擇Excel檔案或檢查Excel檔案內容!!"; } } finally { /*string strScript = string.Format("<script language='javascript'>alert('您沒有輸入Packge_Name!');</script>"); * Page.ClientScript.RegisterStartupScript(this.GetType(), "onload", strScript);*/ } string sql_cate_datetime = "select * from npi_ep_category where npi_ep_category.npi_EP_Cate_UpdateTime >= DATE_FORMAT('" + time + "', '%Y/%m/%d %H:%i:%s')"; //DATE_FORMAT('2016/04/28 11:26:00', '%Y/%m/%d %H:%i:%s') if (sign == true) { clsMySQL ds = new clsMySQL(); clsMySQL.DBReply dr = ds.QueryDS(sql_cate_datetime); gvRecord.DataSource = dr.dsDataSet.Tables[0].DefaultView; gvRecord.DataBind(); ds.Close(); } /*if(sign==true) * { * clsMySQL ds = new clsMySQL(); * clsMySQL.DBReply dr = ds.QueryDS(sql_cate_datetime); * gvRecord2.DataSource = dr.dsDataSet.Tables[0].DefaultView; * gvRecord2.DataBind(); * ds.Close(); * }*/ }
protected void btnUpload_Click(object sender, EventArgs e) { string Schoolid = Session["SchoolId"].ToString(); string directoryPath = Server.MapPath(string.Format("~/ExcelFile/{0}/", Schoolid.Trim())); if (!Directory.Exists(directoryPath)) { Directory.CreateDirectory(directoryPath); } //Get path from web.config file to upload string FilePath = string.Format("~/ExcelFile/{0}/", Schoolid.Trim());//ConfigurationManager.AppSettings["FilePath"].ToString(); string filename = string.Empty; //To check whether file is selected or not to uplaod if (FileUploadToServer.HasFile) { try { string[] allowdFile = { ".xls" };//, ".xlsx" }; //Here we are allowing only excel file so verifying selected file pdf or not string FileExt = System.IO.Path.GetExtension(FileUploadToServer.PostedFile.FileName); //Check whether selected file is valid extension or not bool isValidFile = allowdFile.Contains(FileExt); if (!isValidFile) { divsucrss.Style.Add("display", "none"); divUnsucrss.Style.Remove("display"); ltrNotSucess.Text = "Please upload only Excel"; } else { // Get size of uploaded file, here restricting size of file int FileSize = FileUploadToServer.PostedFile.ContentLength; if (FileSize <= 3048576)//1048576 byte = 1MB { //Get file name of selected file filename = Path.GetFileName(Server.MapPath(FileUploadToServer.FileName)); //Save selected file into server location FileUploadToServer.SaveAs(Server.MapPath(FilePath) + filename); //Get file path string filePath = Server.MapPath(FilePath) + filename; //Open the connection with excel file based on excel version OleDbConnection con = null; if (FileExt == ".xls") { con = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties=Excel 8.0;"); } else if (FileExt == ".xlsx") { // con = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties=Excel 12.0;"); } con.Open(); //Get the list of sheet available in excel sheet DataTable dt = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); //Get first sheet name string getExcelSheetName = dt.Rows[0]["Table_Name"].ToString(); //Select rows from first sheet in excel sheet and fill into dataset OleDbCommand ExcelCommand = new OleDbCommand(@"SELECT * FROM [" + getExcelSheetName + @"]", con); OleDbDataAdapter ExcelAdapter = new OleDbDataAdapter(ExcelCommand); DataSet ExcelDataSet = new DataSet(); ExcelAdapter.Fill(ExcelDataSet); con.Close(); // var aa=ExcelDataSet.Tables[0].Rows. try { string Filds = clsStdDetail.GetSchoolFild(Schoolid).Select(x => x.MyIdFild).FirstOrDefault().ToString(); var lstFild = Filds; //if (lstFild.Contains("[StudentPhoto]")) // dtxtStudentPhoto.Style.Remove("display"); //if (lstFild.Contains("[Status]")) // dtxtStatus.Style.Remove("display"); for (int i = 0; i < ExcelDataSet.Tables[0].Rows.Count; i++) { objStd.SchoolUID = Schoolid; if (lstFild.Contains("[ScholarNo]")) { objStd.ScholarNo = ExcelDataSet.Tables[0].Rows[i]["ScholarNo"].ToString(); } else { objStd.ScholarNo = ""; } if (lstFild.Contains("[StudentName]")) { objStd.StudentName = ExcelDataSet.Tables[0].Rows[i]["StudentName"].ToString(); } else { objStd.StudentName = ""; } if (lstFild.Contains("[FatherName]")) { objStd.FatherName = ExcelDataSet.Tables[0].Rows[i]["FatherName"].ToString(); } else { objStd.FatherName = ""; } if (lstFild.Contains("[MotherName]")) { objStd.MotherName = ExcelDataSet.Tables[0].Rows[i]["MotherName"].ToString(); } else { objStd.MotherName = ""; } if (lstFild.Contains("[BloodGroup]")) { objStd.BloodGroup = ExcelDataSet.Tables[0].Rows[i]["BloodGroup"].ToString(); } else { objStd.BloodGroup = ""; } if (lstFild.Contains("[PhoneNumber]")) { objStd.PhoneNumber = ExcelDataSet.Tables[0].Rows[i]["PhoneNumber"].ToString(); } else { objStd.PhoneNumber = ""; } if (lstFild.Contains("[DateofBarth]")) { try { objStd.DateofBarth = Convert.ToDateTime(ExcelDataSet.Tables[0].Rows[i]["DateofBirth"]).ToString("dd/MM/yyyy"); } catch { objStd.DateofBarth = ""; ltrupdaterecorddetail.Text += " <spain style='color:red'>" + ExcelDataSet.Tables[0].Rows[i]["ScholarNo"].ToString() + "</spain>"; } } else { objStd.DateofBarth = ""; } if (lstFild.Contains("[Class]")) { objStd.Class = ExcelDataSet.Tables[0].Rows[i]["Class"].ToString(); } else { objStd.Class = ""; } if (lstFild.Contains("[EmailId]")) { objStd.EmailId = ExcelDataSet.Tables[0].Rows[i]["EmailId"].ToString(); } else { objStd.EmailId = ""; } if (lstFild.Contains("[Addres]")) { objStd.Addres = ExcelDataSet.Tables[0].Rows[i]["ADDRESS"].ToString(); } else { objStd.Addres = ""; } if (lstFild.Contains("[F2]")) { objStd.F2 = ExcelDataSet.Tables[0].Rows[i]["OTHER1"].ToString(); } else { objStd.F2 = ""; } if (lstFild.Contains("[F3]")) { objStd.F3 = ExcelDataSet.Tables[0].Rows[i]["OTHER2"].ToString(); } else { objStd.F3 = ""; } if (lstFild.Contains("[F4]")) { objStd.F4 = ExcelDataSet.Tables[0].Rows[i]["OTHER3"].ToString(); } else { objStd.F4 = ""; } if (lstFild.Contains("[StudentPhoto]")) { string pto = ExcelDataSet.Tables[0].Rows[i]["StudentPhoto"].ToString(); objStd.StudentPhoto = "StdPhoto/" + Schoolid + "/" + (pto.Contains(".") ? pto : pto + ".jpg"); } else { objStd.StudentPhoto = ""; } objStd.F1 = ExcelDataSet.Tables[0].Rows[i]["TITLE"].ToString(); objStd.LastUpdated = DateTime.Now; objStd.IsActive = 1; objStd.Status = 0; //objStd.StudentPhoto= //img int isExist = clsStdDetail.GetStudentDetail(Schoolid, objStd.ScholarNo).Count; if (isExist > 0) { objStd.ScholarNo_Old = ExcelDataSet.Tables[0].Rows[i]["ScholarNo"].ToString(); string retCode = clsStdDetail.UpdateStudetDetail(objStd); if (retCode != "1") { divsucrss.Style.Add("display", "none"); divUnsucrss.Style.Remove("display"); ltrNotSucess.Text = retCode + "There are some error contact to admin !!"; } else { divUnsucrss.Style.Add("display", "none"); divsucrss.Style.Remove("display"); ltrSucess.Text = "Data Inserted Sucess "; divupdatedrecord.Style.Remove("display"); ltrupdaterecorddetail.Text += objStd.ScholarNo_Old + " "; } } else { string retCode = clsStdDetail.InsertStudetDetail(objStd); if (retCode != "1") { divsucrss.Style.Add("display", "none"); divUnsucrss.Style.Remove("display"); ltrNotSucess.Text = retCode + "There are some error contact to admin !!"; } else { divUnsucrss.Style.Add("display", "none"); divsucrss.Style.Remove("display"); ltrSucess.Text = "Data Inserted Sucess "; } } } GridView1.DataSource = ExcelDataSet; GridView1.DataBind(); } catch (Exception s) { divsucrss.Style.Add("display", "none"); divUnsucrss.Style.Remove("display"); ltrNotSucess.Text = s.Message + "There are some error contact to admin !!"; } } else { divsucrss.Style.Add("display", "none"); divUnsucrss.Style.Remove("display"); ltrNotSucess.Text = "Attachment file size should not be greater then 1 MB !"; } } } catch (Exception ex) { divsucrss.Style.Add("display", "none"); divUnsucrss.Style.Remove("display"); ltrNotSucess.Text = "Error occurred while uploading a file: " + ex.Message; } } else { divsucrss.Style.Add("display", "none"); divUnsucrss.Style.Remove("display"); ltrNotSucess.Text = "Please select a file to upload."; } }
protected void btnUpload_Click(object sender, EventArgs e) { String SavePath = "D:\\brunohuang\\FileUpload_Folder\\"; string sheet_name = ""; int sheet_num; int dLastNum; int cate_items = 0; String Cate_Username = "******"; String Cate_Status = "Y"; /*List<string> Cate_Iiitems = new List<string>(); * List<string> Cate_SpeChar = new List<string>(); * List<string> Cate_Md = new List<string>(); * List<string> Cate_Cate = new List<string>(); * List<string> Cate_KP = new List<string>(); */ List <int> success_insert = new List <int>(); List <int> fail_insert = new List <int>(); int success_count = 0; int fail_count = 0; List <string> history_cate = new List <string>(); string fileName = ""; clsMySQL db = new clsMySQL(); try { fileName = Path.GetFileName(FileUploadToServer.PostedFile.FileName); string filePath = Server.MapPath("D:\\brunohuang\\FileUpload_Folder\\") + Path.GetFileName(FileUploadToServer.PostedFile.FileName); FileUploadToServer.SaveAs(filePath); lblMsg.Text = "上傳成功!!" + SavePath; if (!CheckExcelFile(fileName)) { if (FileUploadToServer.HasFile) { } XSSFWorkbook wk = new XSSFWorkbook(FileUploadToServer.FileContent); XSSFSheet hst; XSSFRow hr; sheet_num = wk.NumberOfSheets; for (int k = 0; k < sheet_num; k++) { hst = (XSSFSheet)wk.GetSheetAt(k); cate_items = hst.LastRowNum; //每一張工作表有幾筆資料 sheet_name = hst.SheetName; hr = (XSSFRow)hst.GetRow(0); dLastNum = hr.LastCellNum; //每一列的欄位數 for (int j = 1; j <= cate_items; j++) { hr = (XSSFRow)hst.GetRow(j); /*for(int i=0;i<dLastNum;i++) * { * string strcell = hr.GetCell(i) == null ? "0" : hr.GetCell(i).ToString(); * * }*/ String insert_cate = string.Format("insert into npi_ep_category" + "(npi_EP_Cate_Username,npi_EP_Cate_UpdateTime,npi_EP_Cate_Status," + "EP_Cate_Stage,EP_Cate_Iiitems,EP_Cate_SpeChar," + "EP_Cate_Md,EP_Cate_Cate,EP_Cate_KP)values" + "('{0}',NOW(),'{1}'," + "'{2}','{3}','{4}','{5}','{6}','{7}')" , Cate_Username, Cate_Status, sheet_name, hr.GetCell(0), hr.GetCell(1), hr.GetCell(2), hr.GetCell(3), hr.GetCell(4)); if (db.QueryExecuteNonQuery(insert_cate) == true) { success_count++; } else { fail_count++; history_cate.Add(hr.GetCell(0) + "|" + hr.GetCell(1) + "|" + hr.GetCell(2) + "|" + hr.GetCell(3) + "|" + hr.GetCell(4)); } } } } else { lblMsg.ForeColor = System.Drawing.Color.Red; lblMsg.Text = "您選擇的[" + fileName + "]無法匯入,請重新選擇Excel檔案或檢查Excel檔案內容!!"; } } catch (Exception exfile) { lblMsg.ForeColor = System.Drawing.Color.Red; if (fileName == "") { lblMsg.Text = "[Import Error Message] 請選擇要匯入的Excel檔案!!"; } else { lblMsg.Text = "[Import Error Message]您選擇的[" + fileName + "]無法匯入,請重新選擇Excel檔案或檢查Excel檔案內容!!"; } } }
protected void btnUpload_Click(object sender, EventArgs e) { string FilePath = ConfigurationManager.AppSettings["FilePath"].ToString(); string filename = string.Empty; if (FileUploadToServer.HasFile) { try { string[] allowedFile = { ".xls", ".xlsx" }; string FileExt = System.IO.Path.GetExtension(FileUploadToServer.PostedFile.FileName); bool isValidFile = allowedFile.Contains(FileExt); if (!isValidFile) { lblMsg.ForeColor = System.Drawing.Color.Red; lblMsg.Text = "Please upload only Excel format file."; } else { int FileSize = FileUploadToServer.PostedFile.ContentLength; if (FileSize <= 5242880) //5242880 bytes = 5MB { filename = Path.GetFileName(Server.MapPath(FileUploadToServer.FileName)); FileUploadToServer.SaveAs(Server.MapPath(FilePath) + filename); string filePath = Server.MapPath(FilePath) + filename; OleDbConnection con = null; if (FileExt == ".xls") { con = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties=Excel 8.0;"); } else if (FileExt == ".xlsx") { con = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties=Excel 12.0;"); } con.Open(); DataTable dt = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); string getExcelSheetName = dt.Rows[0]["Table_Name"].ToString(); OleDbCommand ExcelCommand = new OleDbCommand(@"SELECT F1,F2,F3 FROM [" + getExcelSheetName + @"]", con); OleDbDataAdapter ExcelAdapter = new OleDbDataAdapter(ExcelCommand); DataSet ExcelDataSet = new DataSet(); ExcelAdapter.Fill(ExcelDataSet); con.Close(); GridView1.DataSource = ExcelDataSet; GridView1.DataBind(); } else { lblMsg.Text = "Attached file size should not be greater than 5 MB!"; } } } catch (Exception ex) { lblMsg.Text = "Error occured while uploading: " + ex.Message; } } else { lblMsg.Text = "Please select a file to upload."; } }