bool kiemtra = false;//bien kiem tra xem phuong an co gia tri hay khong. public DataTable MySheet(string name) { DataTable dt = new DataTable(); OleDbConnection myConnection = new OleDbConnection(); try { var path = Path.Combine(Directory.GetCurrentDirectory(), name); // name="~/Files/somefiles/MauNhapLieu_20160808_081958.xlsx"; if (name[name.Length - 1] == 'x') { //myConnection = new OleDbConnection( // "Provider=Microsoft.ACE.OLEDB.12.0; " + // "data source='" + path + "';" + // "Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1\" "); myConnection = new OleDbConnection(string.Format("Provider=Microsoft.ACE.OLEDB.12.0; Data Source={0}; Extended Properties=Excel 8.0;", name)); } else if (name[name.Length - 1] == 's') { // chuỗi connection mở file myConnection = new OleDbConnection(string.Format("Provider=Microsoft.Jet.OLEDB.4.0; data source={0}; Extended Properties=Excel 8.0;", name)); //"provider=Microsoft.Jet.OLEDB.4.0; data source={0};HDR=Yes;IMEX=1;ReadOnly:=false;UpdateLinks:=0\" " } else { return(dt); } myConnection.Open(); DataTable mySheets = myConnection.GetOleDbSchemaTable( OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" } ); //mySheets.Columns.Remove() //dt = mySheets.Columns["TABLE_NAME"].Table; dt.Columns.Add("Sheet", typeof(string)); //đọc trong file có bao nhiêu sheet cho vào datatable foreach (DataRow cot in mySheets.Rows) { DataRow dr = dt.NewRow(); dr["Sheet"] = cot["TABLE_NAME"]; dt.Rows.Add(dr); } } catch (Exception ex) { LogDb.WriteLogError("HieuTM", "MySheet", "TuVanController", ex); } return(dt); }
/// <summary> /// HieuTM: Upload file, phân tích trả về mẫu dữ liệu. /// </summary> /// <returns></returns> public PartialViewResult UploadExcel() { LogDb.WriteLogInfo("HieuTM", "UploadExcel", "TuVanController", "Vao Upload"); BanTinTuVan bttv = null; try { LogDb.WriteLogInfo("HieuTM", "UploadExcel", "TuVanController", "file count: " + Request.Files.Count.ToString()); string linkSaveServer = ""; for (int i = 0; i < Request.Files.Count; i++) { string directoryPath = Path.Combine((Server.MapPath("~/Files/tuvan/"))); var file = Request.Files[i]; string filename = file.FileName; if (!System.IO.Directory.Exists(directoryPath)) { System.IO.Directory.CreateDirectory(directoryPath); } string newname = Lib.Security.CurrentUserName + "_" + DateTime.Now.ToString("yyyyMMdd_HHmmss") + "_" + Path.GetFileName(file.FileName); linkSaveServer = Path.Combine(Server.MapPath("~/Files/tuvan/"), newname); ViewBag.LinkFile = linkSaveServer; file.SaveAs(linkSaveServer); ReadExcel excel = new ReadExcel(); List <DataTable> table = excel.makeDataTableFromSheetName(linkSaveServer, excel.MySheet(linkSaveServer)); bttv = table.Count == 0 ? null : excel.BanTinTuVanExcel(table); if (bttv != null) { bttv.Link = @"Files/tuvan/" + newname; } } } catch (Exception ex) { bttv = null; LogDb.WriteLogInfo("HieuTM", "UploadExcel", "TuVanController", "Error: " + ex.Message); LogDb.WriteLogError("HieuTM", "UploadExcel", "TuVanController", ex); } return(PartialView("TuVan_All_Par", bttv)); }
public List <DataTable> makeDataTableFromSheetName(string filename, DataTable mysheet) { DataTable dtImport; List <DataTable> dsImport = new List <DataTable>(); string connectionString; string sheetName; if (filename[filename.Length - 1] == 's') { connectionString = string.Format("provider=Microsoft.Jet.OLEDB.4.0; data source={0};Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1;ReadOnly:=false;UpdateLinks:=0\"", filename); } else if (filename[filename.Length - 1] == 'x') { connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filename + ";" + "Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1\" "; } else { return(dsImport); } try { System.Data.OleDb.OleDbConnection myConnection = new System.Data.OleDb.OleDbConnection(connectionString); DataSet ds; foreach (DataRow sheetName1 in mysheet.Rows) { sheetName = sheetName1[0].ToString(); OleDbDataAdapter myImportCommand = new OleDbDataAdapter("select * from [" + sheetName + "]", myConnection); ds = new DataSet(); myImportCommand.Fill(ds); dtImport = new DataTable(); dtImport = ds.Tables[0]; dtImport.TableName = sheetName; dsImport.Add(dtImport); } } catch (Exception ex) { LogDb.WriteLogError("HieuTM", "makeDataTableFromSheetName", "TuVanController", ex); } return(dsImport); }
/// <summary> /// HieuTM: Kiểm tra tài khoản bên TLVN /// </summary> /// <param name="user"></param> /// <param name="pass"></param> /// <returns></returns> public UserTLVN CheckLogin(string user, string pass) { UserTLVN result = null; try { #region Gọi service check đăng nhập bên TLVN string url = linkServer + "Home/AcountLoginVHLH"; var request = (HttpWebRequest)WebRequest.Create(url); string postData = String.Format("userName={0}&passWord={1}", user, pass); var data = Encoding.ASCII.GetBytes(postData); request.Method = "POST"; request.ContentType = "application/x-www-form-urlencoded"; request.ContentLength = data.Length; using (var stream = request.GetRequestStream()) { stream.Write(data, 0, data.Length); } var response = (HttpWebResponse)request.GetResponse(); var responseString = new StreamReader(response.GetResponseStream()).ReadToEnd(); #endregion #region Code cũ //// Truyền tham số cho reqest. //// Khởi tạo request goi API //WebRequest req = WebRequest.Create(url); //// req.Credentials = CredentialCache.DefaultCredentials; //req.ContentType = "application/x-www-form-urlencoded"; //// Thiết lập phương thức cho request. //req.Method = "Post"; ////byte[] byteArray = Encoding.UTF8.GetBytes(postData); ////req.ContentLength = byteArray.Length; ////Stream dataStream = req.GetRequestStream(); ////dataStream.Write(byteArray, 0, byteArray.Length); ////dataStream.Close(); //// Nhận response. //WebResponse response = req.GetResponse(); //Stream dataStream = response.GetResponseStream(); //StreamReader reader = new StreamReader(dataStream); //string responseFromServer = reader.ReadToEnd(); //string str = responseFromServer; #endregion if (responseString == "-1") { result = new UserTLVN(); result.UserId = -1; result.GroupRef = "b9bacf66-2439-454d-868c-23d3b04c420e"; result.UserName = "******"; result.FullName = "Chua Xac Dinh"; result.RoleRef = "asd"; result.IsAdminBitType = true; } else { UserTLVN myDeserializedObjList = (UserTLVN)Newtonsoft.Json.JsonConvert.DeserializeObject(responseString, typeof(UserTLVN)); result = myDeserializedObjList; } LogDb.WriteLogInfo("hieutm", "CheckLogin", "commonDataBase", "Đăng nhập thành công"); } catch (Exception ex) { LogDb.WriteLogError("hieutm", "CheckLogin", "commonDataBase", ex); Log.WriteErrorLocation(ex.Message); } return(result); }
public BanTinTuVan BanTinTuVanExcel(List <DataTable> dsImport) { BanTinTuVan bantintuvan = new BanTinTuVan(); List <PhuongAnExcel> lstphuonganexcel = new List <PhuongAnExcel>(); PhuongAnExcel phuonganexcel = new PhuongAnExcel();; List <DienBien_Models> dienbienexcel = new List <DienBien_Models>(); LogDb.WriteLogInfo("HieuTM", "BanTinTuVanExcel", "ReadExcel", "Vao ban tin tu van"); try { foreach (DataTable table in dsImport) { if (table.TableName == "Chung$") { table.Columns[0].ColumnName = "F1"; table.Columns[4].ColumnName = "F5"; string thoigian = ""; thoigian = table.Rows[3]["F5"].ToString(); string[] ngay = { "ngày", "tháng", "năm", ",", " ", "/" }; int b = -1; string date = ""; foreach (string a in thoigian.Split(ngay, StringSplitOptions.RemoveEmptyEntries)) { try { b = int.Parse(a); date = date + a + "/"; } catch { } } b = date.LastIndexOf("/"); date = date.Substring(0, b); bantintuvan.ThoiGian = DateTime.ParseExact(date, "dd/MM/yyyy", CultureInfo.InvariantCulture); //bantintuvan.NhanXetTinhHinh = table.Rows[11]["F1"].ToString(); //bantintuvan.DuKien = table.Rows[14]["F1"].ToString(); List <string> lstDuKien = DuKienDienBien(table, 1, 11); bantintuvan.NhanXetTinhHinh = lstDuKien[0]; bantintuvan.DuKien = lstDuKien[1]; dienbienexcel = DienBien_Excel(table, 1, 21); if (kiemtra == true) { bantintuvan.DienBienExcel = dienbienexcel; bantintuvan.HienTrangExcel = HienTrangExcel(table, 1, 30); } } else { table.Columns[0].ColumnName = "F1"; phuonganexcel = Phuongan_Dettail_excel(table, 1, 3); //kiem tra neu kiemtra = true bang phuong an co gia tri them vao lst phuong an excel if (kiemtra == true) { lstphuonganexcel.Add(phuonganexcel); } } } bantintuvan.PhuongAnExcel = lstphuonganexcel; LogDb.WriteLogInfo("HieuTM", "BanTinTuVanExcel", "ReadExcel", "Doc xong file excel"); } catch (Exception ex) { bantintuvan = null; LogDb.WriteLogInfo("HieuTM", "BanTinTuVanExcel", "ReadExcel", "Error: " + ex.Message); LogDb.WriteLogError("HieuTM", "BanTinTuVanExcel", "ReadExcel", ex); } return(bantintuvan); }