Full Code
protected void btnFileUpload_Click(object sender, EventArgs e)
{
if (fuExcel.HasFile)
{
string Filename = Path.GetFileName(fuExcel.PostedFile.FileName);
string extension = Path.GetExtension(fuExcel.PostedFile.FileName);
string Folderpath = ConfigurationManager.AppSettings["FolderPath"];
string filepath = Server.MapPath(Folderpath + Filename);
fuExcel.SaveAs(filepath);
string strConnString = ConfigurationManager.ConnectionStrings["str"].ConnectionString;
using (SqlConnection con = new SqlConnection(strConnString))
{
con.Open();
SqlCommand cmd = new SqlCommand("sp", con);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
SqlDataReader dr = cmd.ExecuteReader();
{
while (dr.Read())
{
ListItem li=new ListItem();
li.Text = dr["COLUMN_NAME"].ToString();
li.Value = dr["Number"].ToString();
//li.Selected = Convert.ToBoolean(dr["IsSelected"]);
checkBox.Items.Add(li);
}
}
}
Import(filepath, extension, rbHDR.SelectedItem.Text);
}
}
//int count;
public void Import(string FilePath, string extension, string IsHDR)
{
string constr = "";
switch (extension)
{
case ".xls":
constr = ConfigurationManager.ConnectionStrings["Excel03ConString"]
.ConnectionString;
break;
case ".xlsx":
constr = ConfigurationManager.ConnectionStrings["Excel07ConString"]
.ConnectionString;
break;
}
constr = string.Format(constr, FilePath, IsHDR);
OleDbConnection connexcel = new OleDbConnection(constr);
OleDbCommand cmdexcel = new OleDbCommand();
OleDbDataAdapter oda = new OleDbDataAdapter();
DataTable dt = new DataTable();
cmdexcel.Connection = connexcel;
connexcel.Open();
DataTable dtexcelsh;
dtexcelsh = connexcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
string sheetname = dtexcelsh.Rows[0]["TABLE_NAME"].ToString();
connexcel.Close();
connexcel.Open();
cmdexcel.CommandText = "select * from [" + sheetname + "]";
oda.SelectCommand = cmdexcel;
oda.Fill(dt);
connexcel.Close();
grdBindExcel.Caption = Path.GetFileName(FilePath);
ViewState["data"] = dt;
BindData();
PopulateColumnName();
//grdBindExcel.DataSource = dt;
//grdBindExcel.DataBind();
int count = dt.Columns.Count;
lblaaa.Text = count.ToString();
lblData.Visible = true;
lblEsc.Visible = true;
string a1 = Convert.ToString(ViewState["newVal"]);
string[] arr = a1.Split(',');
}
private void BindData()
{
DataTable dt = new DataTable();
dt = (DataTable)ViewState["data"];
grdBindExcel.DataSource = dt;
grdBindExcel.DataBind();
}
protected void grdBindExcel_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
string FolderPath = ConfigurationManager.AppSettings["FolderPath"];
string FileName = grdBindExcel.Caption;
string Extension = Path.GetExtension(FileName);
string FilePath = Server.MapPath(FolderPath + FileName);
Import(FilePath, Extension, rbHDR.SelectedItem.Text);
grdBindExcel.PageIndex = e.NewPageIndex;
grdBindExcel.DataBind();
}
protected void btnSave_Click(object sender, EventArgs e)
{
int index = 0;
}
protected void grdBindExcel_RowDataBound(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.Header)
{
for (int i = 0; i < e.Row.Cells.Count; i++)
{
string header = string.Empty;
//int count1 = e.Row.Cells.Count;
header = e.Row.Cells[i].Text;
Label1.Visible = false;
Label1.Text = header.ToString();
ViewState["vallll"] += Label1.Text+ ",";
}
for (int i = 0; i < e.Row.Cells.Count; i++)
{
newitems.Add(new SelectListItem
{
Text = e.Row.Cells[i].Text,
Value = i
});
}
}
string neww = ViewState["vallll"].ToString();
neww = neww.TrimEnd(',');
ViewState["newVal"] = neww.ToString();
}
protected void cckk_SelectedIndexChanged(object sender, EventArgs e)
{
string chkkkk = string.Empty;
string Joinvalue = ViewState["DtValue"].ToString();
string[] arr = Joinvalue.Split(',');
List<string> columname = new List<string>();
//string ex1 = val.Remove(val.Length - 1);
foreach (ListItem li in cckk.Items)
{
if (li.Selected)
{
columname.Add(li.Text);
chkkkk += li.Value + ',';
}
}
string finalval = chkkkk.Remove(chkkkk.Length - 1);
ViewState["val"] = finalval.ToString();
sortingData(columname);
}
private void sortingData(List<string> columname)
{
string newid = ViewState["val"].ToString();
string[] arr1=newid.Split(',');
DataTable dtsorting = new DataTable();
dtsorting = (DataTable)ViewState["data"];
DataTable dummydt = new DataTable();
string id = ViewState["DtValue"].ToString();
string[] arr = id.Split(',');
string rdr = string.Empty;
if (columname.Count > 0)
{
dummydt = dtsorting.DefaultView.ToTable(false, columname.ToArray());
for (int i = 0; i < columname.Count; i++)
{
dummydt.Columns[columname[i]].SetOrdinal(i);
grdviw.DataSource = dummydt;
grdviw.DataBind();
}
}
else
{
grdviw.DataSource = null;
grdviw.DataBind();
}
}
protected void checkBox_SelectedIndexChanged(object sender, EventArgs e)
{
string chkvalue = string.Empty;
foreach (ListItem ti in checkBox.Items)
{
if (ti.Selected)
{
chkvalue += ti.Value + ',';
}
}
string joinvalue = chkvalue.Remove(chkvalue.Length - 1);
ViewState["DtValue"] = joinvalue;
}
private void PopulateColumnName()
{
cckk.DataSource = newitems;
cckk.DataTextField = "Text";
cckk.DataValueField = "Value";
cckk.DataBind();
}
Everything Works Fine ..I want to reorder My Columns
<asp:CheckBoxList ID="checkBox" runat="server" RepeatDirection="Horizontal" AutoPostBack="true"
onselectedindexchanged="checkBox_SelectedIndexChanged"></asp:CheckBoxList>
<b><asp:Label ID="lblEsc" runat="server" Visible="false" Text="Excel Column Name"></asp:Label></b>
<asp:CheckBoxList ID="cckk" runat="server" RepeatDirection="Horizontal"
onselectedindexchanged="cckk_SelectedIndexChanged" AutoPostBack="true"></asp:CheckBoxList>