public static DataSet FillDataSetFromExcel(string filePath)
{
try
{
DataSet ds_return = new DataSet();
// Lưu file vao server
//string filePath = Microsoft.SqlServer.Server.MapPath("/Content/FlexcelDesignFile/" + uploadFile.FileName);
//uploadFile.SaveAs(filePath);
// Đọc file Excel ra DataSet
string file_extension = System.IO.Path.GetExtension(filePath);
string connectionString_excel = "";
switch (file_extension.ToUpper())
{
case ".XLS": //Excel 97-03
connectionString_excel = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1;\"");
break;
case ".XLSX": //Excel 07~
connectionString_excel = String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1;\"");
break;
}
OleDbConnection con = new OleDbConnection(connectionString_excel);
con.Open();
// Get lis SheetName in file
List <string> lst_sheetname = new List <string>();
DataTable dt = new DataTable();
dt = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
for (int i = 0; i < dt.Rows.Count; i++)
{
string item = dt.Rows[i]["TABLE_NAME"].ToString().Replace("'", "");
if (item.Contains("$"))
{
lst_sheetname.Add(item);
}
}
// Get data in file
if (lst_sheetname.Count > 0)
{
for (int sheet_index = 0; sheet_index < lst_sheetname.Count; sheet_index++)
{
OleDbDataAdapter cmd = new System.Data.OleDb.OleDbDataAdapter("select * from [" + lst_sheetname[sheet_index].ToString() + "]", con);
DataTable dt_data = new DataTable();
cmd.Fill(dt_data);
ds_return.Tables.Add(dt_data);
}
}
con.Close();
// Return DataSet
return(ds_return);
}
catch (Exception ex)
{
Logger.LogException(ex);
return(new DataSet());
}
}
/// <summary>
/// truyền vào ngày tháng trả ra thứ mấy
/// </summary>
/// <param name="pDate"></param>
/// <returns></returns>
public static string GetDayOfWeek(DateTime pDate, string pLanguage)
{
try
{
string pStr = "";
int day = Convert.ToInt32(pDate.DayOfWeek);
if (!string.IsNullOrEmpty(pLanguage))
{
if (pLanguage.ToUpper() == "VI")
{
if (day == 1)
{
pStr = "Thứ hai";
}
else if (day == 2)
{
pStr = "Thứ ba";
}
else if (day == 3)
{
pStr = "Thứ tư";
}
else if (day == 4)
{
pStr = "Thứ năm";
}
else if (day == 5)
{
pStr = "Thứ sáu";
}
else if (day == 6)
{
pStr = "Thứ bẩy";
}
else if (day == 0)
{
pStr = "Chủ nhật";
}
}
else
{
if (day == 1)
{
pStr = "Monday";
}
else if (day == 2)
{
pStr = "Tuesday";
}
else if (day == 3)
{
pStr = "Wednesday";
}
else if (day == 4)
{
pStr = "Thursday";
}
else if (day == 5)
{
pStr = "Friday";
}
else if (day == 6)
{
pStr = "Saturday";
}
else if (day == 0)
{
pStr = "Sunday";
}
}
}
else
{
if (day == 1)
{
pStr = "Monday";
}
else if (day == 2)
{
pStr = "Tuesday";
}
else if (day == 3)
{
pStr = "Wednesday";
}
else if (day == 4)
{
pStr = "Thursday";
}
else if (day == 5)
{
pStr = "Friday";
}
else if (day == 6)
{
pStr = "Saturday";
}
else if (day == 0)
{
pStr = "Sunday";
}
}
return(pStr);
}
catch (Exception ex)
{
Logger.LogException(ex);
return("");
}
}
