Hi Firuz,
I have created a sample which full fill your requirement you need to modify the code according to your need.
Refer below code but you cant name two column names same so you need to give columns differently.
C#
protected void Page_Load(object sender, EventArgs e)
{
//Bind Your DataBase Table Here.
DataTable dt = new DataTable();
dt.Columns.AddRange(new DataColumn[4] { new DataColumn("Id"), new DataColumn("Person"), new DataColumn("Product"), new DataColumn("Date") });
dt.Rows.Add(1, "Ivan", "oil", "22.07.2017");
dt.Rows.Add(2, "Andrey", "tomato", "22.07.2016");
dt.Rows.Add(3, "Sasha", "melon", "22.07.2017");
dt.Rows.Add(4, "Kolya", "meat", "22.07.2015");
bool nextColumn = true;
DataTable dtExport = new DataTable();
dtExport.Columns.AddRange(new DataColumn[] { new DataColumn("Id"), new DataColumn("Person"), new DataColumn("Product"), new DataColumn("Date"), new DataColumn("FProduct"), new DataColumn("FDate") });
for (int i = 0; i < dt.Rows.Count; i++)
{
if (nextColumn)
{
dtExport.Rows.Add(dt.Rows[i]["Id"].ToString(), dt.Rows[i]["Person"].ToString(), "", "", dt.Rows[i]["Product"].ToString(), dt.Rows[i]["Date"].ToString());
nextColumn = false;
}
else
{
dtExport.Rows.Add(dt.Rows[i]["Id"].ToString(), dt.Rows[i]["Person"].ToString(), dt.Rows[i]["Product"].ToString(), dt.Rows[i]["Date"].ToString(), "", "");
nextColumn = true;
}
}
GridView GridView1 = new GridView();
GridView1.AllowPaging = false;
GridView1.DataSource = dtExport;
GridView1.DataBind();
Response.Clear();
Response.Buffer = true;
Response.AddHeader("content-disposition", "attachment;filename=DataTable.xls");
Response.Charset = "";
Response.ContentType = "application/vnd.ms-excel";
StringWriter sw = new StringWriter();
HtmlTextWriter hw = new HtmlTextWriter(sw);
for (int i = 0; i < GridView1.Rows.Count; i++)
{
GridView1.Rows[i].Attributes.Add("class", "textmode");
}
GridView1.RenderControl(hw);
string style = @"<style> .textmode { mso-number-format:\@; } </style>";
Response.Write(style);
Response.Output.Write(sw.ToString());
Response.Flush();
Response.End();
}
VB.Net
Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
'Bind Your DataBase Table Here.
Dim dt As New DataTable()
dt.Columns.AddRange(New DataColumn(3) {New DataColumn("Id"), New DataColumn("Person"), New DataColumn("Product"), New DataColumn("Date")})
dt.Rows.Add(1, "Ivan", "oil", "22.07.2017")
dt.Rows.Add(2, "Andrey", "tomato", "22.07.2016")
dt.Rows.Add(3, "Sasha", "melon", "22.07.2017")
dt.Rows.Add(4, "Kolya", "meat", "22.07.2015")
Dim nextColumn As Boolean = True
Dim dtExport As New DataTable()
dtExport.Columns.AddRange(New DataColumn() {New DataColumn("Id"), New DataColumn("Person"), New DataColumn("Product"), New DataColumn("Date"), New DataColumn("FProduct"), New DataColumn("FDate")})
For i As Integer = 0 To dt.Rows.Count - 1
If nextColumn Then
dtExport.Rows.Add(dt.Rows(i)("Id").ToString(), dt.Rows(i)("Person").ToString(), "", "", dt.Rows(i)("Product").ToString(), dt.Rows(i)("Date").ToString())
nextColumn = False
Else
dtExport.Rows.Add(dt.Rows(i)("Id").ToString(), dt.Rows(i)("Person").ToString(), dt.Rows(i)("Product").ToString(), dt.Rows(i)("Date").ToString(), "", "")
nextColumn = True
End If
Next
Dim GridView1 As New GridView()
GridView1.AllowPaging = False
GridView1.DataSource = dtExport
GridView1.DataBind()
Response.Clear()
Response.Buffer = True
Response.AddHeader("content-disposition", "attachment;filename=DataTable.xls")
Response.Charset = ""
Response.ContentType = "application/vnd.ms-excel"
Dim sw As New StringWriter()
Dim hw As New HtmlTextWriter(sw)
For i As Integer = 0 To GridView1.Rows.Count - 1
GridView1.Rows(i).Attributes.Add("class", "textmode")
Next
GridView1.RenderControl(hw)
Dim style As String = "<style> .textmode { mso-number-format:\@; } </style>"
Response.Write(style)
Response.Output.Write(sw.ToString())
Response.Flush()
Response.[End]()
End Sub
