Convert columns to rows while exporting to excel using ASP .NET C#
Below is my out put in gridview and the output after exporting to excel from gridviewName and No Stages Round Score Scored Marks scored% Result
Stage 1 60 14 70% cleared
Stage 2 60 1 6% not cleared
xyz 263 stage 3 65 2 13% not cleared
stage 4 70 0 0% not cleared
stage 5
Required out put in excel after export
name Rollno Stage 1 Stage 2 stage 3 stage 4 stage 5 Result
xyz 263 14 1 2 0 the result should be in percentage which is sum of (stage2+stage3+stage4)/(15+15+10)
because stage2 is for 15 marks,stage 3 is for 15 marks and stage 3 is for 10 marks now the result should be in percentage that is (obtained marks in stage 2 + stage 3 + stage 4)/(15+15+10) and i am getting roll no and student name in same cell but my first cell in excel should contain name, second cell should have roll no,third cell should have stage1,fourth cell should have stage2,fifth cell should have stage3, sixth cell in excel should have stage4,seventh cell in excel should have stage 5 and finally 8th cell result which should be in percentage which is sum of (stage2+stage3+stage4)/(15+15+10)
<pre> protected void OnDataBound(object sender, EventArgs e)
{
for (int i = GridView1.Rows.Count - 1; i > 0; i--)
{
GridViewRow row = GridView1.Rows[i];
GridViewRow previousRow = GridView1.Rows[i - 1];
for (int j = 0; j < GridView1.Columns.Count; j++)
{
if (j == 0)
{
if (!string.IsNullOrEmpty(row.Cells[j].Text.ToUpper().Replace("&NBSP;", "")))
{
if (row.Cells[j].Text == previousRow.Cells[j].Text)
{
if (previousRow.Cells[j].RowSpan == 0)
{
if (row.Cells[j].RowSpan == 0)
{
previousRow.Cells[j].RowSpan += 2;
}
else
{
previousRow.Cells[j].RowSpan = row.Cells[j].RowSpan + 1;
}
row.Cells[j].Visible = false;
}
}
}
}
}
}
}
protected void btnExportExcel_Click(object sender, EventArgs e)
{
Response.Clear();
Response.Buffer = true;
Response.AddHeader("content-disposition", "attachment;filename=OverAllReport" + txtFromDate.Text.Trim().Replace("\\","").Replace("/","").ToString() + ".xls");
Response.Charset = "";
Response.ContentType = "application/vnd.ms-excel";
using (StringWriter sw = new StringWriter())
{
HtmlTextWriter hw = new HtmlTextWriter(sw);
//To Export all pages
GridView1.AllowPaging = false;
// this.BindGrid();
GridView1.HeaderRow.BackColor = System.Drawing.Color.White;
foreach (TableCell cell in GridView1.HeaderRow.Cells)
{
cell.BackColor = GridView1.HeaderStyle.BackColor;
}
foreach (GridViewRow row in GridView1.Rows)
{
row.BackColor = System.Drawing.Color.White;
foreach (TableCell cell in row.Cells)
{
if (row.RowIndex % 2 == 0)
{
cell.BackColor = GridView1.AlternatingRowStyle.BackColor;
}
else
{
cell.BackColor = GridView1.RowStyle.BackColor;
}
cell.CssClass = "textmode";
}
}
GridView1.RenderControl(hw);
//style to format numbers to string
string style = @"<style> .textmode { } </style>";
Response.Write(style);
Response.Output.Write(sw.ToString());
Response.Flush();
Response.End();
}
}