ramesh g

ramesh g

  • NA
  • 4
  • 6k

How to print year while exporting gridview data to excel?

May 20 2014 6:28 AM
Hi,
In a dropdown required year will be selected for example 2014, I want to print as "FY 14 Revenue" by add new row in grid. I have add new row in grid to add the text I have the following coding,
protected void grvHeading_RowCreated(object sender, GridViewRowEventArgs e)
{
string year = ddlYear.SelectedValue.ToString().Trim();
string yr = year.Substring(year.Length - 2, 2);
string heading = "FY " + yr + " Revenue";
if (e.Row.RowType == DataControlRowType.Header) // If header created
{
GridView ProductGrid = (GridView)sender;

// Creating a Row
GridViewRow HeaderRow = new GridViewRow(0, 0, DataControlRowType.Header, DataControlRowState.Insert);

//Adding Year Column
TableCell HeaderCell = new TableCell();
HeaderCell = new TableCell();
HeaderCell.Text = heading;
HeaderCell.HorizontalAlign = HorizontalAlign.Center;
HeaderCell.Font.Bold = true;
HeaderRow.Cells.Add(HeaderCell);

//Adding the Row at the 0th position (first row) in the Grid
ProductGrid.Controls[0].Controls.AddAt(0, HeaderRow);
}

}
The problem is, in Grid the string "FY 14 Revenue" is displaying correct, but while exporting the grid to excel format the string is displayed as "FY -1 Revenue". Don't know why 14 is displayed as -1 after exporting. Following the coding to export the grid.
public void ExportToExcel()
{
Response.Clear();
Response.Buffer = true;

Response.AddHeader("content-disposition",
"attachment;filename=GridViewExport.xls");
Response.Charset = "";
Response.ContentType = "application/vnd.ms-excel";
StringWriter sw = new StringWriter();
HtmlTextWriter hw = new HtmlTextWriter(sw);

//First Rows Grids
PrepareForExport1(grvHeading);
PrepareForExport(grvPrevMonth);

Unit width = new Unit(1, UnitType.Pixel);
Table tb = new Table();

TableRow tr1 = new TableRow();

TableCell cell1 = new TableCell();
cell1.Width = width;
TableCell cell2 = new TableCell();
cell2.Controls.Add(grvHeading);
TableCell cell3 = new TableCell();
cell3.Width = width;
TableCell cell4 = new TableCell();
cell4.Controls.Add(grvPrevMonth);

tr1.Cells.Add(cell1);
tr1.Cells.Add(cell2);
tr1.Cells.Add(cell3);
tr1.Cells.Add(cell4);


tb.Rows.Add(tr1);


tb.RenderControl(hw);

//style to format numbers to string
string style = @"<style> .textmode { mso-number-format:\@; } </style>";
Response.Write(style);
Response.Output.Write(sw.ToString());
Response.Flush();
Response.End();
}
Output is like tthis:
In Grid View Display
$000's
FY 14 Revenue
ManagedService
StreamLine
Total Payroll
OneTime
MMS
HR
Stats
Total Beyond Payroll
In Excel Sheet:
FY -1 Revenue
$000's
ManagedService
StreamLine
Total Payroll
OneTime
MMS
HR
Stats
Total Beyond Payroll
anyone has idea why the year changes like this during exporting to excel;