.aspx
<form id="form1" runat="server">
<asp:ScriptManager ID="sm" runat="server">
</asp:ScriptManager>
<div>
<asp:FileUpload ID="browseup"
runat="server"
/>
<asp:RequiredFieldValidator
ID="reqbrowseup"
runat="server"
ValidationGroup="vgsub"
ErrorMessage="*"
ControlToValidate="browseup"
ForeColor="Red">
</asp:RequiredFieldValidator>
<asp:Button ID="fileok" runat="server"
Text="Upload To
Gridview" ValidationGroup="vgsub"
OnClick="fileok_Click"
/>
<a href="ExcelFiles/EmptyFormat/Dataformat.xls" target="_blank">Click
here to Download Excel Format</a>
</div>
<div>
<asp:GridView ID="gridexcel"
runat="server"
AutoGenerateColumns="true">
</asp:GridView>
<br />
<asp:TextBox ID="lblmsg" runat="server"
TextMode="MultiLine"
ReadOnly="true"
Width="100%"
Visible="false"
Height="100px"></asp:TextBox>
</div>
</form>
.aspx.cs
protected void Page_Load(object sender, EventArgs
e)
{
}
protected void
fileok_Click(object sender, EventArgs e)
{
ReadExcelData();
}
public void
ReadExcelData()
{
var filename = DateTime.Now.Day.ToString()
+ "_" + DateTime.Now.Month.ToString()
+ "_" + DateTime.Now.Year.ToString()
+ DateTime.Now.Hour.ToString() + DateTime.Now.Minute.ToString() + DateTime.Now.Second.ToString() + DateTime.Now.Millisecond.ToString();
filename += Path.GetFileName(browseup.PostedFile.FileName);
var path = Path.Combine(Server.MapPath("~/ExcelFiles/"), filename);
if (browseup.HasFile)
{
browseup.PostedFile.SaveAs(path);
}
string ext = Path.GetExtension(path);
string strSQL = "SELECT
* FROM [" + GetExcelSheetNames(path, ext) + "]";
OleDbConnection excelConnection = new OleDbConnection(getConnectionString(path,
ext));
excelConnection.Open(); // This code will open
excel file.
OleDbCommand dbCommand = new
OleDbCommand(strSQL, excelConnection);
OleDbDataAdapter dataAdapter = new OleDbDataAdapter(dbCommand);
// create data
table
DataTable dTable = new
DataTable();
dataAdapter.Fill(dTable);
// dispose used objects
dTable.Dispose();
dataAdapter.Dispose();
dbCommand.Dispose();
excelConnection.Close();
excelConnection.Dispose();
ArrayList colName = new
ArrayList();
for (int i = 0; i
< dTable.Columns.Count; i++)
{
string st = dTable.Columns[i].ColumnName;
string sss = st.Substring(1, 1);
//if condition is for not include empty header
columns
if (st.Substring(1, 1) != "0" && st.Substring(1, 1) != "1" && st.Substring(1, 1) != "2" && st.Substring(1, 1) != "3" && st.Substring(1, 1) != "4" && st.Substring(1, 1) != "5" && st.Substring(1, 1) != "6" && st.Substring(1, 1) != "7" && st.Substring(1, 1) != "8" && st.Substring(1, 1) != "9")
{
colName.Add(dTable.Columns[i].ColumnName);
}
}
string[] col_name = (string[])colName.ToArray(typeof(string));
DataTable dt = dTable.DefaultView.ToTable(true, col_name);
int lastrow = dt.Rows.Count - 1;
int emptyrow = 0;
for (int i = 0; i
< col_name.Length; i++)
{
if
(dt.Rows[lastrow][i].ToString().Trim() != string.Empty)
{
emptyrow = 1;
}
}
if (emptyrow == 0)
{
dt.Rows.RemoveAt(dt.Rows.Count - 1);
}
/* Insert Data to list */
string msg = string.Empty;
const string
emailpattern = @"\w+([-+.']\w+)*@\w+([-.]\w+)*\.\w+([-.]\w+)*";
DateTime outdate;
List<excellist>
exli = new List<excellist>();
for (int i = 0; i
< dt.Rows.Count; i++)
{
excellist obj = new excellist();
DataRow dr = dt.Rows[i];
int rownum = dt.Rows.IndexOf(dr) + 2;
try
{
if (dr["EmployeeID"].ToString()
!= null && dr["EmployeeID"].ToString()
!= string.Empty)
{
if (dr["EmployeeName"].ToString()
!= string.Empty && dr["Gender"].ToString() != string.Empty && dr["EmailID"].ToString()
!= string.Empty && dr["DOJ"].ToString() != string.Empty && dr["GroupID"].ToString()
!= string.Empty)
{
if (Regex.IsMatch(dr["EmailID"].ToString(), emailpattern))
{
obj.EMPCOMPANYUSERID = dr["EmployeeID"].ToString();
obj.EMPPASSWORD
= "MyPass" + i;
obj.EMPNAME =
dr["EmployeeName"].ToString();
if (dr["Gender"].ToString()
== "Male" || dr["Gender"].ToString() == "Female")
{
obj.EMPGENDER = dr["Gender"].ToString();
}
else
{
obj.EMPGENDER = "-NA-";
}
if (dr["DOB"].ToString()
== "NA")
{
obj.EMPDOB
= string.Empty;
}
else if (DateTime.TryParse(dr["DOB"].ToString(),
out outdate))
{
obj.EMPDOB
= outdate.ToString("dd-MMM-yyyy");
}
else
{
obj.EMPDOB
= string.Empty;
}
obj.EMPMAILID =
dr["EmailID"].ToString();
if (DateTime.TryParse(dr["DOJ"].ToString(), out outdate))
{
obj.EMPDOJ
= outdate.ToString("dd-MMM-yyyy");
}
else
{
obj.EMPDOJ = string.Empty;
}
if (CheckIsNumber(dr["GroupID"].ToString()))
{
obj.EMPGROUPID = dr["GroupID"].ToString();
}
else
{
obj.EMPGROUPID = "0";
}
exli.Add(obj);
}
else
{
msg += "==> Erro at row number" + rownum + ": Please provide valid email id for Employee ID
:" + dr["EmployeeID"].ToString()
+ " <== ";
}
}
else
{
msg += "==> Erro at row number" + rownum + ": Please provide required details for Employee ID
:" + dr["EmployeeID"].ToString()
+ " <== ";
}
}
else
{
msg += "==> Erro at row number" + rownum + ": Employee ID is required <== ";
}
}
catch (Exception
exe)
{
msg = "==> Erro at row number"
+ rownum + ": Upload Failed.Please try
again.";
}
}
if (msg == string.Empty)
{
//store in DB
string str = string.Empty;
try
{
gridexcel.DataSource = exli;
gridexcel.DataBind();
}
catch (Exception
exe)
{
msg = exe.Message;
}
if (str == string.Empty)
{
msg = "File Uploaded
Successfully.";
}
else
{
msg = "Upload Failed.Please try
again.";
}
}
else
{
//Show error
msg += "Upload Failed.Please try
again.";
}
lblmsg.Text = msg;
lblmsg.ForeColor = System.Drawing.Color.Red;
lblmsg.Visible = true;
}
static private String GetExcelSheetNames(string
excelFile, string ext)
{
OleDbConnection objConn = null;
System.Data.DataTable dt = null;
try
{
// Connection String. Change the excel file to
the file you
// will search.
objConn = new OleDbConnection(getConnectionString(excelFile,
ext));
// Open
connection with the database.
objConn.Open();
// Get the data table containg the schema
guid.
dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,
null);
if (dt == null)
{
return null;
}
String[] excelSheets = new String[dt.Rows.Count];
int i = 0;
// Add the sheet name to the string array.
foreach (DataRow
row in dt.Rows)
{
excelSheets[i] = row["TABLE_NAME"].ToString();
i++;
}
// Loop through all of the sheets if you want
too...
for (int
j = 0; j < excelSheets.Length; j++)
{
// Query each excel sheet.
}
return excelSheets[0];
}
catch
{
return null;
}
finally
{
// Clean up.
if (objConn != null)
{
objConn.Close();
objConn.Dispose();
}
if (dt != null)
{
dt.Dispose();
}
}
}
static public string getConnectionString(string
excelFile, string ext)
{
string connectionstring = string.Empty;
if (ext == ".xls")
connectionstring = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=" + excelFile + "; Extended
Properties='Excel 8.0;HDR=YES;IMEX=1'";
if (ext == ".xlsx")
connectionstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data
Source=" + excelFile + ";Extended
Properties='Excel 12.0 Xml;HDR=YES;IMEX=1' ";
if (ext == ".xlsb")
connectionstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data
Source=" + excelFile + ";Extended
Properties='Excel 12.0;HDR=YES;IMEX=1'";
if (ext == ".xlsm")
connectionstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data
Source=" + excelFile + ";Extended
Properties='Excel 12.0 Macro;HDR=YES;IMEX=1' ";
return connectionstring;
}
public string
ChangeDateFormat(string odate)
{
IFormatProvider format = new
System.Globalization.CultureInfo("en-GB", true);
DateTime dateUK = DateTime.Parse(odate,
format);
string ndate = dateUK.ToString("dd-MMM-yyyy");//change
to default date format
return ndate;
}
public bool
CheckIsNumber(string num)
{
int outnum;
return int.TryParse(num,
out outnum);
}
public class excellist
{
#region Properties
public string
EMPCOMPANYUSERID
{
get;
set;
}
public string
EMPPASSWORD
{
get;
set;
}
public string EMPNAME
{
get;
set;
}
public string
EMPGENDER
{
get;
set;
}
public string EMPDOB
{
get;
set;
}
public string
EMPMAILID
{
get;
set;
}
public string EMPDOJ
{
get;
set;
}
public string
EMPGROUPID
{
get;
set;
}
#endregion
}