private void GetExcelSheets(string FilePath,string fileName, string Extension, string isHDR)
{
try
{
string conStr = "";
switch (Extension)
{
case ".csv": //Excel 97-03
conStr = ConnectionStringXLS.CsvConString;
break;
case ".xls" : //Excel 97-03
conStr = ConnectionStringXLS.Excel03ConString;
break;
case ".xlsx": //Excel 07
conStr = ConnectionStringXLS.Excel07ConString;
break;
}
//Get the Sheets in Excel WorkBoo
conStr = String.Format(conStr, FilePath, isHDR);
OleDbConnection con = new OleDbConnection(conStr);
if (con.State == System.Data.ConnectionState.Closed)
{
con.Open();
}
DataTable dt = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
string query = "";
if(Extension.Substring(0,4) ==".xls")
query = @"Select TOP 5 * from [" + dt.Rows[0]["TABLE_NAME"] + "]";
else
query = @"Select TOP 5 * from [" + fileName + "]";
OleDbCommand cmd = new OleDbCommand(query, con);
OleDbDataAdapter da = new OleDbDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
da.Dispose();
con.Close();
con.Dispose();
DataTable dataDT = ds.Tables[0];
List<MISLibrary.DAL.EmployeeTracking> emList = new List<MISLibrary.DAL.EmployeeTracking>();
foreach (DataRow row in dataDT.Rows)
{
emList.Add(
new MISLibrary.DAL.EmployeeTracking
{
MSISDN = row[0].ToString(),
Name = row[1].ToString(),
PIN = row[2].ToString(),
Designation = row[3].ToString(),
Location = row[4].ToString(),
FirstTrackDate = Convert.ToDateTime(row[5].ToString()),
FirstTrackTime = row[6].ToString(),
LastTrackDate = Convert.ToDateTime((row[7].ToString())),
LastTrackTime = row[8].ToString(),
Duration = row[9].ToString(),
Hit = Convert.ToInt32(row[10].ToString())
});
}
if (emList.Count > 0)
{
int numberOfAffectedRows = new MISLibrary.HRM.EmployeeManager().Save(emList);
if (numberOfAffectedRows > 0)
{
lblMsg.ForeColor = System.Drawing.Color.Green;
lblMsg.Text = "Uploaded Successfully.";
}
else
{
lblMsg.ForeColor = System.Drawing.Color.Red;
lblMsg.Text = "Not Uploaded!";
}
}
}
catch(Exception ex)
{
lblMsg.Text = ex.Message;
lblMsg.ForeColor = System.Drawing.Color.Red;
}
}
public static class ConnectionStringXLS
{
// public const string CsvConString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='text;HDR={1}'";
public const string CsvConString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='text;HDR={1};IMEX=1'";
public const string Excel03ConString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR={1}'";
public const string Excel07ConString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR={1}'";
}
protected void btnUpload_Click(object sender, EventArgs e)
{
if(FileUpload1.HasFile)
{
string fileName = Path.GetFileName(FileUpload1.PostedFile.FileName);
string extension = Path.GetExtension(FileUpload1.PostedFile.FileName);
string folderPath = Path.GetFullPath(FileUpload1.PostedFile.FileName);
if (extension.Substring(0, 4) != ".xls" && extension.Substring(0, 4) != ".csv")
{
lblMsg.Text = "Only .xls and .xlsx or .csv Files are supported.";
lblMsg.ForeColor = System.Drawing.Color.Red;
return;
}
string appFileName = Server.MapPath("~/AttendanceSheet/"+fileName) ;
FileUpload1.SaveAs(appFileName);
if (extension.Substring(0, 4) == ".csv")
GetExcelSheets(Server.MapPath("~/AttendanceSheet"), fileName ,extension, "Yes");
else
GetExcelSheets(appFileName,fileName,extension,"Yes");
}
}
