private DataTable load_data_from_file_to_data_table(string ip_str_path, string ip_name_sheet_import)
{
string conStr = "";
string SheetName = ip_name_sheet_import + "$";
conStr = ConfigurationManager.ConnectionStrings["Excel07ConString"].ConnectionString;
conStr = String.Format(conStr, ip_str_path, "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);
DataTable dt = new DataTable();
if(ExcelDataSet != null && ExcelDataSet.Rows.Count > 0)
{
//string SheetName = ExcelDataSet.Rows[0]["TABLE_NAME"].ToString(); // get sheetname
ExcelCommand.CommandText = "SELECT * From [" + SheetName + "]";// WHERE [" + ExcelHopDong.MA_NHAN_VIEN + "] IS NOT NULL";
OleDbDataAdapter ExcelAdapter = new OleDbDataAdapter(ExcelCommand);
ExcelAdapter.SelectCommand = ExcelCommand;
ExcelAdapter.Fill(dt);
}
con.Close();
DataTable v_dt_result = dt.Rows.Cast<DataRow>().Where(row => !row.ItemArray.All(field => field is System.DBNull || string.IsNullOrEmpty(field.ToString()) == true)).CopyToDataTable();
return v_dt_result;
}