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);
}
